函数专题：sum、row_number、count、rank\dense_rank over

最新推荐文章于 2024-08-27 14:19:14 发布
最新推荐文章于 2024-08-27 14:19:14 发布
阅读量1.6k 收藏
点赞数
分类专栏： # postgre

来源：http://blog.csdn.net/bbliutao/article/details/7727320


一、sum over

    sum over主要用来对某个字段值进行逐步累加

    SELECT   Name, Salary, SUM (Salary) OVER (ORDER BY Salary, Name) SubSal
    FROM   emp
    ORDER BY   Salary;

    Name,   Salary,   Age 
    A，900，   20 
    B，900，   21 
    C，1000，   18 
    D，950，   20 

   执行后格式

    Name,   Salary,   SubSal   
     A，       900，           900 
     B，       900，           1800 
     D，       950，           2750 
     C，       1000，         3750

    sum(sal) over (partition by deptno) --按照不同的部门分组，对部门自身的员工工资连续求和
    partition by 是起着分组的作用


 二、row_number over
    row_number() over ([partition by col1] order by col2) ) as 别名
    表示根据col1分组，在分组内部根据 col2排序
    而这个“别名”的值就表示每组内部排序后的顺序编号（组内连续的唯一的）,[partition by col1] 可省
    略

    row_number() over 按照partion by 分组，然后在每组内部按照 open_date 排序 得到返回
    的是内部排序的顺序编号

    SELECT   t.family_id,
             t.package_id,
             t.phone,
             t.reason,
             t.open_date,
             ROW_NUMBER ()
                OVER (PARTITION BY family_id, package_id, phone
                      ORDER BY open_date DESC)
                rn
       FROM   zj_transaction_log t
     WHERE   TO_CHAR (t.open_date, 'yyyy-mm') >= '2011-03';

FAMILY_ID    PACKAGE_ID    PHONE    REASON    OPEN_DATE    RN
19811    100    ********861    ADC请求[校讯通-精装版]恢复处理    2011-3-6 14:16:29    1
19811    100    ********861    ADC请求[校讯通-精装版]暂停处理    2011-3-6 3:00:26    2
19823    100    ********667    ADC请求[精装版]暂停处理    2011-3-12 9:47:41    1
19823    100    ********667    ADC请求[校讯通-精装版]恢复处理    2011-3-7 19:03:30    2
19823    100    ********667    ADC请求[校讯通-精装版]暂停处理    2011-3-7 6:51:27    3
19841    100    ********328    ADC请求[精装版]恢复处理    2011-3-14 9:01:31    1
19841    100    ********328    ADC请求[精装版]暂停处理    2011-3-14 0:57:33    2
19869    100    ********172    ADC请求[校讯通-精装版]暂停处理    2011-3-5 2:55:17    1
19950    100    ********631    ADC请求[校讯通-精装版]恢复处理    2011-3-2 14:14:51    1

    SELECT   t.family_id,
             t.package_id,
             t.phone,
             t.reason,
             t.open_date,
             ROW_NUMBER ()
                OVER (PARTITION BY family_id, package_id, phone
                  ORDER BY package_id DESC)
            rn
      FROM   zj_transaction_log t
     WHERE   TO_CHAR (t.open_date, 'yyyy-mm') >= '2011-03';

FAMILY_ID    PACKAGE_ID    PHONE    REASON    OPEN_DATE    RN
19811    100    ********861    ADC请求[校讯通-精装版]暂停处理    2011-3-6 3:00:26    1
19811    100    ********861    ADC请求[校讯通-精装版]恢复处理    2011-3-6 14:16:29    2
19823    100    ********667    ADC请求[精装版]暂停处理    2011-3-12 9:47:41    1
19823    100    ********667    ADC请求[校讯通-精装版]恢复处理    2011-3-7 19:03:30    2
19823    100    ********667    ADC请求[校讯通-精装版]暂停处理    2011-3-7 6:51:27    3
19841    100    ********328    ADC请求[精装版]暂停处理    2011-3-14 0:57:33    1
19841    100    ********328    ADC请求[精装版]恢复处理    2011-3-14 9:01:31    2
19869    100    ********172    ADC请求[校讯通-精装版]暂停处理    2011-3-5 2:55:17    1
19950    100    ********631    ADC请求[校讯通-精装版]恢复处理    2011-3-2 14:14:51    1


 三、count over

    SELECT   t.family_id,
             t.package_id,
             t.phone,
             t.open_date,
             t.reason,
             COUNT( * )
                OVER (PARTITION BY family_id, package_id, phone)
                rn
      FROM   zj_transaction_log t
     WHERE   TO_CHAR (t.open_date, 'yyyy-mm') >= '2011-03';

