如何不使用函数或存储过程为每个用户运行streak查询？

问题背景 #

我需要实现不借助任何函数或存储过程，为每个用户计算其连续获得积分的最长 streak（连续有效记录数）。

我已经能为特定用户（比如user_id=27）计算streak，查询语句如下：

SELECT MAX(sum) AS streak
FROM (
  SELECT
    game_date,
    IF(points > 0, @sum:=@sum+1, @sum:=0) AS sum
  FROM (
    SELECT
      game_date,
      (SELECT COUNT(*) FROM point WHERE user_id = 27 AND bet_id = b.id AND goals > 0) AS points
    FROM bet b
    WHERE game_date < NOW()
    ORDER BY game_date DESC
  ) t1, (SELECT @sum:=0) t2
) t;

但当我尝试扩展为所有用户计算时，写出的语句在本地MySQL能运行，但线上phpMyAdmin报错“user_id是WHERE子句中的未知列”，我的尝试语句是：

SELECT DISTINCT user_id,(
SELECT MAX(SUM) AS streak
FROM (
  SELECT
    game_date,
    IF(points > 0, @sum:=@sum+1, @sum:=0) AS SUM
  FROM (
    SELECT
      game_date,
      (SELECT COUNT(*) FROM POINT WHERE user_id = p.user_id AND bet_id = b.id AND goals > 0) AS points
    FROM bet b
    WHERE game_date < NOW()
    ORDER BY game_date DESC
  ) t1, (SELECT @sum:=0) t2
) t) AS streak FROM POINT p;

问题分析 #

• 列作用域限制：内层嵌套的子查询无法直接引用最外层POINT p表的user_id，MySQL不支持跨多层级的外部列引用。
• 全局变量污染：@sum是会话级全局变量，遍历不同用户时不会自动重置，会导致不同用户的streak计算结果互相干扰。

修正后的查询语句 #

兼容旧版MySQL的写法（使用变量） #

以下语句能正确为每个用户计算最长连续积分streak，兼容MySQL 5.x版本：

SELECT
  user_id,
  MAX(streak) AS max_streak
FROM (
  SELECT
    p.user_id,
    b.game_date,
    -- 判断当前投注是否有积分，同时处理用户切换时的变量重置
    IF(
      (SELECT COUNT(*) FROM point WHERE user_id = p.user_id AND bet_id = b.id AND goals > 0) > 0,
      @current_streak := IF(@prev_user = p.user_id, @current_streak + 1, 1),
      @current_streak := IF(@prev_user = p.user_id, 0, 0)
    ) AS streak,
    -- 更新上一个用户ID，用于下一行判断
    @prev_user := p.user_id
  FROM
    -- 获取所有需要计算的用户列表
    (SELECT DISTINCT user_id FROM point) p
  CROSS JOIN
    bet b
  CROSS JOIN
    -- 初始化变量：当前连续计数、上一个用户ID
    (SELECT @current_streak := 0, @prev_user := -1) vars
  WHERE
    b.game_date < NOW()
  -- 必须按用户ID、投注日期倒序排序，保证连续计算的正确性
  ORDER BY
    p.user_id,
    b.game_date DESC
) user_streaks
-- 按用户分组取最大连续值
GROUP BY user_id;

MySQL 8.0+ 更简洁的写法（窗口函数） #

如果你的MySQL版本是8.0及以上，推荐用窗口函数实现，无需依赖变量，逻辑更清晰：

-- 第一步：计算每个用户每笔投注是否获得积分
WITH user_bet_points AS (
  SELECT
    p.user_id,
    b.game_date,
    -- 有积分标记为1，无则为0
    CASE WHEN COUNT(po.id) > 0 THEN 1 ELSE 0 END AS has_points
  FROM
    (SELECT DISTINCT user_id FROM point) p
  CROSS JOIN bet b
  LEFT JOIN point po 
    ON po.user_id = p.user_id 
    AND po.bet_id = b.id 
    AND po.goals > 0
  WHERE b.game_date < NOW()
  GROUP BY p.user_id, b.game_date
),
-- 第二步：为每个用户的连续积分记录分组
user_streaks AS (
  SELECT
    user_id,
    game_date,
    has_points,
    -- 用无积分的记录作为分组边界，划分连续积分的区间
    SUM(1 - has_points) OVER (PARTITION BY user_id ORDER BY game_date DESC) AS streak_group
  FROM user_bet_points
)
-- 第三步：按用户和分组统计连续长度，取最大值
SELECT
  user_id,
  MAX(COUNT(*)) AS max_streak
FROM user_streaks
WHERE has_points = 1
GROUP BY user_id, streak_group;

原语句错误修复说明 #

1. 避免跨层列引用：将用户表与投注表先做关联，再计算积分，确保列的作用域能覆盖到所有需要的子查询。
2. 变量重置逻辑：新增@prev_user变量，在切换用户时重置@current_streak，避免不同用户的计算互相干扰。

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