SQL高效计算客户组当年及上年利润总和的实现方案

需求说明 #

现有一张包含customer_group、customer、project、year、profit字段的SQL表，需要为表中每行添加两个字段：

• total_profit_current_year：当前customer_group的当年利润总和
• total_profit_last_year：当前customer_group的上年利润总和（无上年数据时为NULL）

示例输入数据 #

期望输出数据 #

问题背景 #

• 尝试过LAG()窗口函数：仅能获取单条记录的上年利润，无法统计整个客户组的上年总利润。
• 尝试过WITH子句汇总后两次左连接：在百万级数据量下性能极差，多次扫描原表导致效率低下。

高效解决方案 #

方案思路 #

1. 先通过一次汇总计算出每个客户组、每个年份的总利润，并利用LAG()窗口函数获取该客户组的上年总利润。
2. 将汇总结果与原表进行一次左连接，把两个汇总字段关联到原表的每一行。

这种方式仅需对原表进行一次扫描汇总，再进行一次关联操作，大幅减少IO开销，适合处理百万级数据。

SQL代码实现 #

WITH yearly_group_profit AS (
    SELECT
        customer_group,
        year,
        SUM(profit) AS total_profit_current_year,
        LAG(SUM(profit)) OVER (PARTITION BY customer_group ORDER BY year) AS total_profit_last_year
    FROM your_table_name
    GROUP BY customer_group, year
)
SELECT
    t.*,
    y.total_profit_current_year,
    y.total_profit_last_year
FROM your_table_name t
LEFT JOIN yearly_group_profit y
    ON t.customer_group = y.customer_group
    AND t.year = y.year;

优化说明 #

• 如果你的SQL引擎支持（如PostgreSQL、MySQL 8.0+、SQL Server等），可以考虑在customer_group和year字段上建立联合索引，进一步提升分组和关联的效率：

  CREATE INDEX idx_customer_group_year ON your_table_name(customer_group, year);
  

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