分析函数简单用法举例

SUM()OVER()

SQL> select name,id,sum(id) over(partition by name order by id) from a;
NAME         ID SUM(ID)OVER(PARTITIONBYNAMEORD
---- ---------- ------------------------------
A             1                              1
A             2                              3
B             3                              6
B             3                              6
C             5                              5

SQL> select name,id,sum(id) over(order by id) from a;
NAME         ID SUM(ID)OVER(ORDERBYID)
---- ---------- ----------------------
A             1                      1
A             2                      3
B             3                      9
B             3                      9
C             5                     14

SQL> select name,id,sum(id) over(partition by name) from a;
NAME         ID SUM(ID)OVER(PARTITIONBYNAME)
---- ---------- ----------------------------
A             1                            3
A             2                            3
B             3                            6
B             3                            6
C             5                            5

SQL> select name,id,sum(id) over() from a;
NAME         ID SUM(ID)OVER()
---- ---------- -------------
A             1            14
A             2            14
B             3            14
B             3            14
C             5            14


partition by name: 按name分区, 不指定则不分区
order by id : 按id 排序、分组聚合再与上一组聚合值相加 , 不指定时按partition by指定的列 排序、 分组聚合再与上一组聚合值相加。

lag()over()

with a as 
(select 'A' name,1 value FROM DUAL UNION ALL
 select 'A',2 FROM DUAL UNION ALL
 select 'B',1 FROM DUAL UNION ALL
 select 'B',3 FROM DUAL UNION ALL
 select 'B',5 FROM DUAL UNION ALL
 select 'C',0 FROM DUAL) 
 select name,value, lag(value)over (partition by name order by name,value) value_lag from a;
-------------------------------------------------------------------------------------------------------------------------------
name  value   value_lag
A          1
A          2          1
B          1
B          3          1
B          5          3
C          0

lead()over()

with a as 
(select 'A' name,1 value FROM DUAL UNION ALL
 select 'A',2 FROM DUAL UNION ALL
 select 'B',1 FROM DUAL UNION ALL
 select 'B',3 FROM DUAL UNION ALL
 select 'B',5 FROM DUAL UNION ALL
 select 'C',0 FROM DUAL) 
 select name,value, lead(value)over (partition by name order by name,value) from a;
----------------------------------------------------------------------------------------------------------------------

name  value   value_lag
A           1           2
A           2           
B           1           3 
B           3           5
B           5          
C           0 


rank()over()

with a as 
(select 'A' name,100 value FROM DUAL UNION ALL
 select 'A',90 FROM DUAL UNION ALL
 select 'A',90 FROM DUAL UNION ALL
 select 'A',80 FROM DUAL UNION ALL
 select 'A',70 FROM DUAL UNION ALL
 select 'A',50 FROM DUAL) 
 select name,value,rank()over(order by value desc) from a;
---------------------------------------------------------------------------------------
NAME VALUE RANK()OVER(ORDERBYVALUEDESC)
A          100                 1
A          90                   2
A          90                   2
A          80                   4
A          70                   5
A          50                   6

dense()rank()

with a as 
(select 'A' name,100 value FROM DUAL UNION ALL
 select 'A',90 FROM DUAL UNION ALL
 select 'A',90 FROM DUAL UNION ALL
 select 'A',80 FROM DUAL UNION ALL
 select 'A',70 FROM DUAL UNION ALL
 select 'A',50 FROM DUAL) 
 select name,value,dense_rank()over(order by value desc) from a;
------------------------------------------------------------------------------------------------
NAME VALUE DENSE_RANK()OVER(ORDERBYVALUEDESC)
A           100                 1
A           90                   2
A           90                   2
A           80                   3
A           70                      4
A           50                       5

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/21998432/viewspace-1224738/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/21998432/viewspace-1224738/