常考SQL

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. 留存问题♥♥♥

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：/a/329387.html

如若内容造成侵权/违法违规/事实不符，请联系我们进行投诉反馈qq邮箱809451989@qq.com，一经查实，立即删除！

相关文章

pyqtgraph绘图类

pyqtgraph绘图类

pyqtgraph绘图类 pyqtgraph绘图有四种方法: 方法描述pyqtgraph.plot()创建一个新的QWindow用来绘制数据PlotWidget.plot()在已存在的QWidget上绘制数据PlotItem.plot()在已存在的QWidget上绘制数据GraphicsLayout.addPlot()在网格布局中添加一个绘图 上面四个方法都接收同样…
阅读更多...
Python爬虫实战：IP代理池助你突破限制，高效采集数据

Python爬虫实战：IP代理池助你突破限制，高效采集数据

当今互联网环境中&#xff0c;为了应对反爬虫、匿名访问或绕过某些地域限制等需求&#xff0c;IP代理池成为了一种常用的解决方案。IP代理池是一个包含多个可用代理IP地址的集合&#xff0c;可以通过该代理池随机选择可用IP地址来进行网络请求。 IP代理池是一组可用的代理IP地址…
阅读更多...
【Maven】008-Maven 私服搭建与使用

【Maven】008-Maven 私服搭建与使用

【Maven】008-Maven 私服搭建与使用 文章目录 【Maven】008-Maven 私服搭建与使用一、概述1、简介2、建立私服后依赖查找和下载逻辑第一步&#xff1a;请求本地仓库第二步&#xff1a;请求 Maven 私服第三步&#xff1a;请求外部远程仓库&#xff08;远程中央仓库等&#xff09…
阅读更多...
SpringBoot教程(三) | Spring Boot初体验

SpringBoot教程(三) | Spring Boot初体验

SpringBoot教程(三) | Spring Boot初体验 上篇文章我们创建了SpringBoot 项目&#xff0c;并且进行了简单的启动。整个项目了里其实我们就动了两个文件&#xff0c;一个是pom.xml负责管理springboot的相关依赖&#xff0c;一个是springBoot的启动类。 pom文件中通过starter的…
阅读更多...
Linux环境变量配置全攻略

Linux环境变量配置全攻略

热门IT课程【视频教程】-华为/思科/红帽/oraclehttps://xmws-it.blog.csdn.net/article/details/134398330?spm1001.2014.3001.5502 Linux环境变量配置 在自定义安装软件的时候&#xff0c;经常需要配置环境变量&#xff0c;下面列举出各种对环境变量的配置方法。 下面所有例…
阅读更多...
HTML-鼠标悬浮文案效果

HTML-鼠标悬浮文案效果

文章目录 前言一、 hover属性实现二、title属性 简单实现总结 前言 有时候&#xff0c;我们浏览网站时&#xff0c;鼠标停留在某处后鼠标会提示一些文案。 一、 hover属性实现 HTML 中可以使用 CSS 来实现鼠标悬浮文案效果。 首先&#xff0c;在 HTML 文件中添加需要显示悬浮…
阅读更多...
VS打开报错 未能正确加载 Microsoft Wswalstudio.editorImplementation.editorPackage”

VS打开报错 未能正确加载 Microsoft Wswalstudio.editorImplementation.editorPackage”

VS 打开的时候报错&#xff1a; 未能正确加载 Microsoft Wswalstudio.editorImplementation.editorPackage” 此间题可能是由配查更改或安装另一个扩展导致的&#xff0c;可以通过查看文件 C:\Users\Administrator\AppData\Roaming\Microsoft\VisualStudio\11.0\ActivityLog.x…
阅读更多...
AI客服发展现状与展望：期待技术进步带来更优质的服务体验

AI客服发展现状与展望：期待技术进步带来更优质的服务体验

近年来&#xff0c;随着人工智能技术的飞速发展&#xff0c;越来越多的企业开始采用AI客服&#xff0c;以提高效率、降低成本。然而&#xff0c;一些用户反映AI客服存在回答不准确、难以理解个性化问题等问题&#xff0c;引发了对智能客服发展现状的关注。 在北京市民邹女士的…
阅读更多...
js的防抖与节流

js的防抖与节流

目录 认识防抖与节流防抖节流 手写防抖函数绑定this与参数取消功能立即执行获取返回值最终版 手写节流函数 认识防抖与节流 在JavaScript中&#xff0c;大量操作都会触发事件&#xff0c;这些事件又会被添加到事件队列中进行排队处理 某些事件如果频繁触发的话会对浏览器的性能…
阅读更多...
服务器变矿机，该如何应对？

服务器变矿机，该如何应对？

开始 恶意的挖矿程序会导致服务器cpu的异常占用&#xff0c;很让人讨厌。起初&#xff0c;我只是使用top命令显示出占用cpu不正常的进程&#xff0c;发现其中一个进程占用了百分之九十九点几&#xff0c;然后通过kill -9 <PID>命令干掉它。但总是过不了几天&#xff0c;…
阅读更多...
Windows系统字体尺寸学习

