常考SQL

已于 2024-01-18 15:32:07 修改
阅读量1.1k 收藏 20
点赞数 26
分类专栏： 大数据面试题 # 大数据开发 文章标签： sql 服务器 数据库
于 2024-01-17 22:57:53 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_41246557/article/details/135641595
版权

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为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. 对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;

在这里插入图片描述

5. 同时在线问题♥♥♥

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

idsdtedt
10012021-06-14 12:12:122021-06-14 18:12:12
10032021-06-14 13:12:122021-06-14 16:12:12
10042021-06-14 13:15:122021-06-14 20:12:12
10022021-06-14 15:12:122021-06-14 16:12:12
10052021-06-14 15:18:122021-06-14 20:12:12
10012021-06-14 20:12:122021-06-14 23:12:12
10062021-06-14 21:12:122021-06-14 23:15:12
10072021-06-14 22:12:122021-06-14 23:10:12

第一步：创建表

语句如下:

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;

第二步:插入数据

语句如下:

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. 在一条记录中，把开始直播时间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;

在这里插入图片描述
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

在这里插入图片描述
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;

在这里插入图片描述

韩顺平的小迷弟
关注
  • 26
    点赞
  • 踩
  • 20
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
Hive SQL 练习
bangqin's blog
08-03 1059
hive 数据分析练习题
Hive SQL 五大经典面试题
热门推荐
TRX的博客
04-04 1万+
第 1 题 连续问题 第 2 题 分组问题 第 3 题 间隔连续问题 第 4 题 打折日期交叉问题 第 5 题 同时在线问题
Spark代码可读性与性能优化——示例十一（SQL与代码-蚂蚁森林示例）
蒋含竹的博客
05-31 731
文章目录Spark代码可读性与性能优化——示例十一（SQL与代码-蚂蚁森林示例）前言业务描述使用SQL解决业务问题 - 方案1使用SQL解决业务问题 - 方案2使用代码带来更高的性能 Spark代码可读性与性能优化——示例十一（SQL与代码-蚂蚁森林示例） 前言 编写SQL处理业务问题，通常有简单易用、便捷、适用人群广泛等优点，是数据分析师的不二之选。 但是，SQL易用的同时也带来了性能的问题，当为了解决某些复杂的业务时，你不得不编写几十至几百行很复杂的SQL来处理。由于为了实现复杂的业务，SQL中会存在
蚂蚁森林数据库造数
innovation的博客
08-21 1742
基于之前的数据库建模方法，我们设计好数据库之后还需要往数据库中填充数据以供程序测试使用 建表如下： energy friend steal user 我们需要往该空数据库中填入适当的测试数据，虽然各个表之间有主外键关系，但是我们建库时不需要考虑外键（很多大公司也是这么做），暂时只要给到合适的数据类型和数据范围就行了。 观察得到其中需要随机产生的数据类型大概有： 整形，可变长字符串，时间日...
蚂蚁金服试题hive/sql/hql
JavaBigData的博客
11-29 490
d 原理如图 SELECT username,time,SUM(carbon_g) c2,ROW_NUMBER() OVER(PARTITION by username order by time ) rn FROM test191128.user_low_carbon GROUP by username,time HAVING c2 >=100 //t1 SEL...
常考SQL语句.pdf
06-21
常用的SQL语句笔记，采用例题的形式进行增、删、改、查等基本操作，可用于参考去做数据库的课后习题，也可当作数据库课后练习来做
Sql Server 课程考试系统
09-14
Sql Server课程无纸化考试系统，可以单机运行，也可以部署在网络上。系统面向参加计算机等级考试的考生或计算机相关课程的教师。试题包括选择、判断、填空、改错、编程、数据库操作等25种题型，系统为作者近20年开发...
笔试常考SQL题汇总
03-21
在SQL查询中，行列转换是一种常见的操作，尤其在数据分析和报表生成时十分关键。本题主要涉及了如何将数据从行格式转换为列格式，并提供了两种解决方案：静态SQL和动态SQL。 首先，我们看静态SQL的实现。在这个例子...
关系代数sql考试关系代数sql考试
05-07
关系代数sql考试关系代数sql考试关系代数sql考试关系代数sql考试关系代数sql考试关系代数sql考试关系代数sql考试关系代数sql考试关系代数sql考试关系代数sql考试关系代数sql考试关系代数sql考试关系代数sql考试关系...
Sql Server-课程考试系统
03-28
Sql Server课程无纸化考试系统，可以单机运行，也可以部署在网络上。系统面向参加计算机等级考试的考生或计算机相关课程的教师。试题包括选择、判断、填空、改错、编程、数据库操作等25种题型，系统为作者近20年开发...
HIVE常见五种时间交叉及连续登录类问题
西贝没有横
04-29 1110
连续问题： 如下数据为蚂蚁森林中用户领取的减少碳排放量，找出连续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
hive hql 5道经典面试题
weixin_52953102的博客
02-24 316
hive 经典5道面试题
5道Hive典型题目解析
南洲.的博客
09-08 1327
一、连续问题 如下数据为蚂蚁森林中用户领取的低碳排放量（lowcarbon） 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 … … 找出连续 3 天及以上领取的
有关蚂蚁森林植物申领统计题目(Hive 实例)
weixin_46602525的博客
06-12 928
蚂蚁森林植物申领统计 创建两个表 user_low_carbon：记录了用户每天的蚂蚁森林低碳生活领取的记录流水 plant_carbon：蚂蚁森林植物换购表，用于记录申领环保植物所需要减少的碳排放量 表结构 表一 table_name：user_low_carbon user_id data_dt low_carbon 用户 日期 领取碳排放（g） 表二 table_name: plant_carbon plant_id plant_name low_carbon 植
Hive hql 经典5道面试题
qq_32656561的博客
01-25 7760
最近在深入了解Hive，尚硅谷的这5道题很经典，有引导意义，分步解题也很有用，故记录之，方便回看 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.
Hive常见面试题整理(HQL)
zhaozhufeng的博客
12-06 4288
Hive常见面试题整理(HQL)
面试常考的sql语句
最新发布
05-16
面试中常考的 SQL 语句包括： 1. SELECT 语句：用于查询表中的数据，可以使用聚合函数、WHERE 子句、ORDER BY 子句等。 2. INSERT INTO 语句：用于向表中插入新的行或数据。 3. UPDATE 语句：用于更新表中已有的...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值