orcla计算截至当前每个用户已经连续签到的天数

Oracle 数据库中计算每个用户截至当前日期的连续签到天数
为了实现这一目标，可以通过 ROW_NUMBER() 分析函数来处理用户的登录时间序列，并结合日期差值逻辑完成连续签到天数的统计。以下是完整的解决方案：
SQL 查询设计
假设表名为 USER_LOGIN，其中包含字段 PID 表示用户 ID 和 LOGIN_TIME 表示登录时间。
WITH CTE AS (
SELECT
PID,
TRUNC(LOGIN_TIME) AS LOGIN_DAY,
ROW_NUMBER() OVER(PARTITION BY PID ORDER BY TRUNC(LOGIN_TIME)) AS RN,
TO_NUMBER(TO_CHAR(TRUNC(LOGIN_TIME), 'J')) - ROW_NUMBER() OVER(PARTITION BY PID ORDER BY TRUNC(LOGIN_TIME)) AS GRP
FROM USER_LOGIN
),
CTE2 AS (
SELECT
PID,
MAX(LOGIN_DAY) KEEP (DENSE_RANK LAST ORDER BY LOGIN_DAY) AS LATEST_LOGIN_DAY,
COUNT(*) AS CONSECUTIVE_DAYS
FROM CTE
GROUP BY PID, GRP
)
SELECT DISTINCT
T1.PID,
NVL(T2.CONSECUTIVE_DAYS, 0) AS CONSECUTIVE_SIGN_IN_DAYS
FROM (
SELECT DISTINCT PID FROM USER_LOGIN
) T1
LEFT JOIN CTE2 T2 ON T1.PID = T2.PID AND T2.LATEST_LOGIN_DAY = SYSDATE - 1;
登录后复制

解决方案说明

去重并截取日期部分使用 TRUNC(LOGIN_TIME) 将时间戳转换为仅保留日期的部分[^1]。这一步是为了确保同一日多次登录不会被重复计数。

分配序号利用 ROW_NUMBER() 函数按用户分组 (PARTITION BY PID) 并按照登录日期升序排列 (ORDER BY TRUNC(LOGIN_TIME)) 来生成每条记录的序号[^4]。

构建分组依据计算每一行的 Julian Day 值减去其对应的序号作为新的分组键 GRP。如果两行之间的日期间隔恰好等于它们的序号差，则这两行属于同一个连续区间。

聚合连续区间的最大长度对于每一个 (PID, GRP) 组合，统计该组合内的记录数量即代表此区间的连续天数。同时提取最新的登录日期以便后续筛选最近的有效区间[^2]。

过滤最新有效区间只选取那些截止至昨日为止的最大连续天数组合(SYSDATE-1)，因为今日的数据可能尚未完全录入[^3]。

左连接补充无数据情况如果某些用户从未有过任何登录行为或者他们的最长连续期已经中断超过一天以上，在最终结果集中这些人的连续签到天数应显示为零。因此这里采用了外联结方式引入所有唯一用户列表。

注意事项

上述查询假定系统中的时间为 UTC 或者本地时区一致；如果有跨时区需求需额外调整 SYSDATE 的应用。
若存在未来预设的虚拟打卡记录则可能导致错误结论，建议先清理掉不符合条件的时间点再执行上述操作。