如何统计用户每条记录对应的此前同用户历史记录数？

需求说明 #

现有client_calls表记录用户呼叫历史，需要生成包含calls_so_far字段的结果表，该字段用于统计每条记录对应的userId在当前记录的last_call_to_client日期之前的呼叫次数。

原始表结构与数据 #

client_calls
+------+-------+---------------------+
| id   | userId| last_call_to_client |
+------+-------+---------------------+
| 3004 |   664 | 2013-04-01          |
| 3005 |   664 | 2014-05-09          |
| 3006 |   664 | 2015-12-11          |
| 3007 |   664 | 2021-11-24          |
| 3008 |   664 | 2022-03-05          |
+------+-------+---------------------+

目标表示例 #

client_calls_so_far
+------+-------+---------------------+-----------------+
| id   | userId| last_call_to_client | calls_so_far    |
+------+-------+---------------------+-----------------+
| 3004 |   664 | 2013-04-01          | 0               |
| 3005 |   664 | 2014-05-09          | 1               |
| 3006 |   664 | 2015-12-11          | 2               |
| 3007 |   664 | 2021-11-24          | 3               |
| 3008 |   664 | 2022-03-05          | 4               |
+------+-------+---------------------+-----------------+

实现方案 #

方案一：使用ROW_NUMBER()窗口函数

这是最简洁的实现方式，利用窗口函数对每个用户的呼叫记录按日期排序，行号减1即为当前记录之前的呼叫次数：

SELECT 
    id,
    userId,
    last_call_to_client,
    ROW_NUMBER() OVER (PARTITION BY userId ORDER BY last_call_to_client) - 1 AS calls_so_far
FROM client_calls
ORDER BY id;

• PARTITION BY userId：按用户分组，确保只统计当前用户的历史记录
• ORDER BY last_call_to_client：按呼叫日期排序，保证计数顺序正确
• ROW_NUMBER()会为每个用户的记录从1开始编号，减1后得到当前记录之前的呼叫次数

方案二：使用COUNT()窗口函数

如果需要更明确地指定统计范围，可以用COUNT()结合窗口框架：

SELECT 
    id,
    userId,
    last_call_to_client,
    COUNT(*) OVER (
        PARTITION BY userId 
        ORDER BY last_call_to_client 
        ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
    ) AS calls_so_far
FROM client_calls
ORDER BY id;

• ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING：指定统计范围为分组内从第一条记录到当前记录的前一条
• 对于第一条记录，没有前序行，COUNT(*)返回0，符合需求

注意事项 #

• 两种方案均适用于支持窗口函数的主流数据库（如MySQL 8.0+、PostgreSQL、SQL Server等）
• 如果存在同一用户同一日期多条呼叫记录，需根据业务需求调整排序规则（比如加上id确保顺序稳定）

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