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
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;
----------------------------------------------------------------------------------------------------------------------
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/