[MSSQL]NTILE另类分页有么有?!

最新推荐文章于 2025-05-20 17:24:48 发布
转载 最新推荐文章于 2025-05-20 17:24:48 发布 · 124 阅读 · 0
文章标签：

#数据库

本文介绍NTILE函数在SQL中的应用，演示如何利用NTILE实现数据分片及分页，同时展示NTILE与PARTITION BY结合使用的案例。

NTILE这个关键字干啥妮?ORACLE管它叫分片,大概意思是把查询出来的结果集分成尽量均等的组(片),有点不直观,来看代码

先看测试用的数据表:

--DROP TABLE T
CREATE TABLE T(GRP_A VARCHAR(20),GRP_B VARCHAR(20),GRP_C VARCHAR(20),VAL INT)
INSERT INTO T(GRP_A,GRP_B,GRP_C,VAL)
SELECT 'a1','b1','c1',10 union all
SELECT 'a1','b1','c2',10 union all
SELECT 'a1','b2','c2',40 union all
SELECT 'a1','b2','c3',40 union all
SELECT 'a1','b2','c3',50 union all
 
SELECT 'a2','b3','c3',12 union all
SELECT 'a2','b3','c3',22 union all
SELECT 'a2','b3','c3',32 
 
SELECT * FROM T
 
--------------------------------------------------------------------------
GRP_A                GRP_B                GRP_C                VAL
-------------------- -------------------- -------------------- -----------
a1                   b1                   c1                   10
a1                   b1                   c2                   10
a1                   b2                   c2                   40
a1                   b2                   c3                   40
a1                   b2                   c3                   50
a2                   b3                   c3                   12
a2                   b3                   c3                   22
a2                   b3                   c3                   32
 
(8 行受影响)

 

试下这个分片功能,SQL脚本比较简单:

SELECT 
    *,
    NTILE(3)OVER(ORDER BY VAL) AS NUM
FROM T
 
GRP_A                GRP_B                GRP_C                VAL         NUM
-------------------- -------------------- -------------------- ----------- --------------------
a1                   b1                   c1                   10          1
a1                   b1                   c2                   10          1
a2                   b3                   c3                   12          1
a2                   b3                   c3                   22          2
a2                   b3                   c3                   32          2
a1                   b2                   c2                   40          2
a1                   b2                   c3                   40          3
a1                   b2                   c3                   50          3
 
(8 行受影响)

 

结果集一共八条,分成了111,222,33一共三片,如果是九条刚好分配好,真不巧,八条,最后一片少一个,这没关系啊,

它就这么个功能,灰常简单,再来分个四片看看,分四片完美均分!

SELECT 
    *,
    NTILE(4)OVER(ORDER BY VAL) AS NUM
FROM T
 
 
GRP_A                GRP_B                GRP_C                VAL         NUM
-------------------- -------------------- -------------------- ----------- --------------------
a1                   b1                   c1                   10          1
a1                   b1                   c2                   10          1
a2                   b3                   c3                   12          2
a2                   b3                   c3                   22          2
a2                   b3                   c3                   32          3
a1                   b2                   c2                   40          3
a1                   b2                   c3                   40          4
a1                   b2                   c3                   50          4
 
(8 行受影响)

 

如上所示,刚好11,22,33,44分成四片

分页的又一方法对啵?比较不常见哈,因为你不知道总记录有多少

假设我们总想分成100页,不考虑每页多少条,这个场景下真可以用NTILE关键字!传100进去即可!

 

因为这里一共八条,所以传100进行,一行记录一页都不够,于是产生了ROW_NUMBER的效果,示例

SELECT 
    *,
    NTILE(100)OVER(ORDER BY VAL) AS NUM
FROM T
 
 
 
GRP_A                GRP_B                GRP_C                VAL         NUM
-------------------- -------------------- -------------------- ----------- --------------------
a1                   b1                   c1                   10          1
a1                   b1                   c2                   10          2
a2                   b3                   c3                   12          3
a2                   b3                   c3                   22          4
a2                   b3                   c3                   32          5
a1                   b2                   c2                   40          6
a1                   b2                   c3                   40          7
a1                   b2                   c3                   50          8
 
(8 行受影响)

 

 

NTILE的另类分页

头脑风暴一下,总记录数可以COUNT查询得知,每页大小已知,是不是可以计算得出多少页来?

