欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

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