如何将Table_A的时长总和与关联Table_B的Val计数相乘？

问题背景 #

需要计算Table_A中每个SubId对应的有效时长总和，再乘以其关联的Table_B中不同Val的计数。原SQL可单独计算时长总和，但直接关联Table_B后性能暴跌（Table_A有数千万条记录，日期字段无索引）。

优化实现思路 #

核心是提前聚合Table_B的Val计数，避免在大表关联中重复计算；同时用更高效的关联方式替代原有的IN子查询，减少数据处理量。

完整SQL实现 #

-- 预计算每个Id对应的不同Val数量，仅执行一次聚合
WITH val_counts AS (
    SELECT 
        Id,
        COUNT(DISTINCT Val) AS distinct_val_count
    FROM Table_B
    GROUP BY Id
)
SELECT 
    -- 按Id分组计算总乘积，若需按SubId分组则改为GROUP BY ta.SubId
    SUM((t.mod_stop - t.mod_start) * vc.distinct_val_count) AS total_calculation
FROM (
    SELECT 
        ta.Id,
        ta.SubId,
        -- 修正后的有效起始时间
        CASE WHEN ta.Start < '@begin' THEN '@begin' ELSE ta.Start END AS mod_start,
        -- 修正后的有效结束时间
        CASE 
            WHEN ta.Stop > '@end' THEN '@end'
            WHEN ta.Stop = '0' THEN '@end'
            ELSE ta.Stop 
        END AS mod_stop
    FROM table_a ta
    -- 替换为实际的中间表关联链，连接到Table_B的Id
    JOIN 中间表1 m1 ON ta.Id = m1.TableA_Id
    JOIN 中间表2 m2 ON m1.Id = m2.MiddleTable_Id
    JOIN val_counts vc ON m2.TableB_Id = vc.Id
    WHERE 
        ta.state IN ('Launching', 'Running', 'Finishing')
        -- 用EXISTS替代IN子查询，性能更优
        AND EXISTS (
            SELECT 1
            FROM table_a ta_inner
            WHERE ta_inner.Id = ta.Id
                AND (to_timestamp(ta_inner.Start), to_timestamp(ta_inner.Stop)) OVERLAPS (to_timestamp('@begin'), to_timestamp('@end'))
                AND ta_inner.state IN ('Running', 'Launching', 'Finishing')
                AND ta_inner.Stop != '0'
        )
) t
-- 过滤无效时长（起始时间不早于结束时间的情况）
WHERE t.mod_start < t.mod_stop
-- 若需按Id/SubId分别输出则保留GROUP BY，否则去掉直接计算总和
GROUP BY t.Id;

关键优化点 #

1. 预聚合Val计数：通过CTEval_counts提前计算每个Id对应的不同Val数量，避免在大表遍历中重复执行聚合操作，大幅减少计算开销。
2. EXISTS替代IN：在大表场景下，EXISTS的执行效率远高于IN子查询，因为它找到匹配项后立即停止检索，无需遍历所有结果。
3. 提前过滤数据：将关联和过滤逻辑放在内层查询，只保留需要处理的记录，减少后续计算的数据量。
4. 无效时长过滤：增加mod_start < mod_stop的条件，排除无意义的负时长或零时长记录，避免错误计算。

额外性能建议 #

由于Table_A的日期字段无索引，建议：

• 给Start、Stop字段添加联合索引，加速OVERLAPS条件的判断
• 给state字段添加索引，提升WHERE条件的过滤速度

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