PostgreSQL如何高效统计不同日期对应的多类事件发生次数

原写法使用了相关子查询，外层每返回一个日期就要全表扫描一次deals表做匹配，时间复杂度为O(n²)，数据量上升后耗时会大幅增长，核心优化思路是将多次表扫描简化为1次。

方案1：通用高兼容写法（支持所有主流数据库，复杂度O(n)） #

仅需扫描1次deals表即可完成所有统计，扩展性强，后续新增时间类型统计只需修改对应逻辑：

SELECT
    change_datetime,
    SUM(add_flag) AS add_time_count,
    SUM(last_mail_flag) AS last_mail_count,
    SUM(lost_flag) AS lost_time_count
FROM (
    SELECT add_time::date AS change_datetime, 1 AS add_flag, 0 AS last_mail_flag, 0 AS lost_flag FROM deals WHERE add_time IS NOT NULL
    UNION ALL
    SELECT last_mail_time::date AS change_datetime, 0 AS add_flag, 1 AS last_mail_flag, 0 AS lost_flag FROM deals WHERE last_mail_time IS NOT NULL
    UNION ALL
    SELECT lost_time::date AS change_datetime, 0 AS add_flag, 0 AS last_mail_flag, 1 AS lost_flag FROM deals WHERE lost_time IS NOT NULL
) AS t
GROUP BY change_datetime
ORDER BY change_datetime;

方案2：现代数据库简化写法（支持PostgreSQL/MySQL 8.0+/SQL Server） #

用枚举事件的方式进一步简化代码，逻辑更清晰：

SELECT
    event_date AS change_datetime,
    SUM(CASE WHEN event_type = 'add' THEN 1 ELSE 0 END) AS add_time_count,
    SUM(CASE WHEN event_type = 'mail' THEN 1 ELSE 0 END) AS last_mail_count,
    SUM(CASE WHEN event_type = 'lost' THEN 1 ELSE 0 END) AS lost_time_count
FROM deals
CROSS JOIN (
    VALUES 
        ('add', add_time::date),
        ('mail', last_mail_time::date),
        ('lost', lost_time::date)
) AS event_map(event_type, event_date)
WHERE event_date IS NOT NULL
GROUP BY event_date
ORDER BY event_date;

• 可针对三个时间字段创建函数索引，以PostgreSQL为例：CREATE INDEX idx_deals_add_time_date ON deals((add_time::date));，高频统计场景下查询速度会进一步提升
• 若只需统计特定时间范围的数据，可在内层子查询的WHERE条件中添加时间过滤规则，减少扫描的数据量

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