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 }