sql server周末函数 sql取周末

首先注意一点在dwt层数据已经都经过了聚合，在ads层所有的id都是唯一存在的不需要再group by

dws要根据表来具体分析，如果是日活，不同分区很有可能有重复id

活跃设备数（日、周、月）

table ads_uv_count(
`dt` string COMMENT '统计日期',
`day_count` bigint COMMENT '当日用户数量',
`wk_count` bigint COMMENT '当周用户数量',
`mn_count` bigint COMMENT '当月用户数量',
`is_weekend` string COMMENT 'Y,N 是否是周末,用于得到本周最终结果',
`is_monthend` string COMMENT 'Y,N 是否是月末,用于得到本月最终结果'
) COMMENT '活跃设备数'

导入SQL

因为报表是每天产生的，假设今天是2020-04-28，所以dt='2020-04-28'

是否是星期天的判断if(date_add(next_day('2020-04-28','MO'),-1)='2020-04-28','Y','N')

即4-28的下一个周一再减一天是否是4-28，是即为周天

是否是月末判断if(last_day('2020-04-28')='if(last_day('2020-04-28')='2020-03-10','Y','N')','Y','N')

即当前日期当月的最后一天是否是今天，是即为月末

查每天即最后登录=今天

查每周即最后登录=<本周末 >=本周一

查每月即最后登录月=本月

insert into table ads_uv_count
select
'2020-04-28' dt,
daycount.ct,
wkcount.ct,
mncount.ct,
if(date_add(next_day('2020-04-28','MO'),-1)='2020-04-28','Y','N') ,
if(last_day('2020-04-28')='2020-04-28','Y','N')
from
(
    select
    '2020-04-28' dt,
    count(*) ct
    from dwt_uv_topic
    where login_date_last='2020-04-28'
)daycount join
(
    select
    '2020-04-28' dt,
    count (*) ct
    from dwt_uv_topic
    where login_date_last>=date_add(next_day('2020-04-28','MO'),-7)
    and login_date_last<= date_add(next_day('2020-04-28','MO'),-1)
) wkcount on daycount.dt=wkcount.dt
join
(
    select
    '2020-04-28' dt,
    count (*) ct
    from dwt_uv_topic
    where
    date_format(login_date_last,'yyyy-MM')=date_format('2020-04-28','yyyy-MM')
)mncount on daycount.dt=mncount.dt;

新增设备数

table ads_new_mid_count
(
`create_date` string comment '创建时间' ,
`new_mid_count` BIGINT comment '新增设备数量'
) COMMENT '每日新增设备信息数量'

导入SQL

很简单就是查今天'2020-04-28'是首次登录

insert into table ads_new_mid_count
select
'2020-04-28',
count(*)
from dwt_uv_topic
where login_date_first='2020-04-28'

沉默用户数

沉默用户：只在安装当天启动过，且启动时间是在7 天前

table ads_silent_count(
`dt` string COMMENT '统计日期',
`silent_count` bigint COMMENT '沉默设备数'
)

导入SQL

即首次登录即最后登录，且最后一次登录是7天前

insert into table ads_silent_count
select
'2020-04-28',
count(*)
from dwt_uv_topic
where login_date_first=login_date_last
and login_date_last<=date_add('2020-04-28',-7);

本周回流用户数

本周回流用户：上周未活跃，本周活跃的设备，且不是本周新增设备

table ads_back_count(
`dt` string COMMENT '统计日期',
`wk_dt` string COMMENT '统计日期所在周',
`wastage_count` bigint COMMENT '回流设备数'
)

导入SQL

分成两部分

第一部分查询dwt_uv_topic，在本周活跃且不是本周的新增设备id

第二部分查询dws_uv_detail_daycount，查出上周所有的设备id

本周活跃id left join 上周所有活跃id，右表为空的值即代表上周不活跃但是本周活跃