FAMILY_ID    PACKAGE_ID    PHONE    OPEN_DATE    REASON    RN
19811    100    ********861    2011-3-6 3:00:26    ADC请求[校讯通-精装版]暂停处理    2
19811    100    ********861    2011-3-6 14:16:29    ADC请求[校讯通-精装版]恢复处理    2
19823    100    ********667    2011-3-12 9:47:41    ADC请求[精装版]暂停处理    3
19823    100    ********667    2011-3-7 19:03:30    ADC请求[校讯通-精装版]恢复处理    3
19823    100    ********667    2011-3-7 6:51:27    ADC请求[校讯通-精装版]暂停处理    3
19841    100    ********328    2011-3-14 0:57:33    ADC请求[精装版]暂停处理    2
19841    100    ********328    2011-3-14 9:01:31    ADC请求[精装版]恢复处理    2
19869    100    ********172    2011-3-5 2:55:17    ADC请求[校讯通-精装版]暂停处理    1
19950    100    ********631    2011-3-2 14:14:51    ADC请求[校讯通-精装版]恢复处理    1

    SELECT   t.family_id,
             t.package_id,
             t.phone,
             t.open_date,
             t.reason,
             COUNT( * )
                OVER (PARTITION BY family_id, package_id, phone
                order by open_date)
                rn
      FROM   zj_transaction_log t
     WHERE   TO_CHAR (t.open_date, 'yyyy-mm') >= '2011-03';

FAMILY_ID    PACKAGE_ID    PHONE    OPEN_DATE    REASON    RN
19811    100    ********861    2011-3-6 3:00:26    ADC请求[校讯通-精装版]暂停处理    1
19811    100    ********861    2011-3-6 14:16:29    ADC请求[校讯通-精装版]恢复处理    2
19823    100    ********667    2011-3-7 6:51:27    ADC请求[校讯通-精装版]暂停处理    1
19823    100    ********667    2011-3-7 19:03:30    ADC请求[校讯通-精装版]恢复处理    2
19823    100    ********667    2011-3-12 9:47:41    ADC请求[精装版]暂停处理    3
19841    100    ********328    2011-3-14 0:57:33    ADC请求[精装版]暂停处理    1
19841    100    ********328    2011-3-14 9:01:31    ADC请求[精装版]恢复处理    2
19869    100    ********172    2011-3-5 2:55:17    ADC请求[校讯通-精装版]暂停处理    1
19950    100    ********631    2011-3-2 14:14:51    ADC请求[校讯通-精装版]恢复处理    1

    SELECT   t.family_id,
             t.package_id,
             t.phone,
             t.open_date,
             t.reason,
             COUNT( * )
                OVER (PARTITION BY family_id, package_id, phone
                order by package_id)
                rn
      FROM   zj_transaction_log t
     WHERE   TO_CHAR (t.open_date, 'yyyy-mm') >= '2011-03';

