PostgreSQL 15按日期与枚举筛选并聚合指定ID的SQL查询需求

核心逻辑 #

1. 先获取ID=4021（tipologia_enum='RECEITA'）的日期作为筛选基准
2. 筛选满足以下任一条件的记录：
   • ID在3898到4021之间，且tipologia_enum='RECEITA'
   • ID=4060且日期早于ID=4021的日期
3. 排除ID=4061的记录
4. 仅保留tipologia_enum='DESPESA'的记录，按年月分组后用string_agg聚合ID为逗号分隔字符串

实现SQL #

WITH base_date AS (
    SELECT data AS ref_date
    FROM your_table_name
    WHERE id = 4021 AND tipologia_enum = 'RECEITA'
)
SELECT
    TO_CHAR(data, 'YYYY-MM') AS year_month,
    STRING_AGG(id::TEXT, ', ' ORDER BY id) AS despesa_ids
FROM your_table_name, base_date
WHERE
    (id BETWEEN 3898 AND 4021 AND tipologia_enum = 'RECEITA')
    OR (id = 4060 AND data < base_date.ref_date)
    AND id != 4061
    AND tipologia_enum = 'DESPESA'
GROUP BY TO_CHAR(data, 'YYYY-MM')
ORDER BY year_month;

关键说明 #

• 基准日期CTE：base_date提前获取参考日期，避免重复查询表，提升效率
• 年月分组：TO_CHAR(data, 'YYYY-MM')将日期转换为标准年月格式，确保分组准确
• 有序聚合：STRING_AGG中加入ORDER BY id，保证聚合后的ID按数字顺序排列，结果更规整
• 条件过滤：通过逻辑运算符组合筛选规则，精准匹配需求中的包含/排除条件

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