Postgres中CASE结合ARRAY_AGG处理publish_end_at为null的场景

表结构示例 #

campaign表 #

publish_queue表（关联示例） #

原查询（参考示例） #

假设你之前的查询大致如下（仅实现了明确时间区间的判断）：

SELECT
  ARRAY_AGG(CASE WHEN NOW() BETWEEN publish_start_at AND publish_end_at THEN id END) AS running,
  ARRAY_AGG(CASE WHEN /* 未处理publish_end_at为空的场景 */ THEN id END) AS published
FROM campaign;

修改后的完整查询 #

方式一：使用CASE多分支 #

SELECT
  -- 运行中数组：包含时间区间内的campaign，以及无结束时间且启动不足1分钟的campaign
  ARRAY_REMOVE(
    ARRAY_AGG(
      CASE
        WHEN publish_end_at IS NOT NULL AND NOW() BETWEEN publish_start_at AND publish_end_at THEN id
        WHEN publish_end_at IS NULL AND NOW() - publish_start_at < INTERVAL '1 minute' THEN id
      END
    ),
    NULL
  ) AS running,
  -- 已发布数组：包含无结束时间且启动超过1分钟的campaign
  ARRAY_REMOVE(
    ARRAY_AGG(
      CASE
        WHEN publish_end_at IS NULL AND NOW() - publish_start_at >= INTERVAL '1 minute' THEN id
        -- 可添加其他已发布条件，比如：已过结束时间的campaign
        -- WHEN publish_end_at IS NOT NULL AND NOW() > publish_end_at THEN id
      END
    ),
    NULL
  ) AS published
FROM campaign;

方式二：使用FILTER（更高效直观） #

这种方式直接过滤符合条件的行，避免生成NULL后再移除，性能更优：

SELECT
  ARRAY_AGG(id) FILTER (
    WHERE (publish_end_at IS NOT NULL AND NOW() BETWEEN publish_start_at AND publish_end_at)
       OR (publish_end_at IS NULL AND NOW() - publish_start_at < INTERVAL '1 minute')
  ) AS running,
  ARRAY_AGG(id) FILTER (
    WHERE publish_end_at IS NULL AND NOW() - publish_start_at >= INTERVAL '1 minute'
    -- 可追加其他已发布条件：
    -- OR (publish_end_at IS NOT NULL AND NOW() > publish_end_at)
  ) AS published
FROM campaign;

关键逻辑说明 #

1. running数组的判断逻辑：
   • 覆盖两种场景：有明确结束时间且当前处于发布区间内；无结束时间且启动时间距当前不足1分钟。
   • 方式二中用OR连接两个条件，直接筛选符合要求的id进行聚合，逻辑更清晰。
2. published数组的判断逻辑：
   • 针对无结束时间的campaign，当启动时间距当前超过1分钟时，归入已发布数组。
   • 若需要包含其他已发布场景（比如已结束发布的campaign），只需在FILTER或CASE中追加对应条件即可。

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