分析函数

分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是对于每个组返回多行，而聚合函数对于每个组只返回一行。

开窗函数的的理解：
开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化，举例如下：
over(order by salary) 按照salary排序进行累计，order by是个默认的开窗函数
over(partition by deptno) 按照部门分区
over(order by salary range between 50 preceding and 150 following)
每行对应的数据窗口是之前行幅度值不超过50，之后行幅度值不超过150
over(order by salary rows between 50 preceding and 150 following)
每行对应的数据窗口是之前50行，之后150行
over(order by salary rows between unbounded preceding and unbounded following)
每行对应的数据窗口是从第一行到最后一行，等效：
over(order by salary range between unbounded preceding and unbounded following)

主要函数：

1. 自动汇总函数rollup,cube

举例一、

SQL> select decode(grouping(organ_code), 1, 'All Area', organ_code) organ_code,
  2         count(cust_no) new_user
  3    from cust
  4   group by rollup(organ_code);

ORGAN_CODE             NEW_USER
-------------------- ----------
@@CC                        796
@@CCXJ                        9
@@CS                          9
@@DD                       1242
@@FR                         32
@@JC                         62
@@JL                      12288
@@JLDS                        6
All Area                  14444

9 rows selected

举例二、

SQL> select decode(grouping(organ_code), 1, 'All Area', organ_code) organ_code,
  2         decode(grouping(to_char(create_date,'yyyy-mm')),1,'All Month',to_char(create_date,'yyyy-mm')) create_date,
  3         count(cust_no) new_user
  4    from cust
  5   group by cube(organ_code, to_char(create_date,'yyyy-mm'));

ORGAN_CODE           CREATE_DATE   NEW_USER
-------------------- ----------- ----------
All Area             All Month        14444
All Area             2009-01              1
All Area             2009-02          13403
All Area             2009-03           1040
@@CC                 All Month          796
@@CC                 2009-01              1
@@CC                 2009-02            108
@@CC                 2009-03            687
@@CS                 All Month            9
@@CS                 2009-03              9
@@DD                 All Month         1242
@@DD                 2009-02           1242
@@FR                 All Month           32
@@FR                 2009-02             32
@@JC                 All Month           62
@@JC                 2009-02             62
@@JL                 All Month        12288
@@JL                 2009-02          11953
@@JL                 2009-03            335
@@CCXJ               All Month            9
@@CCXJ               2009-03              9
@@JLDS               All Month            6
@@JLDS               2009-02              6

23 rows selected

2. rank 函数, rank,dense_rank,row_number

举例一、

SQL> -- 跳位排名
SQL> select organ_code,
  2         count(cust_no) new_cust,
  3         rank() over(order by count(cust_no) desc) user_rank
  4    from cust
  5   group by organ_code;

ORGAN_CODE             NEW_CUST  USER_RANK
-------------------- ---------- ----------
@@JL                      12288          1
@@DD                       1242          2
@@CC                        796          3
@@JC                         62          4
@@FR                         32          5
@@CCXJ                        9          6
@@CS                          9          6
@@JLDS                        6          8

8 rows selected

举例二、

SQL> -- 不跳位排名
SQL> select organ_code,
  2         count(cust_no) new_cust,
  3         dense_rank() over(order by count(cust_no) desc) user_rank
  4    from cust
  5   group by organ_code;

ORGAN_CODE             NEW_CUST  USER_RANK
-------------------- ---------- ----------
@@JL                      12288          1
@@DD                       1242          2
@@CC                        796          3
@@JC                         62          4
@@FR                         32          5
@@CCXJ                        9          6
@@CS                          9          6
@@JLDS                        6          7

8 rows selected

举例三、

SQL> -- 不重复名次排名
SQL> select organ_code,
  2         count(cust_no) new_cust,
  3         row_number() over(order by count(cust_no) desc) user_rank
  4    from cust
  5   group by organ_code;

ORGAN_CODE             NEW_CUST  USER_RANK
-------------------- ---------- ----------
@@JL                      12288          1
@@DD                       1242          2
@@CC                        796          3
@@JC                         62          4
@@FR                         32          5
@@CCXJ                        9          6
@@CS                          9          7
@@JLDS                        6          8

8 rows selected

3. lag,lead函数

举例一、

SQL> -- 统计各区域当月、上个月和下个月的开户数
SQL> select organ_code,
  2         to_char(create_date, 'yyyy-mm') stat_month,
  3         count(cust_no) cur_new_uesr,
  4         lag(count(cust_no), 1, 0) over(partition by organ_code order by to_char(create_date, 'yyyy-mm')) last_new_user,
  5         lead(count(cust_no), 1, 0) over(partition by organ_code order by to_char(create_date, 'yyyy-mm')) next_new_user
  6    from cust
  7   group by organ_code, to_char(create_date, 'yyyy-mm');

