MySQL NTILE函数

时间:2019-11-20 08:52:27  来源:igfitidea点击:

简介:在本教程中,您将学习如何使用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的9个整数:

CREATE TABLE t (
    val INT NOT NULL
);

INSERT INTO t(val) 
VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9);

SELECT * FROM t;

如果使用NTILE()函数将九行分为四组,则最终将在第一组中包含三行,而其他三组中包含四行。

请参见以下演示:

SELECT 
    val, 
    NTILE (4) OVER (
        ORDER BY val
    ) bucket_no
FROM 
    t;

这是输出:

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

让我们将组数从四个更改为三个,如以下查询所示:

SELECT 
    val, 
    NTILE (3) OVER (
        ORDER BY val
    ) bucket_no
FROM 
    t;

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

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()函数将每年按产品线划分的销售额分成三组。

这是输出:

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