需求1 : 求支付宝-蚂蚁森林 找出连续3天及以上减少碳排放量在100以上的用户
-- 数据准备
-- DDL
create table test1 (
`id` string comment '用户id',
`occur_date` string comment '发生日期',
`lowcarbon` string comment '减少碳排放量')
comment '减少碳排放量转换表'
row format delimited fields terminated by '\t'
lines terminated by '\n' stored as orc;
-- 数据说明
-- 支付宝-蚂蚁森林 记录运动量转换成 减少碳排放量
-- 每点击一次 会收获一次 减少碳排放量,并生成一条记录
-- 每日每人可多次点击,生成多条记录
-- DML
insert overwrite table test1
select '1001' as id,'2021-12-12' as occur_date,'123' as lowcarbon union all
select '1001' as id,'2021-12-20' as occur_date,'123' as lowcarbon union all
select '1002' as id,'2021-12-12' as occur_date,'45' as lowcarbon union all
select '1001' as id,'2021-12-13' as occur_date,'43' as lowcarbon union all
select '1001' as id,'2021-12-13' as occur_date,'45' as lowcarbon union all
select '1001' as id,'2021-12-13' as occur_date,'23' as lowcarbon union all
select '1002' as id,'2021-12-14' as occur_date,'45' as lowcarbon union all
select '1001' as id,'2021-12-14' as occur_date,'230' as lowcarbon union all
select '1002' as id,'2021-12-15' as occur_date,'45' as lowcarbon union all
select '1001' as id,'2021-12-15' as occur_date,'123' as lowcarbon union all
select '1003' as id,'2021-12-15' as occur_date,'123' as lowcarbon union all
select '1003' as id,'2021-12-16' as occur_date,'125' as lowcarbon union all
select '1003' as id,'2021-12-17' as occur_date,'126' as lowcarbon union all
select '1003' as id,'2021-12-18' as occur_date,'127' as lowcarbon union all
select '1003' as id,'2021-12-29' as occur_date,'127' as lowcarbon union all
select '1003' as id,'2021-12-19' as occur_date,'128' as lowcarbon ;
思路1 :
-- 思路1 :
-- 需求所需函数
-- 1. 窗口函数 : https://www.cnblogs.com/bajiaotai/p/15866840.html
-- 分析思路
-- step1. 按id、occur_date 汇总减少碳排放量,并剔除100以下的
select
id
,occur_date
,sum(lowcarbon)
from test1
group by
id
,occur_date
having sum(lowcarbon) > 100
;
id occur_date _c2
1001 2021-12-12 123.0
1001 2021-12-13 111.0
1001 2021-12-14 230.0
1001 2021-12-15 123.0
1001 2021-12-20 123.0
1003 2021-12-15 123.0
1003 2021-12-16 125.0
1003 2021-12-17 126.0
1003 2021-12-18 127.0
1003 2021-12-19 128.0
1003 2021-12-29 127.0
-- step2. 为每条数据打标签 : 当前行的occur_date - 前1行occur_date 的差值
-- 怎样获取当前行的前一行的occur_date
-- 方式1 : min(occur_date) over(partition by order by 前一行到当前行)
-- 方式2 : lag(occur_date,1,occur_date) over(partition by order by)
-- 方式1
with t1 as (
select id
, occur_date
, sum(lowcarbon) as lowcarbon
from test1
group by id
, occur_date
having sum(lowcarbon) > 100
)
select
id
,occur_date
,lowcarbon
,min(occur_date)
over (partition by id
order by occur_date asc
rows between 1 preceding and current row
) as pre_occur_date -- 获取当前行的前一行的 occur_date
,datediff(occur_date
, min(occur_date)
over (partition by id
order by occur_date asc
rows between 1 preceding and current row
)
) as day_diff -- 当前行 与前一行 occur_date的差值
from t1;
-- 方式2
with t1 as (
select id
, occur_date
, sum(lowcarbon) as lowcarbon
from test1
group by id
, occur_date
having sum(lowcarbon) > 100
)
select
id
,occur_date
,lowcarbon
,lag(occur_date,1,occur_date)
over (partition by id
order by occur_date asc
) as pre_occur_date -- 获取当前行的前一行的 occur_date
,datediff(occur_date
,lag(occur_date,1,occur_date)
over (partition by id
order by occur_date asc
)
) as day_diff -- 当前行 与前一行 occur_date的差值
from t1;
-- 查询结果
id occur_date lowcarbon pre_occur_date day_diff
1001 2021-12-12 123.0 2021-12-12 0
1001 2021-12-13 111.0 2021-12-12 1
1001 2021-12-14 230.0 2021-12-13 1
1001 2021-12-15 123.0 2021-12-14 1
1001 2021-12-20 123.0 2021-12-15 5
1003 2021-12-15 123.0 2021-12-15 0
1003 2021-12-16 125.0 2021-12-15 1
1003 2021-12-17 126.0 2021-12-16 1
1003 2021-12-18 127.0 2021-12-17 1
1003 2021-12-19 128.0 2021-12-18 1
1003 2021-12-29 127.0 2021-12-19 10
-- 最终结果
-- step3. 根据日期差,求连续登入天数 并提出不连续的记录
-- 操作sql
with t1 as (
select id
, occur_date
, sum(lowcarbon) as lowcarbon
from test1
group by id
, occur_date
having sum(lowcarbon) > 100
),
t2 as (
select
id
,occur_date
,lowcarbon
,row_number() over (partition by id order by occur_date asc) as rank
,min(occur_date)
over (partition by id
order by occur_date asc
rows between 1 preceding and current row
) as pre_occur_date -- 获取当前行的前一行的 occur_date
,datediff(occur_date
, min(occur_date)
over (partition by id
order by occur_date asc
rows between 1 preceding and current row
)
) as day_diff -- 当前行 与前一行 occur_date的差值
from t1)
select
id
,lowcarbon
,occur_date
,pre_occur_date
,day_diff
,sum(day_diff) over(partition by id order by occur_date asc) + 1 as cont_days
from t2
where day_diff in (0,1)
;
-- 查询结果
id lowcarbon occur_date pre_occur_date day_diff cont_days
1001 123.0 2021-12-12 2021-12-12 0 1
1001 111.0 2021-12-13 2021-12-12 1 2
1001 230.0 2021-12-14 2021-12-13 1 3
1001 123.0 2021-12-15 2021-12-14 1 4
1003 123.0 2021-12-15 2021-12-15 0 1
1003 125.0 2021-12-16 2021-12-15 1 2
1003 126.0 2021-12-17 2021-12-16 1 3
1003 127.0 2021-12-18 2021-12-17 1 4
1003 128.0 2021-12-19 2021-12-18 1 5
思路2 :
-- 思路2 :
-- 需求所需函数
-- 1. 窗口函数 : https://www.cnblogs.com/bajiaotai/p/15866840.html
-- 2.
-- 分析思路
-- step1. 按id、occur_date 汇总减少碳排放量,并剔除100以下的
select
id
,occur_date
,sum(lowcarbon)
from test1
group by
id
,occur_date
having sum(lowcarbon) > 100
;
id occur_date _c2
1001 2021-12-12 123.0
1001 2021-12-13 111.0
1001 2021-12-14 230.0
1001 2021-12-15 123.0
1001 2021-12-20 123.0
1003 2021-12-15 123.0
1003 2021-12-16 125.0
1003 2021-12-17 126.0
1003 2021-12-18 127.0
1003 2021-12-19 128.0
1003 2021-12-29 127.0
-- step2. 为每条数据打标签 : 当前行的occur_date - 当前序号
-- 执行sql
with t1 as (
select id
, occur_date
, sum(lowcarbon) as lowcarbon
from test1
group by id
, occur_date
having sum(lowcarbon) > 100
)
select
id
,occur_date
,lowcarbon
,row_number()
over (partition by id
order by occur_date asc
) as rank
,date_sub(occur_date,row_number()
over (partition by id
order by occur_date asc
) ) as diff
from t1;
-- 查询结果
id occur_date lowcarbon rank diff
1001 2021-12-12 123.0 1 2021-12-11
1001 2021-12-13 111.0 2 2021-12-11
1001 2021-12-14 230.0 3 2021-12-11
1001 2021-12-15 123.0 4 2021-12-11
1001 2021-12-20 123.0 5 2021-12-15
1003 2021-12-15 123.0 1 2021-12-14
1003 2021-12-16 125.0 2 2021-12-14
1003 2021-12-17 126.0 3 2021-12-14
1003 2021-12-18 127.0 4 2021-12-14
1003 2021-12-19 128.0 5 2021-12-14
1003 2021-12-29 127.0 6 2021-12-23
-- 汇总结果
with t1 as (
select id
, occur_date
, sum(lowcarbon) as lowcarbon
from test1
group by id
, occur_date
having sum(lowcarbon) > 100
),
t2 as (
select
id
,occur_date
,lowcarbon
,row_number()
over (partition by id
order by occur_date asc
) as rank
,date_sub(occur_date,row_number()
over (partition by id
order by occur_date asc
) ) as diff
from t1)
select
id
,diff
,count(occur_date) as cnt
from t2
group by id
,diff
having count(occur_date) >= 3
;
-- 查询结果
id diff cnt
1001 2021-12-11 4
1003 2021-12-14 5
需求2 : 连续x天登录的用户
-- 数据准备
-- DDL
create table test2 (
`user_id` string comment '用户id',
`login_date` string comment '登入日期',
`login_status` string comment '登入状态(1:成功,2:异常)')
comment '用户登入记录表'
row format delimited fields terminated by '\t'
lines terminated by '\n' stored as orc;
-- DML
insert overwrite table test2
select '1001' as id,'2021-12-12' as occur_date,'1' as lowcarbon union all
select '1001' as id,'2021-12-20' as occur_date,'1' as lowcarbon union all
select '1001' as id,'2021-12-20' as occur_date,'0' as lowcarbon union all
select '1002' as id,'2021-12-12' as occur_date,'1' as lowcarbon union all
select '1001' as id,'2021-12-13' as occur_date,'1' as lowcarbon union all
select '1001' as id,'2021-12-13' as occur_date,'1' as lowcarbon union all
select '1001' as id,'2021-12-13' as occur_date,'1' as lowcarbon union all
select '1002' as id,'2021-12-14' as occur_date,'0' as lowcarbon union all
select '1001' as id,'2021-12-14' as occur_date,'1' as lowcarbon union all
select '1002' as id,'2021-12-15' as occur_date,'1' as lowcarbon union all
select '1001' as id,'2021-12-15' as occur_date,'1' as lowcarbon union all
select '1003' as id,'2021-12-15' as occur_date,'0' as lowcarbon union all
select '1003' as id,'2021-12-16' as occur_date,'1' as lowcarbon union all
select '1003' as id,'2021-12-17' as occur_date,'1' as lowcarbon union all
select '1003' as id,'2021-12-18' as occur_date,'1' as lowcarbon union all
select '1003' as id,'2021-12-29' as occur_date,'1' as lowcarbon union all
select '1001' as id,'2022-01-01' as occur_date,'1' as lowcarbon union all
select '1001' as id,'2022-01-01' as occur_date,'1' as lowcarbon union all
select '1001' as id,'2022-01-01' as occur_date,'1' as lowcarbon union all
select '1001' as id,'2022-01-02' as occur_date,'1' as lowcarbon union all
select '1001' as id,'2022-01-03' as occur_date,'1' as lowcarbon union all
select '1001' as id,'2022-01-04' as occur_date,'1' as lowcarbon union all
select '1001' as id,'2022-01-05' as occur_date,'1' as lowcarbon union all
select '1001' as id,'2022-01-06' as occur_date,'1' as lowcarbon union all
select '1003' as id,'2021-12-19' as occur_date,'1' as lowcarbon ;
-- 数据说明
-- 每天每天可能有多次登入记录
思路1 :
-- 连续登入的特点 : login_date - rank = 同一日期
with t1 as (
select
user_id
,login_date
from test2
where login_status = '1'
group by user_id
,login_date
)
select
user_id
,diff_days
,count(distinct login_date) as days -- 连续登入天数
from (
select user_id
, login_date
, row_number() over (partition by user_id order by login_date asc) as rank
, date_sub(login_date, row_number() over (partition by user_id order by login_date asc)) as diff_days
from t1
) as t2
group by
user_id
,diff_days
having count(distinct login_date) >=4
;
-- 分析过程1
user_id login_date rank diff_days
1001 2021-12-12 1 2021-12-11
1001 2021-12-13 2 2021-12-11
1001 2021-12-14 3 2021-12-11
1001 2021-12-15 4 2021-12-11
1001 2021-12-20 5 2021-12-15
1002 2021-12-12 1 2021-12-11
1002 2021-12-15 2 2021-12-13
1003 2021-12-16 1 2021-12-15
1003 2021-12-17 2 2021-12-15
1003 2021-12-18 3 2021-12-15
1003 2021-12-19 4 2021-12-15
1003 2021-12-29 5 2021-12-24
-- 最终结果
user_id diff_days days
1001 2021-12-11 4
1001 2021-12-26 6
1003 2021-12-15 4