如何高效查询各Connection无消费记录的日期列表

背景与表结构 #

现有存储Connection每日消费数据的表Consumptiondata，结构如下：

create table dbo.Consumptiondata
(
    ConnectionId nvarchar(125) not null,
    StartDateTime datetime not null,
    EndDateTime datetime not null,
    Value numeric(20, 10) not null
)

需求是获取每个Connection在指定日期范围内无消费记录的日期。原方案中，先生成日期范围临时表关联消费表无法匹配Connection；改为为每个Connection单独生成日期范围后，查询耗时超8分钟，需要更高效的实现方式。

高效实现方案 #

1. 用CTE生成日期范围+Connection组合 #

通过数字序列快速生成指定日期范围，再与所有唯一ConnectionID做笛卡尔积，最后左连接消费表筛选无记录的日期：

-- 生成数字序列用于构造日期范围
WITH Numbers AS (
    SELECT TOP (10000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS Num
    FROM sys.all_columns ac1
    CROSS JOIN sys.all_columns ac2
),
-- 生成目标日期范围（替换起始/结束日期为你的需求）
DateRange AS (
    SELECT DATEADD(DAY, Num, '2024-01-01') AS TargetDate
    FROM Numbers
    WHERE DATEADD(DAY, Num, '2024-01-01') <= '2024-01-31'
)
-- 关联Connection与日期，筛选无消费记录的条目
SELECT 
    conn.ConnectionId,
    dr.TargetDate
FROM 
    (SELECT DISTINCT ConnectionId FROM dbo.Consumptiondata) conn
CROSS JOIN 
    DateRange dr
LEFT JOIN 
    dbo.Consumptiondata cd 
        ON conn.ConnectionId = cd.ConnectionId
        AND CAST(cd.StartDateTime AS DATE) = dr.TargetDate
WHERE 
    cd.ConnectionId IS NULL
ORDER BY 
    conn.ConnectionId, dr.TargetDate;

2. 关键优化措施 #

• 避免逐Connection生成日期：用CROSS JOIN一次性生成所有Connection与日期的组合，避免循环或逐行处理的开销。
• 添加复合索引：给Consumptiondata表创建以下索引，大幅提升连接匹配的速度：

CREATE NONCLUSTERED INDEX IX_Consumptiondata_ConnectionId_StartDate
ON dbo.Consumptiondata (ConnectionId, CAST(StartDateTime AS DATE))
INCLUDE (Value); -- 若后续需用到Value字段可保留，否则可去掉

如果数据库不支持计算列索引，也可以创建包含ConnectionId和StartDateTime的索引，查询时的日期转换仍能利用索引：

CREATE NONCLUSTERED INDEX IX_Consumptiondata_ConnectionId_StartDateTime
ON dbo.Consumptiondata (ConnectionId, StartDateTime);

3. 长期优化：使用永久日历表 #

如果经常需要进行日期范围查询，建议创建一个永久日历表，提前填充数年的日期数据，后续查询直接复用：

-- 创建日历表
CREATE TABLE dbo.Calendar (
    Date DATE PRIMARY KEY,
    Year INT NOT NULL,
    Month INT NOT NULL,
    Day INT NOT NULL
);

-- 批量填充数据（示例填充2020-2030年）
WITH Numbers AS (
    SELECT TOP (4018) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS Num
    FROM sys.all_columns ac1
    CROSS JOIN sys.all_columns ac2
)
INSERT INTO dbo.Calendar (Date, Year, Month, Day)
SELECT 
    DATEADD(DAY, Num, '2020-01-01') AS Date,
    YEAR(DATEADD(DAY, Num, '2020-01-01')) AS Year,
    MONTH(DATEADD(DAY, Num, '2020-01-01')) AS Month,
    DAY(DATEADD(DAY, Num, '2020-01-01')) AS Day
FROM Numbers
WHERE DATEADD(DAY, Num, '2020-01-01') <= '2030-12-31';

使用时直接将DateRange替换为dbo.Calendar，并添加日期范围过滤条件即可，性能会更稳定。

内容的提问来源于stack exchange，提问作者Fysicus