在SQL中如何利用窗口函数快速统计连续打卡达标天数-SQL-PHP中文网

“连续打卡达标天数”的核心逻辑是：对达标记录按日期升序编号，用日期减去对应序号的天数偏移，差值相同的即属同一连续段；该方法稳定高效，无需递归或自连接。

在sql中如何利用窗口函数快速统计连续打卡达标天数

什么是“连续打卡达标天数”的核心逻辑

连续打卡的本质是：按日期排序后，相邻记录的日期差为1天，且满足“达标”条件（比如签到状态为 1 或 'success'）。窗口函数本身不直接判断“连续”，但能通过 ROW_NUMBER() 和日期序号做差，把同一连续段映射到相同分组——这是最稳定、无需递归或自连接的解法。

关键点在于：对所有达标记录按日期升序编号（ROW_NUMBER() OVER (ORDER BY date)），再用日期本身转成序号（如 date - '2020-01-01'::INTERVAL 或 EXTRACT(EPOCH FROM date)/86400），两者相减结果一致的，就是同一连续段。

PostgreSQL / MySQL 8.0+ 实现连续天数统计

以 PostgreSQL 为例（MySQL 8.0+ 语法几乎一致）：

SELECT
  date,
  cnt,
  COUNT(*) OVER (PARTITION BY grp) AS streak_days
FROM (
  SELECT
    date,
    status,
    -- 只对达标记录参与计算
    (date - INTERVAL '1 day' * ROW_NUMBER() OVER (ORDER BY date)) AS grp
  FROM打卡表
  WHERE status = 1  -- 或其他达标条件
) t
ORDER BY date;

说明：

ROW_NUMBER() OVER (ORDER BY date) 给每条达标记录一个自然序号（1, 2, 3…）
date - INTERVAL '1 day' * ROW_NUMBER() 把每个连续段“拉回”到同一个基准日，形成唯一 grp
只要日期连续，这个差值就恒定；一旦断一天，grp 就跳变
注意：必须先 WHERE status = 1 过滤，否则非达标日会干扰分组

SQL Server 中需用 DATEDIFF 替代日期运算

SQL Server 不支持直接用 date - number，得改用 DATEDIFF 构造等效偏移：

MacsMind

一款AI工具，主要用于电商AI超级智能客服，适合需要提升相关任务效率的用户。

下载

SELECT
  date,
  COUNT(*) OVER (PARTITION BY grp) AS streak_days
FROM (
  SELECT
    date,
    DATEDIFF(day, '1970-01-01', date)
      - ROW_NUMBER() OVER (ORDER BY date) AS grp
  FROM打卡表
  WHERE status = 1
) t;

常见坑：

别用 DATEDIFF(day, date, date) 自减——结果永远是 0
基准日选 '1970-01-01' 是安全的，避免负数溢出；也可用表中最小日期：(SELECT MIN(date) FROM打卡表)
如果日期字段含时间（TIMESTAMP），先 CAST(date AS DATE) 再参与计算，否则同一天多次打卡会被拆开

如何查“当前最长连续天数”或“最新一次连续长度”

很多业务只关心“用户现在连打了几天”，不是全量统计。这时加一层过滤即可：

SELECT MAX(streak_days) AS max_current_streak
FROM (
  SELECT
    COUNT(*) OVER (PARTITION BY grp) AS streak_days,
    ROW_NUMBER() OVER (ORDER BY date DESC) AS rn
  FROM (
    SELECT
      date,
      date - INTERVAL '1 day' * ROW_NUMBER() OVER (ORDER BY date) AS grp
    FROM打卡表
    WHERE status = 1 AND date >= CURRENT_DATE - INTERVAL '30 days'
  ) t
) t2
WHERE rn = 1;  -- 最新一条记录所在组的长度

要点：