ORGAN_CODE           STAT_MONTH CUR_NEW_UESR LAST_NEW_USER NEXT_NEW_USER
-------------------- ---------- ------------ ------------- -------------
@@CC                 2009-01               1             0           108
@@CC                 2009-02             108             1           687
@@CC                 2009-03             687           108             0
@@CCXJ               2009-03               9             0             0
@@CS                 2009-03               9             0             0
@@DD                 2009-02            1242             0             0
@@FR                 2009-02              32             0             0
@@JC                 2009-02              62             0             0
@@JL                 2009-02           11953             0           335
@@JL                 2009-03             335         11953             0
@@JLDS               2009-02               6             0             0

11 rows selected

4. sum,avg,的移动增加,移动平均数

举例一、

SQL> -- 求三个月的客户数总和、平均值
SQL> select organ_code,
  2         stat_month,
  3         cur_new_user,
  4         lag(cur_new_user, 1, 0) over(partition by organ_code order by stat_month) last_new_user,
  5         lead(cur_new_user, 1, 0) over(partition by organ_code order by stat_month) next_new_user,
  6         sum(cur_new_user) over(partition by organ_code order by to_number(stat_month) range between 1 preceding and 1 following) three_mon_sum,
  7         avg(cur_new_user) over(partition by organ_code order by to_number(stat_month) range between 1 preceding and 1 following) three_mon_avg
  8    from (select organ_code,
  9                 to_char(create_date, 'yyyymm') stat_month,
 10                 count(cust_no) cur_new_user
 11            from cust
 12           group by organ_code, to_char(create_date, 'yyyymm'));

ORGAN_CODE           STAT_MONTH CUR_NEW_USER LAST_NEW_USER NEXT_NEW_USER THREE_MON_SUM THREE_MON_AVG
-------------------- ---------- ------------ ------------- ------------- ------------- -------------
@@CC                 200901                1             0           108           109          54.5
@@CC                 200902              108             1           687           796 265.333333333
@@CC                 200903              687           108             0           795         397.5
@@CCXJ               200903                9             0             0             9             9
@@CS                 200903                9             0             0             9             9
@@DD                 200902             1242             0             0          1242          1242
@@FR                 200902               32             0             0            32            32
@@JC                 200902               62             0             0            62            62
@@JL                 200902            11953             0           335         12288          6144
@@JL                 200903              335         11953             0         12288          6144
@@JLDS               200902                6             0             0             6             6

11 rows selected

5. ratio_to_report报表处理函数

举例一、

SQL> -- 某区域的开户数占所有区域开户数的比例
SQL> select organ_code,
  2         to_char(create_date, 'yyyy-mm'),
  3         count(cust_no) new_user,
  4         ratio_to_report(count(cust_no)) over(partition by to_char(create_date, 'yyyy-mm')) area_ration
  5    from cust
  6   group by organ_code, to_char(create_date, 'yyyy-mm');

ORGAN_CODE           TO_CHAR(CREATE_DATE,'YYYY-MM')   NEW_USER AREA_RATION
-------------------- ------------------------------ ---------- -----------
@@CC                 2009-01                                 1           1
@@CC                 2009-02                               108 0.008057897
@@DD                 2009-02                              1242 0.092665821
@@FR                 2009-02                                32 0.002387525
@@JC                 2009-02                                62 0.004625830
@@JL                 2009-02                             11953 0.891815265
@@JLDS               2009-02                                 6 0.000447660
@@CC                 2009-03                               687 0.660576923
@@CCXJ               2009-03                                 9 0.008653846
@@CS                 2009-03                                 9 0.008653846
@@JL                 2009-03                               335 0.322115384

11 rows selected

6. first,last取基数的分析函数

举例一、

SQL> -- 查询各个月份开户最多的区域和最少的区域
SQL> select distinct stat_month,
  2                  first_value(organ_code) over(partition by stat_month order by new_user desc rows unbounded preceding) max_organ,
  3                  first_value(organ_code) over(partition by stat_month order by new_user rows unbounded preceding) min_organ
  4    from (select to_char(create_date, 'yyyy-mm') stat_month,
  5                 organ_code,
  6                 count(cust_no) new_user
  7            from cust
  8           group by to_char(create_date, 'yyyy-mm'), organ_code)
  9   order by stat_month;

STAT_MONTH MAX_ORGAN            MIN_ORGAN
---------- -------------------- --------------------
2009-01    @@CC                 @@CC
2009-02    @@JL                 @@JLDS
2009-03    @@CC                 @@CS