SQL中能否使用子查询替代CASE表达式实现分组统计？

完全可以使用子查询替代CASE表达式实现完全一致的客户维度分组统计效果。

实现逻辑 #

子查询实现的核心思路是把每个条件聚合的逻辑拆成独立的预聚合子查询，先按客户维度单独计算每个指标，再通过客户ID关联，把所有指标合并到同一行结果中，最终返回值和原CASE条件聚合的结果完全一致。

测试用相关代码 #

测试表结构与测试数据 #

DROP TABLE IF exists SAS;

CREATE TABLE sas (
    CUSTOMER_ID INT(3) NOT NULL,
    ACCOUNT_NO INT(3) NOT NULL,
    ACCOUNT_TYPE VARCHAR(11),
    TRAN_AMOUNT INT(11),
    TRAN_DATE DATE NOT NULL
);

INSERT INTO SAS VALUES 
( 100 ,233 ,'SAVINGS' , 75000 , '2020-10-10'),
( 100 , 233 , 'SAVINGS' , 50000, '2020-8-23'),
( 100 , 234 , 'CURRENT' , 30000, '2020-11-1'),
(200, 239 , 'CURRENT' , 5000 , '2020-10-10'),
(200,238 , 'SAVINGS' , 7000 , '2020-11-10'),
(300,221 , 'SAVINGS' , 3000 , '2020-11-10'),
(300,223 , 'SAVINGS' , 45000 , '2020-11-10') ,
(300 , 224 , 'CURRENT' , 20000 , '2020-11-18'),
(300 , 224, 'CURRENT' , 35000 , '2020-11-18');

原CASE表达式实现的查询 #

SELECT 
    CUSTOMER_ID,
    COUNT(DISTINCT ACCOUNT_NO) AS NUM_ACCOUNTS,
    COUNT(DISTINCT CASE account_type WHEN 'SAVINGS' THEN ACCOUNT_NO END) AS NUM_SAVINGS_ACCOUNT,
    COUNT(DISTINCT TRAN_DATE) AS NUM_DATE_TRANSACTED,
    SUM(TRAN_AMOUNT) AS TOT_AMOUNT,
    SUM(CASE account_type WHEN 'current' THEN TRAN_AMOUNT  END) AS tol_curr_amt
FROM
    sas
GROUP BY CUSTOMER_ID; 

注意：原查询中活期账户判断使用小写'current'，测试数据中账户类型值为大写'CURRENT'，大小写敏感的数据库环境下该字段统计结果会为空。

等价子查询实现 #

SELECT 
    base.CUSTOMER_ID,
    acc.NUM_ACCOUNTS,
    sav_acc.NUM_SAVINGS_ACCOUNT,
    tran_dt.NUM_DATE_TRANSACTED,
    total_amt.TOT_AMOUNT,
    curr_amt.tol_curr_amt
FROM
    (SELECT DISTINCT CUSTOMER_ID FROM sas) base
    LEFT JOIN (
        SELECT CUSTOMER_ID, COUNT(DISTINCT ACCOUNT_NO) AS NUM_ACCOUNTS
        FROM sas GROUP BY CUSTOMER_ID
    ) acc ON base.CUSTOMER_ID = acc.CUSTOMER_ID
    LEFT JOIN (
        SELECT CUSTOMER_ID, COUNT(DISTINCT ACCOUNT_NO) AS NUM_SAVINGS_ACCOUNT
        FROM sas WHERE ACCOUNT_TYPE = 'SAVINGS' GROUP BY CUSTOMER_ID
    ) sav_acc ON base.CUSTOMER_ID = sav_acc.CUSTOMER_ID
    LEFT JOIN (
        SELECT CUSTOMER_ID, COUNT(DISTINCT TRAN_DATE) AS NUM_DATE_TRANSACTED
        FROM sas GROUP BY CUSTOMER_ID
    ) tran_dt ON base.CUSTOMER_ID = tran_dt.CUSTOMER_ID
    LEFT JOIN (
        SELECT CUSTOMER_ID, SUM(TRAN_AMOUNT) AS TOT_AMOUNT
        FROM sas GROUP BY CUSTOMER_ID
    ) total_amt ON base.CUSTOMER_ID = total_amt.CUSTOMER_ID
    LEFT JOIN (
        SELECT CUSTOMER_ID, SUM(TRAN_AMOUNT) AS tol_curr_amt
        -- 若要和原查询大小写逻辑完全对齐，把下面的'CURRENT'改成'current'即可
        FROM sas WHERE ACCOUNT_TYPE = 'CURRENT' GROUP BY CUSTOMER_ID
    ) curr_amt ON base.CUSTOMER_ID = curr_amt.CUSTOMER_ID;

两种实现的差异 #

• 性能表现：CASE条件聚合仅需对源表做1次全表扫描即可完成所有指标计算，子查询实现会多次扫描源表（上述示例会扫描6次），数据量较大时CASE写法性能优势非常明显。
• 维护成本：简单统计场景下CASE写法更简洁，当统计逻辑非常复杂、指标数量极多时，拆分的子查询逻辑相互独立，排查单个指标问题时更方便。
• 结果一致性：只要判断条件完全对齐，两种写法返回的统计结果100%一致。

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