package mobvista.dmp.datasource.backflow

/**
 * @package: mobvista.dmp.datasource.backflow
 * @author: wangjf
 * @date: 2021/6/1
 * @time: 5:47 下午
 * @email: jinfeng.wang@mobvista.com
 */
object BackFlowConstant {

  val mapping_sql =
    """
      |SELECT region, devid_key, devid_type, devid_value
      | FROM
      | (SELECT region, devid_key, devid_type, devid_value1 AS devid_value
      |   FROM
      |   (SELECT t1.region as region, devid_key, devid_type, t1.devid_value AS devid_value1, t2.devid_value AS devid_value2
      |     FROM
      |     (SELECT region, devid_key, devid_type, devid_value
      |       FROM default.mapping_dump_table_shulun
      |       WHERE concat(year,month,day) = '@dt'
      |     ) t1
      |     LEFT OUTER JOIN
      |     (SELECT region, devid_value
      |       FROM
      |       (SELECT region, devid_value, count(1) as num
      |         FROM default.mapping_dump_table_shulun
      |         WHERE concat(year,month,day) = '@dt'
      |         GROUP BY region, devid_value
      |       ) tmp
      |       WHERE num > 100
      |     ) t2
      |     ON t1.region = t2.region AND t1.devid_value = t2.devid_value
      |   ) t3
      | WHERE devid_value2 IS NOT NULL
      | UNION ALL
      | SELECT region, devid_key, devid_type, devid_value
      |  FROM default.mapping_dump_table_shulun
      |  WHERE concat(year,month,day) = '@dt' AND (LENGTH(devid_value) < 16 OR LENGTH(devid_key) < 15)
      | ) t
      |""".stripMargin
}