MariaDB NTILE() 函数的基础用法与实例

MariaDB NTILE() 函数用来将数据分成指定数量的组，并为每一行数据分配一个所属组的编号。

发布于

MariaDB NTILE() 函数用来将数据分成指定数量的组，并为每一行数据分配一个所属组的编号。该函数常用于数据分组、排名等场景。

语法

MariaDB NTILE() 函数的语法如下：

NTILE(buckets) OVER (
  PARTITION BY partition_expression
  ORDER BY order_expression
);

这里：

  • buckets: 要将数据分成的组数，是一个正整数。
  • partition_expression: 可选参数，指定分区表达式，用于将数据划分为不同的分区进行分组。
  • order_expression: 可选参数，指定排序表达式，用于确定每个分区中行的顺序。

NTILE() 函数返回一个整数，表示该行数据所在组的编号。

实例

将数据分成四组

DROP TABLE IF EXISTS t;

CREATE TABLE t (
  id INT,
  name VARCHAR(20)
);

INSERT INTO t VALUES
  (1, 'Alice'),
  (2, 'Bob'),
  (3, 'Charlie'),
  (4, 'Dave'),
  (5, 'Eve');

SELECT id, name, NTILE(4) OVER () AS group_id
FROM t;

以下是该语句的输出：

+------+---------+----------+
| id   | name    | group_id |
+------+---------+----------+
|    1 | Alice   |        1 |
|    2 | Bob     |        1 |
|    3 | Charlie |        2 |
|    4 | Dave    |        3 |
|    5 | Eve     |        4 |
+------+---------+----------+

该示例将表 t 中的数据分成四组，并为每一行数据分配一个所属组的编号。由于没有指定 PARTITION BY 子句，因此所有数据都作为一个整体进行分组。

按性别分组并排名

DROP TABLE IF EXISTS t;

CREATE TABLE t (
  id INT,
  name VARCHAR(20),
  gender VARCHAR(10)
);

INSERT INTO t VALUES
  (1, 'Alice', 'female'),
  (2, 'Bob', 'male'),
  (3, 'Charlie', 'male'),
  (4, 'Dave', 'male'),
  (5, 'Eve', 'female');

SELECT id, name, gender, NTILE(2) OVER (PARTITION BY gender ORDER BY name) AS rank
FROM t;

以下是该语句的输出：

+------+---------+--------+------+
| id   | name    | gender | rank |
+------+---------+--------+------+
|    1 | Alice   | female |    1 |
|    5 | Eve     | female |    2 |
|    2 | Bob     | male   |    1 |
|    3 | Charlie | male   |    1 |
|    4 | Dave    | male   |    2 |
+------+---------+--------+------+

说明: 该示例将表 t 中的数据按性别进行分组，并对每个分组中的数据按姓名进行排序。然后，使用 NTILE() 函数为每个分组中的数据分配排名。

使用子查询进行分组

DROP TABLE IF EXISTS t;

CREATE TABLE t (
  id INT,
  value INT
);

INSERT INTO t VALUES
  (1, 10),
  (2, 20),
  (3, 30),
  (4, 40),
  (5, 50);

SELECT t.id, t.value, group_id
FROM t
JOIN (
  SELECT id, value, NTILE(4) OVER (ORDER BY value) AS group_id
  FROM t
) AS subquery
ON t.id = subquery.id;

以下是该语句的输出：

+------+-------+----------+
| id   | value | group_id |
+------+-------+----------+
|    1 |    10 |        1 |
|    2 |    20 |        1 |
|    3 |    30 |        2 |
|    4 |    40 |        3 |
|    5 |    50 |        4 |
+------+-------+----------+

该示例使用子查询来计算每个行的 group_id 值。然后，将子查询的结果与原始表进行连接，以获得最终结果。

使用 CTE 进行分组

DROP TABLE IF EXISTS t;

CREATE TABLE t (
  id INT,
  value INT
);

INSERT INTO t VALUES
  (1, 10),
  (2, 20),
  (3, 30),
  (4, 40),
  (5, 50);

WITH cte AS (
  SELECT id, value, NTILE(4) OVER (ORDER BY value) AS group_id
  FROM t
)
SELECT id, value, group_id
FROM cte;

以下是该语句的输出：

+------+-------+----------+
| id   | value | group_id |
+------+-------+----------+
|    1 |    10 |        1 |
|    2 |    20 |        1 |
|    3 |    30 |        2 |
|    4 |    40 |        3 |
|    5 |    50 |        4 |
+------+-------+----------+

该示例使用 CTE (Common Table Expression) 来计算每个行的 group_id 值。然后，从 CTE 中查询最终结果。

相关函数

结论

MariaDB NTILE() 函数是一个非常有用的函数，可以用于数据分组、排名等场景。通过使用不同的参数和选项，可以实现不同的功能。