sql 常用函数方法
DATEDIFF 返回两个日期之间相差几天 CONCAT 字符串拼接 COALESCE 将空值替换成其他值 返回第一个非空值 TO_DATE 时间格式转化 DATEADD 时间加几天 SUBSTR 字符串做分割
SELECT device_id, coalesce(DATEDIFF(CONCAT(pt, " 00:00:00"), CONCAT('{partition}', ' 00:00:00'), 'dd'), -1) as age FROM basic_data_center.extract_ttgame_extra_app_source__userdaystat WHERE pt in ( SUBSTR(DATEADD(TO_DATE('{partition}', 'yyyy-mm-dd'), 1, 'dd'), 1, 10), SUBSTR(DATEADD(TO_DATE('{partition}', 'yyyy-mm-dd'), 2, 'dd'), 1, 10), SUBSTR(DATEADD(TO_DATE('{partition}', 'yyyy-mm-dd'), 3, 'dd'), 1, 10), SUBSTR(DATEADD(TO_DATE('{partition}', 'yyyy-mm-dd'), 7, 'dd'), 1, 10), SUBSTR(DATEADD(TO_DATE('{partition}', 'yyyy-mm-dd'), 14, 'dd'), 1, 10), SUBSTR(DATEADD(TO_DATE('{partition}', 'yyyy-mm-dd'), 30, 'dd'), 1, 10) ) and day_age != 0) as b on a.device_id = b.device_id