Oracle条件GROUP BY的金额求和逻辑实现求助

需求说明 #

• 按ID和SPLIT对AMOUNT字段求和；
• CUST字段取对应ID、SPLIT分组下最新DATE的取值；
• 若同一ID、SPLIT分组的最新DATE下存在多个不同CUST值，则保留所有这些CUST，并按CUST拆分AMOUNT求和。

示例数据集 #

预期查询结果 #

现有尝试代码（存在问题） #

之前的CTE仅筛选了最新DATE的行，导致旧DATE的AMOUNT未被计入求和，无法满足需求：

WITH LatestDatePerID AS (
    SELECT ID, 
    "SPLIT",
           MAX(DATE_COLUMN) AS MAX_DATE
    FROM your_table_name
    GROUP BY ID, "SPLIT"
),
LatestCustPerID AS (
    SELECT t.ID, 
           t.CUST, 
           t."SPLIT",
           t.DATE_COLUMN, 
           t.AMOUNT
    FROM your_table_name t
    JOIN LatestDatePerID l ON t.ID = l.ID AND t.DATE_COLUMN = l.MAX_DATE and t."SPLIT" = l."SPLIT"
)
SELECT ID, 
       CUST,
       "SPLIT",
       DATE_COLUMN, 
       SUM(AMOUNT) AS AMOUNT
FROM LatestCustPerID
GROUP BY ID, "SPLIT", CUST, DATE_COLUMN
ORDER BY ID, DATE_COLUMN DESC;

解决方案代码 #

WITH GroupInfo AS (
    -- 获取每个ID+SPLIT的最新日期，以及分组总金额
    SELECT 
        ID,
        SPLIT,
        MAX(DATE_COLUMN) AS latest_date,
        SUM(AMOUNT) AS total_amount
    FROM Test_Table_MM
    GROUP BY ID, SPLIT
),
LatestCusts AS (
    -- 获取每个ID+SPLIT最新日期下的所有CUST
    SELECT DISTINCT
        ID,
        SPLIT,
        CUST
    FROM Test_Table_MM t
    JOIN GroupInfo g 
        ON t.ID = g.ID 
        AND t.SPLIT = g.SPLIT 
        AND t.DATE_COLUMN = g.latest_date
),
CustTotal AS (
    -- 计算每个ID+SPLIT+CUST的累计金额
    SELECT
        ID,
        SPLIT,
        CUST,
        SUM(AMOUNT) AS cust_amount
    FROM Test_Table_MM
    GROUP BY ID, SPLIT, CUST
)
SELECT
    g.ID,
    g.SPLIT,
    l.CUST,
    g.latest_date AS DATE_COLUMN,
    -- 根据最新日期下的CUST数量，决定取分组总金额还是单个CUST金额
    CASE 
        WHEN (SELECT COUNT(DISTINCT CUST) FROM LatestCusts WHERE ID = g.ID AND SPLIT = g.SPLIT) > 1 
        THEN c.cust_amount 
        ELSE g.total_amount 
    END AS AMOUNT
FROM GroupInfo g
JOIN LatestCusts l 
    ON g.ID = l.ID 
    AND g.SPLIT = l.SPLIT
LEFT JOIN CustTotal c 
    ON g.ID = c.ID 
    AND g.SPLIT = c.SPLIT 
    AND l.CUST = c.CUST
ORDER BY g.ID, g.SPLIT, l.CUST;

代码逻辑说明 #

1. GroupInfo：先统计每个ID+SPLIT分组的最新日期，以及该分组下所有AMOUNT的总和；
2. LatestCusts：筛选出每个ID+SPLIT分组在最新日期下的所有CUST值，确保不会遗漏多个CUST的情况；
3. CustTotal：提前计算每个ID+SPLIT+CUST组合的累计金额，用于多CUST场景的拆分求和；
4. 最终查询关联三个CTE，通过CASE判断：如果最新日期下存在多个CUST，则取对应CUST的累计金额；否则直接取分组总金额，同时统一使用最新日期作为结果的DATE值。

建表及插入数据脚本 #

CREATE TABLE Test_Table_MM (
    ID VARCHAR2(10),
    SPLIT VARCHAR2(10),
    CUST VARCHAR2(10),
    DATE_COLUMN DATE,
    AMOUNT NUMBER
);

INSERT INTO Test_Table_MM (ID, SPLIT, CUST, DATE_COLUMN, AMOUNT) VALUES ('ID_1', 'SPLIT_YES', 'A', TO_DATE('05/01/2024', 'MM/DD/YYYY'), 100);
INSERT INTO Test_Table_MM (ID, SPLIT, CUST, DATE_COLUMN, AMOUNT) VALUES ('ID_1', 'SPLIT_NO', 'A', TO_DATE('04/01/2024', 'MM/DD/YYYY'), 200);
INSERT INTO Test_Table_MM (ID, SPLIT, CUST, DATE_COLUMN, AMOUNT) VALUES ('ID_1', 'SPLIT_YES', 'B', TO_DATE('03/01/2024', 'MM/DD/YYYY'), 50);
INSERT INTO Test_Table_MM (ID, SPLIT, CUST, DATE_COLUMN, AMOUNT) VALUES ('ID_2', 'SPLIT_YES', 'A', TO_DATE('05/01/2024', 'MM/DD/YYYY'), 50);
INSERT INTO Test_Table_MM (ID, SPLIT, CUST, DATE_COLUMN, AMOUNT) VALUES ('ID_2', 'SPLIT_NO', 'A', TO_DATE('04/01/2024', 'MM/DD/YYYY'), 300);
INSERT INTO Test_Table_MM (ID, SPLIT, CUST, DATE_COLUMN, AMOUNT) VALUES ('ID_2', 'SPLIT_NO', 'B', TO_DATE('03/01/2024', 'MM/DD/YYYY'), 300);
INSERT INTO Test_Table_MM (ID, SPLIT, CUST, DATE_COLUMN, AMOUNT) VALUES ('ID_3', 'SPLIT_YES', 'B', TO_DATE('04/01/2024', 'MM/DD/YYYY'), 90);
INSERT INTO Test_Table_MM (ID, SPLIT, CUST, DATE_COLUMN, AMOUNT) VALUES ('ID_3', 'SPLIT_NO', 'B', TO_DATE('04/01/2024', 'MM/DD/YYYY'), 30);
INSERT INTO Test_Table_MM (ID, SPLIT, CUST, DATE_COLUMN, AMOUNT) VALUES ('ID_3', 'SPLIT_NO', 'A', TO_DATE('04/01/2024', 'MM/DD/YYYY'), 10);
INSERT INTO Test_Table_MM (ID, SPLIT, CUST, DATE_COLUMN, AMOUNT) VALUES ('ID_3', 'SPLIT_NO', 'A', TO_DATE('03/01/2024', 'MM/DD/YYYY'), 10);

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