Oracle 分组统计

Oracle统计函数

--Rank和Dense_Rank
分类:Oracle数据库
聚合函数RANK 和 dense_rank 主要的功能是计算一组数值中的排序值。
在9i版本之前，只有分析功能（analytic ），即从一个查询结果中计算每一行的排序值，是基于order_by_clause子句中的value_exprs指定字段的。
其语法为：
RANK ( ) OVER ( [query_partition_clause] order_by_clause )
在9i版本新增加了合计功能（aggregate），即对给定的参数值在设定的排序查询中计算出其排序值。这些参数必须是常数或常值表达式，且必须和ORDER BY子句中的字段个数、位置、类型完全一致。
其语法为：
RANK ( expr [, expr]... ) WITHIN GROUP
( ORDER BY
expr [ DESC | ASC ] [NULLS { FIRST | LAST }]
[, expr [ DESC | ASC ] [NULLS { FIRST | LAST }]]...
)
例子1：
有表Table内容如下
COL1　COL2
　 1　1
　 2　1
　 3　2
　 3　1
　 4　1
　 4　2
　 5　2
　 5　2
　 6　2
分析功能：列出Col2分组后根据Col1排序,并生成数字列。比较实用于在成绩表中查出各科前几名的信息。
SELECT a.*,RANK() OVER(PARTITION BY col2 ORDER BY col1) "Rank" FROM table a;
结果如下：
COL1　COL2　Rank
　 1　1　　 1
　 2　1　　 2
　 3　1　　 3
　 4　1　　 4
　 3　2　　 1
　 4　2　　 2
　 5　2　　 3
　 5　2　　 3
　 6　2　　 5
例子2：
TABLE：A （科目，分数）
数学，80
语文，70
数学，90
数学，60
数学，100
语文，88
语文，65
语文，77
现在我想要的结果是：（即想要每门科目的前3名的分数）
数学，100
数学，90
数学，80
语文，88
语文，77
语文，70
那么语句就这么写：
select * from (select rank() over(partition by 科目 order by 分数 desc) rk,a.* from a) t
where t.rk<=3;
例子3：
合计功能：计算出数值(4,1)在Orade By Col1,Col2排序下的排序值，也就是col1=4,col2=1在排序以后的位置
SELECT RANK(4,3) WITHIN GROUP (ORDER BY col1,col2) "Rank" FROM table;
结果如下：
Rank
4
dense_rank与rank()用法相当，但是有一个区别：dence_rank在并列关系是，相关等级不会跳过。rank则跳过
例如：表
A　　　　　　B　　　　　　C
a　　　　　liu　　　　　wang
a　　　　　jin　　　　　shu
a　　　　　cai　　　　　kai
b　　　　　yang　　　　 du
b　　　　　lin　　　　　ying
b　　　　　yao　　　　　cai
b　　　　　yang　　　　 99
例如：当rank时为：
select m.a,m.b,m.c,rank() over(partition by a order by b) liu from test3 m
A　　　　　B　　　　　　 C　　　　　LIU
a　　　　　cai　　　　　 kai　　　　　1
a　　　　　jin　　　　　 shu　　　　　2
a　　　　　liu　　　　　 wang　　　　 3
b　　　　　lin　　　　　 ying　　　　 1
b　　　　　yang　　　　　du　　　　　 2
b　　　　　yang　　　　　99　　　　　 2
b　　　　　yao　　　　　 cai　　　　　4
而如果用dense_rank时为：
select m.a,m.b,m.c,dense_rank() over(partition by a order by b) liu from test3 m
A　　　　　B　　　　　　 C　　　　　LIU
a　　　　　cai　　　　　kai　　　　　1
a　　　　　jin　　　　　shu　　　　　2
a　　　　　liu　　　　　wang　　　　 3
b　　　　　lin　　　　　ying　　　　 1
b　　　　　yang　　　　 du　　　　　 2
b　　　　　yang　　　　 99　　　　　 2
b　　　　　yao　　　　　cai　　　　　3
]]> 0.01239

 

 

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

语法：
Sql代码
<analytic-function>(<argument>,<argument>,...)   
over(   
<query-partition-clause>   
<order-by-clause>   
<windowing-clause>   
)  
说明：
<1> over是关键字，用于标识分析函数。
<2> <analytic-function>是指定的分析函数的名字。
<3> <argument>为参数，分析函数可以选取0-3个参数。
<4> 分区子句<query-partition-clause>的格式为：
     partition by<value_exp>[,value_expr]...
    关键字partition by子句根据由分区表达式的条件逻辑地将单个结果集分成N组。这里的"分区partition"和"组group" 都是同义词。
<5> 排序子句order-by-clause指定数据是如何存在分区内的。其格式为：
order[siblings]by{expr|position|c_alias}[asc|desc][nulls first|nulls last]
其中：
     A.asc|desc:指定了排列顺序。
     B.nulls first|nulls last:指定了包含空值的返回行应出现在有序序列中的第一个或最后一个位置。
<6>窗口子句windowing-clause
    给出一个固定的或变化的数据窗口方法，分析函数将对这些数据进行操作。在一组基于任意变化或固定的窗口中，
