对常见的SQL题型总结

已于 2024-06-30 23:44:46 修改
阅读量991 收藏 30
点赞数 20
文章标签： sql 数据库 mysql
于 2024-06-30 17:41:50 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_54572439/article/details/139999878
版权

前言：

        本文主要用来记录我在做SQL题，或者面试时所遇到的题型，怕之后太久没接触遗忘一些函数和解题思路，所以写了本文。本文大部分题型和思路主要来自某客网的SQL题，解题思路则是在评论区中选取最精妙且通俗易懂的。文章会持续更新，插入一些我需要记录的SQL题型

目录

前言：

一丶普通常见类型

1.某个字段的截断平均

2.统计某个字段已完成的总数（中途退出或者放弃不作数）

3.每个月的用户平均活跃天

4.需要生成额外的汇总行，并进行统计总和与处理空值

5.筛选出完成时间小于规定时间的一半

6.时间函数DATEDIFF和TIMESTAMPDIFF的用法和区别  

二丶窗口函数类型

1.获取两种不同类型试卷的前三名

2.用SQL的窗口函数筛选出一个月里连续三天以及以上的汽车ID和他的最大行程公里数

3.近两天的公里数情况

4.LEAD和LAG窗口函数

三丶实际应用常见题型

1.各个视频的平均完播率

2.每个类别视频的平均播放进度

3.每类视频近一个月的转发量率

4.每篇文章同一时刻最大在看人数

5.每个新用户的次日留存率

一丶普通常见类型

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，将其替换为合适的默认值。
示例数据：

regionproductamount
NorthA100
NorthB150
SouthA200
SouthB120
SouthC180
NULLB300

使用 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：这是一个聚合函数，用于计算每个组（地区和产品组合）的销售总金额。

结果：

执行上述查询后，结果会包含每个地区和产品的销售总金额，以及每个地区和产品组合的总计和总体的总计。具体结果如下：

regionproducttotal_amount
NorthA100
NorthB150
NorthTotal250
SouthA200
SouthB120
SouthC180
SouthTotal500
TotalTotal750

思路：主要使用函数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_differencehours_difference
7168

这个示例计算了两个日期和时间戳之间的差异，以天数和小时数表示，结果分别为7天和168小时。

区别

  1. 功能范围：

    • DATEDIFF 只能计算天数差。
    • TIMESTAMPDIFF 可以计算以不同单位（年、月、天、小时等）的时间差。

  2. 精度：

    • 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_idstudent_namescore
AAlice85
ABob78
ACharlie92
ADavid88
BEve90
BFrank82
BGrace95
BHelen87

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;
  1. 在内部查询中，我们仍然使用 ROW_NUMBER() 函数来找出每类试卷的前三名学生，但是在外部查询中，我们加入了 DENSE_RANK() OVER (PARTITION BY exam_id ORDER BY score DESC) 部分。
  2. DENSE_RANK() 函数计算学生在每个试卷类别中的排名，如果有相同分数的学生，将会有并列排名。
  3. 最后，通过外部的 ORDER BY exam_id, rank_num 来确保结果按照试卷类别和排名顺序排列

结果：

执行上述 SQL 查询后，将会得到包含排名数字字段的结果：

exam_idstudent_namescorerank_num
ACharlie921
ADavid882
AAlice853
BGrace951
BEve902
BHelen873

2.用SQL的窗口函数筛选出一个月里连续三天以及以上的汽车ID和他的最大行程公里数

示例数据

假设有如下的表 car_trip，其中包含汽车的行程记录：

car_idstart_timestampend_timestamptrip_distance
12024-06-01 08:00:002024-06-01 09:00:0050
12024-06-02 08:00:002024-06-02 09:00:0060
12024-06-03 08:00:002024-06-03 09:00:0070
12024-06-05 08:00:002024-06-05 09:00:0080
22024-06-10 08:00:002024-06-10 09:00:0090
22024-06-11 08:00:002024-06-11 09:00:00100
22024-06-12 08:00:002024-06-12 09:00:00110
32024-06-15 08:00:002024-06-15 09:00:00120

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_idmax_trip_distance
170
2110

解释 consecutive_days 子查询

这个子查询的目的是识别每辆车连续行程的日期分组。为了实现这一点，我们使用窗口函数和日期运算。

子查询的每一步解释：

  1. 选择字段：

    • 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。

  2. 计算分组标识：

    • 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值会相同。

  3. 示例数据和计算结果：

假设有如下数据：

| 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 的表，其中包含每月的销售数据：

monthsales_amount
2024-01-01100
2024-02-01150
2024-03-01200
2024-04-01250
2024-05-01300

使用 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;

查询结果

monthsales_amountprev_month_salesnext_month_sales
2024-01-01100NULL150
2024-02-01150100200
2024-03-01200150250
2024-04-01250200300
2024-05-01300250NULL

