`

Oracle分析函数2(rank,lag等)

阅读更多
rank函数介绍:
   准备工作:对已有的基础数据做一些修改,将5763的数据改成与5761的数据相同.
  update latty.test_t t1 set local_fare = (
       select local_fare from latty.test_t t2
       where t1.bill_month = t2.bill_month
       and t1.net_type = t2.net_type
       and t2.area_code = '5761'
   ) where area_code = '5763'


  我们先使用rank函数来计算各个地区的话费排名,SQL代码如下:
  
select area_code,sum(local_fare) local_fare,
     rank() over (order by sum(local_fare) desc) fare_rank
         from latty.test_t
              group by area_code

   执行结果显示为:
   AREA_CODE      LOCAL_FARE  FARE_RANK
---------- -------------- ----------
5765            104548.72          1
5761             54225.41          2
5763             54225.41          2 
5764             53156.77          4 
5762             52039.62          5


注意查看结果发现这里没有出现排名3。
下面我们看下dense_rank排名结果,SQL如下:
   select area_code,sum(local_fare) local_fare,
                     dense_rank()  over (order by sum(local_fare)     
desc) fare_rank
   from latty.test_t
   group by area_code

  执行结果如下:
 
AREA_CODE      LOCAL_FARE  FARE_RANK
---------- -------------- ----------
5765            104548.72          1
5761             54225.41          2
5763             54225.41          2
5764             53156.77          3  这是这里出现了第三名
5762             52039.62          4


再来看下row_number,查询SQL语句为:
 select area_code,sum(local_fare) local_fare,
    row_number()  over (order by sum(local_fare) desc) fare_rank
   from latty.test_t
   group by area_code


执行结果如下:
         5765	77418080.18	1
	5761	54225413.04	2
	5763	54225413.04	3
	5762	52039619.6	4
	5764	45814632.6	5
   

   rank如果出现两个相同的数据,那么后面的数据就会直接跳过这个排名,而dense_rank则不会,row_number哪怕是两个数据完全相同,排名也会不一样,这个特性在我们想找出对应没个条件的唯一记录的时候又很大用处。

   看看这个查询:取出各地区的话费收入在各个月份排名.SQL语句如下:
    select bill_month,area_code,sum(local_fare) local_fare,
        rank() over (partition by bill_month order by sum   (local_fare)        desc) area_rank
        from latty.test_t
        group by bill_month,area_code

  执行结果如下:
1	200405	5765	25057737.47	1
2	200405	5761	13060433.89	2
3	200405	5763	13060433.89	2
4	200405	5762	12643792.11	4
5	200405	5764	12487791.94	5
6	200406	5765	26058461.31	1
7	200406	5761	13318931.01	2
8	200406	5763	13318931.01	2
9	200406	5764	13295187.67	4
10	200406	5762	12795060.65	5
11	200407	5765	26301881.4	1
12	200407	5763	13710265.93	2
13	200407	5761	13710265.93	2
14	200407	5764	13444093.76	4
15	200407	5762	13224298.12	5
16	200408	5761	14135782.21	1
17	200408	5763	14135782.21	1
18	200408	5762	13376468.72	3
19	200408	5764	6587559.23	4
  


lag和lead函数介绍:取出每个月的上个月和下个月的话费总额
查询SQL如下:
    select area_code,bill_month, local_fare cur_local_fare,
       lag(local_fare,2,0) over (partition by area_code order by bill_month ) pre_local_fare,
       lag(local_fare,1,0) over (partition by area_code order by bill_month ) last_local_fare,
       lead(local_fare,1,0) over (partition by area_code order by bill_month ) next_local_fare,
       lead(local_fare,2,0) over (partition by area_code order by bill_month ) post_local_fare
         from (
           select area_code,bill_month,sum(local_fare) local_fare
           from latty.test_t
           group by area_code,bill_month
         )


执行结果为:
AREA_CODE BILL_MONTH CUR_LOCAL_FARE PRE_LOCAL_FARE LAST_LOCAL_FARE NEXT_LOCAL_FARE POST_LOCAL_FARE
--------- ---------- -------------- -------------- --------------- --------------- ---------------
5761      200405          13060.433              0               0        13318.93       13710.265
5761      200406           13318.93              0       13060.433       13710.265       14135.781
5761      200407          13710.265      13060.433        13318.93       14135.781               0
5761      200408          14135.781       13318.93       13710.265               0               0
5762      200405          12643.791              0               0        12795.06       13224.297
5762      200406           12795.06              0       12643.791       13224.297       13376.468
5762      200407          13224.297      12643.791        12795.06       13376.468               0
5762      200408          13376.468       12795.06       13224.297               0               0
5763      200405          13060.433              0               0        13318.93       13710.265
5763      200406           13318.93              0       13060.433       13710.265       14135.781
5763      200407          13710.265      13060.433        13318.93       14135.781               0
5763      200408          14135.781       13318.93       13710.265               0               0
5764      200405          12487.791              0               0       13295.187       13444.093
5764      200406          13295.187              0       12487.791       13444.093       13929.694
5764      200407          13444.093      12487.791       13295.187       13929.694               0
5764      200408          13929.694      13295.187       13444.093               0               0
5765      200405          25057.736              0               0        26058.46       26301.881
5765      200406           26058.46              0       25057.736       26301.881       27130.638
5765      200407          26301.881      25057.736        26058.46       27130.638               0
5765      200408          27130.638       26058.46       26301.881               0               0



sum,avg,max,min移动计算数据介绍:
查询为:计算出各个连续3个月的通话费用的平均数

SQL代码为:
select area_code,bill_month, local_fare cur_local_fare,
       lag(local_fare,2,0) over (partition by area_code order by bill_month ) pre_local_fare,
       lag(local_fare,1,0) over (partition by area_code order by bill_month ) last_local_fare,
       lead(local_fare,1,0) over (partition by area_code order by bill_month ) next_local_fare,
       lead(local_fare,2,0) over (partition by area_code order by bill_month ) post_local_fare
    from (
       select area_code,bill_month,sum(local_fare) local_fare
       from latty.test_t
       group by area_code,bill_month
 )


执行结果为:
AREA_CODE BILL_MONTH       LOCAL_FARE 3month_sum 3month_avg 3month_max 3month_min


1	5761	200405	13060433.89	0	0	13318931.01	13710265.93
2	5761	200406	13318931.01	0	13060433.89	13710265.93	14135782.21
3	5761	200407	13710265.93	13060433.89	13318931.01	14135782.21	0
4	5761	200408	14135782.21	13318931.01	13710265.93	0	0
5	5762	200405	12643792.11	0	0	12795060.65	13224298.12
6	5762	200406	12795060.65	0	12643792.11	13224298.12	13376468.72
7	5762	200407	13224298.12	12643792.11	12795060.65	13376468.72	0
8	5762	200408	13376468.72	12795060.65	13224298.12	0	0
9	5763	200405	13060433.89	0	0	13318931.01	13710265.93
10	5763	200406	13318931.01	0	13060433.89	13710265.93	14135782.21
11	5763	200407	13710265.93	13060433.89	13318931.01	14135782.21	0
12	5763	200408	14135782.21	13318931.01	13710265.93	0	0
13	5764	200405	12487791.94	0	0	13295187.67	13444093.76
14	5764	200406	13295187.67	0	12487791.94	13444093.76	6587559.23
15	5764	200407	13444093.76	12487791.94	13295187.67	6587559.23	0
16	5764	200408	6587559.23	13295187.67	13444093.76	0	0
17	5765	200405	25057737.47	0	0	26058461.31	26301881.4
18	5765	200406	26058461.31	0	25057737.47	26301881.4	0
19	5765	200407	26301881.4	25057737.47	26058461.31	0	0


first,last函数使用介绍:
查询为:取出每月通话费最高和最低的两个用户.
查询SQL为:
  select bill_month,area_code,sum(local_fare) local_fare,
       first_value(area_code)
               over (order by sum(local_fare) desc
                       rows unbounded preceding) firstval,
       first_value(area_code)
               over (order by sum(local_fare) asc
                       rows unbounded preceding) lastval
    from latty.test_t
    group by bill_month,area_code
   order by bill_month


执行结果显示为:
   BILL_MONTH AREA_CODE       LOCAL_FARE FIRSTVAL        LASTVAL
---------- --------- ---------------- --------------- ---------------
200405     5764             12487.791 5765            5764
200405     5762             12643.791 5765            5764
200405     5761             13060.433 5765            5764
200405     5765             25057.736 5765            5764
200405     5763             13060.433 5765            5764
200406     5762             12795.060 5765            5764
200406     5763             13318.930 5765            5764
200406     5764             13295.187 5765            5764
200406     5765             26058.460 5765            5764
200406     5761             13318.930 5765            5764
200407     5762             13224.297 5765            5764
200407     5765             26301.881 5765            5764
200407     5761             13710.265 5765            5764
200407     5763             13710.265 5765            5764
200407     5764             13444.093 5765            5764
200408     5762             13376.468 5765            5764
200408     5764             13929.694 5765            5764
200408     5761             14135.781 5765            5764
200408     5765             27130.638 5765            5764
200408     5763             14135.781 5765            5764





分享到:
评论

相关推荐

    Oracle中的分析函数详解

    - `FUNCTION_NAME`:具体的分析函数,如RANK, LAG, LEAD, SUM等。 - `PARTITION BY`:可选,用于将数据集划分为不同的分区,每个分区内部独立进行计算。 - `ORDER BY`:可选,用于指定在每个分区内的行顺序。 - `...

    ORACLE分析函数大全

    Oracle分析函数是数据库管理系统Oracle中的一个强大特性,它允许用户在SQL查询中执行复杂的分析操作。分析函数在处理报表和数据迁移任务时尤其有用,因为它们可以基于分组计算聚合值,并为每个分组返回多行,而不...

    Oracle分析函数

    Oracle 分析函数详解 Oracle 分析函数是 Oracle 数据库中的一种强大功能,能够帮助用户快速进行数据分析和处理。在本文中,我们将对 Oracle 分析函数进行详细的介绍,并对其各个函数进行解释。 一、总体介绍 ...

    ORACLE 分析函数大全

    Oracle 分析函数是一种强大的SQL工具,它允许你在处理数据时执行复杂的分析操作,而不像聚合...参考书籍如Tom Kyte的《Expert One-on-One》和Oracle 9i SQL Reference等,都是深入学习和理解Oracle分析函数的宝贵资源。

    oracle 分析函数学习笔记

    Oracle的分析函数还包括`ROW_NUMBER`, `RANK`, `DENSE_RANK`用于行号分配,`LEAD`, `LAG`用于获取前后行数据,`FIRST_VALUE`, `LAST_VALUE`获取窗口范围内的第一个或最后一个值,以及`NTILE`进行分桶等。这些函数在...

    ORACLE分析函数.pdf

    Oracle分析函数是数据库管理系统Oracle中的一种高级SQL特性,它允许用户在单个查询中对一组行进行计算,而无需使用子查询或自连接。这些函数极大地增强了数据分析和报告的能力,提高了查询性能。以下是对Oracle分析...

    Oracle分析函数.doc

    Oracle分析函数是数据库管理系统Oracle中的一种高级特性,用于处理和分析数据集,提供了一种高效的方式来执行聚合操作,而无需多次查询数据库。分析函数能够直接在单次查询中完成复杂的数据计算,包括排序、分组、...

    oracle分析函数.doc

    2. **Oracle分析函数简单实例** 假设我们有一个销售表,包含产品ID、销售日期和销售额,我们可以使用`ROW_NUMBER()`来为每条记录分配一个顺序编号,或者使用`SUM()`函数计算每个产品的累计销售额。 ```sql ...

    Oracle 分析函数.doc

    Oracle 分析函数是数据库查询中的强大工具,它们允许在单个SQL语句中对结果集进行复杂的计算和分组操作,极大地简化了数据分析的过程。在Oracle 8i版本之后引入,分析函数为处理大量数据提供了高效的方法,避免了...

    ORACLE_分析函数大全

    除了这些,Oracle分析函数还包括RANK、DENSE_RANK、ROW_NUMBER等排名函数,LEAD和LAG用于获取当前行之前或之后的值,FIRST_VALUE和LAST_VALUE则返回窗口内的第一个或最后一个值,以及NTILE用于将数据分桶等。...

    深入浅出Oracle分析函数

    Oracle分析函数不同于聚合函数(如SUM、AVG、COUNT等),它们可以在单个查询中对分组数据进行计算,而不仅仅是简单地对整个结果集进行操作。分析函数可以对数据流进行窗口化处理,提供诸如排名、分段、累积计算等...

    Oracle分析函数教程

    Oracle分析函数是数据库管理系统Oracle中的一个重要特性,自8.1.6版本开始引入,它们用于执行基于组的聚合计算,并且为每个组返回多行结果,而不仅仅是单行。这使得分析函数在数据分析和报表生成方面非常有用,能够...

    oracle分析函数,窗口函数,报表函数

    Oracle 分析函数、窗口函数和报表函数是数据库查询和数据分析中的关键工具,尤其在复杂的OLAP(在线分析处理)系统中发挥着重要作用。在Oracle数据库中,这些功能提供了对大量数据进行高效处理的能力,帮助用户生成...

    oracle的分析函数汇总

    Oracle 分析函数汇总 Oracle 分析函数是 Oracle 8.16 中引入的一个全新的概念,为我们分析数据提供了一种简单高效的处理方式。下面我们将对 Oracle 分析函数进行详细的介绍和分析。 自动汇总函数 自动汇总函数...

    oracle分析函数全面解析

    Oracle分析函数是数据库管理系统Oracle中的一种高级查询工具,主要用于处理复杂的报表统计和数据分析。它们在OLAP(在线分析处理)系统中尤其重要,因为这类系统往往需要处理大量数据并进行复杂的聚合计算,而分析...

    oracle分析函数

    Oracle分析函数是数据库管理系统Oracle中的一种高级SQL特性,自8.1.6版本引入,极大地扩展了SQL在数据处理和分析上的能力。分析函数的主要作用是进行聚合操作,但与传统的聚合函数(如SUM, COUNT, AVG等)不同,它们...

    Oracle分析函数.pdf

    Oracle分析函数是OLAP(在线分析处理)和数据挖掘等领域中不可或缺的工具。 Oracle分析函数简介部分提到,分析函数在OLTP(在线事务处理)和OLAP系统中的应用场景不同。OLTP系统处理实时事务,业务逻辑复杂,而OLAP...

    oracle分析函数及开窗函数

    ### Oracle分析函数及开窗函数详解 #### 一、Oracle分析函数概述 Oracle自8.1.6版本开始引入了分析函数,这类函数主要用于计算基于组的聚合值,并且与传统的聚合函数不同的是,分析函数可以针对每个组返回多行结果...

Global site tag (gtag.js) - Google Analytics