前言:
本文主要用来记录我在做SQL题,或者面试时所遇到的题型,怕之后太久没接触遗忘一些函数和解题思路,所以写了本文。本文大部分题型和思路主要来自某客网的SQL题,解题思路则是在评论区中选取最精妙且通俗易懂的。文章会持续更新,插入一些我需要记录的SQL题型
目录
6.时间函数DATEDIFF和TIMESTAMPDIFF的用法和区别
2.用SQL的窗口函数筛选出一个月里连续三天以及以上的汽车ID和他的最大行程公里数
一丶普通常见类型
1.某个字段的截断平均
round((sum(score) - max(score) - min(score)) /(count(score)- 2),1)
思路:总分去掉一个最大值和一个最小值然后整除总数(保留一位小数)
2.统计某个字段已完成的总数(中途退出或者放弃不作数)
count(if(submit_time is not null, exam_id, null))
count(CASE WHEN submit_time IS NOT NULL THEN exam_id ELSE NULL END)
思路:它会根据submit_time
是否为null来决定返回的值。如果submit_time
不为null,则返回exam_id
,否则返回null。一共有两种写法
3.每个月的用户平均活跃天
(count(distinct uid, date_format(submit_time, '%y%m%d'))) / count(distinct uid)
思路:用DISTINCT统计这个月有多少用户,然后一共活跃多少天,活跃总天数/用户个数
4.需要生成额外的汇总行,并进行统计总和与处理空值
当在使用WITH ROLLUP
进行分组汇总时,有时候需要处理可能的空值(NULL)。这时可以结合COALESCE
函数来处理空值,让我们看一个简单的案例
假设我们有一个销售数据表 sales
,包含 region
、product
和 amount
字段,用于记录不同地区和产品的销售金额。现在我们希望在汇总时,如果某个地区或产品出现了NULL,将其替换为合适的默认值。
示例数据:
region | product | amount |
---|---|---|
North | A | 100 |
North | B | 150 |
South | A | 200 |
South | B | 120 |
South | C | 180 |
NULL | B | 300 |
使用 WITH ROLLUP
和 COALESCE
的查询示例:
SELECT COALESCE(region, 'Total') AS region,
COALESCE(product, 'Total') AS product,
SUM(amount) AS total_amount
FROM sales
GROUP BY region, product WITH ROLLUP;
-
COALESCE(region, 'Total')
:这里的COALESCE
函数会返回第一个非空值。在这个例子中,如果region
是NULL,它会将其替换为字符串 ‘Total’。 -
SUM(amount) AS total_amount
:这是一个聚合函数,用于计算每个组(地区和产品组合)的销售总金额。
结果:
执行上述查询后,结果会包含每个地区和产品的销售总金额,以及每个地区和产品组合的总计和总体的总计。具体结果如下:
region | product | total_amount |
---|---|---|
North | A | 100 |
North | B | 150 |
North | Total | 250 |
South | A | 200 |
South | B | 120 |
South | C | 180 |
South | Total | 500 |
Total | Total | 750 |
思路:主要使用函数COALESCE和WITH ROLLUP
5.筛选出完成时间小于规定时间的一半
timestampdiff (minute, e_r.start_time, e_r.submit_time) * 2 < e_i.duration
思路:(结束时间-开始时间)*2 < 规定时间
6.时间函数DATEDIFF和TIMESTAMPDIFF的用法和区别
DATEDIFF
和 TIMESTAMPDIFF
是用于日期和时间计算的函数,具体用途和区别如下:
DATEDIFF
- 用途:计算两个日期之间的天数差。
- 用法:
DATEDIFF(date1, date2)
- 参数:
date1
:第一个日期。date2
:第二个日期。
示例
SELECT DATEDIFF('2024-06-27', '2024-06-20') AS days_difference;
结果:
days_difference |
---|
7 |
这个示例计算了2024年6月27日和2024年6月20日之间的天数差,结果为7天。
TIMESTAMPDIFF
- 用途:计算两个日期或时间戳之间的差异,可以按不同的时间单位(如年、月、天、小时等)返回结果。
- 用法:
TIMESTAMPDIFF(unit, datetime_expr1, datetime_expr2)
- 参数:
unit
:时间单位,可以是YEAR
、MONTH
、DAY
、HOUR
、MINUTE
、SECOND
等。datetime_expr1
:第一个日期或时间戳。datetime_expr2
:第二个日期或时间戳。
示例
SELECT
TIMESTAMPDIFF(DAY, '2024-06-20 10:00:00', '2024-06-27 10:00:00') AS days_difference,
TIMESTAMPDIFF(HOUR, '2024-06-20 10:00:00', '2024-06-27 10:00:00') AS hours_difference;
结果:
days_difference | hours_difference |
---|---|
7 | 168 |
这个示例计算了两个日期和时间戳之间的差异,以天数和小时数表示,结果分别为7天和168小时。
区别
功能范围:
DATEDIFF
只能计算天数差。TIMESTAMPDIFF
可以计算以不同单位(年、月、天、小时等)的时间差。精度:
DATEDIFF
只精确到天。TIMESTAMPDIFF
可以精确到指定的时间单位(如秒、分钟、小时等)。
二丶窗口函数类型
窗口函数:有三种排序方式
- rank() over() 1 2 2 4 4 6 (计数排名,跳过相同的几个,eg.没有3没有5)
- row_number() over() 1 2 3 4 5 6 (赋予唯一排名)
- dense_rank() over() 1 2 2 3 3 4 (不跳过排名,可以理解为对类别进行计数)
1.获取两种不同类型试卷的前三名
示例数据:一共有A类和B类两种试卷
exam_id | student_name | score |
---|---|---|
A | Alice | 85 |
A | Bob | 78 |
A | Charlie | 92 |
A | David | 88 |
B | Eve | 90 |
B | Frank | 82 |
B | Grace | 95 |
B | Helen | 87 |
SQL 查询示例:
-- 获取类别1(exam_id = 'A')的前三名学生及其排名
SELECT exam_id, student_name, score,
DENSE_RANK() OVER (PARTITION BY exam_id ORDER BY score DESC) AS rank_num
FROM (
SELECT exam_id, student_name, score
FROM (
SELECT exam_id, student_name, score,
ROW_NUMBER() OVER (PARTITION BY exam_id ORDER BY score DESC) AS row_num
FROM exam_scores
WHERE exam_id = 'A'
) AS A_top3
WHERE row_num <= 3
UNION ALL
SELECT exam_id, student_name, score
FROM (
SELECT exam_id, student_name, score,
ROW_NUMBER() OVER (PARTITION BY exam_id ORDER BY score DESC) AS row_num
FROM exam_scores
WHERE exam_id = 'B'
) AS B_top3
WHERE row_num <= 3
) AS top3_with_rank
ORDER BY exam_id, rank_num;
- 在内部查询中,我们仍然使用
ROW_NUMBER()
函数来找出每类试卷的前三名学生,但是在外部查询中,我们加入了DENSE_RANK() OVER (PARTITION BY exam_id ORDER BY score DESC)
部分。 DENSE_RANK()
函数计算学生在每个试卷类别中的排名,如果有相同分数的学生,将会有并列排名。- 最后,通过外部的
ORDER BY exam_id, rank_num
来确保结果按照试卷类别和排名顺序排列
结果:
执行上述 SQL 查询后,将会得到包含排名数字字段的结果:
exam_id | student_name | score | rank_num |
---|---|---|---|
A | Charlie | 92 | 1 |
A | David | 88 | 2 |
A | Alice | 85 | 3 |
B | Grace | 95 | 1 |
B | Eve | 90 | 2 |
B | Helen | 87 | 3 |
2.用SQL的窗口函数筛选出一个月里连续三天以及以上的汽车ID和他的最大行程公里数
示例数据
假设有如下的表 car_trip
,其中包含汽车的行程记录:
car_id | start_timestamp | end_timestamp | trip_distance |
---|---|---|---|
1 | 2024-06-01 08:00:00 | 2024-06-01 09:00:00 | 50 |
1 | 2024-06-02 08:00:00 | 2024-06-02 09:00:00 | 60 |
1 | 2024-06-03 08:00:00 | 2024-06-03 09:00:00 | 70 |
1 | 2024-06-05 08:00:00 | 2024-06-05 09:00:00 | 80 |
2 | 2024-06-10 08:00:00 | 2024-06-10 09:00:00 | 90 |
2 | 2024-06-11 08:00:00 | 2024-06-11 09:00:00 | 100 |
2 | 2024-06-12 08:00:00 | 2024-06-12 09:00:00 | 110 |
3 | 2024-06-15 08:00:00 | 2024-06-15 09:00:00 | 120 |
SQL代码及执行结果 :
WITH trip_dates AS (
SELECT
car_id,
DATE(start_timestamp) AS trip_date,
trip_distance
FROM
car_trip
WHERE
start_timestamp >= '2024-06-01' AND start_timestamp < '2024-07-01'
),
consecutive_days AS (
SELECT
car_id,
trip_date,
trip_distance,
ROW_NUMBER() OVER (PARTITION BY car_id ORDER BY trip_date) AS row_num,
trip_date - INTERVAL '1' DAY * ROW_NUMBER() OVER (PARTITION BY car_id ORDER BY trip_date) AS grp
FROM
trip_dates
),
trip_streaks AS (
SELECT
car_id,
MIN(trip_date) AS start_date,
MAX(trip_date) AS end_date,
COUNT(*) AS consecutive_days,
MAX(trip_distance) AS max_trip_distance
FROM
consecutive_days
GROUP BY
car_id, grp
HAVING
COUNT(*) >= 3
)
SELECT
car_id,
max_trip_distance
FROM
trip_streaks
ORDER BY
car_id;
car_id | max_trip_distance |
---|---|
1 | 70 |
2 | 110 |
解释
consecutive_days
子查询这个子查询的目的是识别每辆车连续行程的日期分组。为了实现这一点,我们使用窗口函数和日期运算。
子查询的每一步解释:
选择字段:
car_id
:汽车的ID。trip_date
:行程的日期。trip_distance
:行程的公里数。ROW_NUMBER() OVER (PARTITION BY car_id ORDER BY trip_date) AS row_num
:这个窗口函数给每辆车的每个行程日期分配一个行号。行号的生成是基于car_id
分区,并按trip_date
排序。例如,如果一辆车有三个行程记录,按日期排序,它们的行号将分别是1、2、3。计算分组标识:
trip_date - INTERVAL '1' DAY * ROW_NUMBER() OVER (PARTITION BY car_id ORDER BY trip_date) AS grp
:
- 这部分的目的是创建一个分组标识符(
grp
),用于标识连续的行程日期。ROW_NUMBER() OVER (PARTITION BY car_id ORDER BY trip_date)
返回的是每个行程的行号。trip_date - INTERVAL '1' DAY * ROW_NUMBER()
将行号转换为天数间隔,并从行程日期中减去这个间隔。这样,如果行程是连续的,减去的结果会是相同的。例如:
- 第一个行程:
2024-06-01 - 1 * 1 = 2024-05-31
- 第二个行程:
2024-06-02 - 1 * 2 = 2024-05-31
- 第三个行程:
2024-06-03 - 1 * 3 = 2024-05-31
- 这样做的结果是,如果行程是连续的(例如1号、2号、3号),那么计算出的
grp
值会相同。示例数据和计算结果:
假设有如下数据:
| car_id | trip_date | trip_distance | |--------|-------------|---------------| | 1 | 2024-06-01 | 50 | | 1 | 2024-06-02 | 60 | | 1 | 2024-06-03 | 70 | | 1 | 2024-06-05 | 80 | | 2 | 2024-06-10 | 90 | | 2 | 2024-06-11 | 100 | | 2 | 2024-06-12 | 110 |
在
consecutive_days
中:| car_id | trip_date | trip_distance | row_num | grp | |--------|-------------|---------------|---------|------------| | 1 | 2024-06-01 | 50 | 1 | 2024-05-31 | | 1 | 2024-06-02 | 60 | 2 | 2024-05-31 | | 1 | 2024-06-03 | 70 | 3 | 2024-05-31 | | 1 | 2024-06-05 | 80 | 4 | 2024-06-01 | | 2 | 2024-06-10 | 90 | 1 | 2024-06-09 | | 2 | 2024-06-11 | 100 | 2 | 2024-06-09 | | 2 | 2024-06-12 | 110 | 3 | 2024-06-09 |
从上面的结果中可以看出:
- 对于
car_id
为1
的数据,2024-06-01、2024-06-02 和 2024-06-03 由于是连续的日期,它们的grp
值相同(2024-05-31)。- 对于
car_id
为2
的数据,2024-06-10、2024-06-11 和 2024-06-12 由于是连续的日期,它们的grp
值相同(2024-06-09)。这样,我们就可以在后续查询中根据
grp
来分组和筛选出连续的日期。
3.近两天的公里数情况
dense_rank()over(partition by uid order by start_timestamp desc) AS rk //当rk<=2时满足题设需求。
4.LEAD和LAG窗口函数
窗口函数
LEAD
和LAG
在SQL中非常有用,可以用来访问一个结果集中某行的前一行或后一行的数据。以下是一个简单的示例,展示如何使用这些函数,并提供详细的表格示例。
示例数据
假设有一个名为 sales
的表,其中包含每月的销售数据:
month | sales_amount |
---|---|
2024-01-01 | 100 |
2024-02-01 | 150 |
2024-03-01 | 200 |
2024-04-01 | 250 |
2024-05-01 | 300 |
使用 LEAD
和 LAG
LAG
:返回当前行的前一行的值。LEAD
:返回当前行的后一行的值。
我们将使用这些函数来计算每月销售额与前一个月和下一个月的销售额的差异。
SQL查询
SELECT
month,
sales_amount,
LAG(sales_amount, 1) OVER (ORDER BY month) AS prev_month_sales,
LEAD(sales_amount, 1) OVER (ORDER BY month) AS next_month_sales
FROM
sales
ORDER BY
month;
查询结果
month | sales_amount | prev_month_sales | next_month_sales |
---|---|---|---|
2024-01-01 | 100 | NULL | 150 |
2024-02-01 | 150 | 100 | 200 |
2024-03-01 | 200 | 150 | 250 |
2024-04-01 | 250 | 200 | 300 |
2024-05-01 | 300 | 250 | NULL |
解释
- prev_month_sales:使用
LAG
函数获取前一个月的销售额。 - next_month_sales:使用
LEAD
函数获取下一个月的销售额。
LAG(sales_amount, 1) OVER (ORDER BY month):
LAG
函数返回前一行的sales_amount
。OVER (ORDER BY month)
指定了数据的排序顺序,使得LAG
函数能正确地访问前一行的数据。LEAD(sales_amount, 1) OVER (ORDER BY month):
LEAD
函数返回后一行的sales_amount
。OVER (ORDER BY month)
指定了数据的排序顺序,使得LEAD
函数能正确地访问后一行的数据。
三丶实际应用常见题型
1.各个视频的平均完播率
round( sum(if(end_time - start_time >= duration,1,0)) / count(start_time),3)
思路:视频完播率 = 视频完播总次数 / 总播次数
2.每个类别视频的平均播放进度
问题:计算各类视频的平均播放进度,将进度大于60%的类别输出
用户-视频互动表tb_user_video_log
id | uid | video_id | start_time | end_time |
1 | 101 | 2001 | 2021-10-01 10:00:00 | 2021-10-01 10:00:30 |
2 | 102 | 2001 | 2021-10-01 10:00:00 | 2021-10-01 10:00:21 |
3 | 103 | 2001 | 2021-10-01 11:00:50 | 2021-10-01 11:01:20 |
4 | 102 | 2002 | 2021-10-01 11:00:00 | 2021-10-01 11:00:30 |
5 | 103 | 2002 | 2021-10-01 10:59:05 | 2021-10-01 11:00:05 |
短视频信息表tb_video_info
id | video_id | author | tag | duration |
1 | 2001 | 901 | 影视 | 30 |
2 | 2002 | 901 | 美食 | 60 |
3 | 2003 | 902 | 旅游 | 90 |
注:
- 播放进度=播放时长÷视频时长*100%,当播放时长大于视频时长时,播放进度均记为100%。
- 结果保留两位小数,并按播放进度倒序排序。
解题思路
- 计算每个类别的平均播放进度:
- 播放进度=播放时长÷视频时长*100%
- 播放时长=TIMESTAMPDIFF(SECOND, start_time, end_time);特殊情况:播放时长大于视频时长时,播放进度为100%(加个IF判断)
- 平均进度=AVG(每个进度)
- 结果保留2位小数:ROUND(x, 2)
- 百分比格式化:CONCAT(x, '%')
- 计算公式:
ROUND(AVG(
IF(TIMESTAMPDIFF(SECOND, start_time, end_time) > duration, 1,
TIMESTAMPDIFF(SECOND, start_time, end_time) / duration)
) * 100, 2) as avg_play_progress
- 筛选播放进度>60%的视频类别:HAVING avg_play_progress > 60
完整SQL
SELECT
tag,
CONCAT (avg_play_progress, "%") as avg_play_progress
FROM
(
SELECT
tag,
ROUND(
AVG(
IF (
TIMESTAMPDIFF (SECOND, start_time, end_time) > duration,
1,
TIMESTAMPDIFF (SECOND, start_time, end_time) / duration
)
) * 100,
2
) as avg_play_progress
FROM
tb_user_video_log
JOIN tb_video_info USING (video_id)
GROUP BY
tag
HAVING
avg_play_progress > 60
ORDER BY
avg_play_progress DESC
) as t_progress;
IF
函数检查这个差异是否大于视频时长(duration
)。如果是真,返回1
。否则,返回差异除以duration
的结果。
3.每类视频近一个月的转发量率
问题:统计在有用户互动的最近一个月(按包含当天在内的近30天算,比如10月31日的近30天为10.2~10.31之间的数据)中,每类视频的转发量和转发率(保留3位小数)。
解题思路:
- 筛选最近30天的记录:
- 找到今天的日期:MAX(DATE(start_time)
- 往过去推移30天:DATE_SUB(MAX(DATE(start_time)), INTERVAL 30 DAY)
- 筛选最近的:WHERE DATE(start_time) > (SELECT DATE_SUB(MAX(DATE(start_time)), INTERVAL 30 DAY) FROM tb_user_video_log)
- 按视频类别分组:GROUP BY tag
- 计算每个类别的转发量:SUM(if_retweet)
- 计算每个类别的转发率(转发率=转发量÷播放量):SUM(if_retweet) / COUNT(1)
- 保留3位小数:ROUND(x, 3)
完整SQL:
SELECT
tag,
SUM(if_retweet) as retweet_cnt,
ROUND(SUM(if_retweet) / COUNT(1), 3) as retweet_rate
FROM
tb_user_video_log
JOIN tb_video_info USING (video_id)
WHERE
DATE (start_time) > (
SELECT
DATE_SUB (MAX(DATE (start_time)), INTERVAL 30 DAY)
FROM
tb_user_video_log
)
GROUP BY
tag
ORDER BY
retweet_rate DESC;
4.每篇文章同一时刻最大在看人数
问题:统计每篇文章同一时刻最大在看人数,如果同一时刻有进入也有离开时,先记录用户数增加再记录减少,结果按最大人数降序。
示例数据:
用户行为日志表tb_user_log
id | uid | artical_id | in_time | out_time | sign_cin |
1 | 101 | 9001 | 2021-11-01 10:00:00 | 2021-11-01 10:00:11 | 0 |
2 | 102 | 9001 | 2021-11-01 10:00:09 | 2021-11-01 10:00:38 | 0 |
3 | 103 | 9001 | 2021-11-01 10:00:28 | 2021-11-01 10:00:58 | 0 |
4 | 104 | 9002 | 2021-11-01 11:00:45 | 2021-11-01 11:01:11 | 0 |
5 | 105 | 9001 | 2021-11-01 10:00:51 | 2021-11-01 10:00:59 | 0 |
6 | 106 | 9002 | 2021-11-01 11:00:55 | 2021-11-01 11:01:24 | 0 |
7 | 107 | 9001 | 2021-11-01 10:00:01 | 2021-11-01 10:01:50 | 0 |
(uid-用户ID, artical_id-文章ID, in_time-进入时间, out_time-离开时间, sign_in-是否签到)
思路:计算实时在线UV的方法,进入定义为1,离开定义为-1,并对时间进行正序排序,最后算累加实时在线UV
第一步,统计进入和离开的表格
SELECT uid,artical_id,in_time,1 AS uv FROM tb_user_log
UNION ALL
SELECT uid,artical_id,out_time,-1 AS uv FROM tb_user_log
101 | 9001 | 2021-11-01 10:00:00 | 1 |
102 | 9001 | 2021-11-01 10:00:09 | 1 |
103 | 9001 | 2021-11-01 10:00:28 | 1 |
104 | 9002 | 2021-11-01 11:00:45 | 1 |
105 | 9001 | 2021-11-01 10:00:51 | 1 |
106 | 9002 | 2021-11-01 11:00:55 | 1 |
107 | 9001 | 2021-11-01 10:00:01 | 1 |
101 | 9001 | 2021-11-01 10:00:11 | -1 |
102 | 9001 | 2021-11-01 10:00:38 | -1 |
103 | 9001 | 2021-11-01 10:00:58 | -1 |
104 | 9002 | 2021-11-01 11:01:11 | -1 |
105 | 9001 | 2021-11-01 10:00:59 | -1 |
106 | 9002 | 2021-11-01 11:01:24 | -1 |
107 | 9001 | 2021-11-01 10:01:50 | -1 |
第二步 ,对统计的uv值进行累加,使用sum() over()函数
SELECT
artical_id,
in_time,
uv,
SUM(uv)OVER(PARTITION BY artical_id ORDER BY in_time,uv DESC) uv_cnt
FROM (SELECT uid,artical_id,in_time,1 AS uv FROM tb_user_log
UNION ALL
SELECT uid,artical_id,out_time,-1 AS uv FROM tb_user_log
)as uv_table
WHERE artical_id<>0;
9001 | 2021-11-01 10:00:00 | 1 | 1 |
9001 | 2021-11-01 10:00:01 | 1 | 2 |
9001 | 2021-11-01 10:00:09 | 1 | 3 |
9001 | 2021-11-01 10:00:11 | -1 | 2 |
9001 | 2021-11-01 10:00:28 | 1 | 3 |
9001 | 2021-11-01 10:00:38 | -1 | 2 |
9001 | 2021-11-01 10:00:51 | 1 | 3 |
9001 | 2021-11-01 10:00:58 | -1 | 2 |
9001 | 2021-11-01 10:00:59 | -1 | 1 |
9001 | 2021-11-01 10:01:50 | -1 | 0 |
9002 | 2021-11-01 11:00:45 | 1 | 1 |
9002 | 2021-11-01 11:00:55 | 1 | 2 |
9002 | 2021-11-01 11:01:11 | -1 | 1 |
9002 | 2021-11-01 11:01:24 | -1 | 0 |
最后进行降序输出最大值
WITH t1 AS (
SELECT artical_id,in_time,
SUM(uv)OVER(PARTITION BY artical_id ORDER BY in_time,uv DESC) uv_cnt
FROM (
SELECT uid,artical_id,in_time,1 AS uv FROM tb_user_log
UNION ALL
SELECT uid,artical_id,out_time,-1 AS uv FROM tb_user_log
) as uv_table
WHERE artical_id<>0
)
SELECT artical_id,MAX(uv_cnt) max_uv FROM t1
GROUP BY artical_id
ORDER BY max_uv DESC
9001 | 3 |
9002 | 2 |
5.每个新用户的次日留存率
问题:统计2021年11月每天新用户的次日留存率(保留2位小数)
用户行为日志表tb_user_log
id | uid | artical_id | in_time | out_time | sign_cin |
1 | 101 | 0 | 2021-11-01 10:00:00 | 2021-11-01 10:00:42 | 1 |
2 | 102 | 9001 | 2021-11-01 10:00:00 | 2021-11-01 10:00:09 | 0 |
3 | 103 | 9001 | 2021-11-01 10:00:01 | 2021-11-01 10:01:50 | 0 |
4 | 101 | 9002 | 2021-11-02 10:00:09 | 2021-11-02 10:00:28 | 0 |
5 | 103 | 9002 | 2021-11-02 10:00:51 | 2021-11-02 10:00:59 | 0 |
6 | 104 | 9001 | 2021-11-02 11:00:28 | 2021-11-02 11:01:24 | 0 |
7 | 101 | 9003 | 2021-11-03 11:00:55 | 2021-11-03 11:01:24 | 0 |
8 | 104 | 9003 | 2021-11-03 11:00:45 | 2021-11-03 11:00:55 | 0 |
9 | 105 | 9003 | 2021-11-03 11:00:53 | 2021-11-03 11:00:59 | 0 |
10 | 101 | 9002 | 2021-11-04 11:00:55 | 2021-11-04 11:00:59 | 0 |
(uid-用户ID, artical_id-文章ID, in_time-进入时间, out_time-离开时间, sign_in-是否签到)
第一步:找出用户活跃表
SELECT DISTINCT uid,DATE(in_time) AS dt FROM tb_user_log
UNION
SELECT DISTINCT uid,DATE(out_time) AS dt FROM tb_user_log
101 | 2021-11-01 |
102 | 2021-11-01 |
103 | 2021-11-01 |
101 | 2021-11-02 |
103 | 2021-11-02 |
104 | 2021-11-02 |
101 | 2021-11-03 |
104 | 2021-11-03 |
105 | 2021-11-03 |
101 | 2021-11-04 |
第二步:找出次活日期和首次登录日期,并判断新用户和次留新用户。
SELECT uid,dt,
MIN(dt) OVER (PARTITION BY uid) AS new_dt, --首次登录
LEAD(dt,1) OVER (PARTITION BY uid ORDER BY dt) AS next_dt --次活
FROM(
SELECT DISTINCT uid,DATE(in_time) AS dt FROM tb_user_log
UNION
SELECT DISTINCT uid,DATE(out_time) AS dt FROM tb_user_log
)act_table
WHERE DATE_FORMAT(dt,'%Y%m')='202111'
LEAD(dt,1) OVER (PARTITION BY uid ORDER BY dt) AS next_dt
通过位移窗口函数对每个用户的活跃日向上迁移一行
uid | dt | new_dt | next_dt |
101 | 2021-11-01 | 2021-11-01 | 2021-11-02 |
101 | 2021-11-02 | 2021-11-01 | 2021-11-03 |
101 | 2021-11-03 | 2021-11-01 | 2021-11-04 |
101 | 2021-11-04 | 2021-11-01 | None |
102 | 2021-11-01 | 2021-11-01 | None |
103 | 2021-11-01 | 2021-11-01 | 2021-11-02 |
103 | 2021-11-02 | 2021-11-01 | None |
104 | 2021-11-02 | 2021-11-02 | 2021-11-03 |
104 | 2021-11-03 | 2021-11-02 | None |
105 | 2021-11-03 | 2021-11-03 | None |
第三步:当dt = new_dt 则说明这个用户为新用户
当dt = new_dt 且 next_dt - new_dt = 1 则这个用户为次日活跃用户
WITH t1 AS(
SELECT uid,dt,
MIN(dt) OVER (PARTITION BY uid) AS new_dt,
LEAD(dt,1) OVER (PARTITION BY uid ORDER BY dt) AS next_dt
FROM(
SELECT DISTINCT uid,DATE(in_time) AS dt FROM tb_user_log
UNION
SELECT DISTINCT uid,DATE(out_time) AS dt FROM tb_user_log
) act_table
WHERE DATE_FORMAT(dt,'%Y%m')='202111'
)
SELECT
uid,
dt,
CASE WHEN dt=new_dt THEN 1 ELSE 0 END is_new_day,
CASE WHEN dt=new_dt AND datediff(next_dt,dt)=1 THEN 1 ELSE 0 END is_next_day
//统计两种情况,满足为1,否则为0
FROM t1;
uid | dt | is_new_day | is_next_day |
101 | 2021-11-01 | 1 | 1 |
101 | 2021-11-02 | 0 | 0 |
101 | 2021-11-03 | 0 | 0 |
101 | 2021-11-04 | 0 | 0 |
102 | 2021-11-01 | 1 | 0 |
103 | 2021-11-01 | 1 | 1 |
103 | 2021-11-02 | 0 | 0 |
104 | 2021-11-02 | 1 | 1 |
104 | 2021-11-03 | 0 | 0 |
105 | 2021-11-03 | 1 | 0 |
题目完整SQL
WITH t1 AS(
SELECT uid,dt,
MIN(dt) OVER (PARTITION BY uid) AS new_dt,
LEAD(dt,1) OVER (PARTITION BY uid ORDER BY dt) AS next_dt
FROM(
SELECT DISTINCT uid,DATE(in_time) AS dt FROM tb_user_log
UNION
SELECT DISTINCT uid,DATE(out_time) AS dt FROM tb_user_log
) act_table
WHERE DATE_FORMAT(dt,'%Y-%m')='2021-11'
)
SELECT dt,
ROUND(SUM(CASE WHEN dt=new_dt AND datediff(next_dt,dt)=1 THEN 1 ELSE 0 END)
/
SUM(CASE WHEN dt=new_dt THEN 1 ELSE 0 END),2) uv_left_rate
FROM t1
GROUP BY dt
HAVING uv_left_rate IS NOT NULL
ORDER BY dt;
2021-11-01 | 0.67 |
2021-11-02 | 1.00 |
2021-11-03 | 0.00 |