RANK() OVER PARTITION BY查询返回结果数量不符合预期问题

问题根因 #

你的SQL返回数据少于预期的核心原因有两点：

• 过滤逻辑与预期不匹配：你当前的WHERE条件rnk <=5 AND rnk2 <=5要求单场比赛同时满足两个规则：
  1. 该场是主队在对应联赛下最近的5场主场完赛比赛之一
  2. 该场同时是客队在对应联赛下最近的5场客场完赛比赛之一
     只要其中一个条件不满足，该场比赛就会被过滤，自然会出现某支球队相关的结果不足5条的情况。
• RANK()函数的跳号特性：如果同分区内存在多条data_evento完全相同的记录，RANK()会给相同排名，下一个排名直接跳号（比如2条并列第4，下一条排名直接为6），此时rnk <=5的过滤条件最多只能返回4条记录。

修复方案 #

1. 调整排名逻辑匹配需求 #

如果你的核心需求是获取每个球队（不分主客场）最近5场已完赛比赛，可以先将主客场数据打平为统一的球队维度再做排名，示例代码如下：

WITH all_team_matches AS (
    -- 拆分主队维度的比赛
    SELECT 
        team_home_name AS team_name,
        campionato_id,
        nome_evento,
        data_evento
    FROM eventi_curseason 
    WHERE status_short = 'FT'
    UNION ALL
    -- 拆分客队维度的比赛
    SELECT 
        team_away_name AS team_name,
        campionato_id,
        nome_evento,
        data_evento
    FROM eventi_curseason 
    WHERE status_short = 'FT'
),
ranked_matches AS (
    SELECT 
        *,
        -- 用ROW_NUMBER避免并列跳号，需保留并列排名可替换为DENSE_RANK()
        ROW_NUMBER() OVER (PARTITION BY team_name, campionato_id ORDER BY data_evento DESC) AS rnk
    FROM all_team_matches
)
SELECT * FROM ranked_matches WHERE rnk <=5;

2. 保留原有逻辑的修复 #

如果你确实需要保留当前主客场分别排名的逻辑，仅解决跳号导致的条数不足问题，可以将RANK()替换为DENSE_RANK()，该函数遇到并列排名时不会跳号，能保证<=5的条件下至少覆盖到第5个档位的记录。

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