ROW_NUMBER

返回行在当前分区内的编号。行按照窗口定义中 ORDER BY 子句定义的顺序从 1 开始编号，否则按任意顺序编号。

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

---

语法

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

参数

PARTITION BY column 可选。将结果集划分为多个分区。ROW_NUMBER 函数分别应用于每个分区，并为每个分区重置行号。

ORDER BY column 指定分配行号的顺序。要获得有意义的结果，必须指定。

返回值

返回一个整数，表示分区内的顺序行号，从 1 开始。

---

示例

在仓库内对贡献者排名

-- Rank contributors within a repository
SELECT
    login,
    contributions,
    ROW_NUMBER() OVER (ORDER BY contributions DESC) as contribution_rank
FROM github.repos.contributors
WHERE owner = 'stackql'
  AND repo = 'stackql';

跟踪 issue 随时间的顺序

-- Track issue growth over time
SELECT
    number,
    title,
    state,
    created_at,
    ROW_NUMBER() OVER (ORDER BY created_at) as issue_sequence
FROM github.issues.issues
WHERE owner = 'stackql'
  AND repo = 'stackql'
ORDER BY created_at;

比较 ROW_NUMBER 与 RANK 和 DENSE_RANK

-- 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';

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