常考SQL

发布时间:2024年01月17日

1 思维导图

在这里插入图片描述

2 题目

mysql8版本
在这里插入图片描述

1. 连续问题???

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

iddtlowcarbon
10012021-12-12123
10022021-12-1245
10012021-12-1343
10012021-12-1345
10012021-12-1323
10022021-12-1445
10012021-12-14230
10022021-12-1545
10012021-12-1523

第一步:创建表,语句如下:

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秒,则分为一个组。

idts(秒)
100117523641234
100117523641253
100217523641278
100117523641334
100217523641434
100117523641534
100117523641544
100217523641634
100117523641638
100117523641654

第一步:创建表

语句如下:

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天登录。数据如下:

iddt
10012021-12-12
10022021-12-12
10012021-12-13
10012021-12-14
10012021-12-16
10022021-12-16
10012021-12-19
10022021-12-17
10012021-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;

在这里插入图片描述

  1. 对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. 对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. 打折日期交叉问题??

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

brandsdtedt
oppo2021-06-052021-06-09
oppo2021-06-112021-06-21
vivo2021-06-052021-06-15
vivo2021-06-092021-06-21
redmi2021-06-052021-06-21
redmi2021-06-092021-06-15
redmi2021-06-172021-06-26
huawei2021-06-052021-06-26
huawei2021-06-092021-06-15
huawei2021-06-172021-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. 留存问题???

文章来源:https://blog.csdn.net/qq_41246557/article/details/135641595
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。