基于account和score分组按日期生成同组同排名的SQL问题求助

原查询的问题 #

你的查询存在两个关键错误：

1. 分区条件错误：PARTITION BY account, score会将同一账号且同分数的记录单独划分到一个分区，再按load_date排序时，DENSE_RANK()会给每个不同日期的记录分配递增排名，这与你“同账号同分数记录共享排名”的需求完全相反。
2. 未使用CTE结果：最终查询直接select * from trans，完全没有用到CTE中计算的ranking字段，相当于白做了窗口函数计算。

正确解法 #

要实现“同账号同分数记录共享排名，排名按分数组首次出现的日期顺序排列”的需求，我们需要先确定每个账号下不同分数组的首次出现日期，再基于这个日期进行排名。

方法一：单窗口函数实现 #

WITH ranked_scores AS (
    SELECT 
        account,
        score,
        load_date,
        -- 先计算每个账号+分数组的最早加载日期，再基于这个日期在账号内排名
        DENSE_RANK() OVER (
            PARTITION BY account 
            ORDER BY MIN(load_date) OVER (PARTITION BY account, score)
        ) AS ranking
    FROM trans
)
SELECT account, score, ranking, load_date
FROM ranked_scores
ORDER BY account, load_date;

方法二：分组+关联实现 #

如果对嵌套窗口函数不太熟悉，可以先分组获取每个账号+分数组的首次加载日期，再给这些组排名，最后关联原表：

WITH score_groups AS (
    -- 计算每个账号+分数组的最早加载日期
    SELECT 
        account,
        score,
        MIN(load_date) AS first_load_date
    FROM trans
    GROUP BY account, score
),
ranked_groups AS (
    -- 给每个账号下的分数组按首次日期排名
    SELECT 
        account,
        score,
        DENSE_RANK() OVER (PARTITION BY account ORDER BY first_load_date) AS ranking
    FROM score_groups
)
-- 关联原表，将排名赋给每条记录
SELECT 
    t.account,
    t.score,
    rg.ranking,
    t.load_date
FROM trans t
JOIN ranked_groups rg 
    ON t.account = rg.account AND t.score = rg.score
ORDER BY t.account, t.load_date;

结果验证 #

执行上述任意查询后，都会得到符合你预期的结果：

• 账号A1中，0分的所有记录排名为1，5分排名为2，10分排名为3；
• 账号A2中，10分的所有记录排名为1，0分排名为2；
• 同账号同分数的不同日期记录共享相同排名，排名按分数组首次出现的日期顺序递增。

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