Skip to main content

通过实例说明SQLite NTILE窗口函数

SQLite NTILE()函数简介

SQLite NTILE()函数是一个窗口函数,它通过指定的表达式将有序结果集划分为若干个存储桶,并为每一行指定适当的存储桶编号。

下面显示了NTILE()函数的语法:

NTILE(expression) OVER ( 
PARTITION BY expression1, expression2,...
ORDER BY expression1 [ASC | DESC]expression2,
)

在这种语法中:

表示

表达式可以是文字正整数,也可以是解析为正整数的任何有效表达式。

函数的作用是:将从1到表达式值的数字分配给每一行。桶中的行数最多可以相差1。行数除以桶数的剩余部分分配给每个桶,从桶1开始。

例如,如果有10行和4个桶。每个桶将有2排。10/4的剩余部分是2。因此,第一个桶将有2+1=3行,第二个桶也将有3行。

下图说明了这个想法:

SQLite NTILE

如果表达式的值大于行数,那么NTILE()函数将填充与行数相等的存储桶数。因此,剩余的桶将是空的。

例如,如果有10行和11个存储桶,则每行将分配一个从1到10的存储桶编号。第11个桶将没有行。

请注意,不能在表达式中使用子查询或窗口函数。

分割

PARTITION BY子句将结果集划分为NTILE函数适用的分区。

订购人

ORDER BY子句指定NTILE()应用到的每个分区中的行顺序。

SQLite NTILE()函数示例

我们将使用示例数据库中的tracks表来演示NTILE()函数:

通过示例将SQLite NTILE()函数与ORDER一起使用

下面的语句使用NTILE()函数将唱片集1中曲目表的毫秒列中的值分成4个存储桶。

在这个相册中,毫秒列有10个值,所以这两个额外的值(10/4的剩余值)被分配到bucket 1和bucket 2,因此它们的值比bucket 3或bucket 4更多。

SELECT
Name,
Milliseconds,
NTILE ( 4 ) OVER (
ORDER BY Milliseconds ) LengthBucket
FROM
tracks
WHERE
AlbumId = 1;

以下是输出:

SQLite NTILE with ORDER BY example

使用带有PARTITION BY子句的SQLite NTILE()函数示例

下面的语句使用NTILE()函数将每个专辑的曲目按字节列中的值分成3个存储桶:

SELECT
AlbumId,
Name,
Milliseconds,
NTILE ( 3 ) OVER (
PARTITION BY AlbumId
ORDER BY Bytes ) SizeBucket
FROM
tracks;

下图显示了输出:

SQLite NTILE with PARTITION BY example

在本例中,PARTITION BY子句将行分配到分区或相册中。ORDER BY子句根据每个专辑中的曲目大小(字节)对其进行排序。函数的作用是:为每首曲目分配一个存储桶编号,并重置每首专辑的存储桶编号。

在本教程中,您学习了如何使用SQLite NTILE()函数将结果集划分为多个存储桶,并为每一行指定一个存储桶编号。