PostgreSQL 按campaign_id分组统计用户及点击量高效查询

表结构说明 #

• users（用户表）
  • 字段：id（主键PK）、campaign_id（活动ID）、createdAt（用户创建时间）
  • 数据量级：单表可达百万级
• user_clicks（用户点击追踪表）
  • 字段：id（主键PK）、user_id（外键FK，关联users.id）、createdAt（点击时间）
  • 数据量级：单表可达百万级

需求边界 #

• 按campaign_id维度分组，输出每个活动的总用户数total_users、总点击数total_clicks
• 样例预期输出：campaign_id=123对应total_users=3、total_clicks=1；campaign_id=1234对应total_users=1、total_clicks=3
• 查询需支持基于createdAt字段添加时间范围过滤条件
• 要求查询性能最优，适配百万级数据规模

性能最优查询语句 #

核心思路是先单表聚合再关联，避免两表直接JOIN产生1对多数据膨胀，同时将过滤条件下推到最内层，提前裁剪无效数据。

-- 时间范围参数可根据业务需求调整，示例为统计2024年1月1日至2024年6月30日的数据
WITH user_agg AS (
    SELECT
        campaign_id,
        COUNT(id) AS total_users
    FROM users
    -- 下推用户侧时间过滤，如不需要约束用户创建时间可删除本行条件
    WHERE createdAt >= '2024-01-01 00:00:00'
      AND createdAt < '2024-07-01 00:00:00'
    GROUP BY campaign_id
),
click_agg AS (
    SELECT
        u.campaign_id,
        COUNT(c.id) AS total_clicks
    FROM user_clicks c
    INNER JOIN users u
        ON c.user_id = u.id
    -- 下推点击侧时间过滤，如不需要约束点击时间可删除本行条件
    WHERE c.createdAt >= '2024-01-01 00:00:00'
      AND c.createdAt < '2024-07-01 00:00:00'
      -- 如需要同步约束用户创建时间，补充下面的条件即可
      -- AND u.createdAt >= '2024-01-01 00:00:00' AND u.createdAt < '2024-07-01 00:00:00'
    GROUP BY u.campaign_id
)
SELECT
    COALESCE(ua.campaign_id, ca.campaign_id) AS campaign_id,
    COALESCE(ua.total_users, 0) AS total_users,
    COALESCE(ca.total_clicks, 0) AS total_clicks
FROM user_agg ua
FULL OUTER JOIN click_agg ca
    ON ua.campaign_id = ca.campaign_id;

注：如果业务上不存在「活动下有点击但无匹配用户」的脏数据，可将FULL OUTER JOIN替换为LEFT JOIN以user_agg为主表关联，性能会进一步提升。

配套索引优化 #

添加以下联合索引后，查询可以直接走覆盖索引扫描，完全避免全表扫描和回表开销，百万级数据下查询耗时可以控制在毫秒级：

• users表索引：CREATE INDEX idx_users_created_camp ON users (createdAt, campaign_id, id);
• user_clicks表索引：CREATE INDEX idx_clicks_created_user ON user_clicks (createdAt, user_id, id);

结果验证 #

基于题目给出的样例数据，上述查询返回结果完全符合预期：

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