如何计算SQL表中每个用户的活跃天数排名_应用DENSE

dense_rank()更适合活跃天数排名，因其对相同活跃天数用户赋予连续名次（如并列第1后为第2），而rank()会跳名；需先聚合计算每人活跃天数，再用cte或子查询配合dense_rank()排序。

如何计算sql表中每个用户的活跃天数排名_应用dense_rank处理重复天数

为什么 DENSE_RANK() 比 RANK() 更适合活跃天数排名

当多个用户在同一天内有行为（比如都登录了），他们对应的「活跃天数」可能完全相同。用 RANK() 会导致跳名：比如两个用户并列第1，下一个就是第3；而 DENSE_RANK() 会连续编号——并列第1后，下一个是第2。这对业务上“第几梯队”的理解更直观。

关键区别在于重复值的处理逻辑：RANK() 统计的是“前面有多少个更优排名 + 1”，DENSE_RANK() 统计的是“前面有多少个不同的更优值 + 1”。

若用户A、B活跃天数都是15天，用户C是14天：DENSE_RANK() 给A/B都是1，C是2
同样数据下：RANK() 也给A/B都是1，但C是3（因为跳过了2）
ROW_NUMBER() 则不管相等，强行按顺序编1/2/3——不适合排名场景

先聚合再排序：必须用子查询或 CTE

直接在原行为表上套 DENSE_RANK() 会出错，因为窗口函数不能和非聚合字段混用。活跃天数本身是每个用户的聚合结果（COUNT(DISTINCT login_date)），所以得先算出每人多少天，再排名。

推荐写法是 CTE，清晰且兼容多数数据库（PostgreSQL / MySQL 8.0+ / SQL Server / Oracle）：

WITH user_active_days AS (
  SELECT
    user_id,
    COUNT(DISTINCT DATE(login_time)) AS active_days
  FROM user_logins
  WHERE login_time >= '2024-01-01'
  GROUP BY user_id
)
SELECT
  user_id,
  active_days,
  DENSE_RANK() OVER (ORDER BY active_days DESC) AS rank
FROM user_active_days;

DATE(login_time) 确保把带时分秒的时间转成纯日期，否则 DISTINCT 可能漏掉同日多次登录
WHERE 条件必须放在 CTE 内部，而不是 OVER 子句里——窗口函数不接收过滤逻辑
别在 GROUP BY 外直接加 DENSE_RANK()，会报 “column must appear in GROUP BY” 错误

MySQL 5.7 或旧版 PostgreSQL 怎么办

这些版本不支持窗口函数，得用变量模拟 DENSE_RANK()。核心思路是：按活跃天数倒序排，用变量记录上一个值，相同时不增秩，不同时才加1。

SELECT
  user_id,
  active_days,
  @rank := IF(@prev = active_days, @rank, @rank + 1) AS rank,
  @prev := active_days
FROM (
  SELECT user_id, COUNT(DISTINCT DATE(login_time)) AS active_days
  FROM user_logins
  WHERE login_time >= '2024-01-01'
  GROUP BY user_id
  ORDER BY active_days DESC
) t
CROSS JOIN (SELECT @rank := 0, @prev := NULL) init;

必须显式 ORDER BY active_days DESC，否则变量无法稳定比较
@prev := active_days 要放在最后，确保每次读取的是当前行的值
该写法在 MySQL 中有效，但在 PostgreSQL 旧版需改用递归 CTE 或自连接，复杂度陡增

注意 NULL 和边界值对排名的影响

如果某用户没有登录记录，COUNT(DISTINCT ...) 返回 0，不是 NULL；但如果用了 LEFT JOIN 导致某些 login_time 为 NULL，则 COUNT(DISTINCT NULL) 结果是 0（标准 SQL 行为），但部分数据库（如早期 SQLite）可能表现不同。