编写PostgreSQL查询统计各时间点快照的未结工单数量

解决方案 #

通过左连接快照表与工单表，结合时间范围判断来统计每个快照点的未结工单数量，核心查询语句如下：

SELECT
    s.snapshot,
    COALESCE(COUNT(c.case_id), 0) AS count_open_cases
FROM
    snapshot s
LEFT JOIN
    cases c ON c.case_open_time <= s.snapshot 
           AND c.case_closed_time > s.snapshot
GROUP BY
    s.snapshot
ORDER BY
    s.snapshot DESC;

逻辑说明 #

1. 快照完整性保障：使用LEFT JOIN确保所有快照时间都出现在结果中，即使该时间点没有未结工单。
2. 未结工单判断规则：工单需同时满足以下两个条件，才被视为快照时间点的未结工单：
   • 工单开启时间早于或等于快照时间（case_open_time <= s.snapshot）
   • 工单关闭时间晚于快照时间（case_closed_time > s.snapshot）

   注：若工单关闭时间与快照时间完全一致，视为已结工单，符合示例中2022-07-10 10:00:00时工单4已关闭的场景。

3. 空值转换：用COALESCE将无匹配工单时的NULL转换为0，与期望输出格式一致。
4. 结果排序：按快照时间倒序排列，匹配示例输出的展示顺序。

性能优化建议 #

• 给工单表的case_open_time和case_closed_time字段创建联合索引，加速时间范围匹配：

  CREATE INDEX idx_cases_open_closed ON cases (case_open_time, case_closed_time);
  

• 若快照表数据量极大，可给snapshot字段创建索引，提升分组排序的执行效率。

示例验证 #

针对你提供的示例数据，执行上述查询后会得到与期望完全一致的结果：

• 2022-07-05 10:00:00：工单1已开启未关闭，计数1
• 2022-07-06 10:00:00：工单2已开启未关闭，计数1
• 2022-07-07 10:00:00：工单2仍未关闭，计数1
• 2022-07-08 10:00:00：工单2和工单3均未关闭，计数2
• 2022-07-09 10:00:00：所有工单已关闭，计数0
• 2022-07-10 10:00:00：所有工单已关闭，计数0

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