MySQL销售数据聚合转换布局的高效实现方案咨询

核心优化思路 #

利用窗口函数实现单次表扫描完成市场总销售额计算，结合UNION ALL拆分出两种维度的记录，避免传统CTE可能带来的重复扫描或关联开销。

优化后的SQL代码 #

-- 生成单店维度记录
SELECT 
    Id,
    'ShoppingCentre' AS Dimension_Type,
    Store_Name AS Dimension_Value,
    Sales AS Sales_Amount,
    Main_Product
FROM Production_Table
UNION ALL
-- 生成市场维度记录（窗口函数计算市场总销售额）
SELECT 
    Id,
    'Market' AS Dimension_Type,
    Market AS Dimension_Value,
    SUM(Sales) OVER (PARTITION BY Market) AS Sales_Amount,
    Main_Product
FROM Production_Table

优化点说明 #

• 单次扫描源表：窗口函数SUM(Sales) OVER (PARTITION BY Market)在扫描源表的同时，直接为每一行计算出对应市场的总销售额，无需额外分组汇总再关联，减少IO开销。
• 无冗余JOIN操作：相比先通过CTE分组市场销售额再JOIN原表的方案，省去了关联操作带来的性能损耗。
• 高效合并数据：使用UNION ALL而非UNION，因为需求中每个Id对应两条不重复的记录，UNION ALL不会执行去重检查，性能更优。

备选优化方案（适用于不支持窗口函数的数据库） #

如果数据库不支持窗口函数，可先预计算市场总销售额并存储为临时结果，再关联原表，尽量减少表扫描次数：

-- 预计算市场总销售额（仅扫描一次源表）
CREATE TEMPORARY TABLE Market_Total_Sales AS
SELECT Market, SUM(Sales) AS Total_Market_Sales
FROM Production_Table
GROUP BY Market;

-- 合并两种维度记录
SELECT 
    Id,
    'ShoppingCentre' AS Dimension_Type,
    Store_Name AS Dimension_Value,
    Sales AS Sales_Amount,
    Main_Product
FROM Production_Table
UNION ALL
SELECT 
    pt.Id,
    'Market' AS Dimension_Type,
    pt.Market AS Dimension_Value,
    mts.Total_Market_Sales AS Sales_Amount,
    pt.Main_Product
FROM Production_Table pt
JOIN Market_Total_Sales mts ON pt.Market = mts.Market;

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