优化按用户单次登录统计活动数量的SQL查询方案

你的问题在大数据量场景下非常典型——原方案通过"给每个活动匹配所有早于它的登录再取最近值"的方式，会产生海量中间结果，自然跑不动。我们可以换个更高效的思路：先为每条登录记录划定它的有效覆盖时段（到下一次登录前），再把活动直接匹配到对应时段，能大幅减少不必要的关联操作。

具体实现步骤&SQL示例 #

1. 给登录记录标记时段边界

用窗口函数LEAD()，可以为每个用户的每条登录记录，获取到他的下一次登录时间。这样每条登录就对应一个明确的时间区间：[logindate, next_login_time)，最后一次登录的区间则延伸到无穷大（用一个极大值替代）。

WITH login_windows AS (
    SELECT
        user,
        logindate AS latestLogin,
        -- 获取下一次登录时间，无后续登录则用极大值兜底
        LEAD(logindate, 1, '9999-12-31 23:59:59') OVER (
            PARTITION BY user 
            ORDER BY logindate ASC
        ) AS next_login_time
    FROM login_table
)

2. 关联活动表并统计数量

现在把活动表和上面生成的时段表关联，只要活动时间落在某条登录的时段范围内，就属于这次登录后的活动。最后按用户和登录时间分组统计即可：

SELECT
    lw.user,
    lw.latestLogin,
    COUNT(a.activity) AS activityCount
FROM login_windows lw
LEFT JOIN activity_table a
    ON lw.user = a.user
    AND a.activitydate >= lw.latestLogin
    AND a.activitydate < lw.next_login_time
GROUP BY lw.user, lw.latestLogin
ORDER BY lw.user, lw.latestLogin;

为什么这个方案性能更好？ #

• 原方案会让每个活动和所有早于它的登录做关联，再筛选最近的一条，中间结果集大小是活动数×平均每个活动匹配的登录数，数据量会爆炸式增长；
• 新方案中，登录表仅被扫描一次生成时段，活动表也仅被扫描一次匹配对应时段，中间结果集大小等于登录数+匹配到的活动数，远小于原方案，计算效率大幅提升。

关键索引优化 #

为了让查询效率最大化，一定要添加这两个联合索引：

• 登录表：CREATE INDEX idx_login_user_date ON login_table(user, logindate);
• 活动表：CREATE INDEX idx_activity_user_date ON activity_table(user, activitydate);

这些索引能让窗口函数的排序逻辑、后续的关联操作快速定位数据，避免全表扫描的性能损耗。

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