检测SQL中的连续日期并启动计数器

Question

日期2021-01-012021-01-022021-01-032021-01-102021-01-112021-011-122021-01-15

Answer 1

解决此类缺口和岛屿问题的一种方法是根据与前一日期的差异计算排名。

然而，计算日期之间天数差异的方法取决于您的RDBMS。

此示例使用DATEDIFF(MS Sql Server(

SELECT 
Dates, 
ROW_NUMBER() OVER (PARTITION BY Rnk ORDER BY Dates) AS Counter
FROM
(
SELECT 
Dates, 
SUM(CASE 
WHEN DATEDIFF(day, prevDate, Dates) = 1 THEN 0 
ELSE 1 END) OVER (ORDER BY Dates) AS Rnk 
FROM
(
SELECT 
Dates, 
LAG(Dates) OVER (ORDER BY Dates) AS prevDate
FROM your_table
) q1
) q2
ORDER BY Dates;

Answer 2

您可以使用self-join:

with cte as (
select row_number() over (order by dt) r, dt from test
)
select c.dt, sum(c.dt = date_add(c1.dt, interval c.r-c1.r day)) 
from cte c join cte c1 on c1.r <= c.r group by c.dt order by c.dt