FAMILY_ID    PACKAGE_ID    PHONE    OPEN_DATE    REASON    RN
19811    100    ********861    2011-3-6 3:00:26    ADC请求[校讯通-精装版]暂停处理    2
19811    100    ********861    2011-3-6 14:16:29    ADC请求[校讯通-精装版]恢复处理    2
19823    100    ********667    2011-3-12 9:47:41    ADC请求[精装版]暂停处理    3
19823    100    ********667    2011-3-7 19:03:30    ADC请求[校讯通-精装版]恢复处理    3
19823    100    ********667    2011-3-7 6:51:27    ADC请求[校讯通-精装版]暂停处理    3
19841    100    ********328    2011-3-14 0:57:33    ADC请求[精装版]暂停处理    2
19841    100    ********328    2011-3-14 9:01:31    ADC请求[精装版]恢复处理    2
19869    100    ********172    2011-3-5 2:55:17    ADC请求[校讯通-精装版]暂停处理    1
19950    100    ********631    2011-3-2 14:14:51    ADC请求[校讯通-精装版]恢复处理    1

    SELECT   t.family_id,
             t.package_id,
             t.phone,
             t.reason,
             COUNT( * )
                OVER (PARTITION BY family_id, package_id, phone
                      ORDER BY open_date DESC
                      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
                 rn
      FROM   zj_transaction_log t
     WHERE   TO_CHAR (t.open_date, 'yyyy-mm') >= '2011-03';

FAMILY_ID    PACKAGE_ID    PHONE    OPEN_DATE    REASON    RN
19811    100    ********861    2011-3-6 3:00:26    ADC请求[校讯通-精装版]暂停处理    1
19811    100    ********861    2011-3-6 14:16:29    ADC请求[校讯通-精装版]恢复处理    2
19823    100    ********667    2011-3-7 6:51:27    ADC请求[校讯通-精装版]暂停处理    1
19823    100    ********667    2011-3-7 19:03:30    ADC请求[校讯通-精装版]恢复处理    2
19823    100    ********667    2011-3-12 9:47:41    ADC请求[精装版]暂停处理    3
19841    100    ********328    2011-3-14 0:57:33    ADC请求[精装版]暂停处理    1
19841    100    ********328    2011-3-14 9:01:31    ADC请求[精装版]恢复处理    2
19869    100    ********172    2011-3-5 2:55:17    ADC请求[校讯通-精装版]暂停处理    1
19950    100    ********631    2011-3-2 14:14:51    ADC请求[校讯通-精装版]恢复处理    1

     SELECT   t.family_id,
             t.package_id,
             t.phone,
             t.reason,
             COUNT( * )
                OVER (PARTITION BY family_id, package_id, phone
                      ORDER BY package_id DESC
                      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
                rn
      FROM   zj_transaction_log t
     WHERE   TO_CHAR (t.open_date, 'yyyy-mm') >= '2011-03';

FAMILY_ID    PACKAGE_ID    PHONE    REASON    RN
19811    100    ********861    ADC请求[校讯通-精装版]暂停处理    1
19811    100    ********861    ADC请求[校讯通-精装版]恢复处理    2
19823    100    ********667    ADC请求[精装版]暂停处理    1
19823    100    ********667    ADC请求[校讯通-精装版]恢复处理    2
19823    100    ********667    ADC请求[校讯通-精装版]暂停处理    3
19841    100    ********328    ADC请求[精装版]暂停处理    1
19841    100    ********328    ADC请求[精装版]恢复处理    2
19869    100    ********172    ADC请求[校讯通-精装版]暂停处理    1
19950    100    ********631    ADC请求[校讯通-精装版]恢复处理    1


四、rank\dense_rank  over

rank 的分析功能语法：
RANK ( ) OVER ( [query_partition_clause] order_by_clause )

rank 的合计功能语法：
RANK ( expr [, expr]... ) WITHIN GROUP
( ORDER BY
expr [ DESC | ASC ] [NULLS { FIRST | LAST }]
[, expr [ DESC | ASC ] [NULLS { FIRST | LAST }]]...
)

对于分析功能，
SELECT a.*,RANK() OVER(PARTITION BY col2 ORDER BY col1) "Rank" FROM table a;
已col2分组col1排序，rank与dense_rank用法相当，区别如下：
　　
例如：当rank时为：
SELECT f.phone, f.student_name,
       RANK () OVER (PARTITION BY f.phone ORDER BY f.student_name) cnt
  FROM zs_family f
WHERE NVL (f.is_test, 0) <> 1
   AND LENGTH (f.phone) = 11
   AND f.phone NOT LIKE '%0%'
　　
PHONE    STUDENT_NAME    CNT
********188    陈雅琳    1
********188    陈雅琳    1
********188    陈镇豪    3

而如果用dense_rank时为：
SELECT f.phone, f.student_name,
       dense_RANK () OVER (PARTITION BY f.phone ORDER BY f.student_name) cnt
  FROM zs_family f
WHERE NVL (f.is_test, 0) <> 1
   AND LENGTH (f.phone) = 11
   AND f.phone NOT LIKE '%0%'
　　
PHONE    STUDENT_NAME    CNT
********188    陈雅琳    1
********188    陈雅琳    1
********188    陈镇豪    2

从上可知，都是对数据分组排序，差别在于以a分组，b排序的时候，dence_rank在并列关系时，相关等级不会跳过。rank则跳过（常用在排名）。

over 分析函数系列都十分类似。

对于合计功能：

   SELECT
       RANK ('********188','陈雅琳') within group (ORDER BY f.phone,f.student_name) cnt
  FROM zs_family f
WHERE NVL (f.is_test, 0) <> 1
   AND LENGTH (f.phone) = 11
   AND f.phone NOT LIKE '%0%';

cnt
258

感觉就是查询定位该记录的在表中位置
sun0322
关注
专栏目录
Hive ,开窗函数，sum,row_number,dense_rank,percent_rank
Top5软件工程硕士，先后在京东、字节从事多年Java后端开发、实时和离线大数据开发
04-28 1591
1 排序：row_number(),dense_rank() select sku_id,sku_name,category_id3,sales_count,price, sales_count*price as s1, dense_rank() over(partition by category_id3 order by s1 desc) as r2 -- Invalid table...
row_number() over(order by) 与count(1)
11-11 1541
2005中的  row_number() over(order by) 给我们带来了什么 例子比较借用无枪的狙击手 与 昨夜小楼 declare @t table(a char(2),b char(2),c char(2),d char(2)) insert @t select a1,b1,c1,d insert @t select a1,b1,c2,d i
ROW_NUMBER() OVER()函数用法详解 （分组排序 例子多）
热门推荐
trouble is a friend
09-18 56万+
语法格式：row_number() over(partition by 分组列 order by排序列 desc) row_number() over()分组排序功能： 在使用 row_number() over()函数时候，over()里头的分组以及排序的执行晚于 where 、group by、 order by 的执行。 例一： 表数据： create table TEST_...
Mysql中count（*） over 用法讲解
最新发布
针线犹存未忍开
08-27 1030
在MySQL中，用于计算每一行在结果集中的总行数。它是一个窗口函数。窗口函数是一种在特定窗口内计算和返回结果的函数。在这种情况下，窗口函数是COUNT(*)，它计算在整个结果集中的总行数。1、COUNT() OVER() 是一种窗口函数，用于计算查询结果集中的行数。‌这种函数不受分组或排序的影响，能够返回查询结果集中的总行数。窗口函数允许用户对一个结果集的窗口（或分区）执行计算，而不需要改变查询的基本结构。使用COUNT(
ROW_NUMBER() OVER(PARTITION BY COLUMN ORDER BY COLUMN)
jvisualvm
02-15 181
原创转载请注明出处：http://agilestyle.iteye.com/blog/2357284   ROW_NUMBER() OVER(PARTITION BY COLUMN ORDER BY COLUMN) create table CREATE TABLE EMPLOYEE ( EMPID INTEGER PRIMARY KEY, DEPTID INTEGER,...
Oracle的ROW_NUMBER() OVER()函数
ConstXiong
03-20 1万+
ROW_NUMBER() OVER() 函数的作用：分组排序 原理：row_number() over() 函数，over() 里的分组以及排序的执行晚于 where、group by、order by 的执行。 语法：row_number() over( partition by 分组列 order by 排序列 desc ) 实践： 建立学生数据表 create tab...
牛客网之SQL非技术快速入门(8)-窗口函数语法：row_number/rank/dense_rank over (partition by FIELD1 order by FIELD2)
weixin_48272780的博客
11-20 293
限定条件：gpa最低，看似min(gpa)，但是要留意，是每个学校里的最低，不是全局最低。方法2：利用窗口函数，先按学校分组计算排序gpa，得到最低gpa的记录在用子查询语法拿到需要的列即可。聚和窗口函数和上面提到的专用窗口函数用法完全相同，只需要把聚合函数写在窗口函数的位置即可，但是函数后面括号里面不能为空，需要指定聚合的列名。这是因为partition by分组后的结果称为“窗口”，这里的窗口不是我们家里的门窗，而是表示“范围”的意思。1）同时具有分组和排序的功能。2）不减少原表的行数。
PostgreSQL窗口函数详解：rank, row_number, dense_rank等
本文主要对PostgreSQL数据库中的窗口函数进行了全面总结，涵盖了row_number、rank、dense_rank等重要窗口函数的使用方法，以及它们之间的区别。同时，文章还讲解了percent_rank、groupingsets等高级窗口函数的应用，...
SQL 开窗函数，row_number, dense_rank, percent_rank
weixin_43862728的博客
04-19 1683
row_number dense_rank percent_rank
ORACLE转MYSQL各种函数说明（ROW_NUMBER() ，COUNT()，DENSE_RANK()）
qq_15845499的博客
04-11 2278
ORACLE转MYSQL各种函数说明 涉及函数： ROW_NUMBER() OVER(order by x desc) ROW_NUMBER() OVER(PARTITION BY x ORDER BY y) COUNT() OVER(PARTITION BY x order by y desc) DENSE_RANK() OVER(ORDER BY x) 1、通用公式：（分组排序加序号） O...
开窗函数ROW_NUMBER() OVER()函数用法详解，并巧妙使用ROW_NUMBER()函数进行数据去重
weixin_46574678的博客
01-18 2万+
ROW_NUMBER() OVER()函数用法详解，并巧用ROW_NUMBER()函数删除重复数据
OVER Clause是个好东西，常和ROW_NUMBER()、Sum、AVG、Count、Min、Max配合使用
weixin_30347335的博客
08-14 173
根据SQL官方帮助的实例： USE AdventureWorks2012; GO SELECT ROW_NUMBER() OVER(PARTITION BY PostalCode ORDER BY SalesYTD DESC) AS "Row Number", p.LastName, s.SalesYTD, a.PostalCode FROM Sales.SalesPers...
【SQL】sum() over&row_number() over用法
ccczzy888的博客
10-04 771
sum() over&row_number() over用法
ROW_NUMBER() OVER
weixin_43125677的博客
08-06 235
ROW_NUMBER() OVER使用 语法：ROW_NUMBER() OVER(PARTITION BY COLUMN ORDER BY COLUMN) 作用: 从1开始，为按组排序的每条记录添加一个序列号 函数只能用于select和order by子句中 不能用在where子句 验证测试 不分组排序 建表 create table test ( num int comment '字段...
ROW_NUMBER() OVER()函数
weixin_45843683的博客
12-26 3053
https://blog.csdn.net/qq_25221835/article/details/82762416 https://www.cnblogs.com/CandiceW/p/6869167.html
PostgreSQL ROW_NUMBER() OVER()
luojinbai的专栏
04-16 5万+
语法： 　ROW_NUMBER() OVER( [ PRITITION BY col1] ORDER BY col2[ DESC ] ) 解释： 　ROW_NUMBER()为返回的记录定义个行编号， PRITITION BY col1 是根据col1分组，ORDER BY col2[ DESC ]是根据col2进行排序。 举例：postgres=# create table student(i
sum、row_number、count、rank\dense_rank over
xiyang5530的专栏
02-28 193
一、sum over    sum over主要用来对某个字段值进行逐步累加    SELECT   Name, Salary, SUM (Salary) OVER (ORDER BY Salary, Name) SubSal    FROM   emp    ORDER BY   Salary;    Name,   Salary,   Age      A，900，   20      B，9...
详述 SQL 中的 distinct 和 row_number() over() 的区别及用法
CG国斌的博客
02-22 4万+
1 前言在咱们编写 SQL 语句操作数据库中的数据的时候，有可能会遇到一些不太爽的问题，例如对于同一字段拥有相同名称的记录，我们只需要显示一条，但实际上数据库中可能含有多条拥有相同名称的记录，从而在检索的时候，显示多条记录，这就有违咱们的初衷啦！因此，为了避免这种情况的发生，咱们就需要进行“去重”处理啦，那么何为“去重”呢？说白了，就是对同一字段让拥有相同内容的记录只显示一条记录。那么，如何实现“去
row_number()over使用
Doris0214的博客
12-05 433
https://blog.csdn.net/qq_25221835/article/details/82762416
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值