跨多年每日数据的SQL列转置查询优化咨询

需求说明 #

需要将DATE列的日期值转置为列，按FirmName（公司名称）填充对应TOTAL数据，生成2013年以来的连续每日时间序列列（允许空值，第一列为公司名，后续依次为每日数据）。

当前问题 #

现有SQL查询中包含约4000个SUM(CASE WHEN...)日期条件，同时P.FirmName IN列表包含约1000个公司名，导致查询运行速度极慢，寻求优化方案。

原查询代码 #

SELECT P.FirmName,
SUM(CASE WHEN [DATE] = '2023-11-30' THEN [TOTAL] END) as '2023-11-30',
SUM(CASE WHEN [DATE] = '2023-12-01' THEN [TOTAL] END) as '2023-12-01',
SUM(CASE WHEN [DATE] = '2023-12-02' THEN [TOTAL] END) as '2023-12-02',
SUM(CASE WHEN [DATE] = '2023-12-03' THEN [TOTAL] END) as '2023-12-03'
-- 后续还有近4000个类似的SUM(CASE...)语句
FROM [Total].[Database] S
LEFT JOIN [Product].[Database] P ON S.Product = P.Product
WHERE FirmLocation NOT IN (-1,-2,-3) AND P.FirmName IN (Apple, Banana, Cherry)
-- IN列表包含约1000个公司名
GROUP BY P.FirmName

优化方案建议 #

• 用动态SQL替代硬编码CASE WHEN：先生成2013年以来的连续日期列表，再动态拼接转置所需的列语句，避免手动写数千个CASE条件，同时让SQL引擎生成更高效的执行计划。
• 替换IN列表为JOIN：把1000个目标公司名存入临时表，用JOIN替代IN语句，SQL对JOIN的优化远优于长IN列表，能大幅减少过滤时的计算量。
• 改用PIVOT语法：相比手动编写大量CASE WHEN，PIVOT是专门的转置语法，配合动态SQL生成列列表，执行效率更高。
• 预聚合减少数据量：先按FirmName和DATE预聚合TOTAL值，存入临时表或CTE，再基于预聚合结果做转置，避免重复计算SUM。
• 添加合适的索引：给关联字段S.Product、P.Product创建索引，给过滤字段FirmLocation、P.FirmName创建索引，加速JOIN和过滤操作。
• 先过滤再关联：提前从Product表筛选出符合条件的FirmName和对应的Product，再和Total表关联，减少JOIN的数据规模。

内容的提问来源于stack exchange，提问作者Neu-to-SQL