使用PSQL/pgAdmin按月份统计可用库存数量的问题

问题背景 #

在PSQL/pgAdmin中需要按月份统计年度内的可用库存总数，尝试使用generate_series生成月份序列，但count的filter子句中日期条件未生效，无法得到正确结果。

原尝试代码 #

select
 g.day::date,
 count(item) filter(
 where install_date <= g.day+'1month'::interval
 and (removed_date is null or removed_date < g.day+'1month'::interval))
FROM table
CROSS JOIN generate_series('2024-01-01'::timestamp, '2025-01-01'::timestamp, '1 month'::interval) AS g(day)
group by 1,2

示例数据 #

期望结果 #

修正后的查询代码 #

SELECT
    to_char(g.month_start, 'FMMM月') AS 月份,
    COUNT(t.item) AS 可用库存数
FROM (
    -- 生成2024年每个月的第一天
    SELECT generate_series('2024-01-01'::date, '2024-12-01'::date, '1 month'::interval) AS month_start
) AS g
LEFT JOIN your_table AS t
    ON t.install_date <= (g.month_start + interval '1 month' - interval '1 day')::date
    AND (t.removed_date IS NULL OR t.removed_date > g.month_start)
GROUP BY g.month_start
ORDER BY g.month_start;

关键修正说明 #

• 日期逻辑调整：判断物品当月可用的正确条件是：
  • 安装日期不晚于当月最后一天（确保物品在当月已完成安装）
  • 移除日期为空（未移除）或移除日期晚于当月第一天（确保物品在当月至少有一天处于可用状态）
• 分组修正：原代码错误地将count(item)纳入分组字段，导致结果异常，只需按月份起始日期分组即可
• 生成准确月份边界：通过计算得到每个月的最后一天，避免直接使用+1month可能出现的日期计算误差（如2月天数问题）
• LEFT JOIN用法：确保即使当月无可用物品，也能返回0的统计结果，符合期望中的4月数据

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