PostgreSQL 13中统计运行时指定多事件区间内的帖子数

表结构与示例数据 #

events表（存储事件） #

posts表（存储帖子） #

原有单个事件统计SQL #

统计单个事件（如ID为1的事件）期间的帖子数，可使用以下SQL：

WITH
    event AS (
        SELECT
            start_date
          , end_date
        FROM
            events
        WHERE
            id = 1
    )
SELECT
    COUNT(*)
FROM
    posts
WHERE
      created_at >= (SELECT start_date FROM event)
  AND created_at < (SELECT end_date FROM event)

（注：原SQL中create_at为笔误，已修正为表结构中的created_at）

待解决问题 #

当目标事件仅在运行时确定（需统计多个指定事件期间的帖子数量），在PostgreSQL 13中该如何实现？

根据不同统计需求，提供以下几种实现方式：

场景1：统计所有目标事件期间的帖子总数（允许重复计数） #

如果帖子出现在多个事件时间范围内时需要重复计数，直接通过关联查询统计：

-- 运行时替换IN中的事件ID集合即可
SELECT COUNT(*)
FROM posts p
JOIN events e 
  ON p.created_at >= e.start_date 
  AND p.created_at < e.end_date
WHERE e.id IN (1, 2); -- 这里填入运行时确定的事件ID

场景2：统计所有目标事件期间的唯一帖子数（去重计数） #

如果帖子出现在多个事件时间范围内仅需计数一次，使用DISTINCT去重：

SELECT COUNT(DISTINCT p.id) AS unique_post_count
FROM posts p
JOIN events e 
  ON p.created_at >= e.start_date 
  AND p.created_at < e.end_date
WHERE e.id IN (1, 2, 3); -- 运行时指定目标事件ID

场景3：按单个事件分别统计对应帖子数 #

如果需要知道每个目标事件各自的帖子数量，通过分组查询实现：

SELECT 
    e.id AS event_id,
    e.title AS event_title,
    COUNT(p.id) AS post_count
FROM events e
LEFT JOIN posts p 
  ON p.created_at >= e.start_date 
  AND p.created_at < e.end_date
WHERE e.id IN (1, 3, 4) -- 运行时指定事件ID
GROUP BY e.id, e.title
ORDER BY e.id;

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