一、TopN问题
需要确定使用什么排名函数，包含三种函数：row_number()、rank()、dense_rank()
每个班级的分数为前3名的学生
1 --建表语句
2 create table score(sid string, class string, score int)
3 row format delimited fields terminated by ' ';
4 load data local inpath '/temp/sql.txt' into table score;
5
6 --数据：学生id、班级、分数
7 s1 A 89
8 s2 C 88
9 s3 A 92
10 s4 A 89
11 s5 B 90
12 s6 B 86
13 s7 C 92
14 s8 C 90
15 s9 A 85
16 s10 C 86
17 s11 B 86
18 s12 B 86
1 --sql语句
2 select * from (
3 select class, sid, score, dense_rank() over(partition by class order by score desc) as rank
4 from score
5 ) t1
6 where t1.rank < 4;
7
8 --结果
9 t1.class t1.sid t1.score t1.rank
10 A s3 92 1
11 A s4 89 2
12 A s1 89 2
13 A s9 85 3
14 B s5 90 1
15 B s11 86 2
16 B s12 86 2
17 B s6 86 2
18 C s7 92 1
19 C s8 90 2
20 C s2 88 3
二、行列转换问题
（1）一行转换为多行 -- lateral view explode
将每个电影的分类列表拆分出单个分类
1 --建表语句
2 create table movie(movie_name string, category string)
3 row format delimited fields terminated by ' ';
4 load data local inpath '/temp/sql.txt' into table movie;
5
6 --数据：电影名、分类
7 让子弹飞 动作、年代
8 长江七号 科幻
9 大进军 战争
1 --sql语句
2 select movie_name, category_name
3 from movie
4 lateral view explode(split(category, '、')) t1 as category_name;
5
6 --结果
7 movie_name category_name
8 让子弹飞 动作
9 让子弹飞 年代
10 长江七号 科幻
11 大进军 战争
（2）多行转换为一行
将年龄相同的姓名合并在一起
1 --建表语句
2 create table person(name string, age int)
3 row format delimited fields terminated by ' ';
4 load data local inpath '/temp/sql.txt' into table person;
5
6 --数据：姓名、年龄
7 A 20
8 B 18
9 C 20
10 D 24
1 --sql语句
2 select concat_ws('|', collect_set(name)) as name_list, age
3 from person
4 group by age;
5
6 --结果
7 name_list age
8 B 18
9 A|C 20
10 D 24
将可枚举的值作为新的字段
1 --建表语句
2 create table game(year int, class string, score int)
3 row format delimited fields terminated by ' ';
4 load data local inpath '/temp/sql.txt' into table game;
5
6 --数据：年份、班级、得分
7 2020 ClassA 10
8 2020 ClassB 12
9 2020 ClassC 9
10 2021 ClassA 12
11 2021 ClassB 8
1 --sql语句
2 select year,
3 max(case when class = 'ClassA' then score end) as A,
4 max(case when class = 'ClassB' then score end) as B,
5 max(case when class = 'ClassC' then score end) as C
6 from game
7 group by year;
8
9 --结果
10 year a b c
11 2020 10 12 9
12 2021 12 8 NULL
连续性问题（重点）
连续3天登录的用户
1 --建表语句
2 create table login(uid int, dt date, status int)
3 row format delimited fields terminated by ' ';
4 load data local inpath '/temp/sql.txt' into table login;
5
6 --数据：用户id、日期、是否登录
7 1 2022-03-01 0
8 1 2022-03-02 1
9 1 2022-03-03 1
10 1 2022-03-04 1
11 1 2022-03-05 1
12 1 2022-03-06 0
13 1 2022-03-07 1
14 2 2022-03-01 0
15 2 2022-03-02 1
16 2 2022-03-03 1
17 2 2022-03-04 0
18 2 2022-03-05 0
19 2 2022-03-06 1
20 2 2022-03-07 1
21 3 2022-03-01 1
22 3 2022-03-02 1
23 3 2022-03-03 1
24 3 2022-03-04 0
25 3 2022-03-05 1
26 3 2022-03-06 1
27 3 2022-03-07 1
1 --sql语句
2 select *
3 from (
4 --查询用户在起始日期及其之后的连续登录次数
5 select t1.uid, min(t1.dt) as start_day, count(*) as day_count
6 from (
7 --按照用户分组，按照日期递增排序，计算连续登录操作的锚定日期
8 select uid, dt, date_sub(dt, row_number() over(partition by uid order by dt asc)) as diff
9 from login
10 where status = 1 --筛选登录的日期
11 ) t1
12 group by t1.uid, t1.diff --按照用户、锚定日期分组
13 ) t2
14 where t2.day_count > 2; --筛选出至少3次的连续登录操作
15
16 --结果
17 t2.uid t2.start_day t2.day_count
18 1 2022-03-02 4
19 3 2022-03-01 3
20 3 2022-03-05 3
连续3年获得冠军的队伍
--建表语句
create table champion(year int, team string)
row format delimited fields terminated by ' ';
load data local inpath '/temp/sql.txt' into table champion;
--数据：年份、获得冠军的队伍
2000 Sun
2001 Lakers
2002 Rockets
2003 Rockets
2004 Rockets
2005 Spurs
2006 Spurs
2007 Sun
2008 Lakers
2009 Lakers
2010 Lakers
2011 Lakers
2012 Warriors
2013 Warriors
2014 Warriors
2015 Heat
2016 Warriors
2017 Cavaliers
2018 Warriors
2019 Sun
2020 Warriors
2021 Warriors
2022 Raptors
1 --sql语句
2 --查询队伍在起始年份及其之后的连续获得冠军的次数
3 select t1.team, min(t1.year) as start_year, count(*) as count
4 from (
5 --按照队伍分组，按照年份递增排序，计算队伍获得冠军的锚定年份
6 select year, team, year - row_number() over(partition by team order by year asc) as diff
7 from champion
8 ) t1
9 group by t1.team, t1.diff --按照队伍、锚定年份分组
10 having count > 2; --筛选出至少3次的连续获得冠军
11
12 --结果
13 t2.team t2.start_year t2.count
14 Lakers 2008 4
15 Rockets 2002 3
16 Warriors 2012 3
间隔连续问题
给定每天登录游戏的所有用户，返回每个用户连续登录的最长天数，间隔一天的两次登录也可以看作连续登录
1 --建表语句
2 create table login(id int, dt date)
3 row format delimited fields terminated by ' ';
4 load data local inpath '/temp/sql.txt' into table login;
5
6 --数据：用户id、登录日期
7 1001 2022-07-01
8 1002 2022-07-01
9 1003 2022-07-01
10 1001 2022-07-02
11 1002 2022-07-03
12 1001 2022-07-04
13 1002 2022-07-05
14 1003 2022-07-05
15 1001 2022-07-07
16 1002 2022-07-07
17 1003 2022-07-08
18 1001 2022-07-09
19 1002 2022-07-09
1 --sql语句
2 --按照用户id分组，查询每个用户连续登录的最长天数
3 select id, max(day_count) as day_count
4 from (
5 --按照用户id、flag字段分组，计算登录日期的最大值、最小值之差，表示一次连续登录的天数
6 select id, datediff(max(dt), min(dt)) + 1 as day_count
7 from (
8 --按照用户id分组，按照登录日期递增排序，判断当前登录、上次登录的日期之差是否大于2，累计求和，
9 --使得属于连续登录的所有日期具有相同取值的flag字段
10 select id, dt,
11 sum(if(diff > 2, 1, 0)) over(partition by id order by dt asc) as flag
12 from (
13 --查询当前登录、上次登录的日期之差diff
14 select id, dt,
15 case when pre_dt is null then 0
16 else datediff(dt, pre_dt) end as diff
17 from (
18 --按照用户id分组，按照登录日期递增排序，查询当前登录日期、上次登录日期
19 select id, dt,
20 lag(dt) over(partition by id order by dt asc) as pre_dt
21 from login
22 ) t1
23 ) t2
24 ) t3
25 group by id, flag
26 ) t4
27 group by id;
28
29 --结果
30 id day_count
31 1001 4
32 1002 9
33 1003 1
波峰波谷问题
股票价格在时间点上的波峰与波谷
1 --建表语句
2 create table price(stock string, time string, price int)
3 row format delimited fields terminated by ' ';
4 load data local inpath '/temp/sql.txt' into table price;
5
6 --数据：股票id、时间点、价格
7 A1 06:00 12
8 A1 09:00 16
9 A1 12:00 24
10 A1 15:00 17
11 A1 18:00 11
12 A1 21:00 13
13 B1 06:00 18
14 B1 09:00 12
15 B1 12:00 13
16 B1 15:00 13
17 B1 18:00 15
18 B1 21:00 17
19 C1 06:00 12
20 C1 09:00 13
21 C1 12:00 15
22 C1 15:00 17
23 C1 18:00 18
24 C1 21:00 20
1 --sql语句
2 select * from (
3 --查询波峰点
4 select t1.stock, t1.time, t1.price, 'top' as top
5 from (
6 select stock, time, price,
7 --按照股票分组，按照时间点递增排序
8 lag(price) over(partition by stock order by time asc) as previous, --前面时间点的价格
9 lead(price) over(partition by stock order by time asc) as next --后面时间点的价格
10 from price
11 ) t1
12 where t1.price > t1.previous and t1.price > t1.next --筛选出波峰点
13
14 union
15
16 --查询波谷点
17 select t2.stock, t2.time, t2.price, 'down' as top
18 from (
19 select stock, time, price,
20 lag(price) over(partition by stock order by time asc) as previous,
21 lead(price) over(partition by stock order by time asc) as next
22 from price
23 ) t2
24 where t2.price < t2.previous and t2.price < t2.next --筛选出波谷点
25 ) t3;
26
27 --结果
28 t3.stock t3.time t3.price t3.top
29 A1 12:00 24 top
30 A1 18:00 11 down
31 B1 09:00 12 down
浏览时长问题
给定用户在多个时间点上的点击浏览记录，如果两次点击浏览的时间间隔不超过30个单位，则两次浏览属于相同的会话。查询用户在每次会话中的浏览时长、浏览步长，步长表示点击浏览的次数
1 --建表语句
2 create table click(id string, time int)
3 row format delimited fields terminated by ' ';
4 load data local inpath '/temp/sql.txt' into table click;
5
6 --数据：用户id、点击浏览时间
7 a 1001
8 a 1005
9 a 1020
10 a 1048
11 a 1078
12 a 1230
13 a 1245
14 a 1270
15 a 1282
16 b 1101
17 b 1132
18 b 1156
19 b 1180
20 b 1200
21 b 1230
22 b 1345
23 b 1370
24 b 1400
1 --sql语句
2 --查询用户在每个会话中的起始时间点、点击浏览次数、浏览时长
3 select id, min(time) as start_time, count(*) as count, max(time) - min(time) as total_time
4 from (
5 --分组排序后，从上到下计算value列的累加和。如果求和结果相同，则表示属于相同的会话
6 select t2.id, t2.time, sum(t2.value) over(partition by id order by time asc) as stage
7 from (
8 --如果与前一次点击的时间之差超过30，则value列为1，否则为0
9 --value列为1表示这次点击属于一个新的会话，为0表示这次点击与前一次属于相同的会话
10 select id, time, diff, case when nvl(diff, 9999) > 30 then 1 else 0 end value
11 from (
12 --按照用户id分组，按照点击浏览时间递增排序，计算前后两次点击的时间之差
13 select id, time, time - lag(time) over(partition by id order by time asc) as diff
14 from click
15 ) t1
16 ) t2
17 ) t3
18 group by id, stage;
19
20 --结果
21 id start_time count total_time
22 a 1001 5 77
23 a 1230 4 52
24 b 1101 1 0
25 b 1132 5 98
26 b 1345 3 55
活动时长问题
每个品牌具有多个打折活动，给定每个活动的开始时间、结束时间，返回每个品牌实际参与打折的天数，重复日期不计算在内
1 --建表语句
2 create table discount(brand int, start_dt date, end_dt date)
3 row format delimited fields terminated by ' ';
4 load data local inpath '/temp/sql.txt' into table discount;
5
6 --数据：品牌id、活动开始时间、活动结束时间
7 1001 2022-07-01 2022-07-03
8 1001 2022-07-05 2022-07-10
9 1002 2022-07-02 2022-07-08
10 1002 2022-07-06 2022-07-09
11 1003 2022-07-12 2022-07-20
12 1003 2022-07-15 2022-07-18
13 1004 2022-07-20 2022-07-25
14 1004 2022-07-22 2022-07-26
15 1004 2022-07-28 2022-07-30
1 --sql语句
2 --按照品牌id分组，计算打折活动的不重复天数之和day_count
3 select brand, sum(count) as day_count
4 from (
5 --根据当前结束时间的最大值max_dt，计算每行打折活动可以贡献的活动天数count
6 select brand, start_dt, end_dt, max_dt,
7 case when max_dt is null then datediff(end_dt, start_dt) + 1
8 when max_dt < start_dt then datediff(end_dt, start_dt) + 1
9 when max_dt < end_dt then datediff(end_dt, max_dt)
10 else 0 end as count
11 from (
12 --按照品牌id分组，按照开始时间、结束时间递增排序，查询当前结束时间的最大值max_dt
13 select brand, start_dt, end_dt,
14 max(end_dt) over(partition by brand order by start_dt asc, end_dt asc rows
15 between unbounded preceding and 1 preceding) as max_dt
16 from discount
17 ) t1
18 ) t2
19 group by brand;
20
21 --结果
22 brand day_count
23 1001 9
24 1002 8
25 1003 9
26 1004 11
同时在线问题
给定每个用户在线的开始时间、结束时间，返回一个时间段与人数，这个时间段具有最多的在线人数
1 --建表语句
2 create table live(id int, start_dt date, end_dt date)
3 row format delimited fields terminated by ' ';
4 load data local inpath '/temp/sql.txt' into table live;
5
6 --数据：用户id、开始时间、结束时间
7 1001 2022-07-01 2022-07-02
8 1001 2022-07-04 2022-07-05
9 1001 2022-07-07 2022-07-10
10 1001 2022-07-13 2022-07-18
11 1002 2022-07-01 2022-07-02
12 1002 2022-07-04 2022-07-05
13 1002 2022-07-07 2022-07-08
14 1002 2022-07-10 2022-07-11
15 1002 2022-07-13 2022-07-14
16 1002 2022-07-16 2022-07-17
17 1002 2022-07-19 2022-07-20
18 1003 2022-07-01 2022-07-20
19 1004 2022-07-04 2022-07-08
20 1004 2022-07-12 2022-07-16
21 1005 2022-07-03 2022-07-06
22 1005 2022-07-09 2022-07-11
23 1006 2022-07-04 2022-07-06
24 1007 2022-07-09 2022-07-12
25 1008 2022-07-06 2022-07-08
26 1008 2022-07-11 2022-07-13
27 1009 2022-07-06 2022-07-08
28 1009 2022-07-18 2022-07-19
29 1010 2022-07-11 2022-07-14
1 --sql语句
2 select dt, online
3 from (
4 select dt,
5 sum(count) over(order by dt asc) as online
6 from (
7 select dt, sum(value) as count
8 from (
9 select id, start_dt as dt, 1 as value from live
10 union
11 select id, date_add(end_dt, 1) as dt, -1 as value from live
12 ) t1
13 group by dt
14 ) t2
15 ) t3
16 order by online desc, dt asc;
17
18 --结果
19 dt online
20 2022-07-04 6
21 2022-07-06 6
22 2022-07-07 6
23 2022-07-11 6
24 2022-07-13 6
25 2022-07-10 5
26 2022-07-12 5
27 2022-07-14 5
28 2022-07-09 4
29 2022-07-16 4
30 2022-07-01 3
31 2022-07-15 3
32 2022-07-17 3
33 2022-07-18 3
34 2022-07-19 3
35 2022-07-03 2
36 2022-07-20 2
37 2022-07-21 0
区间合并问题
给定多个时间段，每个时间段分为开始时间、结束时间，将相互重叠的多个时间段合并为一个区间
1 --建表语句
2 create table time_merge(id int, start_time int, end_time int)
3 row format delimited fields terminated by ' ';
4 load data local inpath '/temp/sql.txt' into table time_merge;
5
6 --数据：id、开始时间、结束时间
7 1 12 15
8 2 57 58
9 3 29 32
10 4 30 31
11 5 17 19
12 6 44 44
13 7 56 57
14 8 16 18
1 --sql语句
2 --按照区间序号进行分组，查询每个分组的最小开始时间作为区间开始时间，最大结束时间作为区间结束时间
3 select flag, min(start_time) as start_time, max(end_time) as end_time
4 from (
5 --判断哪些时间段属于相同区间，flag表示时间段归属的区间序号，值相同表示属于相同区间
6 select id, start_time, end_time,
7 sum(count) over(order by start_time asc, end_time asc) as flag
8 from (
9 --根据当前结束时间的最大值max_dt进行比较，标记每个时间段是否为新的区间
10 select id, start_time, end_time,
11 case when max_dt is null then 1 --作为一个新的区间
12 when max_dt < start_time then 1 --作为一个新的区间
13 else 0 end as count --与前面的区间具有重叠
14 from (
15 --按照开始时间、结束时间递增排序，查询当前结束时间的最大值max_dt
16 select id, start_time, end_time,
17 max(end_time) over(order by start_time asc, end_time asc rows
18 between unbounded preceding and 1 preceding) as max_dt
19 from time_merge
20 ) t1
21 ) t2
22 ) t3
23 group by flag
24 ;
25
26 --结果
27 flag start_time end_time
28 1 12 15
29 2 16 19
30 3 29 32
31 4 44 44
32 5 56 58
共同好友问题
给定每个用户的好友列表，好友关系是互相对称的，返回任意两个用户的共同好友列表
1 --建表语句
2 create table common_friend(id string, friends string)
3 row format delimited fields terminated by ' ';
4 load data local inpath '/temp/sql.txt' into table common_friend;
5
6 --数据：用户id、好友id列表
7 A B,C,D
8 B A,C,E
9 C A,B,D,E,F
10 D A,C,F
11 E B,C
12 F C,D
1 --sql语句
2 --创建临时表，将好友关系分解为最细粒度
3 create table friend as
4 select id, friend from common_friend lateral view explode(split(friends, ',')) temp as friend;
5
6 --按照用户的两两组合进行分组，将所有的共同好友放入列表
7 select t1.ids, concat_ws(',', collect_list(t1.friend)) as common_friend
8 from (
9 --将好友关系表与自身进行连接，查询每个用户是哪两个用户的共同好友
10 select a.friend, concat(a.id, ',', b.id) as ids
11 from friend a
12 join friend b
13 on a.friend = b.friend --按照共同好友进行连接
14 where a.id < b.id --筛选出重复记录
15 ) t1
16 group by t1.ids
17 ;
18
19 --结果
20 t1.ids common_friend
21 A,B C
22 A,C B,D
23 A,D C
24 A,E B,C
25 A,F C,D
26 B,C A,E
27 B,D A,C
28 B,E C
29 B,F C
30 C,D A,F
31 C,E B
32 C,F D
33 D,E C
34 D,F C
35 E,F C
可能好友问题
给定每个用户的好友列表，好友关系是互相对称的，返回每个用户的可能好友。如果两个用户不是好友关系，并且两者拥有至少一个（或者两个）共同好友，则两者互相是可能好友
--建表语句
create table maybe_friend(id string, friends string)
row format delimited fields terminated by ' ';
load data local inpath '/temp/sql.txt' into table maybe_friend;
--数据：用户id、好友id列表
A B,C,D
B A,C,E
C A,B,D,E,F
D A,C,F
E B,C
F C,D
1 --sql语句
2 --创建临时表，将好友关系分解为最细粒度
3 with friend as (
4 select id, friend from common_friend lateral view explode(split(friends, ',')) temp as friend)
5
6 --将具有至少两个共同好友的临时表与好友关系表进行连接，如果临时表的两个用户是好友关系，则在好友关系表中存在对应记录，否则不存在对应记录，
7 --表示两者是可能好友
8 select t2.id1, t2.id2
9 from (
10 --查询具有至少两个共同好友的任意两个用户
11 select t1.id1, t1.id2
12 from (
13 --将好友关系表与自身进行连接，查询任意两个用户具有的共同好友
14 select a.id as id1, b.id as id2, a.friend
15 from friend a
16 join friend b
17 on a.friend = b.friend
18 where a.id < b.id
19 ) t1
20 group by t1.id1, t1.id2
21 having count(t1.friend) >= 2
22 ) t2
23 left join friend
24 on t2.id1 = friend.id
25 and t2.id2 = friend.friend
26 where friend.id is null --排除真实好友，筛选可能好友
27 ;
28
29 --结果
30 t2.id1 t2.id2
31 A E
32 A F
33 B D
推荐商品问题
给定一个用户购买一次商品的记录，返回每个用户可能想要购买的商品。如果其余用户与这个用户购买至少两个相同的商品，则其余用户购买、这个用户没有购买的商品，就是这个用户可能想要购买的商品
1 --建表语句
2 create table shop(id string, product int)
3 row format delimited fields terminated by ' ';
4 load data local inpath '/temp/sql.txt' into table shop;
5
6 --数据：用户id、商品id
7 A 1
8 A 2
9 A 1
10 A 3
11 B 2
12 B 3
13 B 4
14 B 5
15 B 2
16 C 1
17 C 2
18 C 1
19 D 1
20 D 3
21 D 6
1 --sql语句
2 --按照用户、商品进行分组、去重
3 with temp as (
4 select id, product from shop group by id, product)
5
6 --将已购买与推荐购买的临时表与已购买表进行连接，如果临时表的商品已购买，则在已购买表中存在对应记录，否则不存在对应记录，表示推荐商品
7 select t4.id1 as id, t4.product
8 from (
9 --查询每个用户已购买与推荐购买的商品
10 select t3.id1, t3.product
11 from (
12 --查询每个用户、以及具有相同购买倾向的其余用户、其余用户已购买的商品
13 select t2.id1, t2.id2, temp.product
14 from (
15 --查询已购买至少两个相同商品的任意两个用户
16 select t1.id1, t1.id2
17 from (
18 --查询已购买相同商品的任意两个用户
19 select a.id as id1, b.id as id2, a.product
20 from temp a
21 join temp b
22 on a.product = b.product
23 and a.id != b.id
24 ) t1
25 group by t1.id1, t1.id2
26 having count(t1.product) >= 2
27 ) t2
28 join temp
29 on t2.id2 = temp.id
30 ) t3
31 group by t3.id1, t3.product
32 ) t4
33 left join temp
34 on t4.product = temp.product
35 and t4.id1 = temp.id --相同用户购买相同商品
36 where temp.product is null --排除已购买商品，筛选推荐商品
37 ;
38
39 --结果
40 id t4.product
41 A 4
42 A 5
43 A 6
44 B 1
45 C 3
46 D 2
登录行为分析
有关的统计指标包含：访问量、活跃用户、新增用户、留存用户、流失用户、沉默用户、回流用户
含义解释：（1）活跃用户，每日登录应用的用户，（2）新增用户，在当前日期第一次登录应用的用户，（3）留存用户，在当前日期登录应用的用户，并且在之前日期登录过应用，（4）流失用户，指定时间内没有登录应用的用户，（5）沉默用户，只有第一次登录应用的用户，之后没有登录过应用，（6）回流用户，在当前日期登录应用的用户，并且在之前的指定时间内没有登录过应用
角色分配：活跃、新增 a、留存 b、留存 c、流失 d、沉默 e、回流 f
--建表语句
create table login_action(uid string, login_date int)
row format delimited fields terminated by ' ';
load data local inpath '/temp/sql.txt' into table login_action;
--数据：用户id、登录日期
d 20220321
e 20220321
f 20220321
a 20220322
b 20220322
d 20220322
a 20220323
b 20220323
c 20220323
a 20220324
b 20220324
c 20220324
a 20220325
b 20220325
c 20220325
f 20220325
1 --新增用户：需要一张全量的用户表，一张某日的登录行为表
2 --将日期20220321的登录用户作为初始表，表示已知的用户，然后查询日期20220322的新增用户
3 --登录行为表与全量用户表进行关联，行为表中存在、用户表中不存在的记录表示这个日期的新增用户
4 --查询出每个日期的新增用户后，需要追加到全量的用户表中
5
6 --全量的用户表：表示用户成为新增用户的日期
7 create table user_add(uid string, add_date int);
8 insert into table user_add (select * from login_action where login_date = 20220321);
9
10 select t1.uid, t1.login_date
11 from login_action t1
12 left join user_add t2
13 on t1.uid = t2.uid
14 where t1.login_date = 20220322 --查询日期20220322的新增用户
15 and t2.uid is null --查询在用户表中不存在的记录，即这个日期的新增用户
16 ;
17
18 --20220322的新增用户
19 t1.uid t1.add_date
20 a 20220322
21 b 20220322
22
23 --所有日期的新增用户
24 user_add.uid user_add.add_date
25 d 20220321
26 e 20220321
27 f 20220321
28 a 20220322
29 b 20220322
30 c 20220323
1 --留存用户：需要一张全量的用户表，一张某日的登录行为表
2 --登录行为表与全量用户表进行关联，行为表中的登录日期与用户表中的登录日期之差为1天，表示1日的留存用户
3 --使用union all合并1日、2日的留存用户
4
5 select t1.uid, t1.login_date, t2.add_date
6 from login_action t1
7 join user_add t2
8 on t1.uid = t2.uid
9 where t1.login_date = 20220324 --查询日期20220324的留存用户
10 and t2.add_date = (20220324 - 1) --查询1日留存用户
11
12 union all --合并1日、2日留存用户
13
14 select t1.uid, t1.login_date, t2.add_date
15 from login_action t1
16 join user_add t2
17 on t1.uid = t2.uid
18 where t1.login_date = 20220324 --查询日期20220324的留存用户
19 and t2.add_date = (20220324 - 2) --查询2日留存用户
20 ;
21
22 --20220324的留存用户：1日、2日
23 _u1.uid _u1.login_date _u1.add_date
24 c 20220324 20220323
25 b 20220324 20220322
26 a 20220324 20220322
1 --流失用户：需要一张全量的登录行为表
2 --查询每个用户的登录日期的最大值，与当前日期之差超过2日，表示流失用户
3
4 select uid, max(login_date) as last_login
5 from login_action
6 where login_date <= 20220325 --查询日期20220325的流失用户
7 group by uid
8 having max(login_date) < (20220325 - 2) --超过2日表示流失用户
9 ;
10
11 --20220325的流失用户
12 uid last_login
13 d 20220322
14 e 20220321
1 --沉默用户：需要一张全量的登录行为表
2 --查询每个用户的登录日期的数量，只有一次登录操作，表示沉默用户
3
4 select uid, max(login_date) as once_login
5 from login_action
6 group by uid
7 having count(login_date) = 1 --只有一次登录操作的用户表示沉默用户
8 ;
9
10 --20220325的沉默用户
11 uid once_login
12 e 20220321
1 --回流用户：需要一张全量的登录行为表
2 --日期20220325的活跃用户，如果在之前的日期为流失用户，则在日期20220325为回流用户
3
4 select t1.uid, t1.login_date, t2.last_login
5 from (
6 select uid, login_date
7 from login_action
8 where login_date = 20220325 --查询20220325的活跃用户
9 ) t1
10 join
11 (
12 select uid, max(login_date) as last_login
13 from login_action
14 where login_date < 20220325 --查询20220325之前的流失用户
15 group by uid
16 having max(login_date) < (20220325 - 2)
17 ) t2
18 on t1.uid = t2.uid
19 ;
20
21 --20220325的h用户
22 t1.uid t1.login_date t2.last_login
23 f 20220325 20220321
购买行为分析
有关的统计指标包含：
点击量、下单数、支付数
点击行为分析
JSON解析
解析嵌套Json字符串
get_json_object()方法
