如何创建带日期过滤的PostgreSQL多表关联聚合查询

针对你的需求，核心要解决两张表重复caseId导致的计算错误问题，正确的查询需要分两步预处理数据再聚合：

最终查询语句 #

SELECT
    a.booking_date AS date,
    SUM(r.case_quantity) AS bookedquantity,
    SUM(r.case_amount) AS amount
FROM (
    -- 预处理Activity：每个caseId在同一天仅保留一条记录，避免重复关联
    SELECT DISTINCT
        caseId,
        DATE(bookingtime) AS booking_date
    FROM Activity
    WHERE bookingtime BETWEEN :start_date AND :end_date
) a
JOIN (
    -- 预处理Request：按caseId聚合，得到每个caseId的总数量和金额
    SELECT
        caseId,
        SUM(quantity) AS case_quantity,
        SUM(amount) AS case_amount
    FROM Request
    GROUP BY caseId
) r ON a.caseId = r.caseId
GROUP BY a.booking_date
ORDER BY a.booking_date;

关键逻辑说明 #

1. 预处理Activity表：用DISTINCT确保同一个caseId在同一天仅出现一次，避免后续关联时因重复记录导致数量/金额被多次计算。
2. 预处理Request表：按caseId聚合求和，解决Request表内重复caseId的问题，保证每个caseId的数量和金额只计算一次。
3. 关联后按日期聚合：将两个预处理后的结果关联，再按日期分组求和，得到每日所有不同caseId的数量和金额总和。

可选调整 #

如果需要包含Activity中有记录但Request中无匹配caseId的日期（此时数量和金额显示为0），可以改用LEFT JOIN并配合COALESCE函数：

SELECT
    a.booking_date AS date,
    COALESCE(SUM(r.case_quantity), 0) AS bookedquantity,
    COALESCE(SUM(r.case_amount), 0) AS amount
FROM (
    SELECT DISTINCT
        caseId,
        DATE(bookingtime) AS booking_date
    FROM Activity
    WHERE bookingtime BETWEEN :start_date AND :end_date
) a
LEFT JOIN (
    SELECT
        caseId,
        SUM(quantity) AS case_quantity,
        SUM(amount) AS case_amount
    FROM Request
    GROUP BY caseId
) r ON a.caseId = r.caseId
GROUP BY a.booking_date
ORDER BY a.booking_date;

参数说明 #

• :start_date 和 :end_date 为传入的起止日期参数，格式需符合PostgreSQL要求（例如 '2024-01-01'）。

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