檢索 SQL Server 中的日期間隔

我試圖弄清楚如何檢索時間序列數據中的空白。下面是我的例子。

CREATE TABLE MYTABLE
   ([ID] int, [DATE] DATE)
;

INSERT INTO MYTABLE
   ([ID], [DATE])
VALUES
   (1, '2022-01-01'),
   (1, '2022-02-01'),
   (1, '2022-03-01'),
   (1, '2022-04-01'),
   (1, '2022-05-01'),
   (1, '2022-06-01'),
   (1, '2022-10-01'),
   (1, '2022-11-01'),
   (1, '2022-12-01'),
   (2, '2022-01-01'),
   (2, '2022-02-01'),
   (2, '2022-03-01'),
   (2, '2022-04-01'),
   (2, '2022-05-01'),
   (2, '2022-06-01'),
   (2, '2022-07-01'),
   (2, '2022-08-01'),
   (2, '2022-10-01'),
   (2, '2022-11-01'),
   (2, '2022-12-01')
;

日期欄位遵循一個約定，其中日期始終是每月的 1 號。對於上面的範例，差距如下。

| ID |       DATE |
|----|------------|
|  1 | 2022-07-01 |
|  1 | 2022-08-01 |
|  1 | 2022-09-01 |
|  2 | 2022-09-01 |

我將如何編寫查詢以返回上述結果？

這是一種簡單的方法。首先，您在 MYTABLE 中生成介於 min 和 max 之間的所有日期：

with min_max(min_dt, max_dt) as ( 
   select min([DATE]), max([DATE]) from MYTABLE
), all_dt(d) as (
   select min_dt from min_max
   union all 
   select DATEADD(month, 1, d) from all_dt
   where d < (select max_dt from min_max)
)
select y.d 
from all_dt y

現在您可以在 MYTABLE 中的使用者集和使用者集之間獲取笛卡爾積

with min_max(min_dt, max_dt) as ( 
   select min([DATE]), max([DATE]) from MYTABLE
), all_dt(d) as (
   select min_dt from min_max
   union all 
   select DATEADD(month, 1, d) from all_dt
   where d < (select max_dt from min_max)
)
select x.id, y.d 
from all_dt y, (select distinct [ID] as id from MYTABLE) as x

我使用了 “,” 連接，因為我不確定 SQL 伺服器是否支持顯式 CROSS JOIN。最後，您可以從該集合中減去所有現有行：

with min_max(min_dt, max_dt) as ( 
   select min([DATE]), max([DATE]) from MYTABLE
), all_dt(d) as (
   select min_dt from min_max
   union all 
   select DATEADD(month, 1, d) from all_dt
   where d < (select max_dt from min_max)
)
select x.id, y.d 
from all_dt y, (select distinct [ID] as id from MYTABLE) as x
except
select [ID], [DATE] from MYTABLE

我建議您避免使用 DATE 和 ID 等標識符，但我認為這只是一個範例

編輯： CROSS JOIN 顯然可以根據我的小提琴工作，因此您可以將其改寫為：

...
SELECT x.id, y.d 
FROM all_dt y 
CROSS JOIN (SELECT distinct [ID] AS id FROM MYTABLE) AS x
EXCEPT
SELECT [ID], [DATE] FROM MYTABLE

小提琴

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