为阅读次数相同的用户按阅读时间分配小数排序值

嘿，这个需求我刚好有现成的解决方案，咱们用SQL窗口函数就能轻松搞定！先理清楚核心逻辑：要先把每个用户对目标报告的阅读次数和最后阅读时间统计出来，然后针对阅读次数相同的用户，按最后阅读时间从晚到早排序，再给他们分配对应的小数——最晚的拿0.1，最早的拿0.3。

咱们一步步来实现：

第一步：统计用户核心阅读数据 #

首先得聚合每个用户的关键数据：阅读次数和最后一次阅读的时间。假设咱们的业务表叫report_tracking，字段包含user_id（用户ID）、report_name（报告名称）、read_timestamp（阅读时间戳）。

第二步：给并列用户按阅读时间排名 #

接下来，在同一个报告、相同阅读次数的分组里，用窗口函数按最后阅读时间倒序排名，这样最晚阅读的用户会排第1位。

第三步：映射对应的小数排名 #

最后根据排名直接分配你需要的小数即可，第1名（最晚阅读）给0.1，第2名给0.2，第3名（最早阅读）给0.3。

下面是完整的SQL代码示例：

-- 先统计每个用户的阅读次数和最后阅读时间
WITH user_report_stats AS (
    SELECT
        user_id,
        report_name,
        COUNT(*) AS read_count,
        MAX(read_timestamp) AS last_read_time
    FROM report_tracking
    WHERE report_name = '游泳' -- 可去掉该条件以处理所有报告
    GROUP BY user_id, report_name
),
-- 给阅读次数相同的用户按最后阅读时间降序排名
ranked_users AS (
    SELECT
        *,
        RANK() OVER (PARTITION BY report_name, read_count ORDER BY last_read_time DESC) AS read_rank
    FROM user_report_stats
)
-- 分配对应的小数排名
SELECT
    user_id,
    report_name,
    read_count,
    last_read_time,
    CASE read_rank
        WHEN 1 THEN 0.1
        WHEN 2 THEN 0.2
        WHEN 3 THEN 0.3
        -- 若有更多并列用户，可继续扩展CASE分支，比如WHEN 4 THEN 0.4
    END AS decimal_rank
FROM ranked_users
WHERE read_count = 2 -- 筛选阅读次数为2的用户，也可去掉该条件查看所有数据
ORDER BY report_name, read_count, last_read_time DESC;

代码细节说明 #

• user_report_stats：这个CTE负责聚合每个用户对每个报告的阅读次数，以及他们最后一次阅读的时间，是后续排名的基础数据来源。
• ranked_users：用RANK()窗口函数，在report_name和read_count相同的分组内，按照last_read_time降序排序，确保最晚阅读的用户拿到read_rank = 1。
• 最后的CASE语句：直接把排名映射成你需要的小数，完美匹配“最后阅读的用户分配.1，最先阅读的用户分配.3”的需求。

如果你的表结构里已经有现成的累计read_count字段（不是每次阅读生成一条记录），可以简化第一个CTE，直接取last_read_time即可，核心逻辑完全一致。

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