如何编写SQL统计各帖子2秒内并发屏幕浏览的用户数

现有存储用户屏幕浏览行为的业务表，表结构及样例数据如下：

user_id | timestamp                | post_number
123     | 2022-04-01 17:32:52 UTC  |  1
234     | 2022-04-01 17:35:52 UTC  |  1
546     | 2022-04-01 17:32:52 UTC  |  1
765     | 2022-04-01 18:32:52 UTC  |  1
143     | 2022-04-01 17:32:54 UTC  |  1
123     | 2022-04-01 18:32:52 UTC  |  2
234     | 2022-04-01 18:32:53 UTC  |  2
546     | 2022-04-01 18:40:02 UTC  |  2

需求为按帖子维度统计存在并发屏幕浏览行为的用户总数：

• 并发判定规则：同一帖子下，两次浏览行为的时间差在2秒以内即判定为并发
• 期望输出结果：

post_number | concurrent_screenviews
1           | 3
2           | 4

当前运行环境为BigQuery MySQL方言，已知单时间点可通过TIMESTAMPDIFF函数计算时间差，需要实现指定日期范围内全量数据的并发统计，无需手动遍历所有时间戳。

不需要遍历所有时间戳，通过同维度自关联匹配符合时间差条件的记录对，再去重计数即可，逻辑步骤：

1. 按post_number分区，将同帖子下的浏览记录做自关联
2. 筛选出时间差绝对值≤2秒、且分属不同用户的记录对
3. 按帖子维度去重统计符合条件的用户数，即为存在并发浏览行为的用户总数

可直接运行的SQL代码如下（将user_view_log替换为你的实际表名即可）：

WITH concurrent_visitors AS (
    SELECT DISTINCT
        a.post_number,
        a.user_id
    FROM user_view_log a
    INNER JOIN user_view_log b
        ON a.post_number = b.post_number
        AND a.user_id <> b.user_id
        -- 匹配前后2秒窗口内的浏览记录，有时间字段索引时该写法性能优于ABS计算
        AND b.timestamp BETWEEN TIMESTAMP_SUB(a.timestamp, INTERVAL 2 SECOND) 
                            AND TIMESTAMP_ADD(a.timestamp, INTERVAL 2 SECOND)
    -- 按需添加日期过滤条件，示例为统计2022-04-01全天数据
    WHERE DATE(a.timestamp) = '2022-04-01'
)
SELECT
    post_number,
    COUNT(user_id) AS concurrent_screenviews
FROM concurrent_visitors
GROUP BY post_number
ORDER BY post_number;

逻辑说明：该写法会自动将所有满足2秒时间差条件的用户配对，单用户只要有任意一条浏览记录和其他用户的浏览记录满足并发条件，就会被统计1次，完全匹配「存在并发浏览行为」的统计规则，针对样例数据可直接返回期望结果。
数据量较大时，建议给post_number、timestamp字段建立联合索引，可大幅提升查询效率。

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