MySQL NTILE 函数
简介:在本教程中,您将学习如何使用MySQL
NTILE()
函数将行划分为指定数量的组。
MySQL NTILE() 函数简介
MySQLNTILE()
函数将排序分区中的行划分为特定数量的组。从每个组分配一个从一开始的桶号。对于每一行,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()
函数将行分配到指定数量的组中。