主页 / dba / 问题 / 306573
Accepted
Mr. Geologist
Asked: 2022-01-26 10:52:27 +0800 CST

检索 SQL Server 中的日期间隔

  • 772

我试图弄清楚如何检索时间序列数据中的空白。下面是我的例子。

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 |

我将如何编写查询以返回上述结果？

sql-server gaps-and-islands

1 个回答

  1. Best Answer

    这是一种简单的方法。首先，您在 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

    小提琴

    • 2

相关问题