留存信息统计
需求:
看某一段时间内,注册的用户7日内留存,比如输入一个时间区间,显示这7日内注册用户总数,然后从第1天到第7天每一天的留存数据。
## ================================================== ## 让读书成为一种生活方式。就像吃喝拉撒每天必须要干的事, ## 终有一天你的举止、言谈、气质会不一样。 ## —- 5sdba ## ## Created Date: Tuesday, 2021-03-03, 10:54:59 am ## copyright (c): SZWW Tech. LTD. ## Engineer: async ## Module Name: ## Revision: v0.01 ## Description: ## ## Revision History : ## Revision editor date Description ## v0.01 async 2021-03-02 File Created ## ================================================== -- sum_cnt 当日查寻当月注册人数 -- d0_14 最近14天总注册人数 -- d0 当天注册人数 -- d1 第一天注册人数 -- d2 第二天注册人数 SELECT register_date,count(1) as sum_cnt,sum(case when day_diff between 0 and 14 then 1 else 0 end) as d0_d14, sum(case when day_diff = 0 then 1 else 0 end) day_0, sum(case when day_diff = 1 then 1 else 0 end) day_1, sum(case when day_diff = 2 then 1 else 0 end) day_2, sum(case when day_diff = 3 then 1 else 0 end) day_3, sum(case when day_diff = 4 then 1 else 0 end) day_4, sum(case when day_diff = 5 then 1 else 0 end) day_5, sum(case when day_diff = 6 then 1 else 0 end) day_6, sum(case when day_diff = 7 then 1 else 0 end) day_7, sum(case when day_diff = 8 then 1 else 0 end) day_8, sum(case when day_diff = 9 then 1 else 0 end) day_9, sum(case when day_diff = 10 then 1 else 0 end) day_10, sum(case when day_diff = 11 then 1 else 0 end) day_11, sum(case when day_diff = 12 then 1 else 0 end) day_12, sum(case when day_diff = 13 then 1 else 0 end) day_13, sum(case when day_diff = 14 then 1 else 0 end) day_14 from (select d.userid, date_format(ma.create_time,'%Y-%m-%d') as register_date, DATEDIFF(from_unixtime(d.CreateTime,'%Y-%m-%d'),date_format(ma.create_time,'%Y-%m-%d')) as day_diff from xxx.xxx ma join xxx.xx o on ma.id=o.user_id left join xx.xx d on ma.id=d.UserId where extract(year_month from ma.create_time)={{month}} and ma.account_role<50 [[ and ma.service_area={{area}}]] group by 1,2,3 ) xa group by 1 order by 1
业余经济爱好者