`

row_number() over()分析函数用法

阅读更多

 

row_number()over(partition by col1 order by col2)表示根据col1分组,在分组内部根据col2排序,而此函数计算的值就表示每组内部排序后的顺序编号(组内连续的唯一的)。 

与rownum的区别在于:使用rownum进行排序的时候是先对结果集加入伪劣rownum然后再进行排序,而此函数在包含排序从句后是先排序再计算行号码。 

row_number()和rownum差不多,功能更强一点(可以在各个分组内从1开始排序)。 

rank()是跳跃排序,有两个第二名时接下来就是第四名(同样是在各个分组内) 

dense_rank()也是连续排序,有两个第二名时仍然跟着第三名。相比之下row_number是没有重复值的。 

 

oracle 分析函数 row_number(),返回一个整数值(>=1); 

 

语法格式: 

 

1.row_number() over (order by col_1[,col_2 ...]) 

 

作用:按照col_1[,col_2 ...]排序,返回排序后的结果集, 

 

此用法有点像rownum,为每一行返回一个不相同的值: 

 

select rownum,ename,job,    

       row_number() over (order by rownum) row_number    

from emp;    

    ROWNUM ENAME      JOB       ROW_NUMBER    

---------- ---------- --------- ----------    

         1 SMITH      CLERK              1    

         2 ALLEN      SALESMAN           2    

         3 WARD       SALESMAN           3    

         4 JONES      MANAGER            4    

         5 MARTIN     SALESMAN           5    

         6 BLAKE      MANAGER            6    

         7 CLARK      MANAGER            7    

         8 SCOTT      ANALYST            8    

         9 KING       PRESIDENT          9    

        10 TURNER     SALESMAN          10    

        11 ADAMS      CLERK             11    

        12 JAMES      CLERK             12    

        13 FORD       ANALYST           13    

        14 MILLER     CLERK             14  

 

如果没有partition by子句, 结果集将是按照order by 指定的列进行排序; 

 

with row_number_test as(    

     select 22 a,'twenty two' b from dual union all    

     select 1,'one' from dual union all    

     select 13,'thirteen' from dual union all    

     select 5,'five' from dual union all    

     select 4,'four' from dual)    

select a,b,    

       row_number() over (order by b)    

from row_number_test    

order by a;  

 

正如我们所期待的,row_number()返回按照b列排序的结果, 

 

然后再按照a进行排序,才得到下面的结果: 

 

A B          ROW_NUMBER()OVER(ORDERBYB)    

-- ---------- --------------------------    

1 one                                 3    

4 four                                2    

5 five                                1    

13 thirteen                            4    

22 twenty two                          5  

 

2.row_number() over (partition by col_n[,col_m ...] order by col_1[,col_2 ...]) 

 

作用:先按照col_n[,col_m ...进行分组, 

 

再在每个分组中按照col_1[,col_2 ...]进行排序(升序), 

 

最后返回排好序后的结果集: 

 

with row_number_test as(    

     select 22 a,'twenty two' b,'*' c from dual union all    

     select 1,'one','+' from dual union all    

     select 13,'thirteen','*' from dual union all    

     select 5,'five','+' from dual union all    

     select 4,'four','+' from dual)    

select a,b,    

       row_number() over (partition by c order by b) row_number    

from row_number_test    

order by a;  

  

 

这个例子中,我们先按照c列分组,分为2组('*'组,'+'组), 

 

再按照每个小组的b列进行排序(按字符串首字母的ascii码排), 

 

最后按照a列排序,得到下面的结果集: 

 

A B          ROW_NUMBER    

-- ---------- ----------    

1 one                 3    

4 four                2    

5 five                1    

13 thirteen            1    

22 twenty two 

 

示例:

有以下需求,实现抽取分组的随机前3条对某个字段不重复的数据?

 

即按id1分组,随机抽取 id2不重复的前N条记录。

 

例:

 

with temp as (                                                

select 'dg' id1,13907551201 id2 from dual union all                                                

select 'dg' id1,13907551201 id2 from dual union all                                                

select 'dg' id1,13907551201 id2 from dual union all                                                

select 'dg' id1,13907551204 id2 from dual union all                                                

select 'dg' id1,13907551205 id2 from dual union all                                                

select 'dg' id1,13907551206 id2 from dual union all                                                

select 'dg' id1,13907551207 id2 from dual union all                                                

select 'dg' id1,13907551207 id2 from dual union all                                                

select 'dc' id1,13907551209 id2 from dual union all                                                

select 'dc' id1,13907551210 id2 from dual union all                                                

select 'dc' id1,13907551210 id2 from dual union all                                                

select 'dc' id1,13907551212 id2 from dual                                                 

 

实现方法:

with temp as (                                                

select 'dg' id1,13907551201 id2 from dual union all                                                

select 'dg' id1,13907551201 id2 from dual union all                                                

select 'dg' id1,13907551201 id2 from dual union all                                                

select 'dg' id1,13907551204 id2 from dual union all                                                

select 'dg' id1,13907551205 id2 from dual union all                                                

select 'dg' id1,13907551206 id2 from dual union all                                                

select 'dg' id1,13907551207 id2 from dual union all                                                

select 'dg' id1,13907551207 id2 from dual union all                                                

select 'dc' id1,13907551209 id2 from dual union all                                                

select 'dc' id1,13907551210 id2 from dual union all                                                

select 'dc' id1,13907551210 id2 from dual union all                                                

select 'dc' id1,13907551212 id2 from dual                                                 

)                                                

select * from (

select  temp.*

,row_number() over(partition by id1 order by dbms_random.random) rid1

from (

select temp.*

--,row_number() over(partition by id1 order by id1) rid1

,row_number() over(partition by id1,id2 order by id1,id2) rn 

from temp

) temp

where rn=1)

where rid1<=3

 

 

但由于temp表的数据量很大,是否还有更优的SQL实现?

 

 

select * from (

select  temp.*

,row_number() over(partition by id1 order by dbms_random.random) rid1

from (

select distinct id1, id2 from temp)

)

where rid1<=3;

 

分享到:
评论

相关推荐

    ROW_NUMBER() OVER函数的基本用法

    ROW_NUMBER() OVER 函数的基本用法 ROW_NUMBER() OVER 函数是 SQL Server 中的一个窗口函数,用于对查询结果进行编号。该函数可以根据指定的列进行分组和排序,并为每一组记录返回一个唯一的编号。 语法: ROW_...

    oracle分析函数row_number() over()使用

    下面我们将详细讨论`ROW_NUMBER() OVER()`的使用方法及其在不同场景下的应用。 首先,`ROW_NUMBER() OVER()`的基本语法是: ```sql ROW_NUMBER() OVER ( [PARTITION BY column1, column2, ...] ORDER BY column3...

    oracle row_number用法

    除了以上基础用法外,`ROW_NUMBER()`还可以与其他窗口函数组合使用,实现更复杂的数据分析需求。例如,结合`LAG()`和`LEAD()`函数可以实现数据的前后对比;与`RANK()`和`DENSE_RANK()`结合则可以进一步细化排序逻辑...

    row_number.rar_SQL中row_number用法_number

    在SQL语言中,`ROW_NUMBER()`函数是一种非常重要的窗口函数,用于为查询结果集中的每一行分配一个唯一的整数。这个数字通常按照行的出现顺序进行分配,但也可以根据特定的排序条件进行调整。`ROW_NUMBER()`在数据...

    深入探讨:oracle中row_number() over()分析函数用法

    Oracle数据库中的`row_number() over()`分析函数是一个非常实用的工具,用于为数据集中的每一行分配一个唯一的整数,这个数字基于指定的排序条件。在处理大数据集时,它可以帮助我们进行分页、排名或者在复杂的查询...

    row_number

    ### row_number() 函数详解及应用 #### 一、row_number()函数介绍 `row_number()` 是 SQL 中的一个窗口函数,常用于为查询结果中的每一行分配一个唯一且...掌握好这一函数的使用方法,对于提升 SQL 技能大有裨益。

    ROW_NUMBER、RANK、DENSE_RANK 和 NTILE

    以下查询通过使用 ROW_NUMBER 函数并指定 OVER (ORDER BY score DESC) 生成所需的结果: ``` SELECT ROW_NUMBER() OVER(ORDER BY score DESC) AS rownum, speaker, track, score FROM SpeakerStats ``` rownum ...

    分析函数ROW_NUMBER、RANK、DENSE_RANK的用法

    ### 分析函数ROW_NUMBER、RANK、DENSE_RANK的用法 #### 一、ROW_NUMBER()函数 **ROW_NUMBER()** 函数是SQL Server 2005引入的一个新的窗口函数,它为每一行返回一个唯一的整数值。该函数特别适用于需要对查询结果...

    SQLServer中Partition By及row_number 函数使用详解

    在SQL Server中,`PARTITION BY` 和 `ROW_NUMBER()` 是两种非常重要的分析函数,它们在处理大数据集时尤其有用。本文将详细讲解这两个函数的使用方法及其在实际场景中的应用。 `PARTITION BY` 关键字是分析函数的一...

    sqlserver 通用存储过程分页代码(附使用ROW_NUMBER()和不使用ROW_NUMBER()两种情况性能分析)

    本篇将详细探讨两种不同的分页实现方式,一种是使用ROW_NUMBER()函数,另一种是不使用ROW_NUMBER()函数,并分析它们的性能差异。 首先,ROW_NUMBER()函数在SQL Server中被广泛用于生成行号,常用于分页查询。它为每...

    SQLSERVER 2005的ROW_NUMBER、RANK、DENSE_RANK的用法

    在SQL Server 2005中,`ROW_NUMBER()`、`RANK()`和`DENSE_RANK()`是三种常用的排名函数,它们在处理大数据集时非常有用,尤其在数据分析和报表生成方面。这些函数都可以帮助我们根据指定的条件对数据进行排序并赋予...

    利用ROW_NUMBER() OVER函数给SQL数据库中每一条记录分配行号的方法

    本篇文章将深入探讨ROW_NUMBER() OVER函数的工作原理、使用方法以及其在实际应用中的价值。 ROW_NUMBER() OVER函数是窗口函数(Window Function)的一种,它可以基于查询结果集的一个或多个列进行排序,并为每一行...

    SQL Server 排序函数 ROW_NUMBER和RANK 用法总结

    SELECT SalesOrderID, CustomerID, ROW_NUMBER() OVER (ORDER BY SalesOrderID) AS RowNumber FROM Sales.SalesOrderHeader ``` 结果集中,RowNumber字段将从1开始,随着SalesOrderID的增大而递增。 2. RANK() ...

    SQL server中row_number(),rank(),dense_rank()排序

    在创建示例表SC之后,我们可以使用`row_number()`来对某一列进行排序并生成序列: ```sql SELECT Sno, Cno, Grade, ROW_NUMBER() OVER (ORDER BY Grade DESC) AS RowNum FROM SC; ``` 上述查询将按照Grade降序为每...

    oracle分析函数

    ### Oracle分析函数详解:row_number() over 在Oracle数据库中,分析函数(Analytic Functions)是一种强大而灵活的数据处理工具,它们能够在查询结果集中对每一行数据进行计算,而不像传统的聚合函数那样只返回...

    Oracle 分析函数RANK(),ROW_NUMBER(),LAG()等的使用方法

    在上述信息中,提到了几个关键的分析函数:RANK()、ROW_NUMBER()和LAG(),下面将详细阐述这些函数的使用方法和特点。 1. **ROW_NUMBER()** ROW_NUMBER()函数用于为每个分组内的行分配一个唯一的序列号,这个编号是...

    SQL学习笔记八 索引,表连接,子查询,ROW_NUMBER

    在SQL学习过程中,索引、表连接、子查询和ROW_NUMBER函数是四个非常重要的概念,它们对于提升查询效率和编写复杂查询语句至关重要。 首先,我们来了解一下**索引**。索引是一种特殊的数据结构,它能显著提高数据...

Global site tag (gtag.js) - Google Analytics