可用该子句让分析函数计算出它的值。
格式：
{rows|range}
{between
{unbounded preceding|current row |<value_expr>{preceding|following}
}and
{unbounded preceding|current row |<value_expr>{preceding|following}
}|{unbounded preceding|current row |<value_expr>{preceding|following
}}
    A.rows|range:此关键字定义了一个window。
     B.between...and...:为窗品指一个起点和终点。
     C.unbounded preceding：指明窗口是从分区(partition)的第一行开始。
     D.current row:指明窗口是从当前行开始。

开窗函数：
       开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化，举例如下：
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、Oracle ROLLUP和CUBE 用法    
      Oracle的GROUP BY语句除了最基本的语法外，还支持ROLLUP和CUBE语句。如果是Group by  ROLLUP(A, B, C)的话，首先会对(A、B、C)进行GROUP BY，然后对(A、B)进行GROUP BY，然后是(A)进行GROUP BY，最后对全表进行GROUP BY操作。

      如果是GROUP BY CUBE(A, B, C)，则首先会对(A、B、C)进行GROUP BY，然后依次是(A、B)，(A、C)，(A)，(B、C)，(B)，(C)，最后对全表进行GROUP BY操作。也可以 Group by Rollup(A,(B,C)) ，Group by A Rollup(B,C)，……

      如何来区分到底是根据那个字段做的汇总呢,这时候,oracle的grouping函数就粉墨登场了.如果当前的汇总记录是利用该字段得出的,grouping函数就会返回1,否则返回0。
示例：
Sql代码
select nvl(area_code,'合计') area_code,sum(local_fare) local_fare  
 from t  
group by rollup(nvl(area_code,'合计'));  
Sql代码
select area_code,bill_month,sum(local_fare) local_fare  
from t  
group by cube(area_code,bill_month)  
order by area_code,bill_month nulls last;  
Sql代码
select decode(grouping(area_code),1,'all area',to_char(area_code)) area_code,  
 decode(grouping(bill_month),1,'all month',bill_month) bill_month,  
 sum(local_fare) local_fare  
from t  
group by cube(area_code,bill_month)  
order by area_code,bill_month nulls last; 
    简单点说：为了生成数据统计以及横向小计统计，可以在GROUP BY子句中使用ROLLUP操作符。为了生成数据统计、横向小计、纵向小计结果，可以使用CUBE操作符。
2、Rank的用法

       功能描述：根据ORDER BY子句中表达式的值，从查询返回的每一行，计算它们与其它行的相对位置。组内的数据按ORDER BY子句排序，然后给每一行赋一个号，从而形成一个序列，该序列从1开始，往后累加。每次ORDER BY表达式的值发生变化时，该序列也随之增加。有同样值的行得到同样的数字序号（认为null时相等的）。然而，如果两行的确得到同样的排序，则序数将随后跳跃。若两行序数为1，则没有序数2，序列将给组中的下一行分配值3，DENSE_RANK则没有任何跳跃。
       rank()是跳跃排序，比如有两个第二名时接下来就是第四名（同样是在各个分组内）

       dense_rank()是连续排序，比如有两个第二名时仍然跟着第三名。
示例：
Sql代码
select area_code,sum(local_fare) local_fare,  
rank() over (order by sum(local_fare) desc) fare_rank  
from 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
Sql代码
select area_code,sum(local_fare) local_fare,  
dense_rank() over (order by sum(local_fare) desc ) fare_rank  
from 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 t  
group by area_code; 
结果：
AREA_CODE LOCAL_FARE FARE_RANK
---------- -------------- ----------
5765     104548.72    1
5761     54225.41     2
5763     54225.41     3
5764     53156.77     4
rank()示例：
a. 取出数据库中最后入网的n个用户
Sql代码
select user_id,tele_num,user_name,user_status,create_date   
from (  
select user_id,tele_num,user_name,user_status,create_date,  
rank() over (order by create_date desc) add_rank  
from user_info  
)  
where add_rank <= :n; 
b.根据object_name删除数据库中的重复记录
create table t as select obj#,name from sys.obj$;
再insert into t1 select * from t1 数次.
Sql代码
delete from t1 where rowid in (  
select row_id from (  
select rowid row_id,row_number() over (partition by obj# order by rowid ) rn  
) where rn <> 1  
); 
c. 取出各地区的话费收入在各个月份排名.
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 t  
 group by bill_month,area_code 
结果：
BILL_MONTH AREA_CODE LOCAL_FARE AREA_RANK
--------------- --------------- -------------- ----------
200405        5765    25057.74      1
200405        5761    13060.43      2
200405        5763    13060.43      2
200405        5762    12643.79      4
200405        5764    12487.79      5
200406        5765    26058.46      1
200406        5761    13318.93      2
200406        5763    13318.93      2
200406        5764    13295.19      4
200406        5762    12795.06      5
200407        5765    26301.88      1
200407        5761    13710.27      2
200407        5763    13710.27      2
200407        5764    13444.09      4
200407        5762    13224.30      5

3、First/Last的用法
    First功能描述：从DENSE_RANK返回的集合中取出排在最前面的一个值的行（可能多行，因为值可能相等），因此完整的语法需要在开始处加上一个集合函数以从中取出记录。
     Last功能描述：从DENSE_RANK返回的集合中取出排在最后面的一个值的行（可能多行，因为值可能相等），因此完整的语法需要在开始处加上一个集合函数以从中取出记录。

示例：下面例子中DENSE_RANK按部门分区，再按佣金commission_pct排序，FIRST取出佣金最低的对应的所有行，然后前面的MIN函数从这个集合中取出薪水最低的值；LAST取出佣金最高的对应的所有行，然后前面的MAX函数从这个集合中取出薪水最高的值。
Sql代码
SELECT last_name, department_id, salary,  
MIN(salary) KEEP (DENSE_RANK FIRST ORDER BY commission_pct)  
OVER (PARTITION BY department_id) "Worst",  
MAX(salary) KEEP (DENSE_RANK LAST ORDER BY commission_pct)  
OVER (PARTITION BY department_id) "Best" 
FROM employees  
WHERE department_id in (20,80)  
ORDER BY department_id, salary; 
结果：
LAST_NAME DEPARTMENT_ID SALARY Worst Best
------------------------- ------------- ---------- ---------- ----------
Fay 20 6000 6000 13000
Hartstein 20 13000 6000 13000
Kumar 80 6100 6100 14000
Banda 80 6200 6100 14000
Johnson 80 6200 6100 14000

4、FIRST_VALUE/LAST_VALUE的用法

      FIRST_VALUE、LAST_VALUE是两个分析函数。返回结果集中排在第一位和最后一位的值。语法是：
FIRST_VALUE (expr) OVER ( analytic_clause)
示例：
计算按部门分区按薪水排序的数据窗口的第一个值对应的名字，如果薪水的第一个值有多个，则从多个对应的名字中取缺省排序的第一个名字。
Sql代码
SELECT department_id, last_name, salary, FIRST_VALUE(last_name)  
OVER (PARTITION BY department_id ORDER BY salary ASC ) AS lowest_sal  
FROM employees  
WHERE department_id in(20,30); 
结果：
DEPARTMENT_ID LAST_NAME SALARY LOWEST_SAL
------------- ------------------------- ---------- --------------
20 Fay 6000 Fay
20 Hartstein 13000 Fay
30 Colmenares 2500 Colmenares
30 Himuro 2600 Colmenares
30 Tobias 2800 Colmenares
30 Baida 2900 Colmenares
30 Khoo 3100 Colmenares
30 Raphaely 11000 Colmenares

5、Lag/Lead的用法

       功能描述：可以访问结果集中的其它行而不用进行自连接。它允许去处理游标，就好像游标是一个数组一样。在给定组中可参考当前行之前的行，这样就可以从组中与当前行一起选择以前的行。Offset是一个正整数，其默认值为1，若索引超出窗口的范围，就返回默认值（默认返回的是组中第一行），其相反的函数是LEAD。
示例：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 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

6.RATIO_TO_REPORT用法

功能描述：该函数计算expression/(sum(expression))的值，它给出相对于总数的百分比，即当前行对sum(expression)的贡献。
格式：RATIO_TO_REPORT (expr) OVER (query_partition_clause)
示例：计算每个员工的工资占该类员工总工资的百分比
Sql代码
SELECT last_name, salary, RATIO_TO_REPORT(salary) OVER () AS rr  
FROM employees  
WHERE job_id = 'PU_CLERK'; 
7.GROUPING SETS用法
     从Oracle9i开始，使用GROUPING SETS操作符可以合并多个分组的结果。并且可以用GROUP BY GROUPING SETS来代替GROUP BY CUBE。你可以应用来指定你感兴趣的总数组合。因为它不必计算它不需要集合（也不会产生太多结果），所以对SQL引擎来说更为高效。
其格式为：
GROUP BY GROUPING SETS ((list), (list) ... )
      这里（list）是圆括号中的一个列序列，这个组合生成一个总数。要增加一个总和，必须增加一个(NUlL)分组集。

示例：如果只要生成每项产品（包括所有顾客和通道）和每个顾客/通道组合（包括所有产品）的总数。
Sql代码
SELECT prod_id, cust_id, channel_id, SUM(quantity_sold)  
FROM sales  
WHERE cust_id < 3  
GROUP BY GROUPING SETS (  
(prod_id), (cust_id, channel_id) ,NULL 
); 
示例：统计人员的获奖数
Sql代码
SELECT   
      (CASE WHEN grouping(a.c_xm)=1 AND grouping(a.n_jxdm)=1 AND grouping(a.c_rydm)=1  
            THEN '合计'   
            WHEN grouping(a.n_jxdm)=1 AND grouping(a.c_rydm)=1  
                 THEN '小计' 
                   ELSE a.c_xm  
        END) c_xm  
      ,a.n_jxdm,a.c_rydm,COUNT(*)  
FROM T_SK_JPGL_HJMD a  
GROUP BY grouping sets (a.c_xm,(a.c_xm,a.n_jxdm,a.c_rydm),NULL)  ;

 

一、行变列
SELECT REPLACE(wmsys.wm_concat(t.drug_class_nme), ',', '/') AS drug_class_nme
  FROM drug_category t
 START WITH t.drug_class = 49683
CONNECT BY PRIOR t.spuer_code = t.drug_class
       AND t.dflag IS NULL
       AND t.ref_class_code = 1

SELECT REPLACE(wmsys.wm_concat(drug_class_nme), ',', '\')
  FROM (SELECT *
          FROM (SELECT *
                  FROM drug_category t
                CONNECT BY PRIOR t.spuer_code = t.drug_class
                 START WITH t.drug_class = 30948)
         ORDER BY serial_id ASC)

SELECT REPLACE(wmsys.wm_concat(drug_class_nme), ',', '\')
  FROM (SELECT *
          FROM (SELECT t.*, rownum AS my_row
                  FROM drug_category t
                CONNECT BY PRIOR t.spuer_code = t.drug_class
                 START WITH t.drug_class = 30948)
         ORDER BY my_row DESC)

二、列变行
如表ROW2COLUMN2有以下数据：

名字 课程 分数
1 张三 语文 80
2 张三 数学 86
3 张三 英语 75
4 李四 语文 78
5 李四 数学 85
6 李四 英语 78

想变成下面记录：

名字 语文 数学 英语
1 李四 78 85 78
2 张三 80 86 75
----------------------------------------------
CREATE TABLE ROW2COLUMN2(NAME_ VARCHAR2(20),
                         CLASS VARCHAR2(50),
                         SCORE NUMBER(3));

INSERT INTO ROW2COLUMN2 VALUES ('张三', '语文', 80);
INSERT INTO ROW2COLUMN2 VALUES ('张三', '数学', 86);
INSERT INTO ROW2COLUMN2 VALUES ('张三', '英语', 75);
INSERT INTO ROW2COLUMN2 VALUES ('李四', '语文', 78);
INSERT INTO ROW2COLUMN2 VALUES ('李四', '数学', 85);
INSERT INTO ROW2COLUMN2 VALUES ('李四', '英语', 78);

SELECT * FROM ROW2COLUMN2;

SELECT NAME_,
       MAX(DECODE(CLASS, '语文', T.SCORE, 0)) 语文,
       MAX(DECODE(CLASS, '数学', T.SCORE, 0)) 数学,
       MAX(DECODE(CLASS, '英语', T.SCORE, 0)) 英语
FROM ROW2COLUMN2 T
GROUP BY NAME_;

三、右连接
a.pay_id=b.pay_id(+)   and   a.pay_id=c.pay_id(+)

四、根据年份统计

直接举例，忘大侠们指教：
表A(todate,itemid,fee),有这么三个字段（todate：数据产生日期;itemid:数据标识；fee:金额），其数据如下：
todate      itemid    fee
20061015   1         100
20061120   2         200
20070201   1         220
20070201   2         310
20070606   1         410
20070804   2         200
20080410   1         100
20080412   1         200
20090910   2         100
20090713   2         110
20100807   1         100
20100725   2         110
现如何通过oracle的语句，得到如下格式的查询结果：
itemid  2006年汇总 07年汇总 08年汇总 09年汇总  2010年汇总
 1    100         630       300       0          100
 2    200         510       0         210        110

即以itemid为查询条件，汇总出其在各年度的总额，并且分别将各年度的总额作为查询结果展现出来呢？
最佳答案
如果DATE为数字或者CHAR类型
SELECT itemid, SUM(CASE WHEN todate LIKE '2006%' THEN fee ELSE 0 END),
SUM(CASE WHEN todate LIKE '2007%' THEN fee ELSE 0 END),
SUM(CASE WHEN todate LIKE '2008%' THEN fee ELSE 0 END),
SUM(CASE WHEN todate LIKE '2009%' THEN fee ELSE 0 END),
SUM(CASE WHEN todate LIKE '2010%' THEN fee ELSE 0 END)
FROM A
GROUP BY itemid

如果todate是日期类型
SELECT itemid, SUM(CASE WHEN YEAR(todate) = 2006 THEN fee ELSE 0 END),
SUM(CASE WHEN YEAR(todate) = 2007 THEN fee ELSE 0 END),
SUM(CASE WHEN YEAR(todate) = 2008 THEN fee ELSE 0 END),
SUM(CASE WHEN YEAR(todate) = 2009 THEN fee ELSE 0 END),
SUM(CASE WHEN YEAR(todate) = 2010 THEN fee ELSE 0 END)
FROM A
GROUP BY itemid

五、统计杂谈

1. 使用普通sql语句求出各地区的汇总数据
SELECT areaid, SUM(mcqj_head) local_fare
  FROM mine
 GROUP BY areaid
UNION ALL
SELECT '合计' areaid, SUM(mcqj_head) local_fare FROM mine ;

SELECT d.fname dept_name, SUM(e.fsalary) count_sal
  FROM my_emp e, my_dept d
 WHERE e.fdepartmentid = d.fid
 GROUP BY d.fname
 ORDER BY count_sal;

2.使用分析函数rollup得出的汇总数据
SELECT nvl(areaid, '合计') area_code, SUM(mcqj_head) local_fare
  FROM mine
 GROUP BY ROLLUP(nvl(areaid, '合计'))
 ORDER BY area_code ;

SELECT d.fname dept_name, SUM(e.fsalary) count_sal
  FROM my_emp e, my_dept d
 WHERE e.fdepartmentid = d.fid
 GROUP BY ROLLUP(d.fname)
 ORDER BY count_sal;

3.为了介绍cube函数我们再来看看另外一个使用rollup的例子
SELECT substr(areaid, 1, 4) area_code, areaid, SUM(mcqj_head) local_fare
  FROM mine
 GROUP BY ROLLUP(substr(areaid, 1, 4), areaid)
 ORDER BY area_code

4.使用cube函数的结果
SELECT substr(areaid, 1, 4) area_code, areaid, SUM(mcqj_head) local_fare
  FROM mine
 GROUP BY CUBE(substr(areaid, 1, 4), areaid)
 ORDER BY area_code, areaid NULLS LAST

5.如果当前的汇总记录是利用该字段得出的,grouping函数就会返回1,否则返回0
SELECT decode(GROUPING(substr(areaid, 1, 4)), 1, 'all area', to_char(substr(areaid, 1, 4))) area_code,
       decode(GROUPING(areaid), 1, to_char(substr(areaid, 1, 4)), areaid) areaid,
       SUM(mcqj_head) local_fare
  FROM mine
 GROUP BY CUBE(substr(areaid, 1, 4), areaid)
 ORDER BY area_code, areaid NULLS LAST

6.使用rank函数来计算各个地区的话费排名,出现跳位
select areaid,sum(MCQJ_HEAD) local_fare,
    rank() over (order by sum(MCQJ_HEAD) ) fare_rank
 from mine
 group by areaid

SELECT d.fname dept_name, SUM(e.fsalary) count_sal, rank() over(ORDER BY SUM(e.fsalary)) f_rank
  FROM my_emp e, my_dept d
 WHERE e.fdepartmentid = d.fid
 GROUP BY ROLLUP(d.fname);

7.dense_rank查询的结果
select areaid,sum(MCQJ_HEAD) local_fare,
    dense_rank() over (order by sum(MCQJ_HEAD) ) fare_rank
 from mine
 group by areaid

SELECT d.fname dept_name, SUM(e.fsalary) count_sal, dense_rank() over(ORDER BY SUM(e.fsalary)) f_rank
  FROM my_emp e, my_dept d
 WHERE e.fdepartmentid = d.fid
 GROUP BY ROLLUP(d.fname);

8.row_number哪怕是两个数据完全相同,排名也会不一样,这个特性在我们想找出对应没个条件的唯一记录的时候又很大用处
select areaid,sum(MCQJ_HEAD) local_fare,
    row_number() over (order by sum(MCQJ_HEAD) ) fare_rank
 from mine
 group by areaid;

SELECT d.fname dept_name, SUM(e.fsalary) count_sal, row_number() over(ORDER BY SUM(e.fsalary)) f_rank
  FROM my_emp e, my_dept d
 WHERE e.fdepartmentid = d.fid
 GROUP BY ROLLUP(d.fname);

9. 取出数据库中最后入网的n个用户
select user_id,tele_num,user_name,user_status,create_date
from (
   select user_id,tele_num,user_name,user_status,create_date,
      rank() over (order by create_date desc) add_rank
   from user_info
)
where add_rank <= :n;

SELECT *
  FROM (SELECT d.fname dept_name, SUM(e.fsalary) count_sal, row_number() over(ORDER BY SUM(e.fsalary)) f_rank
          FROM my_emp e, my_dept d
         WHERE e.fdepartmentid = d.fid
         GROUP BY ROLLUP(d.fname)) tmp
 WHERE tmp.f_rank <= 3;

10.根据object_name删除数据库中的重复记录
create table t as select obj#,name from sys.obj$;
再insert into t1 select * from t1 数次.
delete from t where rowid in
(select row_id from (select rowid row_id,row_number() over (partition by obj# order by rowid) rn from t) where rn<>1
);

DELETE FROM test_tab
 WHERE ROWID IN (SELECT ROWID
                   FROM (SELECT ROWID row_id, row_number() over(PARTITION BY id ORDER BY ROWID) rn FROM test_tab)
                  WHERE rn <> 1);

11.取出各地区的话费收入在各个月份排名.
 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 t
    group by bill_month,area_code

12.取出每个月的上个月和下个月的话费总额,利用lag和lead函数,我们可以在同一行中显示前n行的数据,也可以显示后n行的数据
  1  select area_code,bill_month, local_fare cur_local_fare,
  2     lag(local_fare,2,0) over (partition by area_code order by bill_month ) pre_local_fare,
  3     lag(local_fare,1,0) over (partition by area_code order by bill_month ) last_local_fare,
  4     lead(local_fare,1,0) over (partition by area_code order by bill_month ) next_local_fare,
  5     lead(local_fare,2,0) over (partition by area_code order by bill_month ) post_local_fare
  6  from (
  7     select area_code,bill_month,sum(local_fare) local_fare
  8     from t
  9     group by area_code,bill_month
10* )
SQL> /
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
20 rows selected.

13.sum,avg,max,min移动计算数据介绍

计算出各个连续3个月的通话费用的平均数
  1  select area_code,bill_month, local_fare,
  2     sum(local_fare)
  3             over (  partition by area_code
  4                     order by to_number(bill_month)
  5                     range between 1 preceding and 1 following ) "3month_sum",
  6     avg(local_fare)
  7             over (  partition by area_code
  8                     order by to_number(bill_month)
  9                     range between 1 preceding and 1 following ) "3month_avg",
10     max(local_fare)
11             over (  partition by area_code
12                     order by to_number(bill_month)
13                     range between 1 preceding and 1 following ) "3month_max",
14     min(local_fare)
15             over (  partition by area_code
16                     order by to_number(bill_month)
17                     range between 1 preceding and 1 following ) "3month_min"
18  from (
19     select area_code,bill_month,sum(local_fare) local_fare
20     from t
21     group by area_code,bill_month
22* )
SQL> /

AREA_CODE BILL_MONTH       LOCAL_FARE 3month_sum 3month_avg 3month_max 3month_min
--------- ---------- ---------------- ---------- ---------- ---------- ----------
5761      200405            13060.433  26379.363 13189.6815   13318.93  13060.433
5761      200406            13318.930  40089.628 13363.2093  13710.265  13060.433
5761      200407            13710.265  41164.976 13721.6587  14135.781   13318.93
40089.628 = 13060.433 + 13318.930 + 13710.265
13363.2093 = (13060.433 + 13318.930 + 13710.265) / 3
13710.265 = max(13060.433 + 13318.930 + 13710.265)
13060.433 = min(13060.433 + 13318.930 + 13710.265)
5761      200408            14135.781  27846.046  13923.023  14135.781  13710.265
5762      200405            12643.791  25438.851 12719.4255   12795.06  12643.791
5762      200406            12795.060  38663.148  12887.716  13224.297  12643.791
5762      200407            13224.297  39395.825 13131.9417  13376.468   12795.06
5762      200408            13376.468  26600.765 13300.3825  13376.468  13224.297
5763      200405            13060.433  26379.363 13189.6815   13318.93  13060.433
5763      200406            13318.930  40089.628 13363.2093  13710.265  13060.433
5763      200407            13710.265  41164.976 13721.6587  14135.781   13318.93
5763      200408            14135.781  27846.046  13923.023  14135.781  13710.265
5764      200405            12487.791  25782.978  12891.489  13295.187  12487.791
5764      200406            13295.187  39227.071 13075.6903  13444.093  12487.791
5764      200407            13444.093  40668.974 13556.3247  13929.694  13295.187
5764      200408            13929.694  27373.787 13686.8935  13929.694  13444.093
5765      200405            25057.736  51116.196  25558.098   26058.46  25057.736
5765      200406            26058.460  77418.077 25806.0257  26301.881  25057.736
5765      200407            26301.881  79490.979  26496.993  27130.638   26058.46
5765      200408            27130.638  53432.519 26716.2595  27130.638  26301.881

20 rows selected.

13.ratio_to_report函数的介绍
  Quote:
  1  select bill_month,area_code,sum(local_fare) local_fare,
  2     ratio_to_report(sum(local_fare)) over
  3       ( partition by bill_month ) area_pct
  4  from t
  5* group by bill_month,area_code
SQL> break on bill_month skip 1
SQL> compute sum of local_fare on bill_month
SQL> compute sum of area_pct on bill_month
SQL> /

BILL_MONTH AREA_CODE       LOCAL_FARE   AREA_PCT
---------- --------- ---------------- ----------
200405     5761             13060.433 .171149279
           5762             12643.791 .165689431
           5763             13060.433 .171149279
           5764             12487.791 .163645143
           5765             25057.736 .328366866
**********           ---------------- ----------
sum                         76310.184          1

200406     5761             13318.930 .169050772
           5762             12795.060 .162401542
           5763             13318.930 .169050772
           5764             13295.187 .168749414
           5765             26058.460 .330747499
**********           ---------------- ----------
sum                         78786.567          1

200407     5761             13710.265 .170545197
           5762             13224.297 .164500127
           5763             13710.265 .170545197
           5764             13444.093 .167234221
           5765             26301.881 .327175257
**********           ---------------- ----------
sum                         80390.801          1

200408     5761             14135.781 .170911147
           5762             13376.468 .161730539
           5763             14135.781 .170911147
           5764             13929.694 .168419416
           5765             27130.638 .328027751
**********           ---------------- ----------
sum                         82708.362          1

20 rows selected.
 

14.first,last函数使用介绍

  Quote:
取出每月通话费最高和最低的两个用户.
1  select bill_month,area_code,sum(local_fare) local_fare,
  2     first_value(area_code)
  3             over (order by sum(local_fare) desc
  4                     rows unbounded preceding) firstval,
  5     first_value(area_code)
  6             over (order by sum(local_fare) asc
  7                     rows unbounded preceding) lastval
  8  from t
  9  group by bill_month,area_code
10* order by bill_month
SQL> /

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

20 rows selected.

取出每月通话费最高和最低的两个用户.
1 select bill_month,area_code,sum(local_fare) local_fare,
2 first_value(area_code)
3 over (order by sum(local_fare) desc
4 rows unbounded preceding) firstval,
5 first_value(area_code)
6 over (order by sum(local_fare) asc
7 rows unbounded preceding) lastval
8 from t
9 group by bill_month,area_code
10* order by bill_month

注意：如果为每月的话，上面的语句应修改为
1 select bill_month,area_code,sum(local_fare) local_fare,
2 first_value(area_code)
3 over (partition by bill_month order by sum(local_fare) desc
4 rows unbounded preceding) firstval,
5 first_value(area_code)
6 over (partition by bill_month order by sum(local_fare) asc
7 rows unbounded preceding) lastval
8 from t
9 group by bill_month,area_code
10* order by bill_month

1.row_number() 的使用
原表信息：
SQL> break on deptno skip 1  -- 为效果更明显，把不同部门的数据隔段显示。
SQL> select deptno,ename,sal
  2  from emp
  3  order by deptno,sal desc;
    DEPTNO ENAME             SAL
---------- ---------- ----------
        10 KING             5000
           CLARK            2450
           MILLER           1300
        20 SCOTT            3000
           FORD             3000
           JONES            2975
           ADAMS            1100
           SMITH             800
        30 BLAKE            2850
           ALLEN            1600
           TURNER           1500
           WARD             1250
           MARTIN           1250
           JAMES             950

已选择14行。

使用row_number()查出各部门薪水最高的三个员工姓名、薪水，多于三个的只取三个。
SQL> select * from (
  2  select deptno,row_number() over(partition by deptno order by sal desc) rw,ename,sal
  3  from emp
  4  )
  5  where rw<=3;
    DEPTNO         RW ENAME             SAL
---------- ---------- ---------- ----------
        10          1 KING             5000
                    2 CLARK            2450
                    3 MILLER           1300
        20          1 SCOTT            3000
                    2 FORD             3000
                    3 JONES            2975
        30          1 BLAKE            2850
                    2 ALLEN            1600
                    3 TURNER           1500
已选择9行。

SELECT *
  FROM (SELECT e.fname ename,
               e.fsalary,
               e.fjoindate,
               d.fname dname,
               row_number() over(PARTITION BY d.fid ORDER BY e.fsalary) d_rank
          FROM my_emp e, my_dept d
         WHERE e.fdepartmentid = d.fid) tmp
 WHERE tmp.d_rank <= 3;

体会：row_number() 返回的主要是“行”的信息，并没有按照sal排名，如
        20          1 SCOTT            3000
                    2 FORD             3000
scott与ford薪水一样多，ford前面的2仅仅是行数、记录条数的感念。

2.rank()、dense_rank() 的使用
原表信息：
SQL> break on deptno skip 1  -- 为效果更明显，把不同部门的数据隔段显示。
SQL> select deptno,ename,sal
  2  from emp
  3  order by deptno,sal desc;
    DEPTNO ENAME             SAL
---------- ---------- ----------
        10 KING             5000
           CLARK            2450
           MILLER           1300
        20 SCOTT            3000
           FORD             3000
           JONES            2975
           ADAMS            1100
           SMITH             800
        30 BLAKE            2850
           ALLEN            1600
           TURNER           1500
           WARD             1250
           MARTIN           1250
           JAMES             950

已选择14行。

使用rank()查出各部门薪水前三名的员工姓名、薪水。
SQL> select * from (
  2  select deptno,rank() over(partition by deptno order by sal desc) rk,ename,sal
  3  from emp
  4  )
  5  where rk<=3
  6  /
    DEPTNO         RK ENAME             SAL
---------- ---------- ---------- ----------
        10          1 KING             5000
                    2 CLARK            2450
                    3 MILLER           1300
        20          1 SCOTT            3000
                    1 FORD             3000
                    3 JONES            2975
        30          1 BLAKE            2850
                    2 ALLEN            1600
                    3 TURNER           1500

已选择9行。
使用dense_rank()查出各部门薪水前三名的员工姓名、薪水。
SQL> select * from (
  2  select deptno,dense_rank() over(partition by deptno order by sal desc) drk,ename,sal
  3  from emp
  4  )
  5  where drk<=3
  6  /
    DEPTNO        DRK ENAME             SAL
---------- ---------- ---------- ----------
        10          1 KING             5000
                    2 CLARK            2450
                    3 MILLER           1300
        20          1 SCOTT            3000
                    1 FORD             3000
                    2 JONES            2975
                    3 ADAMS            1100
        30          1 BLAKE            2850
                    2 ALLEN            1600
                    3 TURNER           1500

已选择10行。

 

第二次全国经济普查国家级数据处理工作正在进行,即将进入数据反馈和资料开发阶段。数据处理软件提供了大部分普查公报要求的汇总表式,其他课题研究需要的特定表式需要用户自行定制。为了满足用户方便地临时汇总特定数据的要求，下面介绍Oracle数据库的分析函数等高级用法供参考，适用于此次普查采用的Oracle数据库各个版本。本文一些实例来自一经普，稍作变更即可适用于二经普。
 
一、分析函数的概念
 
分析函数是Oracle从8.1.6开始引入的一个新的概念，为我们分析数据提供了一种简单高效的处理方式。在分析函数出现以前，我们必须使用自关联查询，子查询或者内联视图，甚至复杂的存储过程实现的语句，现在只要一条简单的SQL语句就可以实现了，而且在执行效率方面也有相当大的提高。下面我将针对分析函数做一些具体的说明。
 
分析函数的一般格式是：
 
函数名(参数列表) over ([partition by 字段名或表达式] [order by 字段名或表达式])，其中over()部分称为开窗函数，它是可以选填的。
 
开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化，举例如下：
 
over(order by salary) 按照salary排序进行累计，order by是个默认的开窗函数
 
over(partition by deptno)按照部门分区
 
分析函数最大特点是可以通过对基本表的一遍扫描，得出符合条件的单个记录组成的结果集，而传统的子查询或自关联都需要多次扫描基本表或临时表。这是它效率较好的原因所在，感兴趣的用户可以查看语句的执行计划自行分析。
 
我们在SQL语言的学习中了解了聚合函数，包括max,min,avg,sum,count等。分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是对于每个组返回多行，而聚合函数对于每个组只返回一行。
 
许多分析函数同时也是聚合函数，比如sum()函数，这样使用就是聚合函数。
 
SQL> select department_id,sum(salary) sum_salary from employees group by department_id;--分别汇总各部门的工资
 
而这样使用就是分析函数。
 
SQL> select distinct department_id,sum(salary) over(partition by department_id) sum_salary from employees ;
 
它们得出的结果是相同的，都是：
 
DEPARTMENT_ID SUM_SALARY
------------- ----------
           10       4400
           20      19000
...
          110      20300
                    7000
 
 
请注意，这里我们用到了distinct 关键字，如果不用distinct，第2个查询将返回多行重复数据，即employees表的每行记录都将返回一行sum_salary，因为不用distinct的含义是：针对每个雇员计算他/她所在的部门的工资总数。
 
在这个例子中，用聚合函数更好，但在另外一些情形下，我们更应该使用分析函数。 比如，在统计分析中，我们经常关心分组以后的排序，分组占总体的比重，某个分组的行列转换等，这都是分析函数擅长的。
 
二、典型实例
 
下面通过几个实例来介绍部分分析函数的用途。
 
问题1：求出每个省工业企业利润总额最多的前10名。
 
我们利用传统的聚合函数max可以方便地取出利润总额最多的一家，但是取出多家就无能为力了，同样，如果不分组我们可以通过排序取出任何一个省利润总额最多的前10名工业企业，但无法实现对多个省的分组。而采用rank聚合函数，可以方便地实现我们的要求。
 
完整的语句如下:
 
select * from
(select  substr(z01_04,1,2) 地区码,
DENSE_RANK() OVER (PARTITION BY substr(z01_04,1,2) order by b04_50 desc) 名次, b04_50 "利润总额"
from cj604,cj601 where b04_50>0 and cj601.uuid=cj604.uuid  ) where 名次<=10;
 
我们在开窗函数中使用地区码作为分组标志,并按照利润总额倒序排列。
 
结果如下(数据为模拟数据,以下同)
 
地区       名次   利润总额
---- ---------- ----------
31            1     ..3799
31            2     ..9643
...
31            9     ..5917
31           10     ..5245
32            1     ..9940
32            2     ..0587
...
 
注意:RANK()函数有3组,分别是rank, dense_rank, row_number,它们的区别是：
 
rank如果出现两个相同的数据,那么后面的数据就会直接跳过这个排名,比如：当第2名和第3名的利润相等时,rank的结果是1,2,2,4；而dense_rank则不会跳过这个排名,结果是1,2,2,3；而row_number哪怕是两个数据完全相同,排名也会不一样,结果是1,2,3,4
 
 
 
问题2:求出按登记注册类型分组的职工人数和销售额占总体的比重
 
分析函数ratio_to_report专门用来解决个体占总体的比重一类的问题。
 
语句
 
select d.*,round((ratio_to_report(职工人数) over())*100,1) as 人数百分比,
round((ratio_to_report(销售额) over())*100,1) as 销售额百分比
from
(select c.code 代码 , substr(b.reg_type,1,10) 登记注册类型, 职工人数, 销售额from
(select substr(z01_08,1,1)||'00' code, sum(z01_171_01) 职工人数,sum(b03_01)销售额
from cj603 c,cj601
j where c.uuid=j.uuid group by  substr(z01_08,1,1)
)c, djzclx b where c.code=b.reg_code
)d;
 
可以得出下面的结果：
 
代码 登记注册类型           职工人数     销售额 人数百分比 销售额百分比
---- -------------------- ---------- ---------- ---------- ------------
100  内资企业                ..10509 ..02627283         63      56.2
200  港、澳、台商投资企业    ..66175  .46728306       15.3        14
300  外商投资企业            ..36984 ..97046896       21.7      29.9
 
 
其中内层的子查询语句
 
select substr(z01_08,1,1)||'00' code, sum(z01_171_01) 职工人数,sum(b03_01)销售额
from cj603 c,cj601 j where c.uuid=j.uuid group by  substr(z01_08,1,1)
 
获得如下的结果
 
CODE   职工人数     销售额
---- ---------- ----------
100     ..10509 ..02627283
200     ..66175  .46728306
300     ..36984 ..97046896
 
外层查询中ratio_to_report函数自动对结果集中的职工人数和销售额计算比重。
 
问题3 求按行业中类划分的大中小型企业个数
 
case语句不是分析函数，但它在统计汇总中的作用非常重要，可以用来设定复杂的分组条件，不但可以用于要查询的列表，还可用于条件子句和分组标志。它的语法是：
 
CASE
WHEN表达式1 THEN返回值1
WHEN表达式2 THEN返回值2
...
ELSE 返回值x
END
 
或
 
CASE 表达式
WHEN 匹配值1 THEN 返回值1
WHEN 匹配值2 THEN 返回值2
...
ELSE返回值x
END
 
其中, 有多少不同条件或匹配值就写多少个WHEN...THEN子句，如果没有默认返回值，ELSE子句可以省略，当无匹配值时返回null。
 
以下是统计上工业大中小型企业划分标准。
 
指标名称 计量单位 大型 中型 小型
从业人员数 人 2000及以上且 300-2000以下 300以下或
销售收入 万元 30000及以上且 3000-30000以下 3000以下或
资产合计 万元 40000及以上 4000-40000以下 4000以下
 
说明：大型和中型企业须同时满足所列各项条件的下限指标，否则下划一档。
 
比如某企业虽然从业人员数和销售收入符合大型企业的要求，但资产合计30000万元，不满足大型企业的要求，只能划归中型企业。实际上，中型企业单位数=企业单位总数-大型企业单位数-小型企业单位数。
 
因此，表述中型标准的正确写法应该是：
 
not(b04_71>=2000 and b04_29>=300000 and b04_16>=400000) and not(b04_71<300 or b04_29<30000 or b04_16<40000)，当然前提是这3个字段没有空值null。
 
完整的SQL语句如下：
 
select code 代码 , substr(INDUSTRY_NAME,1,10) 行业名称, c.*  from
(
select substr(z01_064,1,2) as code ,count(*) as TOL,
count(case when b04_71>=2000 and b04_29>=300000 and b04_16>=400000 then 1 else null end) as big,
count(case when not(b04_71>=2000 and b04_29>=300000 and b04_16>=400000)
and not(b04_71<300 or b04_29<30000 or b04_16<40000) then 1 else null end) as mid,
count(case when b04_71<300 or b04_29<30000 or b04_16<40000 then 1 else null end) as small
from cj604 a,cj601 b where a.uuid=b.uuid group by substr(z01_064,1,2)
)c, industry b where c.code=b.INDUSTRY_CODE
 
输出结果如下：
 
代码 行业名称           CODE        TOL        BIG        MID      SMALL
---- ------------------ ---- ---------- ---------- ---------- ----------
06   煤炭开采和洗选业   06            9          1          2          6
07   石油和天然气开采业 07            3          1          0          2
08   黑色金属矿采选业   08           13          1          3          8
13   农副食品加工业     13         1342          2         48       1269
14   食品制造业         14          784          3         66        691
15   饮料制造业         15          385          0         31        331
...
 
 
问题4 求按地区划分的3种登记注册类型的营业利润率
 
decode函数是Oracle专有的，它不是分析函数，但它在统计汇总中的作用非常重要，它的格式是：
 
decode(字段名或表达式,比较值1,返回值1, [比较值2,返回值2,...] 默认返回值)，它的作用是当字段或表达式的值等于比较值1时，就得出返回值1，当字段或表达式的值等于比较值2时，就得出返回值2，以此类推，如果都不符合，就返回默认返回值。其中从比较值2开始的参数对可以不提供。第二种Case语句也能实现与decode函数相同的功能，而语句稍长，如果为了移植到符合标准SQL的其他数据库，可以用Case语句代替decode函数。Case语句的语法见问题3。
 
因为decode函数只针对一行内的数据进行处理，要使结果符合要求，我们需要在第二层查询语句的外面再加一层按地区代码的分组汇总，完整写法如下：
 
语句4-1（不用分析函数）
 
select substr(name,1,4) 地区名称, c.*
from(
select 地区代码,SUM(A1) A1,SUM(A2) A2,SUM(A3) A3 from(
select 地区代码, --利用decode函数将注册类型行列转置
decode(注册类型码,'1',营业利润率,null) A1,
decode(注册类型码,'2',营业利润率,null) A2,
decode(注册类型码,'3',营业利润率,null) A3
from(
select --查出按地区和注册类型复合分组的营业利润率
substr(z01_04,1,2) 地区代码,substr(z01_08,1,1) 注册类型码,
round(sum(b04_45)/sum(b04_29)*100,2) 营业利润率
from cj601 a,cj604 b where a.uuid=b.uuid
group by substr(z01_04,1,2),substr(z01_08,1,1)
)
)group by 地区代码 )c,dq
where 地区代码=dq.code;
 
这样就得到了正确的结果：
 
地区名称 地区     A1     A2     A3
------- ---- ------ ------ ------
上海     31     6.74   5.30   6.37
江苏     32     3.94   4.85   4.32
浙江     33     4.55   5.25   5.76
 
这个问题我们也可以通过lead分析函数来完成。
 
语句4-2（用分析函数lead）
 
select substr(name,1,4) 地区名称, 地区代码, A1,A2,A3
from(
select * from(
select 地区代码,
lead(营业利润率,    0) over(partition by 地区代码 order by 注册类型码) A1,
lead(营业利润率,    1) over(partition by 地区代码 order by 注册类型码) A2,
lead(营业利润率,    2) over(partition by 地区代码 order by 注册类型码) A3,
row_number( ) over(partition by 地区代码 order by 注册类型码) rn
from(
select
substr(z01_04,1,2) 地区代码,substr(z01_08,1,1) 注册类型码,
round(sum(b04_45)/sum(b04_29)*100,2) 营业利润率
from cj601 a,cj604 b where a.uuid=b.uuid
group by substr(z01_04,1,2),substr(z01_08,1,1)
))where rn=1
)c,dq
where 地区代码=dq.code;
 
lead函数的第一个参数是我们关心的值，第2个参数是偏移量n，对本例就是下n种注册类型码。
 
之所以要限定rn=1，还是因为分析函数对每一行都返回分组值，而我们关心的是注册类型为1的那一行。
 
利用lag和lead函数，我们可以在同一行中显示前n行的数据，也可以显示后n行的数据。
 
如果本例改用lag函数实现，代码如下：
 
注意过滤条件rn=3以及lag函数第2个参数的变化，我们把第3行作为当前行，取出它前面的2行。
 
语句4-3（用分析函数lag）
 
select substr(name,1,4) 地区名称, 地区代码, A1,A2,A3
from(
select * from(
select 地区代码,
lag(营业利润率,    2) over(partition by 地区代码 order by 注册类型码) A1,
lag(营业利润率,    1) over(partition by 地区代码 order by 注册类型码) A2,
lag(营业利润率,    0) over(partition by 地区代码 order by 注册类型码) A3,
row_number( ) over(partition by 地区代码 order by 注册类型码) rn
from(
select
substr(z01_04,1,2) 地区代码,substr(z01_08,1,1) 注册类型码,
round(sum(b04_45)/sum(b04_29)*100,2) 营业利润率
from cj601 a,cj604 b where a.uuid=b.uuid
group by substr(z01_04,1,2),substr(z01_08,1,1)
))where rn=3
)c,dq
where 地区代码=dq.code;
 
这种方法比前一种方法利用sum分组汇总的好处是对字符类型和其他非数值类型字段都可以采用。
 
问题5 求按登记注册类型多个层次划分的单位个数小计和总计
 
例如要得出如下的结果：
 
代码   登记注册类型                            家数        
------ --------------------------------------- ---------
100    内资企业                                    ..920
110      国有企业                                  ..365
140      联营企业                                  ..476
141        国有联营企业                               52
...
200    港、澳、台商投资企业                        ..004
210      合资经营企业(港或澳、台资)                ..454
220      合作经营企业(港或澳、台资)                  556
300    外商投资企业                                ..396
310      中外合资经营企业                          ..070
320      中外合作经营企业                            663
 
我们有3种方法，都可以完成任务。
 
方法1
 
select code 代码 , substrb('    ',1,item_level*2-2)||b.reg_type 登记注册类型, cnt 家数 from
(
(select substr(z01_08,1,1)||'00' code ,count(*) cnt from cj601 group by substr(z01_08,1,1))
union
(select substr(z01_08,1,2)||'0' code ,count(*) cnt from cj601 group by substr(z01_08,1,2))
union
(select substr(z01_08,1,3) code ,count(*) cnt from cj601 group by substr(z01_08,1,3))
)
c, djzclx b where c.code=b.reg_code order by 1;
 
方法2
 
select code 代码 , substrb('    ',1,item_level*2-2)||b.reg_type 登记注册类型, cnt 家数 from
(
select
case when code3 is not null then code3
     when code2<>'0' then code2
else code1
end code,cnt from (
select substr(z01_08,1,1)||'00' code1 , substr(z01_08,1,2)||'0' code2 , substr(z01_08,1,3) code3 ,count(*) cnt
    from cj601
    group by rollup(substr(z01_08,1,1),substr(z01_08,1,2),substr(z01_08,1,3))
) where code2<>code3 or code3 is null and code1<>'00'
)
c, djzclx b where c.code=b.reg_code order by 1;
 
方法3
 
select code 代码 , substrb('    ',1,item_level*2-2)||b.reg_type 登记注册类型, cnt 家数 from
(
select
case when code3 is not null then code3
     when code2<>'0' then code2
else code1
end code,cnt from (
select substr(z01_08,1,1)||'00' code1 , substr(z01_08,1,2)||'0' code2 , substr(z01_08,1,3) code3 ,sum(cnt) cnt
    from (select substr(z01_08,1,3) z01_08,count(*) cnt from cj601 group by substr(z01_08,1,3))
    group by rollup(substr(z01_08,1,1),substr(z01_08,1,2),substr(z01_08,1,3))
) where code2<>code3 or code3 is null and code1<>'00'
)
c, djzclx b where c.code=b.reg_code order by 1;
 
上述3种写法都能得出正确的结果，但执行效率有巨大差别，第一种写法最简单，但是使用union要对cj601表作了3遍全表扫描，还要进行排序合并，执行效率最低，第2种写法对cj601表做rollup（卷积）分组，自动求小计和总计，第3种写法先对cj601表做分组汇总，再对结果集做rollup分组，自动求小计和总计，在数据量中等的时候方法2与方法3效率差不多，数据量大的时候，方法3效率更好些，因为方法3的rollup分组要处理的记录数比方法2更少，而rollup分组比普通分组开销大一些。
 
三、总结
 
Oracle提供的分析函数一共有10多个，但有些专门的统计函数比如求标准差，相关系数，协方差等我们一般用不到，主要用到的是本文提到的RANK, lead,lag, ratio_to_report等，我们如果能够将它们和decode函数或case语句配合，善加利用，就能编写出执行效率高的汇总语句，高效完成统计数据处理任务。更加详细的关于分析函数的信息，请参考Oracle9i Data Warehousing Guide 第19章SQL for Analysis in Data Warehouses。同时，也应认识到，分析函数要经常练习才能熟练掌握，而且它也有适用的范围，有时在任务比较紧急，没有足够时间比较分析函数和传统办法的优劣的情况下，来不及查资料，或者你用的数据库不支持分析函数，这时就要用传统的办法解决了。但是，无论用什么工具语言，我们都要尽量多思考，写出效率较好的语句，以满足我们统计分析的要求。
 

 

oracle分组排序查询函数：
1 row_number()

  select daopin_dat.*,row_number() over(partition by id order by ecio desc) r from daopin_dat
  以上语句意思为：在表中以ID分组，ecio排序的 每条记录的序号（r），序号递增
  ID    MAXECIO    ECIO    NUM    R

  447634    -9.5    -9.5        1
  447634    -9.5    -14        2
  447636    -9.5    -9.5        1
  447636    -9.5    -14        2
  447637    -8.5    -8.5        1
  447637    -8.5    -16        2
  447637    -8.5    -21        3
  447638    -8.5    -8.5        1
  447638    -8.5    -16        2
  447638    -8.5    -21        3
  447640    -11    -11        1
  447640    -11    -11        2
  447641    -11    -11        1
  447641    -11    -11        2
  447647    -14    -14        1
  447647    -14    -14        2
  447647    -14    -15.5        3

  *先按no分组，再以每5条记录取vv平均值，不足五条按也做统计
  select no,avg(vv) as vv from(
    select no, row_number() over(partition by no order by 1) as num,vv from test  order by no,num)
        group by no,trunc((num-1)/5)

  *按no分组后排序，给出顺序号
  select no, row_number() over(partition by no order by 1) as num,vv from test  order by no,num

 

2 rank()
  select daopin_dat.*,rank() over(partition by id order by ecio desc) r from daopin_dat
  以上语句意思为：在表中以ID分组，ecio排序， 每条记录的等级（r），等级从小到大排列，相同值重复，并跳过一个等级数

  ID    MAXECIO    ECIO    NUM    R

  447634    -9.5    -9.5        1
  447634    -9.5    -14        2
  447636    -9.5    -9.5        1
  447636    -9.5    -14        2
  447637    -8.5    -8.5        1
  447637    -8.5    -16        2
  447637    -8.5    -21        3
  447638    -8.5    -8.5        1
  447638    -8.5    -16        2
  447638    -8.5    -21        3
  447640    -11    -11        1
  447640    -11    -11        1
  447641    -11    -11        1
  447641    -11    -11        1
  447647    -14    -14        1
  447647    -14    -14        1
  447647    -14    -15.5        3

  合计功能：计算出数值(4,1)在Orade By Col1,Col2排序下的排序值，也就是col1=4,col2=1在排序以后的位置
　　
　SELECT RANK(4,3) WITHIN GROUP (ORDER BY col1,col2) "Rank" FROM table;
　　

3 dense_rank
  select daopin_dat.*,dense_rank() over(partition by id order by ecio desc) r from daopin_dat
  以上语句意思为：在表中以ID分组，ecio排序， 每条记录的等级（r），等级从小到大排列，相同值重复，等级数递增

  ID    MAXECIO    ECIO    NUM    R

  447634    -9.5    -9.5        1
  447634    -9.5    -14        2
  447636    -9.5    -9.5        1
  447636    -9.5    -14        2
  447637    -8.5    -8.5        1
  447637    -8.5    -16        2
  447637    -8.5    -21        3
  447638    -8.5    -8.5        1
  447638    -8.5    -16        2
  447638    -8.5    -21        3
  447640    -11    -11        1
  447640    -11    -11        1
  447641    -11    -11        1
  447641    -11    -11        1
  447647    -14    -14        1
  447647    -14    -14        1
  447647    -14    -15.5        2

dense_rank与rank()用法相当，但是有一个区别：dence_rank在并列关系是，相关等级不会跳过。rank则跳过。

oracle集合函数
UNION:并集，合并两个操作的结果，去掉重复的部分
UNION ALL:并集，合并两个操作的结果，保留重复的部分
MINUS:差集，从前面的操作结果中去掉与后面操作结果相同的部分
INTERSECT:交集，取两个操作结果中相同的部分

   SELECT  job FROM emp WHERE deptno=10 
    UNION 
     SELECT  job FROM emp WHERE deptno=20; 

一：分析函数over
Oracle从8.1.6开始提供分析函数，分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是
对于每个组返回多行，而聚合函数对于每个组只返回一行。
下面通过几个例子来说明其应用。                                      
1：统计某商店的营业额。       
     date       sale
     1           20
     2           15
     3           14
     4           18
     5           30
    规则：按天统计：每天都统计前面几天的总额
    得到的结果：
    DATE   SALE       SUM
    ----- -------- ------
    1      20        20           --1天          
    2      15        35           --1天＋2天          
    3      14        49           --1天＋2天＋3天          
    4      18        67            .         
    5      30        97            .
    
2:统计各班成绩第一名的同学信息
    NAME   CLASS S                        
    ----- ----- ----------------------
    fda    1      80                    
    ffd    1      78                    
    dss    1      95                    
    cfe    2      74                    
    gds    2      92                    
    gf     3      99                    
    ddd    3      99                    
    adf    3      45                    
    asdf   3      55                    
    3dd    3      78             
  
    通过：  
    --
    select * from                                                                      
    (                                                                           
    select name,class,s,rank()over(partition by class order by s desc) mm from t2
    )                                                                           
    where mm=1
    --
    得到结果：
    NAME   CLASS S                       MM                                                                                       
    ----- ----- ---------------------- ----------------------
    dss    1      95                      1                     
    gds    2      92                      1                     
    gf     3      99                      1                     
    ddd    3      99                      1         
  
    注意：
    1.在求第一名成绩的时候，不能用row_number()，因为如果同班有两个并列第一，row_number()只返回一个结果         
    2.rank()和dense_rank()的区别是：
      --rank()是跳跃排序，有两个第二名时接下来就是第四名
      --dense_rank()l是连续排序，有两个第二名时仍然跟着第三名
    
    
3.分类统计 (并显示信息)
    A   B   C                     
    -- -- ----------------------
    m   a   2                     
    n   a   3                     
    m   a   2                     
    n   b   2                     
    n   b   1                     
    x   b   3                     
    x   b   2                     
    x   b   4                     
    h   b   3
   select a,c,sum(c)over(partition by a) from t2               
   得到结果：
   A   B   C        SUM(C)OVER(PARTITIONBYA)     
   -- -- ------- ------------------------
   h   b   3        3                       
   m   a   2        4                       
   m   a   2        4                       
   n   a   3        6                       
   n   b   2        6                       
   n   b   1        6                       
   x   b   3        9                       
   x   b   2        9                       
   x   b   4        9                       

   如果用sum，group by 则只能得到
   A   SUM(C)                           
   -- ----------------------
   h   3                     
   m   4                     
   n   6                     
   x   9                     
   无法得到B列值      

＝＝＝＝＝
select * from test
数据:
A B C
1 1 1
1 2 2
1 3 3
2 2 5
3 4 6

---将B栏位值相同的对应的C 栏位值加总
select a,b,c, SUM(C) OVER (PARTITION BY B) C_Sum
from test
A B C C_SUM
1 1 1 1
1 2 2 7
2 2 5 7
1 3 3 3
3 4 6 6
---如果不需要已某个栏位的值分割,那就要用 null
eg: 就是将C的栏位值summary 放在每行后面
select a,b,c, SUM(C) OVER (PARTITION BY null) C_Sum
from test
A B C C_SUM
1 1 1 17
1 2 2 17
1 3 3 17
2 2 5 17
3 4 6 17
求个人工资占部门工资的百分比
SQL> select * from salary;
NAME DEPT SAL
---------- ---- -----
a 10 2000
b 10 3000
c 10 5000
d 20 4000
SQL> select name,dept,sal,sal*100/sum(sal) over(partition by dept) percent from salary;
NAME DEPT SAL PERCENT
---------- ---- ----- ----------
a 10 2000 20
b 10 3000 30
c 10 5000 50
d 20 4000 100
二：开窗函数          
      开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化，举例如下：
1：    
   over（order by salary） 按照salary排序进行累计，order by是个默认的开窗函数
   over（partition by deptno）按照部门分区
2：
over（order by salary range between 5 preceding and 5 following）
   每行对应的数据窗口是之前行幅度值不超过5，之后行幅度值不超过5
   例如:对于以下列
     aa
     1
     2
     2
     2
     3
     4
     5
     6
     7
     9
  
   sum(aa)over（order by aa range between 2 preceding and 2 following）
   得出的结果是
            AA                       SUM
            ---------------------- -------------------------------------------------------
            1                       10                                                     
            2                       14                                                     
            2                       14                                                     
            2                       14                                                     
            3                       18                                                     
            4                       18                                                     
            5                       22                                                     
            6                       18                                                               
            7                       22                                                               
            9                       9                                                                
            
   就是说，对于aa=5的一行 ，sum为   5-1<=aa<=5+2 的和
   对于aa=2来说 ，sum=1+2+2+2+3+4=14     ；
   又如 对于aa=9 ，9-1<=aa<=9+2 只有9一个数，所以sum=9    ；
             
3：其它：
     over（order by salary rows between 2 preceding and 4 following）
          每行对应的数据窗口是之前2行，之后4行
4：下面三条语句等效：          
     over（order by salary rows between unbounded preceding and unbounded following）
          每行对应的数据窗口是从第一行到最后一行，等效：
     over（order by salary range between unbounded preceding and unbounded following）
           等效
     over(partition by null)
sum(nid) over(partition by v1 order by nid)(2007-05-16 16:22:48)
  分类：sql语句
 
SQL> select n1,v1,nid,sum(nid) over(order by nid) as sum
  2  from t1;
        N1 V1                NID        SUM
---------- ---------- ---------- ----------
         1 aa                 61         61
         2 aa                 62        123
         3 aa                 63        186
         4 aa                 64        250
取nid列的累积和，即下面以emp表为例的按部门“连续”求总和
 ==================================================================
按v1分组取nid的和
SQL> select v1,sum(nid) over (partition by v1 order by v1) as sum_nid from t1;
V1            SUM_NID
---------- ----------
aa                187
aa                187
aa                187
bb                 83
按v1分组取nid的和，并重复行只显示一行
SQL> select distinct * from (select v1,sum(nid) over (partition by v1) as sum_nid from t1);
V1            SUM_NID
---------- ----------
aa                187
bb                 83
==================================================================
再以emp为例
使用子分区查出各部门薪水连续的总和。注意按部门分区 over(...)条件的不同
sum(sal) over (partition by deptno order by ename) 按部门“连续”求总和
sum(sal) over (partition by deptno) 按部门求总和
sum(sal) over (order by deptno，ename) 不按部门“连续”求总和
sum(sal) over () 不按部门，求所有员工总和，效果等同于sum(sal)。
sql> break on deptno skip 1  -- 为效果更明显，把不同部门的数据隔段显示。
SQL> select deptno,ename,sal,
  2      sum(sal) over (partition by deptno order by ename) 部门连续求和,
  3      sum(sal) over (partition by deptno) 部门总和,
  4      100*round(sal/sum(sal) over (partition by deptno),4) 部门份额,
  5      sum(sal) over () 总和,
  6      sum(sal) over (order by deptno,ename) 连续求和,
  7      100*round(sal/sum(sal) over (),4) 总份额
  8  from emp;
    DEPTNO ENAME             SAL 部门连续求和   部门总和   部门份额       总和   连续求和     总份额
---------- ---------- ---------- ------------ ---------- ---------- ---------- ---------- ----------
        10 CLARK            2450         2450       8750         28      29025       2450       8.44
           KING             5000         7450       8750      57.14      29025       7450      17.23
           MILLER           1300         8750       8750      14.86      29025       8750       4.48

        20 ADAMS            1100         1100      10875      10.11      29025       9850       3.79
           FORD             3000         4100      10875      27.59      29025      12850      10.34
           JONES            2975         7075      10875      27.36      29025      15825      10.25
           SCOTT            3000        10075      10875      27.59      29025      18825      10.34
           SMITH             800        10875      10875       7.36      29025      19625       2.76

        30 ALLEN            1600         1600       9400      17.02      29025      21225       5.51
           BLAKE            2850         4450       9400      30.32      29025      24075       9.82
           JAMES             950         5400       9400      10.11      29025      25025       3.27
           MARTIN           1250         6650       9400       13.3      29025      26275       4.31
           TURNER           1500         8150       9400      15.96      29025      27775       5.17
           WARD             1250         9400       9400       13.3      29025      29025       4.31
已选择14行。
综合的例子，求和规则有按部门分区的，有不分区的例子
SQL> select deptno,ename,sum(sal) over(partition by deptno order by sal) as sum_sal,
  2         sum(sal) over(order by deptno,sal) as sum_dept_sal
  3  from emp;
    DEPTNO ENAME         SUM_SAL SUM_DEPT_SAL
---------- ---------- ---------- ------------
        10 MILLER           1300         1300
           CLARK            3750         3750
           KING             8750         8750
        20 SMITH             800         9550
           ADAMS            1900        10650
           JONES            4875        13625
           SCOTT           10875        19625
           FORD            10875        19625
        30 JAMES             950        20575
           WARD             3450        23075
           MARTIN           3450        23075
           TURNER           4950        24575
           ALLEN            6550        26175
           BLAKE            9400        29025

已选择14行。
来一个逆序的，即部门从大到小排列，部门里各员工的薪水从高到低排列，累计和的规则不变。
SQL> select deptno,ename,sal,
  2         sum(sal) over (partition by deptno order by deptno desc,sal desc) as sum_sal_order,
  3         sum(sal) over (order by deptno desc,sal desc) as sum
  4  from emp;
    DEPTNO ENAME             SAL SUM_SAL_ORDER        SUM
---------- ---------- ---------- ------------- ----------
        30 BLAKE            2850          2850       2850
           ALLEN            1600          4450       4450
           TURNER           1500          5950       5950
           WARD             1250          8450       8450
           MARTIN           1250          8450       8450
           JAMES             950          9400       9400
        20 SCOTT            3000          6000      15400
           FORD             3000          6000      15400
           JONES            2975          8975      18375
           ADAMS            1100         10075      19475
           SMITH             800         10875      20275
        10 KING             5000          5000      25275
           CLARK            2450          7450      27725
           MILLER           1300          8750      29025

已选择14行。