獲取連續日期的計數

我想獲取有連續日期的日期列表及其相應的計數。

例如，如果我有以下數據集

Date
2021-07-28
2021-07-27
2021-07-26
2021-07-25
2021-07-24
2021-07-23
2021-07-22
2021-07-21
2021-07-18
2021-07-17
2021-07-14
2021-07-11
2021-07-09
2021-07-06
2021-07-04
2021-07-03
2021-07-02

我想要的結果是連續日期計數 > x

2021-07-21  8
2021-07-17  2
2021-07-02  3

我不確定如何解決這個問題。如果可以為查詢提供解釋，那就太好了，儘管不是必需的。

正如Charlieface正確指出的那樣，這是一個差距和島嶼問題。解決這個特定變化的另一種方法——也涉及一個視窗函式，雖然這次不同——應該是這樣的：

WITH
 partitioned AS
 (
   SELECT
     *
   , DATEDIFF(Date, '1970-01-01') - ROW_NUMBER() OVER (ORDER BY Date ASC) AS PartID
   FROM
     YourTable
 )
SELECT
 MIN(Date) AS StartDate
, COUNT(*)  AS DayCount
FROM
 partitioned
GROUP BY
 PartID
HAVING
 COUNT(*) > 1
ORDER BY
 PartID
;

該解決方案依賴於這樣一個事實：將日期表示為整數 ( DATEDIFF(...)) 與日期在有序序列中的數字位置( ) 之間的差異ROW_NUMBER() OVER ...是一個常數值。如果我們查看PartID表達式中函式返回的中間值，我們會發現以下內容：

如您所見，DATEDIFF和ROW_NUMBER（由列表示PartID）之間的差異在日期連續的情況下是相同的，並且對於不同的序列是不同的，這使其成為 GROUP BY 標準的完美候選者。這正是查詢使用它的目的。順便說一句，1970-01-01在這種情況下，日期沒有具體含義。可以使用任何日期來代替它，只要它是一個常數值。

另一個需要注意的重要事項——它使這個答案與Charlieface 的建議大不相同——是所有日期都必須是唯一的，該方法才能按預期工作。

可以在db<>fiddle找到此解決方案的現場展示。

引用自：https://dba.stackexchange.com/questions/297319