虚谷数据库求最大连续天数

最新推荐文章于 2024-07-01 10:34:25 发布

q838459755

最新推荐文章于 2024-07-01 10:34:25 发布

阅读量418

点赞数

分类专栏： sql 文章标签：数据库 sql

本文链接：https://blog.csdn.net/q838459755/article/details/121553127

版权

SQL查询数据连续性 RANK函数时间序列分析数据分组

关键词由CSDN通过智能技术生成

sql 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

直接上结果

*字段说明：
station_id_d 站点ID
DATETIME 时间
*

SELECT
	station_id_d,
	MAX(days) total
FROM
	(
	SELECT
		station_id_d, count(1) days , d_r + MAX(RANK) date_time
	FROM
		(
		SELECT
			RANK() OVER (PARTITION BY `station_id_d`
		ORDER BY
			`DATETIME` )AS RANK, station_id_d , `DATETIME`-RANK d_r, `DATETIME`
		FROM
			CMADB.t_aws_realtime
		WHERE
			pre_time_2020 = 0
			AND `DATETIME` BETWEEN '2021-07-04 00:00:00' AND '2021-07-13 00:00:00' )
	GROUP BY
		station_id_d, d_r )
GROUP BY
	station_id_d
HAVING 
	total>1

拆解：

1)：
--将station_id_d分区，并在分区的基础上将时间排序，所得序列号 别名为rank
--用datetime-rank 所得的日期 别名为d_r

	SELECT 
			RANK() OVER (PARTITION BY `station_id_d`
		ORDER BY
			`DATETIME` )AS RANK, station_id_d , `DATETIME`-RANK d_r, `DATETIME`
		FROM
			CMADB.t_aws_realtime
		WHERE
			pre_time_2020 = 0
			AND `DATETIME` BETWEEN '2021-07-04 00:00:00' AND '2021-07-13 00:00:00'

"RANK"	STATION_ID_D		D_R						"DATETIME"
	1		50136		2021-07-06 00:00:00		2021-07-07 00:00:00
	2		50136		2021-07-10 00:00:00		2021-07-12 00:00:00
	1		50137		2021-07-03 00:00:00		2021-07-04 00:00:00
	2		50137		2021-07-04 00:00:00		2021-07-06 00:00:00
	3		50137		2021-07-04 00:00:00		2021-07-07 00:00:00
	4		50137		2021-07-04 00:00:00		2021-07-08 00:00:00
	5		50137		2021-07-04 00:00:00		2021-07-09 00:00:00
	6		50137		2021-07-05 00:00:00		2021-07-11 00:00:00
	7		50137		2021-07-05 00:00:00		2021-07-12 00:00:00

-2)：
--将所求的d_r进行分组，求同组d_r下有多少条数据，（相同d_r表示的就是连续）
--dr+max(rank) 目的是还原datetime

	SELECT
		station_id_d, count(1) days , d_r + MAX(RANK) date_time
	FROM
		(
		SELECT
			RANK() OVER (PARTITION BY `station_id_d`
		ORDER BY
			`DATETIME` )AS RANK, station_id_d , `DATETIME`-RANK d_r, `DATETIME`
		FROM
			CMADB.t_aws_realtime
		WHERE
			pre_time_2020 = 0
			AND `DATETIME` BETWEEN '2021-07-04 00:00:00' AND '2021-07-13 00:00:00' )
	GROUP BY
		station_id_d, d_r

	STATION_ID_D  DAYS	DATE_TIME
		50136		1	2021-07-07 00:00:00
		50136		1	2021-07-12 00:00:00
		50137		2	2021-07-12 00:00:00
		50137		1	2021-07-04 00:00:00
		50137		4	2021-07-09 00:00:00
		50246		2	2021-07-09 00:00:00
		50246		1	2021-07-12 00:00:00
		50247		1	2021-07-08 00:00:00
		50247		1	2021-07-04 00:00:00
		50247		1	2021-07-12 00:00:00
		50349		1	2021-07-12 00:00:00
		50353		2	2021-07-12 00:00:00
		50353		3	2021-07-09 00:00:00