oracle++统计函数,oracle统计函数

Windowing-ClauseThe windowing clause gives us a way to define a sliding or anchored window of data, on which the analytic function will operate, within a group. This clause can be used to have the analytic function compute its value based on any arbitrary sliding or anchored window within a group.

;例如:

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)例子:

CREATE TABLE vote_count (

submit_date  DATE NOT NULL,

num_votes    NUMBER NOT NULL);

INSERT INTO vote_count VALUES (TRUNC(SYSDATE)-4, 100);

INSERT INTO vote_count VALUES (TRUNC(SYSDATE)-3, 150);

INSERT INTO vote_count VALUES (TRUNC(SYSDATE)-2, 75);

INSERT INTO vote_count VALUES (TRUNC(SYSDATE)-3, 25);

INSERT INTO vote_count VALUES (TRUNC(SYSDATE)-1, 50);

COMMIT;

SELECT * FROM vote_count;

1 2007-6-28 100

2 2007-6-29 150

3 2007-6-29 25

4 2007-6-30 75

5 2007-7-1 50统计每天提交数量在所有中的百分比:select submit_date,num_votes/sum(num_votes)over() from vote_count 统计每次提交的数据占本天的百分比:select submit_date,num_votes/sum(num_votes)over(partition by submit_date) from vote_count 统计本次提交以及之前所有提交的总量:select submit_date,sum(num_votes)over(order by submit_date) from vote_count 或select submit_date,sum(num_votes)over(order by submit_date rows between unbounded preceding and 0 following) from vote_count