PostgreSQL大表中统计一周内重复交易次数的SQL查询问题

首先修正你SQL里的语法错误：WHERE子句开头多了一个AND，需要删掉；另外你写的表名myTable要换成实际的表名user_transaction。

你的核心误解在于：每个用户最后一行的next_timestamp为null是正常的，不会漏统计符合条件的交易对。我们要统计的是「相邻交易对」的数量，每一对只需要被计算一次——比如用户1的交易2023-11-13和2023-11-20，在2023-11-20的交易行中，lead已经取到2023-11-13的时间并计算了间隔，这一对已经被计入统计，不需要在2023-11-13的行重复判断。

修正后的SQL（基于你的原逻辑） #

WITH source AS (
  SELECT 
    user_id, 
    transaction_id, 
    transaction_timestamp 
  FROM 
    user_transaction
  WHERE 
    transaction_timestamp >= CURRENT_TIMESTAMP - INTERVAL '365 DAY' 
    AND transaction_timestamp <= CURRENT_TIMESTAMP 
  ORDER BY 
    user_id, 
    transaction_timestamp DESC
), 
cte AS (
  SELECT 
    user_id, 
    transaction_timestamp, 
    lead(transaction_timestamp) OVER (
      PARTITION BY user_id 
      ORDER BY 
        transaction_timestamp DESC
    ) AS next_timestamp 
  FROM 
    source
) 
SELECT 
  count(*) 
FROM 
  cte 
WHERE 
  next_timestamp IS NOT NULL 
  AND transaction_timestamp - next_timestamp <= INTERVAL '7 DAY';

更直观的写法（用lag函数） #

如果觉得lead的逻辑绕，也可以用lag按时间升序排序，直接获取当前交易的上一笔交易时间，计算间隔：

WITH source AS (
  SELECT 
    user_id, 
    transaction_timestamp 
  FROM 
    user_transaction
  WHERE 
    transaction_timestamp >= CURRENT_TIMESTAMP - INTERVAL '365 DAY' 
    AND transaction_timestamp <= CURRENT_TIMESTAMP
),
cte AS (
  SELECT 
    user_id,
    transaction_timestamp,
    lag(transaction_timestamp) OVER (
      PARTITION BY user_id 
      ORDER BY transaction_timestamp ASC
    ) AS prev_timestamp
  FROM source
)
SELECT COUNT(*)
FROM cte
WHERE 
  prev_timestamp IS NOT NULL 
  AND transaction_timestamp - prev_timestamp <= INTERVAL '7 DAY';

这两个版本的查询结果都是你需要的4次，逻辑等价。

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