解释

  • prev_month_sales：使用 LAG 函数获取前一个月的销售额。
  • next_month_sales：使用 LEAD 函数获取下一个月的销售额。

  1. LAG(sales_amount, 1) OVER (ORDER BY month):

    • LAG 函数返回前一行的 sales_amount。
    • OVER (ORDER BY month) 指定了数据的排序顺序，使得 LAG 函数能正确地访问前一行的数据。

  2. 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

uid-用户ID, video_id-视频ID, start_time-开始观看时间, end_time-结束观看时间
iduidvideo_idstart_timeend_time
110120012021-10-01 10:00:002021-10-01 10:00:30
210220012021-10-01 10:00:002021-10-01 10:00:21
310320012021-10-01 11:00:502021-10-01 11:01:20
410220022021-10-01 11:00:002021-10-01 11:00:30
510320022021-10-01 10:59:052021-10-01 11:00:05

短视频信息表tb_video_info

idvideo_idauthortagduration
12001901影视30
22002901美食60
32003902旅游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

iduidartical_idin_timeout_timesign_cin
110190012021-11-01 10:00:002021-11-01 10:00:110
210290012021-11-01 10:00:092021-11-01 10:00:380
310390012021-11-01 10:00:282021-11-01 10:00:580
410490022021-11-01 11:00:452021-11-01 11:01:110
510590012021-11-01 10:00:512021-11-01 10:00:590
610690022021-11-01 11:00:552021-11-01 11:01:240
710790012021-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

iduidartical_idin_timeout_timesign_cin
110102021-11-01 10:00:002021-11-01 10:00:421
210290012021-11-01 10:00:002021-11-01 10:00:090
310390012021-11-01 10:00:012021-11-01 10:01:500
410190022021-11-02 10:00:092021-11-02 10:00:280
510390022021-11-02 10:00:512021-11-02 10:00:590
610490012021-11-02 11:00:282021-11-02 11:01:240
710190032021-11-03 11:00:55

2021-11-03 11:01:24

0
810490032021-11-03 11:00:452021-11-03 11:00:550
910590032021-11-03 11:00:532021-11-03 11:00:590
1010190022021-11-04 11:00:552021-11-04 11:00:590

（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

         通过位移窗口函数对每个用户的活跃日向上迁移一行

uiddtnew_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;

  

uiddtis_new_dayis_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

