如何使用窗口函数优化用户近7天交易笔数统计SQL

完全可以使用窗口函数实现需求，性能远高于自关联、普通分组聚合的写法，能直接匹配逐行统计7天内交易笔数的输出要求。

首先先指出你原有查询的几个明显问题：

• 字段名错误：表中存储日期的字段是date，不是timestamp
• 时间范围逻辑写反：BETWEEN语法要求小值在前、大值在后，你写的CURRENT_DATE()到DATE_SUB(CURRENT_DATE(), INTERVAL 3 DAYS)属于无效范围，且你需要统计的是每条交易对应日期往前7天的范围，不是固定以当前日期为基准的范围
• 输出粒度不对：原查询按用户分组聚合，只能得到每个用户单条统计值，没法做到每条交易记录对应一个7天交易数的输出
• 缺少无效数据过滤：样例数据里存在is_blocked、交易金额为空的无效记录，统计时需要排除

窗口函数实现方案 #

使用支持范围滑动的窗口函数，按用户分区，按交易日期排序，划定窗口范围为「当前行日期往前6天到当前行日期」（总共7天区间，包含交易当日），统计窗口内的交易数即可，注意要先过滤掉无效空记录。

以下为MySQL 8.0+/Spark SQL/Presto等支持标准窗口函数引擎的可运行代码：

WITH valid_trans AS (
    SELECT
        transaction_id,
        user_id,
        date
    FROM transactions
    -- 过滤空值、被拦截的无效交易
    WHERE is_blocked IS NOT NULL 
      AND transaction_amount IS NOT NULL
)
SELECT
    t.transaction_id,
    t.user_id,
    t.date,
    -- 统计同用户下，交易日期前6天到当日的总交易数，减1是排除当前交易本身，匹配样例首条交易计数为0的规则
    COUNT(*) OVER (
        PARTITION BY user_id
        ORDER BY date
        RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW
    ) - 1 AS `Transaction within 7 Days`
FROM valid_trans t
ORDER BY user_id, date;

如果你使用的SQL引擎不支持在窗口范围定义中直接写INTERVAL，可以改用日期左关联写法，性能略低于窗口函数，但逻辑完全一致：

SELECT
    a.transaction_id,
    a.user_id,
    a.date,
    COUNT(b.transaction_id) AS `Transaction within 7 Days`
FROM valid_trans a
LEFT JOIN valid_trans b
    ON a.user_id = b.user_id
    AND b.date BETWEEN DATE_SUB(a.date, INTERVAL 6 DAY) AND DATE_SUB(a.date, INTERVAL 1 DAY)
GROUP BY a.transaction_id, a.user_id, a.date

逻辑匹配说明 #

上述写法完全匹配你给出的目标输出规则：

• 每条交易单独返回一行，保留transaction_id/user_id/date字段
• 统计值为当前交易发生前7天内（不含当前交易本身） 该用户的累计交易笔数：
  • 用户首条交易没有更早的记录，计数为0
  • 间隔超过7天的交易不会被计入，比如2020-01-01的交易到2020-01-15时已经超出7天范围，计数重置为0
  • 同用户相邻日期的交易按时间区间累计，和样例输出的计数逻辑完全对齐

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