分析函数

select empnum, dept, salary,
rank()       over (partition by dept order by salary desc nulls last) as rank,
dense_rank() over (partition by dept order by salary desc nulls last) as denserank,
row_number() over (partition by dept order by salary desc nulls last) as rownumber
from emptab;
EMPNUM DEPT SALARY RANK DENSERANK ROWNUMBER
------ ---- ------ ---- --------- ---------
6 1 78000 1 1 1
2 1 75000 2 2 2
7 1 75000 2 2 3
11 1 53000 4 3 4
5 1 52000 5 4 5
1 1 50000 6 5 6
--------------------------------------------------
9 2 51000 1 1 1
4 2 - 2 2 2

 

select empnum, salary,
 digits(salary) as digits
 from emptab
 where dept = 1;
EMPNUM SALARY DIGITS
----------- ----------- ----------
 1 50000 0000050000
 2 75000 0000075000
 5 52000 0000052000

select dept, salary,
 sum(salary) over (partition by dept) as deptsum,
 avg(salary) over (partition by dept) as avgsal,
 count(*) over (partition by dept) as deptcount,
 max(salary) over (partition by dept) as maxsal
 from emptab;
DEPT SALARY DEPTSUM AVGSAL DEPTCOUNT MAXSAL
----- ------- -------- ------- --------- --------
 1 50000 383000 63833 6 78000
 1 75000 383000 63833 6 78000
 1 52000 383000 63833 6 78000
 1 78000 383000 63833 6 78000
 1 75000 383000 63833 6 78000
 1 53000 383000 63833 6 78000
 2 - 51000 51000 2 51000
 2 51000 51000 51000 2 51000
 3 79000 209000 69666 3 79000
 3 55000 209000 69666 3 79000
 3 75000 209000 69666 3 79000
 - - 84000 84000 2 84000
 - 84000 84000 84000 2 84000

 

select empnum, dept, salary,
 sum(salary) over (partition by dept) as deptsum,
 decimal(salary,10,2) /
 sum(salary) over(partition by dept)as percentage
 from emptab;
EMPNUM DEPT SALARY DEPTSUM PERCENTAGE
------ ----- -------- ----------- ----------
 1 1 50000 383000 0.1305
 2 1 75000 383000 0.1958
 5 1 52000 383000 0.1357
 6 1 78000 383000 0.2036
 7 1 75000 383000 0.1958
 11 1 53000 383000 0.1383
 4 2 - 51000
 9 2 51000 51000 1.0000
 8 3 79000 209000 0.3779
 10 3 55000 209000 0.2631
 12 3 75000 209000 0.3588
 0 - - 84000
 3 - 84000 84000 1.0000

select date, sales,
 sum(sales) over (order by date) as cume_sum,
 count(*) over (order by date) as setcount
 from sales
 where year(date) = 2000;
DATE SALES CUME_SUM SETCOUNT
---------- ------------ ------------ ---------
01/01/2000 968871.12 968871.12 1
02/01/2000 80050.05 1048921.17 2
03/01/2000 757866.14 1806787.31 3
04/01/2000 58748.13 1865535.44 4
05/01/2000 40711.69 1906247.13 5
06/01/2000 241187.78 2147434.91 6
07/01/2000 954924.16 3102359.07 7
08/01/2000 502822.96 3605182.03 8
09/01/2000 97201.45 3702383.48 9
10/01/2000 853999.45 4556382.93 10
11/01/2000 358775.59 4915158.52 11
12/01/2000 437513.35 5352671.87 12

 

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

转载于:http://blog.itpub.net/783385/viewspace-691598/