SQL中的一致间隔

Question

考虑一个简单的日记表：

Date   Mood
----   ----
1      Good
2      Good
3      Good
4      Bad
5      Bad
6      Good

我对情绪区间感兴趣，所以我会得到这样的结果：

Mood   BeginDate   EndDate
----   ---------   -------
Good   1           3
Bad    4           5 
Good   6           6

在不使用迭代的SQL中，这可能吗？

Answer 1

如果你从每个情绪的开始天数中减去一个序列号，当值相邻时，你会得到一个常数：

Date   Mood    Seqnum
1      Good       1
2      Good       2
3      Good       3
4      Bad        1
5      Bad        2
6      Good       4

然后您可以使用聚合来执行您想要的操作。如果date真的是一个数字：

select mood, min(date), max(date)
from (select t.*,
row_number() over (partition by mood order by date) as seqnum
from t
) t
group by mood, (date - seqnum);

如果date真的是date:

select mood, min(date), max(date)
from (select t.*,
row_number() over (partition by mood order by date) as seqnum
from t
) t
group by mood, dateadd(day, - seqnum, date)

Answer 2

针对Gordon Linoff的回答中的这一评论：

如果date真的是一个数字：

如果是而不是怎么办？只要稍微调整一下，你还有一条路要走。

WITH d AS (
SELECT * FROM (VALUES
(cast('2021-04-22 00:00' as datetime2(0)), 'Good'),
(cast('2021-04-22 00:05' as datetime2(0)), 'Good'),
(cast('2021-04-22 00:07' as datetime2(0)), 'Good'),
(cast('2021-04-22 00:10' as datetime2(0)), 'Bad'),
(cast('2021-04-22 00:25' as datetime2(0)), 'Bad'),
(cast('2021-04-22 01:43' as datetime2(0)), 'Good')
) AS x([date], mood)        
), t AS (
SELECT *, 
ROW_NUMBER() OVER (ORDER BY [date]) AS a,
ROW_NUMBER() OVER (PARTITION BY mood ORDER BY [date]) AS seqnum
FROM d
)
select mood, min(date), max(date)
from t
group by mood, a - seqnum
order by min(date)

细分来看，第一个cte只是您的数据，但现在有一个日期时间列，而不是date的整数。注意，连续行之间的间隔不是恒定的(它可以是，但我这样做是为了表明它不必是(。从那里，我们用row_number()函数计算两个值——一个枚举整个集合，另一个按心情分区。其余的(在精神上(与戈登的回答相同。