insert into table ads_back_count
select
'2020-04-28',
count(*)
from
(
    select
        mid_id
    from 
        dwt_uv_topic
    where login_date_last>=date_add(next_day('2020-04-28','MO'),-7)
    and login_date_last<= date_add(next_day('2020-04-28','MO'),-1)
    and login_date_first<date_add(next_day('2020-04-28','MO'),-7)
)current_wk
left join
(
    select
        mid_id
    from 
        dws_uv_detail_daycount
    where dt>=date_add(next_day('2020-04-28','MO'),-7*2)
    and dt<= date_add(next_day('2020-04-28','MO'),-7-1)
    group by mid_id
)last_wk
on current_wk.mid_id=last_wk.mid_id
where last_wk.mid_id is null;

流失用户数

流失用户：最近7 天未活跃的设备

table ads_wastage_count(
`dt` string COMMENT '统计日期',
`wastage_count` bigint COMMENT '流失设备数'
)

导入SQL

最后一次登录在七天前的用户

select
'2020-04-28',
count(*)
from dwt_uv_topic
where login_date_last<=date_add('2020-04-28',-7);

留存率

留存用户：某段时间内的新增用户（活跃用户），经过一段时间后，又继续使用应用的被认为是留存用户。

留存率：留存用户占当时新增用户（活跃用户）的比例即为留存率

table ads_user_retention_day_rate
(
`stat_date` string comment '统计日期',
`create_date` string comment '设备新增日期',
`retention_day` int comment '截止当前日期留存天数',
`retention_count` bigint comment '留存数量',
`new_mid_count` bigint comment '设备新增数量',
`retention_ratio` decimal(10,2) comment '留存率'
) COMMENT '每日用户留存情况'

统计近三天留存率，导入sql

首先明确相对日期2020-04-28

其次明确统计日期2020-04-27

求04-27的一天留存数，即4-27是首次登录，且4-28是末次登录

求4-27的新增数，即4-27是首次登录

前二者相除得最后结果

以此类推

。。。。。。

insert into table ads_user_retention_day_rate
select
    '2020-04-28',--统计日期
    date_add('2020-04-28',-1),--新增日期'2020-04-27'
    1,--留存天数
    sum(if(login_date_first=date_add('2020-04-28',-1) 
        and login_date_last='2020-04-28',1,0)),--2020-04-27 的1 日留存数
    sum(if(login_date_first=date_add('2020-04-28',-1),1,0)),--2020-04-27 新增
    sum(if(login_date_first=date_add('2020-04-28',-1) 
        and login_date_last='2020-04-28',1,0))/sum(if(login_date_first=date_add('2020-04-28',-
        1),1,0))*100-- 2020-04-27 相对 2020-04-28 1天留存率
from dwt_uv_topic
union all
select
    '2020-04-28',--统计日期
    date_add('2020-04-28',-2),--新增日期
    2,--留存天数
    sum(if(login_date_first=date_add('2020-04-28',-2) and
        login_date_last='2020-04-28',1,0)),--2020-04-26 的2 日留存数
    sum(if(login_date_first=date_add('2020-04-28',-2),1,0)),--2020-04-26 新增
    sum(if(login_date_first=date_add('2020-04-28',-2) and
        login_date_last='2020-04-28',1,0))/sum(if(login_date_first=date_add('2020-04-28',-
        2),1,0))*100-- 2020-04-26 相对 2020-04-28 2天留存率
from dwt_uv_topic
union all
select
    '2020-04-28',--统计日期
    date_add('2020-04-28',-3),--新增日期
    3,--留存天数
    sum(if(login_date_first=date_add('2020-04-28',-3) and
        login_date_last='2020-04-28',1,0)),--2020-04-25 的3 日留存数
    sum(if(login_date_first=date_add('2020-04-28',-3),1,0)),--2020-04-25 新增
    sum(if(login_date_first=date_add('2020-04-28',-3) and
        login_date_last='2020-04-28',1,0))/sum(if(login_date_first=date_add('2020-04-28',-
        3),1,0))*100-- 2020-04-25 相对 2020-04-28 2天留存率
