SQLite ntile() 函数使用指南

SQLite ntile() 函数将当前行所在的分区内的所有行尽可能平均的分成指定数量的区间，并返回当前行所在的区间编号。

每个区间， SQLite 称之为一个排名桶。 ntile() 根据指定排序为每个桶指设定排名。

ntile() 语法

这里是 SQLite ntile() 函数的语法：

ntile(buckets)
OVER (
  [PARTITION BY partition_column_list]
  [ORDER BY order_column_list]
)

参数

buckets
必需的。桶的数量。桶的数量最大为此分区内的行的数量。
partition_column_list
参与分区的列的列表。
order_column_list
参与排序的列的列表。

返回值

SQLite ntile() 函数将当前行所在的分区内的所有行尽可能平均的分成指定数量的排名桶，并返回当前行所在的桶的排名。

假设，您有 1 到 9 这 9 个数字， 您使用 ntile(3) 将他们按照升序分成 3 个桶，按照尽可能平均分配的原则，那么 1-3 的桶排名是 1， 4-6 的桶排名是 2， 7-9 的桶排名是 3。 在下面的语句中使用 generate_series() 产生一个包括 1 到 9 的结果集：

SELECT
  value,
  ntile(3) over (
    ORDER BY value
  ) ntile
FROM
  generate_series(1, 9);

value  ntile
-----  -----
1      1
2      1
3      1
4      2
5      2
6      2
7      3
8      3
9      3

ntile() 示例

演示数据准备

使用下面的 CREATE TABLE 语句创建一个表 tax_revenue 以存储每季度的税收收益：

CREATE TABLE tax_revenue (
  id INTEGER PRIMARY KEY,
  year CHAR(4) NOT NULL,
  quarter CHAR(1) NOT NULL,
  revenue INT NOT NULL
);

这里创建了一个 tax_revenue 表，它有 5 个列：

  • id - 行 ID，主键。
  • year - 年份。
  • quarter - 季节，1 - 4。
  • revenue - 税收收益。

使用下面的 INSERT 语句向 tax_revenue 表中插入一些行：

INSERT INTO tax_revenue
  (year, quarter, revenue)
VALUES
  ('2020', '1', 3515),
  ('2020', '2', 3678),
  ('2020', '3', 4203),
  ('2020', '4', 3924),
  ('2021', '1', 3102),
  ('2021', '2', 3293),
  ('2021', '3', 3602),
  ('2021', '4', 2901);

使用下面的 SELECT 语句检索表中的数据：

SELECT * FROM tax_revenue;

id  year  quarter  revenue
--  ----  -------  -------
1   2020  1        3515
2   2020  2        3678
3   2020  3        4203
4   2020  4        3924
5   2021  1        3102
6   2021  2        3293
7   2021  3        3602
8   2021  4        2901

使用 SQLite ntile() 分成 2 个桶

下面的语句，在使用 SQLite ntile() 函数将每年的收益按照升序分成 2 桶：

SELECT
  *,
  ntile(2) OVER (
    PARTITION BY year
    ORDER BY revenue
  ) ntile
FROM tax_revenue;

id  year  quarter  revenue  ntile
--  ----  -------  -------  -----
1   2020  1        3515     1
2   2020  2        3678     1
4   2020  4        3924     2
3   2020  3        4203     2
8   2021  4        2901     1
5   2021  1        3102     1
6   2021  2        3293     2
7   2021  3        3602     2

注意，上面 SQL 语句中的窗口函数：

ntile(2) OVER (
  PARTITION BY year
  ORDER BY revenue
)

在 OVER 子句中，

  • PARTITION BY year 将所有行按照年份进行分区
  • ORDER BY revenue 将每个分区内的行按照收益升序排列
  • ntile(2) 将每个分区的收益尽可能平均的分成 2 个桶。由于每年有 4 行，所以每个桶有 2 行。所以每年的前两行的桶排名为 1， 后两行的桶排名为 2。