如何优化耗时极长的双表关联统计查询？

原方案的核心问题 #

原方案对每个时间点执行一次全表子查询统计，相当于820万次全表扫描，时间复杂度达到O(N*M)，完全无法处理百万级数据。

优化思路：事件流累加 #

把每条记录的起始、结束时间转化为事件，通过排序累加快速计算覆盖数：

• 把start_date标记为**+1事件**：表示该时间点新增一条覆盖记录
• 把end_date标记为**-1事件**：表示该时间点减少一条覆盖记录
• 将所有事件按时间排序，依次累加事件值，就能得到每个时间点的覆盖数

优化后的SQL代码 #

-- 生成所有事件点（起始时间+1，结束时间-1）
DROP TABLE IF EXISTS event_points;
SELECT 
    start_date AS event_time,
    1 AS delta
INTO event_points
FROM data_table
UNION ALL
SELECT 
    end_date AS event_time,
    -1 AS delta
FROM data_table;

-- 按时间排序并累加计算覆盖数
DROP TABLE IF EXISTS results;
SELECT 
    event_time AS time_point,
    SUM(delta) OVER (ORDER BY event_time ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS n
INTO results
FROM event_points
ORDER BY event_time;

-- 查看最终结果
SELECT * FROM results;

额外优化建议 #

如果原表的start_date和end_date没有索引，先创建以下索引，能进一步提升事件点生成速度：

CREATE NONCLUSTERED INDEX IX_data_table_start ON data_table(start_date);
CREATE NONCLUSTERED INDEX IX_data_table_end ON data_table(end_date);

结果说明 #

优化后的结果逻辑和原方案完全一致：每个time_point对应的n就是该时间点被覆盖的记录数。若多个事件在同一时间发生，窗口函数会自动累加所有事件值，保证结果准确。

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