MySQL NTILE 函数

简介：在本教程中，您将学习如何使用MySQL NTILE()函数将行划分为指定数量的组。

MySQL NTILE() 函数简介

MySQL NTILE()函数将排序分区中的行划分为特定数量的组。从每个组分配一个从一开始的桶号。对于每一行，NTILE()函数返回一个桶号，表示行所属的组。

以下显示了NTILE()函数的语法：

NTILE(n) OVER (
    PARTITION BY <expression>[{,<expression>...}]
    ORDER BY <expression> [ASC|DESC], [{,<expression>...}]
)

在这个语法中：

•  n是一个字面正整数。桶号的范围是1到n。
• 在PARTITION BY从返回的结果集划分FROM子句为分区到的NTILE()函数被应用。
• ORDER BY子句指定将NTILE()值分配给分区中的行的顺序。

请注意，如果分区行的数量不能被整除n，则NTILE()函数将生成两个大小的组，差异为1。较大的组总是以ORDER BY子句指定的顺序位于较小的组之前。

另一方面，如果分区行的总数可被整除n，则行将在组之间平均分配。

请参阅下表，表存储从1到9的九个整数：

CREATE TABLE  ntileDemo (
    val INT NOT NULL
);
 
INSERT INTO ntileDemo(val) 
VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9);
 
 
SELECT * FROM ntileDemo;

+-----+
| val |
+-----+
|   1 |
|   2 |
|   3 |
|   4 |
|   5 |
|   6 |
|   7 |
|   8 |
|   9 |
+-----+
9 rows in set (0.00 sec)

如果使用NTILE()函数将九行划分为四个组，则最终将在第一组中包含三行，而另外三组包含四行。

请参阅以下演示：

SELECT 
    val, 
    NTILE(4) OVER (
        ORDER BY val
    ) group_no
FROM 
    ntileDemo;

这是输出：

+-----+----------+
| val | group_no |
+-----+----------+
|   1 |        1 |
|   2 |        1 |
|   3 |        1 |
|   4 |        2 |
|   5 |        2 |
|   6 |        3 |
|   7 |        3 |
|   8 |        4 |
|   9 |        4 |
+-----+----------+
9 rows in set (0.01 sec)

从输出中可以看出，第一组有三行，而其他组有两行。

让我们将组的数量从四个更改为三个，如以下查询所示：

SELECT 
    val, 
    NTILE (3) OVER (
        ORDER BY val
    ) group_no
FROM 
    ntileDemo;

结果集现在有三个具有相同行数的组。

+-----+----------+
| val | group_no |
+-----+----------+
|   1 |        1 |
|   2 |        1 |
|   3 |        1 |
|   4 |        2 |
|   5 |        2 |
|   6 |        2 |
|   7 |        3 |
|   8 |        3 |
|   9 |        3 |
+-----+----------+
9 rows in set (0.01 sec)

MySQL NTILE示例

我们将使用orders，orderDetails 和products表从示例数据库的演示。

请参阅以下查询：

WITH productline_sales AS (
    SELECT productline,
           year(orderDate) order_year,
           ROUND(SUM(quantityOrdered * priceEach),0) order_value
    FROM orders
    INNER JOIN orderdetails USING (orderNumber)
    INNER JOIN products USING (productCode)
    GROUP BY productline, order_year
)
SELECT
    productline, 
    order_year, 
    order_value,
    NTILE(3) OVER (
        PARTITION BY order_year
        ORDER BY order_value DESC
    ) product_line_group
FROM 
    productline_sales;

在这个例子中：

• 首先，在productline_sales公用表表达式中，我们按年获得每个产品系列的总订单价值。
• 然后，我们使用NTILE()功能将每年的产品线划分为三组。

这是他的输出：

+------------------+------------+-------------+--------------------+
| productline      | order_year | order_value | product_line_group |
+------------------+------------+-------------+--------------------+
| Classic Cars     |       2013 |     1374832 |                  1 |
| Vintage Cars     |       2013 |      619161 |                  1 |
| Trucks and Buses |       2013 |      376657 |                  1 |
| Motorcycles      |       2013 |      348909 |                  2 |
| Planes           |       2013 |      309784 |                  2 |
| Ships            |       2013 |      222182 |                  3 |
| Trains           |       2013 |       65822 |                  3 |
| Classic Cars     |       2014 |     1763137 |                  1 |
| Vintage Cars     |       2014 |      854552 |                  1 |
| Motorcycles      |       2014 |      527244 |                  1 |
| Planes           |       2014 |      471971 |                  2 |
| Trucks and Buses |       2014 |      465390 |                  2 |
| Ships            |       2014 |      337326 |                  3 |
| Trains           |       2014 |       96286 |                  3 |
| Classic Cars     |       2015 |      715954 |                  1 |
| Vintage Cars     |       2015 |      323846 |                  1 |
| Motorcycles      |       2015 |      245273 |                  1 |
| Trucks and Buses |       2015 |      182066 |                  2 |
| Planes           |       2015 |      172882 |                  2 |
| Ships            |       2015 |      104490 |                  3 |
| Trains           |       2015 |       26425 |                  3 |
+------------------+------------+-------------+--------------------+
21 rows in set (0.07 sec)

在本教程中，您学习了如何使用MySQL NTILE()函数将行分配到指定数量的组中。