如何将包含3个CTE的SQL查询改写为仅含1个CTE且结果完全一致

WITH customer_agg AS (
    SELECT 
        t1.customerid AS cid,
        t1.sumEP,
        t2.smf
    FROM (
        -- 对应原Q1逻辑：按客户统计所有Extendedprice总和
        SELECT customerid, SUM(Extendedprice) AS sumEP
        FROM "Invoices"
        GROUP BY customerid
    ) t1
    JOIN (
        -- 对应原Q2+Q3逻辑：先对订单去重再按客户统计运费总和
        SELECT customerid, SUM(freight) AS smf
        FROM (
            SELECT DISTINCT customerid, orderid, freight
            FROM "Invoices"
        ) t_unique_order
        GROUP BY customerid
    ) t2 ON t1.customerid = t2.customerid
)
SELECT cid, sumEP + smf AS total
FROM customer_agg
LIMIT 10;

• 改写后仅保留1个CTE，通过在CTE内部嵌套子查询实现原3个CTE的全部逻辑，计算结果和原语句完全一致
• 子查询t1完全复用原Q1的聚合逻辑，按客户维度汇总所有发票行的Extendedprice总额
• 子查询t_unique_order完全复用原Q2的去重逻辑，避免同一订单对应多条发票行导致运费重复计算，外层t2对去重后的运费按客户汇总，对应原Q3的逻辑
• 整个改写过程没有修改原有业务逻辑，仅调整了SQL的结构层级

如果使用的数据库支持窗口函数，也可以用更简洁的写法：

WITH customer_total AS (
    SELECT 
        customerid AS cid,
        SUM(Extendedprice) OVER (PARTITION BY customerid) AS sumEP,
        SUM(CASE WHEN ROW_NUMBER() OVER (PARTITION BY customerid, orderid ORDER BY 1) = 1 THEN freight ELSE 0 END) OVER (PARTITION BY customerid) AS smf
    FROM "Invoices"
)
SELECT DISTINCT cid, sumEP + smf AS total
FROM customer_total
LIMIT 10;

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