计算最大的连续日期天数

思路：将日期天数与行号的差值作为分组条件

SELECT
DATE,
TO_DAYS(DATE)-ROW_NUMBER() OVER () DIFF -- 分组
FROM DATE
ORDER BY DATE

 

 

SELECT DIFF,COUNT(*) FROM
(SELECT
DATE,
TO_DAYS(DATE)-ROW_NUMBER() OVER () DIFF -- 分组
FROM DATE
ORDER BY DATE
) T
GROUP BY DIFF