oracle分析函数求总,ORACLE分析函数的常用查询总结

ORACLE分析函数的常用查询总结

上一篇 / 下一篇 2012-06-29 15:26:37 / 个人分类：analytic_function

查看( 172 ) / 评论( 2 ) / 评分( 0 / 0 )

在OLAP系统中，由于数据量比较大，该类系统对查询功能的要求比较高！

ORACLE的分析函数正好能解决这类问题。

分析函数主要用over()来标识，在11g的官方文档上，可以看到下述函数都能用在分析函数中：

AVG *

CORR *

COUNT *

COVAR_POP *

COVAR_SAMP *

CUME_DIST

DENSE_RANK

FIRST

FIRST_VALUE *

LAG

LAST

LAST_VALUE *

LEAD

LISTAGG

MAX *

MIN *

NTH_VALUE*

NTILE

PERCENT_RANK

PERCENTILE_CONT

PERCENTILE_DISC

RANK

RATIO_TO_REPORT

REGR_ (Linear Regression) Functions *

ROW_NUMBER

STDDEV *

STDDEV_POP *

STDDEV_SAMP *

SUM *

VAR_POP *

VAR_SAMP *

VARIANCE *

下面通过实际例子来说明分析函数的应用！

1、实现累加的效果:sum(sum())

SQL> select * from t;

YEARS MONTHS PRODUCT_NA SALES

---------- ---------- ---------- ----------

2008 1 A 1000

2008 1 B 1500

2008 2 A 2000

2008 2 B 3000

2008 2 C 1000

2008 3 A 3000

已选择6行。

SQL> select years,months,sum(sales) sum1,

2 sum(sum(sales)) over(partition by years order by months) sum2 from t

3 group by years,months

4 /

YEARS MONTHS SUM1 SUM2

---------- ---------- ---------- ----------

2008 1 2500 2500

2008 2 6000 8500

2008 3 3000 11500

2、为记录排名:row_number()、rank()、dense_rank()

--在以下的查询结果中，如果用传统的rownum，那么显然取前五名时便会漏了第六名

SQL> select rownum,tt.* from

2 (select * from t order by sales desc)tt

3 /

ROWNUM YEARS MONTHS PRODUCT_NA SALES

---------- ---------- ---------- ---------- ----------

1 2008 2 B 3000

2 2008 3 A 3000

3 2008 2 A 2000

4 2008 1 B 1500

5 2008 1 A 1000

6 2008 2 C 1000

已选择6行。

--使用row_number()

SQL> select t.years,t.months,t.product_name,t.sales,

2 row_number() over(order by t.sales desc) row_numer,

3 rank() over(order by t.sales desc) rank,

4 dense_rank() over(order by t.sales desc) dense_rank

5 from t

6 group by t.years,t.months,t.product_name,t.sales

7 /

YEARS MONTHS PRODUCT_NA SALES ROW_NUMER RANK DENSE_RANK

---------- ---------- ---------- ---------- ---------- ---------- ----------

2008 2 B 3000 1 1 1

2008