DECLARE @TOTAL_COUNT INT,@PAGE_COUNT INT
SET @TOTAL_COUNT = 8
SET @PAGE_COUNT = 3
总页数 = @TOTAL_COUNT/@PAGE_COUNT

是不是这样,总记录数/每页大小=总页数,然后妮?你想干啥?想分页...继续

知道总页数了吧,分片呀,呵呵

SELECT 
    *,
    NTILE(@TOTAL_COUNT/@PAGE_COUNT)OVER(ORDER BY VAL) AS NUM
FROM T

刚试了把,NTILE参数可以是表达式滴,于是全新的分页出来啦~,以上代码执行后结果如下 
GRP_A                GRP_B                GRP_C                VAL         NUM
-------------------- -------------------- -------------------- ----------- --------------------
a1                   b1                   c1                   10          1
a1                   b1                   c2                   10          1
a2                   b3                   c3                   12          1
a2                   b3                   c3                   22          1
a2                   b3                   c3                   32          2
a1                   b2                   c2                   40          2
a1                   b2                   c3                   40          2
a1                   b2                   c3                   50          2
 
(8 行受影响)

然后对外边直接传第几页,就返回NUM等于几的数据,如NUM=2表示第二片的数据,爽不爽?不用再计算@PageIndex * @PageSize然后BETWEEN算(@PageIndex+1) * @PageSize谁用谁知道..

传统分页代码,也不传统啊,ROW_NUMBER分页脚本:

DECLARE @PageSize INT,@PageIndex INT
SELECT @PageIndex = 0,@PageSize = 2
SELECT * FROM 
(
    SELECT
        *,
        ROW_NUMBER()OVER(ORDER BY VAL) AS NUM
    FROM T
) AS T
WHERE NUM BETWEEN @PageIndex * @PageSize AND (@PageIndex + 1) * @PageSize

NTILE另类分页: 
DECLARE @TOTAL_COUNT INT,@PAGE_SIZE INT
SET @TOTAL_COUNT = 8
SET @PAGE_SIZE = 2
----总页数 = @TOTAL_COUNT/@PAGE_COUNT
 
SELECT * FROM 
(
    SELECT 
        *,
        NTILE(@TOTAL_COUNT/@PAGE_SIZE)OVER(ORDER BY VAL) AS NUM
    FROM T
)AS T
WHERE NUM = 3

功能是一样滴啊,除了分页还能揍啥?它的功能就是这么个功能,能揍啥,自己发挥吧

 

NTILE与PARTITION的配合使用:

SELECT 
    *,
    NTILE(4)OVER(PARTITION BY GRP_A ORDER BY VAL ASC) AS NUM
FROM T
 
 
 
GRP_A                GRP_B                GRP_C                VAL         NUM
-------------------- -------------------- -------------------- ----------- --------------------
a1                   b1                   c1                   10          1
a1                   b1                   c2                   10          1
a1                   b2                   c2                   40          2
a1                   b2                   c3                   40          3
a1                   b2                   c3                   50          4
a2                   b3                   c3                   12          1
a2                   b3                   c3                   22          2
a2                   b3                   c3                   32          3
 
(8 行受影响)

 

也没什么特别之处,就是先分组,再排序,最后分片,

这里是先按GRP_A分组,分成了a1,a2两组,在每组内再按VAL排序,最后在每组内再分片,a1组分成了11,2,3,4四片,a2组分成了1,2,3片

 

见笑了

 

 

猜测您可能对下边的文章感兴趣

SQL SERVER 2008 函数大全 - 字符串函数

SQL2008系统统计函数

[MSSQL]GROUPING SETS,ROLLUP,CUBE初体验

[MSSQL]ROW_NUMBER函数

[MSQL]RANK函数

[MSSQL]NTILE另类分页有么有?!

[MSQL]也说SQL中显示星期几函数

[MSSQL]COALESCE与ISNULL函数

[MSSQL]PIVOT函数

[MSSQL]FOR XML AUTO I

[MSSQL]FOR XML AUTO II

[MSSQL]TRY…CATCH…通用格式

如果您喜欢该博客请点击右下角推荐按钮,您的推荐是作者创作的动力!

