SQL如何查询用户连续达标的天数_窗口函数状态机模型

首页

数据库

热心网友

转载

2026-04-27

SQL如何查询用户连续达标的天数：窗口函数状态机模型

SQL如何查询用户连续达标的天数_窗口函数状态机模型

说起查询“连续达标”天数，很多人的第一反应可能是用日期相减。但这里有个本质问题需要先想清楚：我们到底在识别什么？

“连续达标”的本质是识别不间断的满足条件时间序列，需用LAG()判断状态延续性并用SUM() OVER构造段ID，而非依赖日期相减。

什么是“连续达标”的本质问题

连续达标，核心在于“连续”二字。它不是一个简单的求和或计数，而是要在一串时间序列里，精准地揪出那些满足条件且不间断的片段。SQL本身并没有“连续”这个原生概念，这就需要我们借助窗口函数，构建一套状态转移的逻辑：如果前一天达标，并且当天也达标，那么状态就延续；否则，就视为一个新连续段的开始。这里的关键在于，要用LAG()或SUM() OVER这类工具来“感知”连续性是否被打断，而不是粗暴地用日期相减——一旦数据有缺失，后者立马就会误判。

具体操作时，可以遵循这几个步骤：

首先，明确定义什么叫“达标”，通常是一个布尔表达式（比如score >= 80），并统一转换成0或1。
接着，可以考虑用SUM() OVER (ORDER BY date ROWS UNBOUNDED PRECEDING)来累加一个“中断标记”。
不过，更稳妥、也更清晰的思路是：利用LAG()函数获取前一行的达标状态，然后与当前行进行逻辑比较，从而生成一个用于分组的“连续段ID”。

用LAG+条件累计生成连续段ID

这个思路的核心非常直观：如果当前行达标，并且前一天也达标，那么它们就属于同一个连续段；反之，则开启一个新段。实现起来需要两层计算：第一层，判断当前位置是否发生了“中断”；第二层，通过SUM() OVER累积这些中断次数，这个累积值本身，就成了每个连续段的唯一ID。

来看一个在PostgreSQL、MySQL 8.0+或SQL Server中都适用的示例：

SELECT
  user_id,
  date,
  score,
  -- 第一步：标记是否“中断连续”（即：今日达标但昨日不达标，或今日不达标）
  CASE
    WHEN score >= 80 AND COALESCE(LAG(score) OVER (PARTITION BY user_id ORDER BY date), 0) < 80
      THEN 1
    WHEN score < 80 THEN 1
    ELSE 0
  END AS is_break,
  -- 第二步：按用户累计中断次数，得到每段连续区间的唯一ID
  SUM(CASE
        WHEN score >= 80 AND COALESCE(LAG(score) OVER (PARTITION BY user_id ORDER BY date), 0) < 80
          THEN 1
        WHEN score < 80 THEN 1
        ELSE 0
      END) OVER (PARTITION BY user_id ORDER BY date) AS streak_group
FROM user_daily_score;

这里有个细节值得注意：COALESCE(LAG(...), 0)是为了处理第一行数据LAG()返回NULL的情况，避免整个CASE表达式失效——这个坑不少人都踩过。

按streak_group聚合出最大连续天数

一旦有了streak_group，问题就变得简单了。连续达标段现在变成了一个标准的分组聚合问题。但别忘了，我们只关心那些“全部达标”的段，也就是说，只有组内score >= 80的记录才参与统计。

接着上面的例子，进行聚合：

WITH labeled AS (
  SELECT
    user_id,
    date,
    score,
    SUM(CASE
          WHEN score >= 80 AND COALESCE(LAG(score) OVER (PARTITION BY user_id ORDER BY date), 0) < 80 THEN 1
          WHEN score < 80 THEN 1
          ELSE 0
        END) OVER (PARTITION BY user_id ORDER BY date) AS streak_group
  FROM user_daily_score
),
streaks AS (
  SELECT
    user_id,
    streak_group,
    COUNT(*) AS days,
    MIN(date) AS start_date,
    MAX(date) AS end_date
  FROM labeled
  WHERE score >= 80  -- 只取达标日参与连续段统计
  GROUP BY user_id, streak_group
)
SELECT
  user_id,
  MAX(days) AS max_consecutive_days
FROM streaks
GROUP BY user_id;

这里有三个关键点需要把握：

WHERE score >= 80这个过滤条件，必须放在streaks这个子查询里。如果放到最外层，可能会错误地漏掉那些被不达标日包围的短连续段。
虽然不同数据库对LAG()的NULL处理逻辑基本一致，但如果ORDER BY的日期字段存在重复值，务必增加一个次级排序列（比如id），以保证结果的确定性。
这个方案依赖于窗口函数，因此像MySQL 5.7这样的旧版本无法使用。在那些版本上，只能通过用户变量或复杂的自连接来模拟，不仅性能差，而且极易出错。