常考SQL

1 思维导图

2 题目

mysql8版本

1. 连续问题???

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

id	dt	lowcarbon
1001	2021-12-12	123
1002	2021-12-12	45
1001	2021-12-13	43
1001	2021-12-13	45
1001	2021-12-13	23
1002	2021-12-14	45
1001	2021-12-14	230
1002	2021-12-15	45
1001	2021-12-15	23

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

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;

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

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

select
	id, dt, sum(lowcarbon) lowcarbon
from
	carbon 
group by id, dt 
having lowcarbon > 100;

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;

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;

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;

2. 分组问题??

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

id	ts（秒）
1001	17523641234
1001	17523641253
1002	17523641278
1001	17523641334
1002	17523641434
1001	17523641534
1001	17523641544
1002	17523641634
1001	17523641638
1001	17523641654

第一步：创建表

语句如下：

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;

第二步：插入数据

语句如下:

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. 按照用户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;

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;

3. 间隔连续问题?

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

id	dt
1001	2021-12-12
1002	2021-12-12
1001	2021-12-13
1001	2021-12-14
1001	2021-12-16
1002	2021-12-16
1001	2021-12-19
1002	2021-12-17
1001	2021-12-20

第一步：创建表

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;

第二步：插入数据

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. 根据用户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;

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;

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;

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;

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

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

brand	sdt	edt
oppo	2021-06-05	2021-06-09
oppo	2021-06-11	2021-06-21
vivo	2021-06-05	2021-06-15
vivo	2021-06-09	2021-06-21
redmi	2021-06-05	2021-06-21
redmi	2021-06-09	2021-06-15
redmi	2021-06-17	2021-06-26
huawei	2021-06-05	2021-06-26
huawei	2021-06-09	2021-06-15
huawei	2021-06-17	2021-06-21

第一步：创建表

语句如下：

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;

第二步：插入数据

语句如下:

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. 求出当前行的前面所有行的终止时间的最大值记为字段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;

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

select brand, sdt, edt, maxEdt,
	DATEDIFF(edt, 
		CASE 
			WHEN maxEdt is NULL THEN sdt 
			WHEN sdt > maxEdt THEN sdt
			ELSE DATE_ADD(maxEdt,INTERVAL 1 DAY)
		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;

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

select brand, sum(dazhe_day_num) tatal_day
from 
(
	select brand, sdt, edt, maxEdt,
		DATEDIFF(edt, 
			CASE 
				WHEN maxEdt is NULL THEN sdt 
				WHEN sdt > maxEdt THEN sdt
				ELSE DATE_ADD(maxEdt,INTERVAL 1 DAY)
			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;

5. 同时在线问题???

6. 最大连续登录的最大天数问题???

7. 留存问题???