Windows系统字体尺寸学习

调用GetTextMetrics来获得字体尺寸信息, 函数返回设备描述表中当前选定的字体信息&#xff1b; 返回值到TEXTMETRIC类型的结构中&#xff1b; 返回字段值的单位取决于当前设备描述表映射方式&#xff1b;默认映射方式是MM_TEXT&#xff0c;值的单位是像素&#xff1b; 前7个字…
阅读更多...
【MATLAB源码-第113期】基于matlab的孔雀优化算法(POA)机器人栅格路径规划，输出做短路径图和适应度曲线。

【MATLAB源码-第113期】基于matlab的孔雀优化算法(POA)机器人栅格路径规划，输出做短路径图和适应度曲线。

操作环境&#xff1a; MATLAB 2022a 1、算法描述 POA&#xff08;孔雀优化算法&#xff09;是一种基于孔雀羽毛开屏行为启发的优化算法。这种算法模仿孔雀通过展开其色彩斑斓的尾羽来吸引雌性的自然行为。在算法中&#xff0c;每个孔雀代表一个潜在的解决方案&#xff0c;而…
阅读更多...
CSS3弹性盒布局详解

CSS3弹性盒布局详解

CSS3的弹性盒布局 简介 弹性盒&#xff08; Flexible Box 或 Flexbox&#xff09; 布局是CSS3提供的一种新的布局模式&#xff0c;是一种当页面需要适应不同的屏幕大小及设备类型时&#xff0c;确保元素拥有恰当行为的一种布局方式。 弹性盒的结构: 从图中所知&#xff0c…
阅读更多...
K8s（一）Pod资源——Pod介绍、创建Pod、Pod简单资源配额

K8s（一）Pod资源——Pod介绍、创建Pod、Pod简单资源配额

目录 Pod概述 pod网络 pod存储 pod和容器对比 创建pod的方式 pod运行方式分类 Pod的创建 Pod的创建过程 通过kubectl run来创建pod 通过yaml文件创建&#xff0c;yaml文件简单写法 Pod简单操作 Pod的标签labels Pod的资源配额resource 测试 Pod概述 Kubernetes …
阅读更多...
嵌入式学习-网络编程-Day4

嵌入式学习-网络编程-Day4

思维导图 广播通信模型 #include <myhead.h> int main(int argc, char const *argv[]) {int rfd socket(AF_INET,SOCK_DGRAM,0);if(rfd-1){perror("socket error");return -1;}//填充地址信息结构体struct sockaddr_in rin;rin.sin_familyAF_INET;rin.sin_por…
阅读更多...
[ceph] ceph应用

[ceph] ceph应用

一、资源池 Pool 管理 #创建一个 Pool 资源池&#xff0c;其名字为 mypool&#xff0c;PGs 数量设置为 64&#xff0c;设置 PGs 的同时还需要设置 PGP&#xff08;通常PGs和PGP的值是相同的&#xff09;&#xff1a; PG (Placement Group)&#xff0c;pg 是一个虚拟的概念&…
阅读更多...
基于变换域的模版匹配

基于变换域的模版匹配

模板匹配原理 图像的空间域与其他域之间的变换&#xff0c;如傅里叶变换&#xff0c;小波变换&#xff0c;轮廓波变换&#xff0c;剪切波变换等&#xff0c;实际上是图像在其他坐标领域中的表现。在空间域中&#xff0c;图像的信息是像素值和坐标位置&#xff1b;在其他域中&a…
阅读更多...
交通流量预测HSTGCNT：Hierarchical Spatio–Temporal Graph Convolutional

交通流量预测HSTGCNT：Hierarchical Spatio–Temporal Graph Convolutional

Hierarchical Spatio–Temporal Graph Convolutional Networks and Transformer Network for Traffic Flow Forecasting 交通流预测的层次时空图卷积网络和Transformer网络 Abstract 图卷积网络&#xff08;GCN&#xff09;具有图形化描述道路网络不规则拓扑结构的能力&#…
阅读更多...
【C语言】数据结构——排序三（归并与计数排序）

【C语言】数据结构——排序三（归并与计数排序）

&#x1f497;个人主页&#x1f497; ⭐个人专栏——数据结构学习⭐ &#x1f4ab;点击关注&#x1f929;一起学习C语言&#x1f4af;&#x1f4ab; 目录 导读&#xff1a;1. 归并排序1.1 基本思想1.2 递归实现1.3 非递归实现 2. 计数排序2.1 基本思想2.2 代码实现 导读&#x…
阅读更多...
如何用Python进行数据分析(保姆级教程）

如何用Python进行数据分析(保姆级教程）

有小伙伴在学Python新手教程的时候说学Python比较复杂的地方就是资料太多了&#xff0c;比较复杂。 很多网上的资料都是从语法教起的&#xff0c;花了很多时间还是云里雾里&#xff0c;摸不清方向。今天就给大家来捋一捋思路&#xff01;帮助大家提高学习效率&#xff01; Pyt…
阅读更多...
最新文章

Copyright @ 2022~2023