PostgreSQL双条件过滤列生成新列并多表关联的技术问题

需求 #

需要对数值列按两个时间条件过滤，生成对应两个时间段的数值列：

• june_purch：仅2024年6月的交易金额总和
• bal_june：截至2024年6月30日的累计交易金额总和
  之后将约5个同结构的表进行关联合并。当前基于两个表的尝试SQL未得到预期结果，使用Python开发，数据库为PostgreSQL。

尝试的SQL（原代码） #

SELECT item, purch_date_1, SUM(purch_amt_1) AS june_purch 
FROM table_purch1
WHERE table_purch1.purch_date_1 BETWEEN '2024-06-01' AND '2024-06-30'
GROUP BY purch_date_1, item
FULL JOIN (
  SELECT SUM(purch_amt1) AS bal_juin 
  FROM table_purch1
  WHERE table_purch1.purch_date_1 <= '2024-06-30'
  GROUP BY purch_date_1
) AS T1 ON purch_date_1
UNION
SELECT item2, purch_date_2, SUM(purch_amt2) AS june_purch2
FROM table_purch2
WHERE table_purch2.purch_date_2 BETWEEN '2024-06-01' AND '2024-06-30'    -- For june only
GROUP BY purch_date_2
FULL JOIN (
  SELECT SUM(purch_amt2) AS bal_june2 FROM table_purch2
  WHERE table_purch2.purch_date_2 <= '2024-06-30' -- From the start to June(the report month)
  GROUP BY purch_date_2
) AS T2 ON purch_date_2;

表数据 #

table_purch1 #

Date            item        amount
"2024-05-16"     "A"         100.0
"2024-06-05"     "B"         150.0
"2024-06-05"     "B"         200.0
"2024-06-12"     "D"         250.0
"2024-06-12"     "D"         300.0

table_purch2 #

Date             item       amount
"2024-05-16"      "A"        100.0
"2024-06-05"      "B"        100.0
"2024-06-12"      "D"        100.0

预期结果 #

item    purch_date     june_purch  bal_june
  A      "2024-05-16"     0          200.0
  B      "2024-06-05"   450.0        450.0
  C      "2024-06-11"   500.0        500.0
  D      "2024-06-12"   650.0        650.0

正确SQL实现 #

原SQL存在语法错误（JOIN位置错误、列名不统一、UNION逻辑不符合需求），正确思路是先合并所有同结构表的数据，再通过条件聚合计算两个时间段的金额：

-- 先合并两个表的数据，后续可直接添加UNION ALL连接其他同结构表
WITH merged_data AS (
    SELECT "Date" AS purch_date, item, amount FROM table_purch1
    UNION ALL
    SELECT "Date" AS purch_date, item, amount FROM table_purch2
    -- 如需添加更多表，继续加UNION ALL即可
    -- UNION ALL
    -- SELECT "Date" AS purch_date, item, amount FROM table_purch3
)
SELECT 
    item,
    -- 取每个item最早的交易日期，或根据需求调整为对应日期
    MIN(purch_date) AS purch_date,
    -- 计算6月的金额总和，非6月的交易金额计为0
    SUM(CASE WHEN purch_date BETWEEN '2024-06-01' AND '2024-06-30' THEN amount ELSE 0 END) AS june_purch,
    -- 计算截至6月30日的累计金额总和
    SUM(CASE WHEN purch_date <= '2024-06-30' THEN amount ELSE 0 END) AS bal_june
FROM merged_data
GROUP BY item
-- 如需包含没有交易的item（比如预期中的C），需关联item维度表，示例：
-- RIGHT JOIN item_dimension ON merged_data.item = item_dimension.item
ORDER BY item;

说明 #

1. 合并数据：用UNION ALL合并所有同结构表，避免UNION自动去重导致数据丢失
2. 条件聚合：通过CASE WHEN实现按时间条件过滤求和，一次分组即可计算两个指标
3. 日期处理：如果每个item的purch_date需要取对应交易的日期（而非最早日期），需调整分组逻辑，比如按item和purch_date分组，但需确保同一item同一日期的交易合并计算
4. 空item处理：预期结果中的C无交易数据，需关联一个包含所有item的维度表，用RIGHT JOIN保留所有item记录，此时june_purch和bal_june会自动显示0

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