如何按每日每小时分组PostgreSQL表数据并统计销售完成与可用量

问题说明 #

现有sales表，字段如下：

• sale_id - 销售ID
• sale_start - 销售流程启动时间（timestamp类型）
• sale_canceled - 销售取消时间（timestamp类型）
• sale_completed - 销售完成时间（timestamp类型）

需要统计指定日期前一周内，每个小时的已完成销售数量和可用销售数量（即该小时内已启动且未取消的销售）。原查询因使用CROSS JOIN导致数据量爆炸，仅加LIMIT时能运行，否则崩溃。

原查询的核心问题 #

1. 笛卡尔积导致数据过载：CROSS JOIN会将每个时间槽与所有销售记录匹配，若销售表有N条记录，会生成168*N条临时数据，远超数据库处理能力。
2. 时间槽生成逻辑错误：关联sales表生成时间槽会导致重复生成相同的小时记录，浪费资源。
3. 时间范围不合理：原条件覆盖长达一年的数据，而非目标一周范围，进一步增加数据量。

修正后的查询语句 #

WITH s AS (
  -- 生成目标一周内的所有小时时间槽，共7*24=168条唯一记录
  SELECT generate_series(
    date_trunc('hour', '2022-08-10'::date - INTERVAL '1 week'),
    date_trunc('hour', '2022-08-10'::date),
    INTERVAL '1 hour'
  ) AS h_slot
),
dt AS (
  -- 筛选目标一周内的销售数据，将时间字段截断到小时粒度
  SELECT 
    DATE_TRUNC('hour', sale_start) AS sale_start_hour,
    DATE_TRUNC('hour', sale_canceled) AS sale_canceled_hour,
    DATE_TRUNC('hour', sale_completed) AS sale_completed_hour
  FROM sales
  WHERE sale_start >= '2022-08-10'::date - INTERVAL '1 week'
    AND sale_start <= '2022-08-10'::date
)
SELECT 
  s.h_slot,
  -- 统计当前小时完成的销售数量
  COUNT(dt.sale_completed_hour) FILTER (WHERE dt.sale_completed_hour = s.h_slot) AS completed_count,
  -- 统计当前小时处于可用状态的销售数量（已启动且未取消）
  COUNT(dt.sale_start_hour) FILTER (WHERE s.h_slot >= dt.sale_start_hour 
                                     AND s.h_slot <= dt.sale_canceled_hour) AS available_count
FROM s
LEFT JOIN dt ON 
  -- 仅关联可能符合统计条件的销售记录，避免无效匹配
  (s.h_slot >= dt.sale_start_hour AND s.h_slot <= dt.sale_canceled_hour)
  OR dt.sale_completed_hour = s.h_slot
GROUP BY s.h_slot
ORDER BY s.h_slot;

关键优化点 #

1. 精准生成时间槽：直接用generate_series生成连续小时，不关联销售表，确保每个小时仅出现一次。
2. 缩小数据范围：仅筛选目标一周内的销售记录，大幅减少待处理数据量。
3. 替换笛卡尔积为条件JOIN：通过LEFT JOIN的关联条件，只匹配可能参与统计的销售记录，彻底解决数据过载问题。
4. 提前过滤数据：在CTE中完成时间截断和范围筛选，减少后续计算的开销。

额外优化建议 #

如果销售数据量极大，可创建时间截断索引提升查询速度：

CREATE INDEX idx_sales_sale_start_hour ON sales (DATE_TRUNC('hour', sale_start));
CREATE INDEX idx_sales_sale_canceled_hour ON sales (DATE_TRUNC('hour', sale_canceled));
CREATE INDEX idx_sales_sale_completed_hour ON sales (DATE_TRUNC('hour', sale_completed));

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