oracle统计函数

原文出处：http://blog.csdn.net/shangqiao/archive/2007/07/02/1676169.aspx

统计函数的语法:
Analytic-Function(,,...)
OVER (



)

Analytic-FunctionSpecify the name of an analytic function, Oracle actually provides many analytic functions such as AVG, CORR, COVAR_POP, COVAR_SAMP, COUNT, CUME_DIST, DENSE_RANK, FIRST, FIRST_VALUE, LAG, LAST, LAST_VALUE, LEAD, MAX, MIN, NTILE, PERCENT_RANK, PERCENTILE_CONT, PERCENTILE_DISC, RANK, RATIO_TO_REPORT, STDDEV, STDDEV_POP, STDDEV_SAMP, SUM, VAR_POP, VAR_SAMP, VARIANCE.

ArgumentsAnalytic functions take 0 to 3 arguments.Query-Partition-ClauseThe PARTITION BY clause logically breaks a single result set into N groups, according to the criteria set by the partition expressions. The words "partition" and "group" are used synonymously here. The analytic functions are applied to each group independently, they are reset for each group.

Order-By-ClauseThe ORDER BY clause specifies how the data is sorted within each group (partition). This will definitely affect the outcome of any analytic function.

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

[@more@]

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

转载于:http://blog.itpub.net/7282477/viewspace-1005263/