from dwt_uv_topic;

最近连续三周活跃用户数

table ads_continuity_wk_count(
`dt` string COMMENT '统计日期,一般用结束周周日日期,如果每天计算一次,可用当天日
期',
`wk_dt` string COMMENT '持续时间',
`continuity_count` bigint COMMENT '活跃次数'
)

导入sql

要求连续三周活跃，则每周都要有活跃，这需要一个设备的历史数据

历史数据dwt层不存在，只有dws层有

要连续三周活跃，则分解成近三周每周都活跃

先去重得到最近三周的活跃设备（记得求每周都要group by，因为dws_uvdetail_daycount表的细粒度只到天级，本周很可能有重复mid_id）

union all 三张表得到三周活跃设备信息（未去重，这时候的细粒度来到了周级，而三周的规模表很可能存在重复）

去重并加上条件having count(*)=3得到三周都存在的mid_id

对结果进行count(*),得到最后结果

insert into table ads_continuity_wk_count
select
    '2020-04-29',
    concat(date_add(next_day('2020-04-29','MO'),-7*3),'_',date_add(next_day('
    2020-04-29','MO'),-1)),
    count(*)
from
(
    select
        mid_id
    from
    (
        select
            mid_id
        from dws_uv_detail_daycount
        where dt>=date_add(next_day('2020-04-29','monday'),-7)
        and dt<=date_add(next_day('2020-04-29','monday'),-1)
        group by mid_id
        
        union all
        select
            mid_id
        from dws_uv_detail_daycount
        where dt>=date_add(next_day('2020-04-29','monday'),-7*2)
        and dt<=date_add(next_day('2020-04-29','monday'),-7-1)
        group by mid_id
        
        union all
        select
            mid_id
        from dws_uv_detail_daycount
        where dt>=date_add(next_day('2020-04-29','monday'),-7*3)
        and dt<=date_add(next_day('2020-04-29','monday'),-7*2-1)
        group by mid_id
    )t1
    group by mid_id
    having count(*)=3
)t2

最近七天内连续三天活跃用户数

table ads_continuity_uv_count(
`dt` string COMMENT '统计日期',
`wk_dt` string COMMENT '最近7 天日期',
`continuity_count` bigint
) COMMENT '连续活跃设备数'

导入sql

这是一个多层嵌套sql，我们一层一层往外解析

第一层通过dws_uv_detail_daycount求出最近7天所有活跃用户

通过开窗函数根据mid_id分区做rank（这里不会出现rank重复，因为dws已在天级细粒度实现去重），时间升序排序

第二层对dt和rk做一个差值，这样连续的天数相减会得到一个定值

第三层对设备id mid_id 和 差值 diff做一个分组，且保留同组计数大于3的分组（这时的细粒度是由mid_id和diff共同构成）

第四层我们最后需要得到的是mid_id,所以对mid_id再做一次去重，去掉midid相同但是时间不同的分组，得到唯一的mid_id

因为比较复杂这里给出图解

insert into table ads_continuity_uv_count
select
    '2020-04-29',
    concat(date_add('2020-04-29',-6),'_','2020-04-29'),
    count(*)
from
    (
    select 
        mid_id
    from
        (
        select 
            mid_id
        from
            (
            select
                mid_id,
                date_sub(dt,rank) date_dif
            from
                (
                select
                    mid_id,
                    dt,
                    rank() over(partition by mid_id order by dt) rank
                from 
                    dws_uv_detail_daycount
                where dt>=date_add('2020-04-29',-6) and
                    dt<='2020-04-29'
                )t1
            )t2
        group by 
            mid_id,date_dif
        having count(*)>=3
        )t3
    group by 
        mid_id
    )t4;