如何在SQL中通过条件逻辑复现Excel SUMIFS计算品类销售额

需求说明 #

需要复现Excel SUMIFS 函数计算逻辑，完成客户需求份额（忠诚度）指标中Category_Sales（品类销售额）字段的开发：

• 原Excel SUMIFS匹配规则：对同一Cust_ID（客户ID）下，满足First_Buy >= 当前行Brand_Str且Last_Buy <= 当前行Brand_End的所有记录的Brand_Sales（品牌销售额）求和
• 计算规则示例：样例中Innocent品牌行的Category_Sales值为268，是Innocent、Cresco、Supply、PTS四个品牌的销售额之和——这四个品牌的首次、末次购买日期均落在Innocent品牌的生效起止日期区间内。

样例数据结构 #

实现方案 #

假设存储上述数据的原表名为sales_data，可通过自连接条件聚合实现逻辑，代码如下：

SELECT 
    t1.State,
    t1.Brand,
    t1.Cust_ID,
    t1.First_Buy,
    t1.Last_Buy,
    t1.Brand_Str,
    t1.Brand_End,
    t1.Brand_Sales,
    SUM(t2.Brand_Sales) AS Category_Sales
FROM sales_data t1
LEFT JOIN sales_data t2
    ON t1.Cust_ID = t2.Cust_ID -- 匹配同一客户
    AND t2.First_Buy >= t1.Brand_Str -- 被求和品牌首次购买在当前品牌生效起始后
    AND t2.Last_Buy <= t1.Brand_End -- 被求和品牌末次购买在当前品牌生效结束前
GROUP BY 
    t1.State,
    t1.Brand,
    t1.Cust_ID,
    t1.First_Buy,
    t1.Last_Buy,
    t1.Brand_Str,
    t1.Brand_End,
    t1.Brand_Sales;

补充说明 #

• 上述代码中t1为当前计算行，t2为待求和的匹配行，连接条件完全对齐Excel SUMIFS的三个匹配规则，聚合求和结果和样例数值完全一致。
• 如果使用支持窗口函数/相关子查询的SQL引擎（MySQL8.0+、PostgreSQL、SQL Server、BigQuery等），也可以用子查询写法，大数据量下性能更稳定：

SELECT 
    *,
    (
        SELECT SUM(t2.Brand_Sales)
        FROM sales_data t2
        WHERE t2.Cust_ID = t1.Cust_ID
          AND t2.First_Buy >= t1.Brand_Str
          AND t2.Last_Buy <= t1.Brand_End
    ) AS Category_Sales
FROM sales_data t1;

注意：执行前请确认日期字段为SQL可识别的日期类型，若为字符串存储请先做日期格式转换，避免大小比较逻辑出错。

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