青春猪头少年不会梦见加班
关注
  • 20
    点赞
  • 踩
  • 30
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
数据库中几种常见的题型
wangwuao的专栏
03-14 1002
<br />--统计本班的平均成绩，如果平均分在70分以上，则显示“成绩优秀”，并显示出最好成绩及学员姓名，如果在70分以下则显示“成绩较差”，并显示出最差成绩及学员姓名。<br />declare @score int<br />select @score=AVG(score) from Score<br />IF(@score>70)<br />  BEGIN<br />  print '成绩优秀'<br />  --select Max(score)as '成绩',max(studentname)as
java面试宝典题型总结—张孝祥
11-27
《Java面试宝典题型总结—张孝祥》是一份由传智播客精心整理的Java面试准备资料，涵盖了广泛且深入的Java技术领域。这份资料以帮助Java开发者准备面试为目标，提供了丰富的知识点和题型，包括J2SE、J2EE、Web、EJB、...
【SQL笔试】牛客网32道SQL常见笔试题集锦包含SQL试题常见考点——建议保存(*^_^*)
量化Mike
05-29 1384
文章目录基本表1中数据1、题目：现在运营想要查看用户信息表中所有的数据，请你取出相应结果2、题目：现在运营同学想要用户的设备id对应的性别、年龄和学校的数据，请你取出相应数据3、题目：现在运营需要查看用户来自于哪些学校，请从用户信息表中取出学校的去重数据。4、题目：现在运营只需要查看前2个用户明细设备ID数据，请你从用户信息表 user_profile 中取出相应结果。5、题目：现在你需要查看前2个用户明细设备ID数据，并将列名改为 'user_infos_example',，请你从用户信息表取出相应结果。
SQL常见题型总结
星回昭以烂的博客
08-17 1187
SQL常见题型总结
常见SQL题型汇总
qq_41536774的博客
08-18 219
常见SQL题型汇总 最近从网上找到了一套sql题，基本上每个公司的笔试常见的题型都包含了 先建表 --学生表 create table Student(SId varchar(10),Sname varchar(10),Sage date,Ssex varchar(10)); insert into Student values('01' , '赵雷' , '1990-01-01' , '男'); insert into Student values('02' , '钱电' , '1990-12-21' ,
SQL常见面试题（学生表_课程表_总表）
weixin_34216107的博客
10-10 283
问题描述： 为管理岗位业务培训信息，建立3个表: S (S#,SN,SD,SA) S#,SN,SD,SA 分别代表学号、学员姓名、所属单位、学员年龄 C (C#,CN ) C#,CN 分别代表课程编号、课程名称 SC ( S#,C#,G ) S#,C#,G 分别代表学号、所选修的课程编号、学习成绩 要求实现如下5个处理： 1. 使用标准SQL嵌套语句查询选修课程名称为’税收基础’的学...
SQL面试题（常见题型）
jc
04-26 350
SQL 1.用一条SQL 语句 查询出每门课都大于80 分的学生姓名 name kecheng fenshu 张三 语文 81 张三 数学 75 李四 语文 76 李四 数学 90 王五 语文 81 王五 数学 100 王五 英语 90 select name from table group by name having min(fenshu)>80 2.删除除了自动编号不同, 其他都相同的学生冗余信息 自动编号 学号
数据库常见面试题型总结_数据库语句面试题
01-11
数据库常见面试题型总结是数据库方面常见面试题型的总结，涵盖了数据库语句、SQL 优化、Web 相关知识点等。以下是对标题和描述中所说的知识点的详细说明： 一、IN 和 EXIST 的区别 IN 和 EXISTS 是两种常见的...
数据库72种题型总结
01-11
以上内容涵盖了数据库的基本概念、SQL语句、查询优化、事务处理、视图和触发器等多个方面，是数据库面试中常见的话题。理解并掌握这些知识点对于数据库管理员、开发人员和数据分析者来说至关重要。
sql 笔试题总结版
10-29
根据给定的文件信息，以下是对SQL笔试题的...以上题目涵盖了SQL的基本语法、逻辑运算符、特殊条件语句以及一些Oracle特有的日期处理函数，是SQL面试和笔试中的常见题型。掌握这些知识点对于理解和操作数据库至关重要。
007-CTF web题型总结-第七课 CTF WEB实战练习(三).pdf
07-09
007-CTF Web题型总结的第七课主要涵盖了三个部分：入门第一部分、入门第二部分-社工篇和入门第三部分-高级篇。这些部分详细介绍了在CTF（Capture The Flag）网络安全竞赛中常见的Web挑战类型，并通过Bugku平台上的...
SQL基础知识整理--干货！常见的SQL面试题：经典50例！
最新发布
liujiaping的专栏
01-30 1481
ps：这些题考察SQL的编写能力，对于这类型的题目，需要你先把4张表之间的关联关系搞清楚了，最好的办法是自己在草稿纸上画出关联图，然后再编写对应的SQL语句就比较容易了。问题：查询与141号或174号员工的manager_id和department_id相同的其他员工的employee_id, manager_id, department_id。/ drop table …/ rename …成绩表是学生的成绩，里面有学号（学生的学号），课程号（学生选修课程的课程号），成绩（学生选修该课程取得的成绩）
多表查询建表语句
qq_21853321的博客
10-05 7万+
–1.学生表 Student(s_id,s_name,s_birth,s_sex) –学生编号,学生姓名, 出生年月,学生性别 –2.课程表 Course(c_id,c_name,t_id) – –课程编号, 课程名称, 教师编号 –3.教师表 Teacher(t_id,t_name) –教师编号,教师姓名 –4.成绩表 Score(s_id,c_id,s_score) –学生编号,课程编号,分数 –建表 –学生表 CREATE TABLE `Student`( `s_id` VARCHAR(20), `s
面试 SQL整理 常见的SQL面试题：经典50题
热门推荐
sh_c1314
09-13 26万+
目录 ​ SQL基础知识整理: 常见的SQL面试题：经典50题 三、50道面试题 2.汇总统计分组分析 3.复杂查询 sql面试题：topN问题 4.多表查询 【面试题类型总结】这类题目属于行列如何互换，解题思路如下： 其他面试题: SQL基础知识整理: select 查询结果 如:[学号,平均成绩：组函数avg(成绩)]from 从哪张表中查找数...
SQL常考笔试题
vwinner的博客
10-21 1万+
(一) 1.写出一条Sql语句：取出表A中第31到第40记录（SQLServer, 以自动增长的ID作为主键,  注意：ID可能不是连续的。）  select top 10 * from A where id not in (select top 30 id from A)  解2: select top 10 * from A where id > (selectmax(id)
经典的SQL练习50题，附答案及笔者的实践内容
Python数据之道
12-05 5396
相信大多学习了 Mysql 数据库语言的同学都会上网找练习来练手，而大部分的人肯定知道有一篇 Mysql 经典练习题50题的帖子，上面的题目基本上涵盖了 Mysql 查询语句的关键知识点...
SQL 经典50题（题目+解答）（1）
Robin_Pi的博客
03-10 1万+
SQL 经典50题（题目+解答）（1）
五大常考SQL面试题
前景
06-21 2111
大厂sql面试题
JAVA笔试重点：知识点总结与常见题型解析
13. **数据库连接与JDBC**：JDBC是Java访问数据库的标准API，涉及数据库连接、执行SQL语句、结果集处理等。 14. **Lambda表达式**：Java 8引入了Lambda表达式，简化了函数式编程，使得处理集合和事件驱动编程更加...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值