20201124格式转换为2020-11-24
程序员文章站
2022-07-12 16:32:23
...
ds=20201124,如何转换为2020-11-24格式呢,
concat(substr(ds,1,4),'-',substr(ds,5,2),'-',substr(7,2)),这种报错
以下为正确写法
from_unixtime(unix_timestamp(ds,'yyyyMMdd'),'yyyy-MM-dd')
以下取30天流失率的分母
SELECT a1.ds
,COUNT(DISTINCT a1.passenger_id) AS fenzi
,COUNT(DISTINCT a2.passenger_uuid) AS fenmu
FROM pub_cockpit_db.dws_user_upgn_passenger_dt a1
LEFT JOIN
(SELECT ds
,passenger_uuid
,last_order_time
FROM pub_cockpit_db.dws_user_finish_ord_3m_dt
WHERE ds >= '20201024' ----该表从1024起连续日期有数据
AND source = 't3app'
AND last_order_time >= date_sub(from_unixtime(unix_timestamp(ds,'yyyyMMdd'),'yyyy-MM-dd'),60)
AND last_order_time <= date_sub(from_unixtime(unix_timestamp(ds,'yyyyMMdd'),'yyyy-MM-dd'),30)
) a2
ON a1.ds = a2.ds
WHERE a1.user_pool_code = 'CMYHC' ----沉默用户池
GROUP BY a1.ds
ORDER BY a1.ds
上一篇: TypeScript笔记二:基础类型
下一篇: Webassebmly测试ITK是否可用