20201124格式转换为2020-11-24

最新推荐文章于 2020-12-01 11:02:03 发布

原创最新推荐文章于 2020-12-01 11:02:03 发布 · 476 阅读

1 ·

CC 4.0 BY-SA版权

博客提及了30天流失率分母的选取相关内容，但未给出更多详细信息。

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