`
wangxiao5530
  • 浏览: 136839 次
  • 性别: Icon_minigender_2
  • 来自: 大连
社区版块
存档分类
最新评论

sum、row_number、count、rank\dense_rank over

 
阅读更多

一、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

   
三、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

   
四、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%';

分享到:
评论

相关推荐

    ORACLE分析函数

    根据提供的文件信息,我们可以深入探讨Oracle分析函数的相关知识点,特别是`SUM()`函数配合`OVER`子句的不同用法,以及`RANK()`, `DENSE_RANK()`, 和 `ROW_NUMBER()` 这三个窗口函数的应用场景。 ### Oracle分析...

    oracle常用分析函数与聚合函数的用法

    dense_rank() over (order by to_number(score) desc) "dense_rank", row_number() over (order by to_number(score) desc) "row_number" from lcy; ``` 接下来,我们讨论Oracle的聚合函数。这些函数用于汇总数据...

    ORACLE OLAP函数语法的总结

    排列函数如RANK(), DENSE_RANK(), ROW_NUMBER()等,用于在结果集中创建行的排序等级。 ```sql SELECT empno, deptno, sal, RANK() OVER (PARTITION BY deptno ORDER BY sal DESC NULLS LAST) AS rank, DENSE_RANK...

    MySQL对window函数执行sum函数可能出现的一个Bug

    DENSE_RANK() OVER (PARTITION BY s.c_id ORDER BY s.s_score DESC) AS dense_id FROM score s JOIN course_totals c ON s.c_id = c.c_id; ``` 这样,`total_score`列将包含每个课程的正确总分,而不会受到...

    Oracle 分析函数

    - **等级(Ranking)函数**: 这些函数用于确定数据的相对位置,如`RANK`, `DENSE_RANK`, `ROW_NUMBER`等。它们常用于找到前N项或为每一行分配一个特定的排名。 - **开窗(Windowing)函数**: 这类函数用于计算不同的...

    oracle分析函数学习

    - 窗口函数(如RANK, DENSE_RANK, ROW_NUMBER)在选定的窗口内进行计算,窗口可以是整个结果集,也可以是根据某些条件定义的子集。 - 报表函数(如CUME_DIST, PERCENT_RANK)通常用于计算相对位置或百分比分布,...

    深入浅出Oracle分析函数

    Oracle中的分析函数主要包括RANK()、DENSE_RANK()、ROW_NUMBER()用于排名,LEAD()和LAG()用于获取当前行前后行的值,FIRST_VALUE()和LAST_VALUE()用于获取分区的第一行和最后一行的值,以及SUM()、AVG()、MIN()、MAX...

    oracle分析函数在BI分析中应用事例

    这里,`聚合函数`可以是`sum`, `count`, `avg`, `max`, `min`, `first_value`, `last_value`, `rank`, `dense_rank`, `row_number`, `ratio_to_report`等。`over`关键字定义了一个分析上下文,`partition by`用于将...

    oracle分析函数

    - 分析函数主要分为以下几类:排名函数(Rank, Dense_Rank, Row_Number)、分组函数(如Percent_Rank, Cume_Dist)、移动平均函数(Moving_Average)、累积和/积函数(Cumulative_Sum, Cumulative_Product)以及...

    Oracle_详解分析函数

    1. **等级函数**:如`RANK`, `DENSE_RANK`, `ROW_NUMBER`等,用于对数据进行排序和排名。 2. **窗口函数**:如`SUM`, `COUNT`, `AVG`, `MIN`, `MAX`等,用于在特定的数据窗口上进行统计计算。 3. **报表函数**:与...

    深入浅出oracle分析函数(全)

    Oracle提供了26个内置的分析函数,包括`SUM()`, `COUNT()`, `AVG()`, `MIN()`, `MAX()`, `LAG()`, `LEAD()`, `CUME_DIST()`, `PERCENT_RANK()`, `RANK()`, `DENSE_RANK()`, `ROW_NUMBER()`, `NTILE()`, `FIRST_...

    Oracle分析函数

    DENSE_RANK 函数用于计算行的相对排序,相同的值具有一样的序数。 3.8 FIRST_VALUE 函数 FIRST_VALUE 函数用于获取一个组的第一个值。 3.9 LAG 函数 LAG 函数用于访问之前的行。 3.10 LAST_VALUE 函数 LAST_...

    【转】Oracle分析函数简述

    ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) as rank FROM employees; ``` 在这个例子中,我们首先按照部门(department_id)进行分区,然后在每个部门内按工资(salary)降序排列,...

    sql开窗函数详解.rar

    1. RANK()、DENSE_RANK()和ROW_NUMBER():这三个函数用于生成行的排名。RANK()跳过空值,DENSE_RANK()不跳过,ROW_NUMBER()为每一行分配唯一的数字。 2. LAG()和LEAD():这两个函数用于获取当前行之前或之后的值。...

    oracle分析函数指南

    例如,`RANK()`, `DENSE_RANK()` 和 `ROW_NUMBER()` 函数可以用来为每一行分配一个唯一的排名,`LEAD()` 和 `LAG()` 可以获取当前行前后行的值,而 `SUM() OVER()` 可以计算累计和。 2. **分析函数OVER解析** `...

    oracle开窗函数学习技巧总结

    1. **排名函数**:包括`RANK()`、`DENSE_RANK()`和`ROW_NUMBER()`等,用于按指定顺序为每行分配一个排名。 2. **分布函数**:如`NTILE()`,用于将行划分为指定数量的组。 3. **聚合函数**:如`SUM()`、`AVG()`等,...

Global site tag (gtag.js) - Google Analytics