虚谷数据库求大连续天数

直接上结果

*字段说明：
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"

"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`

 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

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