SQL中按客户ID计算不同时间窗口的交易速度聚合

需求：从transactions表中，仅处理should_aggregate为true的交易，统计每笔交易发生前，对应客户在1小时、1天、1周内的历史交易总数，最终生成transaction_velocity表。

原表结构 #

TABLE transactions (
  transaction_id int,
  customer_id int,
  date Timestamp,
  should_aggregate boolean,
)

目标表结构 #

TABLE transaction_velocity (
  transaction_id int,
  customer_id int,
  date Timestamp,
  previous_1hour int,
  previous_1day  int,
  previous_1week int
)

输入示例 #

输出示例 #

解决方案 #

可以利用窗口函数结合时间范围高效计算，核心是通过PARTITION BY customer_id按客户分组，用时间范围定义窗口边界，最后减去当前交易的计数（窗口会包含当前交易）。

通用SQL实现（以PostgreSQL为例） #

SELECT
  transaction_id,
  customer_id,
  date,
  -- 统计当前交易前1小时内的历史交易数
  COUNT(*) OVER (
    PARTITION BY customer_id
    ORDER BY date
    RANGE BETWEEN INTERVAL '1 hour' PRECEDING AND CURRENT ROW
  ) - 1 AS previous_1hour,
  -- 统计当前交易前1天内的历史交易数
  COUNT(*) OVER (
    PARTITION BY customer_id
    ORDER BY date
    RANGE BETWEEN INTERVAL '1 day' PRECEDING AND CURRENT ROW
  ) - 1 AS previous_1day,
  -- 统计当前交易前1周内的历史交易数
  COUNT(*) OVER (
    PARTITION BY customer_id
    ORDER BY date
    RANGE BETWEEN INTERVAL '1 week' PRECEDING AND CURRENT ROW
  ) - 1 AS previous_1week
FROM transactions
WHERE should_aggregate = true
ORDER BY transaction_id;

注意事项 #

1. 数据库兼容性：不同数据库对时间范围窗口的语法支持不同：
   • MySQL 8.0+ 可通过TIMESTAMPDIFF结合条件计数实现：

     SELECT
       transaction_id,
       customer_id,
       date,
       COUNT(CASE WHEN TIMESTAMPDIFF(HOUR, t.date, t_current.date) BETWEEN 0 AND 1 THEN 1 END) OVER (PARTITION BY t_current.customer_id ORDER BY t_current.date) -1 AS previous_1hour,
       COUNT(CASE WHEN TIMESTAMPDIFF(DAY, t.date, t_current.date) BETWEEN 0 AND 1 THEN 1 END) OVER (PARTITION BY t_current.customer_id ORDER BY t_current.date) -1 AS previous_1day,
       COUNT(CASE WHEN TIMESTAMPDIFF(WEEK, t.date, t_current.date) BETWEEN 0 AND 1 THEN 1 END) OVER (PARTITION BY t_current.customer_id ORDER BY t_current.date) -1 AS previous_1week
     FROM transactions t_current
     WHERE t_current.should_aggregate = true;
     

2. 统计范围：上述逻辑会统计该客户所有历史交易（无论should_aggregate是否为true），与示例结果一致。
3. 排除当前交易：窗口函数默认包含当前交易，因此需要用-1得到纯历史交易的数量。

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