RANK

返回当前行的排名，排名留有间隔。该值是每组中第一个同级行的 ROW_NUMBER。排序列的值相等的行获得相同的排名，下一个排名值按并列行的数量递增（留有间隔）。

如果没有 ORDER BY 子句，则所有行都被视为同级行，该函数始终返回 1。

另请参阅： [SELECT] [ROW_NUMBER] [DENSE_RANK]

---

语法

SELECT RANK() OVER ([PARTITION BY column] ORDER BY column [ASC|DESC])
FROM <multipartIdentifier>;

参数

PARTITION BY column 可选。将结果集划分为多个分区。RANK 函数分别应用于每个分区。

ORDER BY column 指定分配排名的顺序。如果没有 ORDER BY 子句，则所有行都被视为同级行，排名均为 1。

返回值

返回一个整数，表示当前行在分区内的排名，并列的值会产生间隔。

---

示例

按贡献数对贡献者排名（留有间隔）

-- Rank contributors with gaps for ties
SELECT
    login,
    contributions,
    RANK() OVER (ORDER BY contributions DESC) as rank
FROM github.repos.contributors
WHERE owner = 'stackql'
  AND repo = 'stackql';

比较 RANK 与 DENSE_RANK 和 ROW_NUMBER

-- Compare RANK vs DENSE_RANK vs ROW_NUMBER for contributors
SELECT
    login,
    contributions,
    RANK() OVER (ORDER BY contributions DESC) as rank,
    DENSE_RANK() OVER (ORDER BY contributions DESC) as dense_rank,
    ROW_NUMBER() OVER (ORDER BY contributions DESC) as row_num
FROM github.repos.contributors
WHERE owner = 'stackql'
  AND repo = 'stackql';

使用 PARTITION BY 在每个仓库内对贡献者排名

-- Rank contributors within each repo
WITH repo_contributors AS (
    SELECT
        repo,
        login,
        contributions,
        RANK() OVER (PARTITION BY repo ORDER BY contributions DESC) as rank_in_repo
    FROM (
        SELECT 'stackql' as repo, login, contributions
        FROM github.repos.contributors
        WHERE owner = 'stackql' AND repo = 'stackql'
        UNION ALL
        SELECT 'stackql-provider-registry' as repo, login, contributions
        FROM github.repos.contributors
        WHERE owner = 'stackql' AND repo = 'stackql-provider-registry'
    ) t
)
SELECT
    repo,
    login,
    contributions,
    rank_in_repo
FROM repo_contributors
ORDER BY repo, rank_in_repo;

有关更多信息，请参阅 https://sqlite.org/windowfunctions.html#built-in_window_functions。