常考SQL_如下数据为蚂蚁森林中用户领取的减少碳排放量

1 思维导图

2 题目

mysql8版本

1. 连续问题♥♥♥

  问题描述：如下数据为蚂蚁森林中用户领取的减少碳排放量，找出连续3天及以上减少碳排量在100以上的用户。

第一步：创建表，语句如下：

DROP TABLE IF EXISTS `carbon`;
CREATE TABLE `carbon`  (
  `id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `dt` date NULL DEFAULT NULL,
  `lowcarbon` int(11) NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
1
2
3
4
5
6

第二步：插入数据，语句如下：

INSERT INTO `carbon` VALUES ('1001', '2021-12-12', 123);
INSERT INTO `carbon` VALUES ('1002', '2021-12-12', 45);
INSERT INTO `carbon` VALUES ('1001', '2021-12-13', 43);
INSERT INTO `carbon` VALUES ('1001', '2021-12-13', 45);
INSERT INTO `carbon` VALUES ('1001', '2021-12-13', 23);
INSERT INTO `carbon` VALUES ('1002', '2021-12-14', 45);
INSERT INTO `carbon` VALUES ('1001', '2021-12-14', 230);
INSERT INTO `carbon` VALUES ('1002', '2021-12-15', 45);
INSERT INTO `carbon` VALUES ('1001', '2021-12-15', 23);
1
2
3
4
5
6
7
8
9

第三步 思路分析：

1. 按照用户的id，领取减少碳排放量时间dt分组，同时组内过滤掉lowcarbon <=100 的，sql语句如下。查询结果作为中间表t:

select
	id, dt, sum(lowcarbon) lowcarbon
from
	carbon 
group by id, dt 
having lowcarbon > 100;
1
2
3
4
5
6

2. 对中间表t进行ROW_NUMBER()，加上行号,SQL语句如下。查询结果作为中间表t1。

select id, dt, lowcarbon,
ROW_NUMBER() over (partition by id ORDER BY dt) rk
from
(select
	id, dt, sum(lowcarbon) lowcarbon
from
	carbon 
group by id, dt 
having lowcarbon > 100)t;
1
2
3
4
5
6
7
8
9

3. 对中间表t1求dt与rk的差值得到新的属性new_dt,SQL语句如下。同时结果作为中间值t2。

select id, dt, lowcarbon, rk,
DATE_SUB(dt, INTERVAL rk DAY) new_dt
from
(
select id, dt, lowcarbon,
ROW_NUMBER() over (partition by id ORDER BY dt) rk
from
(select
	id, dt, sum(lowcarbon) lowcarbon
from
	carbon 
group by id, dt 
having lowcarbon > 100)t
)t1;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

4. 对t2表按照id, new_dt分组，分组内数据量>=3即为符合要求的用户。sql代码如下:

select id 
from
(
	select id, dt, lowcarbon, rk,
	DATE_SUB(dt, INTERVAL rk DAY) new_dt
	from
	(
	select id, dt, lowcarbon,
	ROW_NUMBER() over (partition by id ORDER BY dt) rk
	from
	(select
		id, dt, sum(lowcarbon) lowcarbon
	from
		carbon 
	group by id, dt 
	having lowcarbon > 100)t
	)t1
)t2 group by id, new_dt having count(id) >= 3;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

2. 分组问题♥♥

  问题描述：如下为电商公司用户访问时间数据，某个用户连续的访问记录如果时间间隔小于60秒，则分为一个组。

第一步：创建表

语句如下：

DROP TABLE IF EXISTS `group_table`;
CREATE TABLE `group_table`  (
  `id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `ts` bigint NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = DYNAMIC;
1
2
3
4
5

第二步：插入数据

语句如下:

INSERT INTO `group_table` VALUES ('1001', 17523641234);
INSERT INTO `group_table` VALUES ('1001', 17523641253);
INSERT INTO `group_table` VALUES ('1001', 17523641334);
INSERT INTO `group_table` VALUES ('1001', 17523641534);
INSERT INTO `group_table` VALUES ('1001', 17523641544);
INSERT INTO `group_table` VALUES ('1001', 17523641638);
INSERT INTO `group_table` VALUES ('1001', 17523641654);
INSERT INTO `group_table` VALUES ('1002', 17523641278);
INSERT INTO `group_table` VALUES ('1002', 17523641434);
INSERT INTO `group_table` VALUES ('1002', 17523641634);
1
2
3
4
5
6
7
8
9
10

第三步：思路分析

1. 按照用户id进行分区，并在分区内按照ts升序，使用lag()取上一行的ts,用当前ts-上一行ts。sql语句如下。查询的结果记为t1表

select id , ts,
	ts - LAG(ts, 1, 0) over (partition by id order by ts) diff_ts
from 
	group_table;
1
2
3
4

2. 对t1表中的diff_ts字段进行转换操作，把大于等于60的转换为1，小于60的转换为0。对转换后的数据进行累加，从首行累加到当前行的值即为该行的分组。sql语句如下:

select id, ts,diff_ts,
	sum(if(diff_ts >= 60, 1, 0)) over(partition by id order by ts) groupid
FROM
(
select id , ts,
	ts - LAG(ts, 1, 0) over (partition by id order by ts) diff_ts
from 
	group_table
)t1;
1
2
3
4
5
6
7
8
9

3. 间隔连续问题♥

  问题描述：某游戏公司记录的用户每日登录数据，计算每个用户最大的连续登录天数，可以间隔一天。解释：如果一个用户在1，3，5，6登录游戏，则视为连续6天登录。数据如下：

第一步：创建表

sql语句如下：

DROP TABLE IF EXISTS `lianxu_table`;
CREATE TABLE `lianxu_table`  (
  `id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `dt` date NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
1
2
3
4
5

第二步：插入数据

sql语句如下:

INSERT INTO `lianxu_table` VALUES ('1001', '2021-12-19');
INSERT INTO `lianxu_table` VALUES ('1002', '2021-12-17');
INSERT INTO `lianxu_table` VALUES ('1001', '2021-12-13');
INSERT INTO `lianxu_table` VALUES ('1001', '2021-12-16');
INSERT INTO `lianxu_table` VALUES ('1001', '2021-12-14');
INSERT INTO `lianxu_table` VALUES ('1002', '2021-12-16');
INSERT INTO `lianxu_table` VALUES ('1001', '2021-12-12');
INSERT INTO `lianxu_table` VALUES ('1002', '2021-12-12');
INSERT INTO `lianxu_table` VALUES ('1001', '2021-12-20');
1
2
3
4
5
6
7
8
9

第三步：需求分析

1. 根据用户id进行分区，分区内按照dt进行升序排序。使用lag(dt, 1, ‘2000-10-10’)取上一行的dt，记为pre_dt,中间查询结果记为t1，查询语句如下:

select id, dt ,
	lag(dt, 1, '2000-10-10') over (partition by id order by dt) pre_dt
from lianxu_table;
1
2
3

2. 对t1表按照用户id分区，dt进行升序排序，求dt-pre_dt 记为相差天数diff_day,查询结果记为t2。查询语句如下:

select id, dt, pre_dt,
	DATEDIFF(dt,pre_dt) diff_day 
from 
(
select id, dt ,
	lag(dt, 1, '2000-10-10') over (partition by id order by dt) pre_dt
from lianxu_table
)t1;
1
2
3
4
5
6
7
8

3. 对t2表中的diff_day 进行分组，diff_day <= 2算同一组，diff_day > 2算新的一组，查询结果记为t3。查询语句如下:

select id, dt, pre_dt, diff_day,
	sum(if(diff_day > 2, 1, 0)) over(partition by id order by dt) groupid
from
(
	select id, dt, pre_dt,
	DATEDIFF(dt,pre_dt) diff_day 
	from 
	(
		select id, dt ,
			lag(dt, 1, '2000-10-10') over (partition by id order by dt) pre_dt
		from lianxu_table
	)t1
)t2;
1
2
3
4
5
6
7
8
9
10
11
12
13

4. 对t3表，按照用户id和groupid进行分组，求分组内的个数，个数值即为用户连续登录的天数。查询语句如下:

select id, count(id) lianxu_day
from 
(
	select id, dt, pre_dt, diff_day,
		sum(if(diff_day > 2, 1, 0)) over(partition by id order by dt) groupid
	from
	(
		select id, dt, pre_dt,
		DATEDIFF(dt,pre_dt) diff_day 
		from 
		(
			select id, dt ,
				lag(dt, 1, '2000-10-10') over (partition by id order by dt) pre_dt
			from lianxu_table
		)t1
	)t2
)t3 GROUP BY id, groupid;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

4. 打折日期交叉问题♥♥

  问题描述：如下为平台商品促销数据；字段为品牌，打折开始日期，打折结束日期，计算每个品牌总的打折销售天数，注意其中的交叉日期

第一步：创建表

语句如下：

DROP TABLE IF EXISTS `dazhe_tablle`;
CREATE TABLE `dazhe_tablle`  (
  `brand` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `sdt` date NULL DEFAULT NULL,
  `edt` date NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
1
2
3
4
5
6

第二步：插入数据

语句如下:

INSERT INTO `dazhe_tablle` VALUES ('oppo', '2021-06-05', '2021-06-09');
INSERT INTO `dazhe_tablle` VALUES ('oppo', '2021-06-11', '2021-06-21');
INSERT INTO `dazhe_tablle` VALUES ('vivo', '2021-06-05', '2021-06-15');
INSERT INTO `dazhe_tablle` VALUES ('vivo', '2021-06-09', '2021-06-21');
INSERT INTO `dazhe_tablle` VALUES ('redmi', '2021-06-05', '2021-06-21');
INSERT INTO `dazhe_tablle` VALUES ('redmi', '2021-06-09', '2021-06-15');
INSERT INTO `dazhe_tablle` VALUES ('redmi', '2021-06-17', '2021-06-26');
INSERT INTO `dazhe_tablle` VALUES ('huawei', '2021-06-05', '2021-06-26');
INSERT INTO `dazhe_tablle` VALUES ('huawei', '2021-06-09', '2021-06-15');
INSERT INTO `dazhe_tablle` VALUES ('huawei', '2021-06-17', '2021-06-21');
1
2
3
4
5
6
7
8
9
10

第三步：需求分析

1. 求出当前行的前面所有行的终止时间的最大值记为字段maxEdt，结果记为t1表。sql语句如下:

select brand, sdt, edt,
	max(edt) over (partition by brand order by edt rows BETWEEN unbounded preceding and 1 preceding) maxEdt 
from dazhe_tablle;
1
2
3

2. 在t1表中使用edt - max(sdt, maxEdt),如果maxEdt为null或者sdt大于maxEdt，结果+1，计算结果为当前记录活动持续时间，记为dazhe_day_num。中间查询结果记为t2。sql语句如下：

select brand, sdt, edt, maxEdt,
	DATEDIFF(edt, 
		CASE 
			WHEN maxEdt is NULL THEN DATE_SUB(sdt,INTERVAL 1 DAY) 
				WHEN sdt > maxEdt THEN DATE_SUB(sdt,INTERVAL 1 DAY) 
				ELSE maxEdt
		END
	) dazhe_day_num
from 
(
	select brand, sdt, edt,
		max(edt) over (partition by brand order by edt rows BETWEEN unbounded preceding and 1 preceding) maxEdt 
	from dazhe_tablle
)t1;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

3. 对t2表按brand进行分组，然后对dazhe_day_num求和，即为答案。sql语句如下:

select brand, sum(dazhe_day_num) tatal_day
from 
select brand, sum(dazhe_day_num) tatal_day
from 
(
	select brand, sdt, edt, maxEdt,
		DATEDIFF(edt, 
			CASE 
				WHEN maxEdt is NULL THEN DATE_SUB(sdt,INTERVAL 1 DAY) 
				WHEN sdt > maxEdt THEN DATE_SUB(sdt,INTERVAL 1 DAY) 
				ELSE maxEdt
			END
		) dazhe_day_num
	from 
	(
		select brand, sdt, edt,
			max(edt) over (partition by brand order by edt rows BETWEEN unbounded preceding and 1 preceding) maxEdt 
		from dazhe_tablle
	)t1
)t2 GROUP BY brand;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

5. 同时在线问题♥♥♥

  问题描述：如下为某平台主播开播及关播时间，根据该数据计算出平台最高峰同时在线的主播人数。数据如下:

第一步：创建表

语句如下:

DROP TABLE IF EXISTS `zaixian_table`;
CREATE TABLE `zaixian_table`  (
  `id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `sdt` datetime NULL DEFAULT NULL,
  `edt` datetime NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
1
2
3
4
5
6

第二步:插入数据

语句如下:

INSERT INTO `zaixian_table` VALUES ('1001', '2021-06-14 12:12:12', '2021-06-14 18:12:12');
INSERT INTO `zaixian_table` VALUES ('1003', '2021-06-14 13:12:12', '2021-06-14 16:12:12');
INSERT INTO `zaixian_table` VALUES ('1004', '2021-06-14 13:15:12', '2021-06-14 20:12:12');
INSERT INTO `zaixian_table` VALUES ('1002', '2021-06-14 15:12:12', '2021-06-14 16:12:12');
INSERT INTO `zaixian_table` VALUES ('1005', '2021-06-14 15:18:12', '2021-06-14 20:12:12');
INSERT INTO `zaixian_table` VALUES ('1001', '2021-06-14 20:12:12', '2021-06-14 23:12:12');
INSERT INTO `zaixian_table` VALUES ('1006', '2021-06-14 21:12:12', '2021-06-14 23:15:12');
INSERT INTO `zaixian_table` VALUES ('1007', '2021-06-14 22:12:12', '2021-06-14 23:10:12');
1
2
3
4
5
6
7
8

第三步：思路分析

1. 在一条记录中，把开始直播时间sdt和结束直播时间edt拆分为两个表，开始直播sdt添加一个增加在线人数数量标签，并置为1，表示在线人数+1。在结束直播edt添加一个增加在线人数数量标签，并置为-1，表示在线人数-1。中间查询结果记为t1。sql语句如下:

select id, sdt dt, 1 as flag 
from zaixian_table
union 
select id, edt dt, -1 as flag 
from zaixian_table;
1
2
3
4
5

2. 在t1表中按照dt时间升序进行窗口计算，统计当前时间在线人数，查询结果记为t2。sql语句如下:

select dt, sum(flag) over(order by dt) cnt 
from 
(
	select id, sdt dt, 1 as flag 
	from zaixian_table
	union 
	select id, edt dt, -1 as flag 
	from zaixian_table
)t1
1
2
3
4
5
6
7
8
9

3. 在t2表中求最大值即为在线人数的最大值。sql如下：

select max(cnt)
from 
(
	select dt, sum(flag) over(order by dt) cnt 
	from 
	(
		select id, sdt dt, 1 as flag 
		from zaixian_table
		union 
		select id, edt dt, -1 as flag 
		from zaixian_table
	)t1
)t2;

1
2
3
4
5
6
7
8
9
10
11
12
13
14