SQL-窗口函数进阶用法：ntile(),percent_rank(),cume_dist()
weixin_47198715的博客
05-23 1689
ntile(),percent_rank(),cume_dist()
SQLServer ntile获取每组前10%的数据
09-11
在SQL Server中，`NTILE`函数是一种窗口函数（Window Function），它被用来将结果集分割成若干等大小（或接近等大小）的组，这些组被称为“块”或“桶”。`NTILE(x)`中的参数`x`代表你想将数据分成多少个组。这个...
SQLServer中利用NTILE函数对数据进行分组的一点使用
weixin_34162695的博客
05-26 469
本文出处：http://www.cnblogs.com/wy123/p/6908377.html    　　NTILE函数可以按照指定的排序规则，对数据按照指定的组数（M个对象，按照某种排序分N个组）进行分组，可以展现出某一条数据被分配在哪个组中.　　不仅可以单单利用这个特性，还可以借助该特实现更加有意思的功能.　　 NTILE的基本使用 　　NTILE的作用是对数据进行整体上的分组，比如...
【SQL面试】MySQL的NTILE函数使用
gzjgzj12345的博客
03-07 1728
MySQL的NTILE函数使用 MySQL的NTILE函数用于将一个数据集分成n个分组，每个分组中包含尽可能相等数量的行。该函数返回一个数字，该数字表示该行属于的分组号。 NTILE函数在ORDER BY子句中指定排序的列，然后将结果集按照该列的值从小到大或从大到小进行排序。然后，将排序后的结果集分成指定数量的组。NTILE函数为每个行分配一个组号，表示该行属于哪个组。 以下是NTILE函数的语法： NTILE(n) OVER ( [PARTITION BY&nbsp
NTILE函数在SQL Server 2000中的实现方法
weixin_33671935的博客
08-05 169
众所周知，NTILE函数是SQL Server 2005的新特性之一，用于将有序分区中的行分发到指定数目的组中。各个组有编号，编号从一开始。对于每一个行，NTILE 将返回此行所属的组的编号。如果分区的行数不能被整数整除，则将导致一个成员有两种大小不同的组。按照 OVER 子句指定的顺序，较大的组排在较小的组前面。用NTILE函数计算排名值与其它方法一样简单（分区排名方案和排名值效率分析【图文+测...
mysql另类分页方法
02-09 104
mysql> select sql_calc_found_rows id,rankName,control,action from xin_rank where id<50 limit 0,10;select found_rows(); +----+-----------------+---------+--------+ | id | rankName ...
sql四大排名函数之ROW_NUMBER、RANK、DENSE_RANK、NTILE使用介绍
09-08
总结来说，ROW_NUMBER()是按顺序分配唯一的序号，RANK()和DENSE_RANK()处理相同值时有不同的排名策略，RANK()会跳跃，DENSE_RANK()保持连续，而NTILE()则用于将数据分组到固定数量的分区。理解并熟练运用这些函数，...
ntile
最新发布
05-23
例如，假设有 10 条记录，并且设置了 `ntile(3)`： - 每个桶的基础大小为 \( \lfloor 10 / 3 \rfloor = 3 \)。 - 多余的行数为 \( 10 \% 3 = 1 \)。 - 因此，第一个桶会有 \( 3 + 1 = 4 \) 条记录，剩下的两个桶各...
SQL2005 四个排名函数(row_number、rank、dense_rank和ntile)的比较
09-11
rank函数与rank函数类似，同样考虑到了...这些函数在数据分析、报表生成以及分页查询等方面都有广泛应用，理解并掌握它们可以帮助开发者更高效地处理SQL查询任务。在实际使用中，应根据具体需求选择合适的排名函数。
排名函数NTILE高级应用
12-30
例如，如果我们有100个学生，想要将他们平均分为4个班级，我们可以使用`NTILE(4)`。这个函数会为每个学生分配一个1到4之间的数字，表示他们所在的班级。前25名学生会被分配到班级1，接下来25名学生被分配到班级2，...
JavaScript另类网页特效
qaz835461的博客
11-30 249
我平常特别喜爱研讨页面特效，这里是平常“截流”下来的几个JavaScript页面特效，在此跟我们一同共享。 　　★反续摆放 　　代码：在需求处参加   　　反续列 　　特色：当你需求把一段话反起来摆放时，你怎么办呢？反起来一个一个地输入吗？只需运用以上代码，即可完成。 　　★主动刷新 　　代码：在页面的和之间参加 　　
网站设计欣赏：20个另类的单页网站作品案例
萌萌的It人 www.itmmd.com
05-23 2001
早在1月份，我向大家分享了《谈谈构建单页布局网站的创意技术》，随着单页设计得到越来越多的设计师的关注，今天我们决定向您展示一组鼓舞人心的单页网站例子，一起欣赏。 您可能感兴趣的相关文章 经典网页设计：超炫动画效果单页网站 30个独具匠心的精美单页网站设计案例 带给你灵感的20个漂亮的单页网站作品 分享35个非常漂亮的单页网站设计案例 25个优秀的国外
Sql 四大排名函数（ROW_NUMBER、RANK、DENSE_RANK、NTILE）简介
天下任我行
05-15 1066
原文：https://www.cnblogs.com/52xf/p/4209211.html 排名函数是Sql Server2005新增的功能，下面简单介绍一下他们各自的用法和区别。我们新建一张Order表并添加一些初始数据方便我们查看效果。 表结构和初始数据Sql CREATE TABLE [dbo].[Order]( [ID] [int] IDENTITY(1,1) NOT NULL, [UserId] [int] NOT NULL, [TotalPrice] [int] NO
SQL Server排名函数（ROW_NUMBER、RANK、DENSE_RANK、NTILE）
li_gege的博客
07-05 4209
1、 ROW_NUMBER Row_number的用途非常广泛，排序最好用他，一般可以用来实现web分页，他会为查询出来的每一页记录生成一个序号，依次排序且不重复，注意使用row_number函数时必须要用over子句选择对某一列进行排序才能生成序号。 例如： select ROW_NUMBER() OVER(order by salse desc) as row_num,* from rank...
SQL四大排名函数（ROW_NUMBER、RANK、DENSE_RANK、NTILE）对比手册
WL456258的博客
05-20 1202
在数据分析中，SQL排名函数提供了一种高效的方式来处理数据排序和分组，解决了传统方法中复杂子查询的问题。主要函数包括：ROW_NUMBER()，为每行分配唯一连续序号，适用于分页查询和TopN筛选；RANK()，允许并列排名并跳过后续序号，常用于竞赛排名；DENSE_RANK()，允许并列但不跳号，适用于薪资或成绩梯队分析；NTILE(n)，将数据均分为n个桶，用于数据分箱或分层抽样。这些函数各有特点，适用于不同的场景。在大数据量时，建议在OVER()中使用PARTITIONBY提高性能，并利用CTE提高复
Hive分析函数之ntile、排名函数学习
BabyFish13
11-16 5975
1、Ntile使用 可以看成是：它把有序的数据集合平均分配到指定的数量（num）个桶中, 将桶号分配给每一行。如果不能平均分配，则优先分配较小编号的桶，并且各个桶中能放的行数最多相差1。 语法是：ntile (num)  over ([partition_clause]  order_by_clause)  as your_bucket_num 然后可以根据桶号，选取前或后 n分之几的数据。
OLAP常用命令总结
weixin_49139876的博客
12-09 257
分析函数、窗口函数、Over从句 分析函数: ntile() :数据分片,100条数据,切成10片,自动平均配平序列数量 row_number: 先查询排列在前的进行排序,依次进行,列相同比较下一位,可以用作分页,数据有规律 rank():排序1,2,2,4,4,6 dense_rank():1,2,3,3,4 窗口函数 lag(col,n,default):第一个参数是列名,第二个参数是往上第几行,默认为1,第三个参数为默认值,往上第n行为null时,取默认值,不指定,为null lead(col,n,
SQL中常用的窗口函数（排序函数）-row_number/rank/dense_rank/ntile
热门推荐
momomuabc的博客
11-17 1万+
SQL Server中的排序函数有四个：row_number()，rank()，dense_rank()及ntile()函数；
排名函数row_number()、 rank()、 dense_rank()、ntile()、partition by的区别及具体用法示例
weixin_45875358的博客
10-25 4032
row_number()排序相同时不会重复，会根据顺序排序；排名举例：1、2、3、4； rank()排序相同时会重复，总数不变；排名举例：1、2、2、4； dense_rank()排序相同时会重复，总数会减少；排名举例：1、2、2、3； ntile()确认最高排名，最高排名需要小于等于总数；排名举例ntile(N)：1、2、3...N; partition by先分组，在进行组内排名；
mysql有直接算分位数的函数么
03-22
<think>嗯，我现在需要在MySQL里计算分位数，但不确定有没有内置函数。让我想想，之前用过一些聚合函数，比如AVG、SUM，但分位数可能更复杂。首先，我应该查一下MySQL的文档，看看有没有像PERCENTILE_CONT这样的函数。记得在PostgreSQL和SQL Server里有类似的窗口函数，但MySQL可能不太一样。 查了一下，发现用户手册里提到MySQL 8.0增加了窗口函数，比如ROW_NUMBER和NTILE，但好像没有直接计算分位数的内置函数。比如引用[2]里提到，Oracle有专门的函数，但其他解决方案可能要用自连接或窗口函数。MySQL可能需要用其他方法模拟。 那怎么办呢？可能需要用子查询和变量来计算。例如，用ORDER BY排序数据，然后用变量给行编号，再根据总行数计算分位点的位置。比如中位数的话，如果是奇数行取中间值，偶数取平均。或者用NTILE函数将数据分成100组，但NTILE的参数是分桶数，可能不太灵活。 另外，引用[2]提到传统方法用自连接，但窗口函数更高效。比如用PERCENT_RANK()来获取百分比排名，然后结合条件筛选。不过MySQL的PERCENT_RANK()是否存在呢？查一下，MySQL 8.0确实有PERCENT_RANK()作为窗口函数，可以计算行的相对排名百分比。那或许可以用这个函数来找到分位点。 具体步骤可能如下：先为数据集排序，计算每行的百分比排名，然后找到对应百分比的位置，再取值。比如要计算75th分位数，可以用子查询筛选出PERCENT_RANK() >= 0.75的第一行。或者用线性插值法，像PERCENTILE_CONT那样。不过这可能比较复杂，需要用到CASE WHEN和聚合函数。 比如，用户可能需要这样的查询： SELECT AVG(val) AS percentile FROM ( SELECT val, PERCENT_RANK() OVER (ORDER BY val) AS p FROM data ) AS t WHERE p >= 0.75 ORDER BY p LIMIT 1; 不过这可能不完全准确，需要测试一下。或者结合COUNT和变量来计算位置。比如计算总行数，确定分位点的索引，然后取对应的值。如果索引不是整数，可能需要插值。例如，对于第p分位数，位置是(N-1)*p +1，然后取该位置的值或插值。 所以，总结来说，MySQL没有直接的内置分位数函数，但可以通过窗口函数和子查询组合实现。需要根据具体需求选择合适的方法，并注意性能问题，尤其是大数据集时。</think>### MySQL中计算分位数的实现方法 MySQL目前（截至8.0版本）**没有直接计算分位数的内置函数**（如`PERCENTILE_CONT`或`PERCENTILE_DISC`），但可以通过以下方法实现： --- #### **方法1：使用窗口函数（推荐，需MySQL 8.0+）** 通过`PERCENT_RANK()`或`ROW_NUMBER()`结合条件筛选实现： ```sql -- 计算75th分位数示例 SELECT AVG(val) AS 75th_percentile FROM ( SELECT val, PERCENT_RANK() OVER (ORDER BY val) AS p_rank FROM your_table ) AS ranked WHERE p_rank >= 0.75 ORDER BY p_rank LIMIT 1; ``` **说明**： - `PERCENT_RANK()`计算每行的百分比排名（范围$[0,1]$） - 筛选最接近目标分位数的值，并用`AVG`处理边界情况[^2] --- #### **方法2：使用变量和子查询** 适用于所有MySQL版本： ```sql -- 计算中位数示例（50th分位数） SET @row_index = -1; SELECT AVG(val) AS median FROM ( SELECT @row_index := @row_index +1 AS row_index, val FROM your_table ORDER BY val ) AS indexed WHERE row_index IN ( FLOOR(@row_index/2), CEIL(@row_index/2) ); ``` **说明**： - 使用变量`@row_index`生成行号 - 通过数学计算确定分位数位置 --- #### **方法3：使用`NTILE()`分桶（近似计算）** 将数据分为100组后取目标分位桶： ```sql SELECT MAX(val) AS 90th_percentile FROM ( SELECT val, NTILE(100) OVER (ORDER BY val) AS bucket FROM your_table ) AS buckets WHERE bucket = 90; ``` **注意**：此方法为近似值，数据分布不均匀时可能有误差 --- ### **性能与注意事项** 1. **窗口函数优化**：MySQL 8.0的窗口函数性能优于传统自连接方法，但仍需对排序字段建立索引。 2. **大数据集处理**：若数据量过大（如超过100万行），建议结合`WHERE`条件分段计算[^3]。 3. **精确性权衡**：`PERCENT_RANK`方法更精确，`NTILE`方法更高效但为近似值。 ---
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值