PostgreSQL中SUM与GROUP BY避免同记录重复聚合的方案

数据表说明 #

系统包含4张数据表：

• product（产品表）
• projecteddata（预计数据表）
• payoutblock（支付关联表）
• users（用户表）

产品表（product） #

CREATE TABLE IF NOT EXISTS public.product
(
    id integer NOT NULL DEFAULT nextval('product_id_seq'::regclass),
    "productType" character varying COLLATE pg_catalog."default" NOT NULL, -- 产品类型
    amount numeric NOT NULL DEFAULT '0'::numeric, -- 金额
    "startDate" date NOT NULL -- 起始日期
)

预计数据表（projecteddata） #

CREATE TABLE IF NOT EXISTS public.projecteddata
(
    id integer NOT NULL DEFAULT nextval('projecteddata'::regclass),
    "productType" character varying COLLATE pg_catalog."default" NOT NULL, -- 产品类型
    amount numeric NOT NULL DEFAULT '0'::numeric, -- 金额
    "startDate" date NOT NULL, -- 起始日期
    "systemGeneratedStartDate" date NOT NULL, -- 系统生成起始日期
    "systemGeneratedEndDate" date NOT NULL, -- 系统生成结束日期
    "productId" integer, -- 关联产品ID
    CONSTRAINT "FK_c6c14f315490aceb2f7a9ffb3ed" FOREIGN KEY ("productId")
        REFERENCES public.product (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
)

支付关联表（payoutblock） #

CREATE TABLE IF NOT EXISTS public.payoutblock
(
    id integer NOT NULL DEFAULT nextval('payoutblock_id_seq'::regclass),
    "productId" integer, -- 关联产品ID
    "payeeId" uuid, -- 收款人ID
    CONSTRAINT "FK_1bc960a61d22c0daf3efd691d70" FOREIGN KEY ("productId")
        REFERENCES public.product (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION,
    CONSTRAINT "FK_e6f4e4b0f0750910b39ec67f816" FOREIGN KEY ("payeeId")
        REFERENCES public.users (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
)

用户表（users） #

CREATE TABLE IF NOT EXISTS public.users
(
    id uuid NOT NULL DEFAULT uuid_generate_v4(), -- 用户ID（主键）
    CONSTRAINT "PK_a3ffb1c0c8416b9fc6f907b7433" PRIMARY KEY (id)
)

示例数据 #

产品表插入数据 #

INSERT INTO public.product(
    id, "productType", amount, "startDate")
    VALUES (1, 'ABCD', 100, '2022-10-02'), -- 产品ID1，类型ABCD，金额100，起始日期2022-10-02
    VALUES (2, 'XYZ', 100, '2022-12-14'); -- 产品ID2，类型XYZ，金额100，起始日期2022-12-14

支付关联表插入数据 #

INSERT INTO public.payoutblock(
    id,"productId", "payeeId")
    VALUES (1, 1, '0005c2b6-29f2-491f-8565-c30567f232f9'), -- 关联产品1，收款人ID0005c2b6...
        VALUES (2, 1 , 'ec5e7cbb-2f82-4b05-8135-73746b59afb0'), -- 关联产品1，收款人IDec5e7cbb...
    VALUES (3, 2, '0005c2b6-29f2-491f-8565-c30567f232f9'); -- 关联产品2，收款人ID0005c2b6...

用户表插入数据 #

INSERT INTO public.users(
    id)
    VALUES ('0005c2b6-29f2-491f-8565-c30567f232f9'),
    ('ec5e7cbb-2f82-4b05-8135-73746b59afb0');

预计数据表插入数据 #

INSERT INTO public.projecteddata(
    id, "productType", amount, "startDate", "systemGeneratedStartDate", "systemGeneratedEndDate",
    "productId")
    VALUES (1, 'ABCD', 100, '2022-10-02', '2022-10-01', '2022-10-31', 1),
    VALUES (2, 'ABCD', 100, '2022-10-02', '2022-11-01', '2022-11-30', 1),
    VALUES (3, 'XYZ', 100, '2022-12-14', '2022-12-01', '2022-12-31', 2),
    VALUES (4, 'ABCD', 100, '2022-10-02', '2022-12-01', '2022-12-31', 1),
    VALUES (5, 'XYZ', 100, '2022-12-14', '2023-01-01','2023-01-31', 2),
    VALUES (6, 'ABCD', 100, '2022-10-02', '2023-01-01', '2023-01-31', 1),
    VALUES (7, 'XYZ', 100, '2022-12-14', '2023-02-01', '2023-02-28', 2),
    VALUES (8, 'ABCD', 100, '2022-10-02', '2023-02-01', '2023-02-28', 1);

问题描述 #

执行以下包含SUM和GROUP BY的查询时，未得到预期结果：

SELECT sum(rpd."amount"),to_char(rpd."systemGeneratedStartDate", 'YYYY-MM-DD') as "systemGeneratedStartDate",
            to_char(rpd."systemGeneratedEndDate", 'YYYY-MM-DD') as "systemGeneratedEndDate"
            FROM "projecteddata" rpd
            left join public.product rp
            on rpd."productId" = rp."id"
            left join public.payoutblock pb
            on pb."productId" = rp."id"
            WHERE "systemGeneratedStartDate" >= '2022-10-01' AND "systemGeneratedStartDate" < '2024-04-01' 
             AND (pb."payeeId" IN ( 'ec5e7cbb-2f82-4b05-8135-73746b59afb0' , '0005c2b6-29f2-491f-8565-c30567f232f9' )) 
            GROUP BY "systemGeneratedStartDate","systemGeneratedEndDate",rp."id"
                    ORDER BY "systemGeneratedStartDate" ASC

当前输出中每个时间区间的sum值为200，而非预期的100。尝试将payeeId加入GROUP BY后，sum值恢复正确，但每个时间区间会出现两行重复数据。

问题根源在于payoutblock表中单个产品对应多个收款人ID，导致关联查询时数据被重复展开，最终聚合计算时金额被重复累加。需求是：即使一个产品关联多个收款人，每个时间区间的sum值仍保持原始金额，且每个时间区间仅返回一行数据。

预期输出：

解决方案 #

方案1：使用EXISTS过滤替代JOIN #

payoutblock仅用于筛选关联指定收款人的产品，无需关联展开数据，用EXISTS子查询避免数据重复：

SELECT 
    sum(rpd."amount") AS sum,
    to_char(rpd."systemGeneratedStartDate", 'YYYY-MM-DD') AS "systemGeneratedStartDate",
    to_char(rpd."systemGeneratedEndDate", 'YYYY-MM-DD') AS "systemGeneratedEndDate"
FROM "projecteddata" rpd
LEFT JOIN public.product rp ON rpd."productId" = rp."id"
WHERE 
    rpd."systemGeneratedStartDate" >= '2022-10-01' 
    AND rpd."systemGeneratedStartDate" < '2024-04-01'
    AND EXISTS (
        SELECT 1 
        FROM public.payoutblock pb 
        WHERE pb."productId" = rp."id"
        AND pb."payeeId" IN ('ec5e7cbb-2f82-4b05-8135-73746b59afb0', '0005c2b6-29f2-491f-8565-c30567f232f9')
    )
GROUP BY rpd."systemGeneratedStartDate", rpd."systemGeneratedEndDate", rp."id"
ORDER BY rpd."systemGeneratedStartDate" ASC;

方案2：先聚合再关联过滤 #

先对projecteddata按时间区间和产品ID聚合，再关联过滤，避免提前关联导致的数据重复：

WITH aggregated_projected AS (
    SELECT 
        "productId",
        "systemGeneratedStartDate",
        "systemGeneratedEndDate",
        sum("amount") AS amount
    FROM "projecteddata"
    WHERE "systemGeneratedStartDate" >= '2022-10-01' 
        AND "systemGeneratedStartDate" < '2024-04-01'
    GROUP BY "productId", "systemGeneratedStartDate", "systemGeneratedEndDate"
)
SELECT 
    ap.amount AS sum,
    to_char(ap."systemGeneratedStartDate", 'YYYY-MM-DD') AS "systemGeneratedStartDate",
    to_char(ap."systemGeneratedEndDate", 'YYYY-MM-DD') AS "systemGeneratedEndDate"
FROM aggregated_projected ap
LEFT JOIN public.product rp ON ap."productId" = rp."id"
WHERE EXISTS (
    SELECT 1 
    FROM public.payoutblock pb 
    WHERE pb."productId" = rp."id"
    AND pb."payeeId" IN ('ec5e7cbb-2f82-4b05-8135-73746b59afb0', '0005c2b6-29f2-491f-8565-c30567f232f9')
)
ORDER BY ap."systemGeneratedStartDate" ASC;

内容的提问来源于Stack Exchange，提问作者Ayush Singh Gaur