分享
 
 
 

Oracle笔记-分析函数

王朝oracle·作者佚名  2008-05-19
窄屏简体版  字體: |||超大  

第 12 章 分析函数

12.1 分析函数如何工作

语法

FUNCTION_NAME(<参数>,…)

OVER

(<PARTITION BY 表达式,…> <ORDER BY 表达式 <ASC | DESC> <NULLS FIRST | NULLS LAST>> <WINDOWING子句>)

PARTITION子句

ORDER BY子句

WINDOWING子句

缺省时相当于RANGE UNBOUNDED PRECEDING

1. 值域窗(RANGE WINDOW)

RANGE N PRECEDING

仅对数值或日期类型有效,选定窗为排序后当前行之前,某列(即排序列)值大于/小于(当前行该列值 ?/+ N)的所有行,因此与ORDER BY子句有关系。

2. 行窗(ROW WINDOW)

ROWS N PRECEDING

选定窗为当前行及之前N行。

还可以加上BETWEEN AND 形式,例如RANGE BETWEEN m PRECEDING AND n FOLLOWING

函数

AVG(<distinct | all> expr)

一组或选定窗中表达式的平均值

CORR(expr, expr)

即COVAR_POP(exp1,exp2) / (STDDEV_POP(expr1) * STDDEV_POP(expr2)),两个表达式的互相关,-1(反相关) ~ 1(正相关),0表示不相关

COUNT(<distinct> <*> <expr>)

计数

COVAR_POP(expr, expr)

总体协方差

COVAR_SAMP(expr, expr)

样本协方差

CUME_DIST

累积分布,即行在组中的相对位置,返回0 ~ 1

DENSE_RANK

行的相对排序(与ORDER BY搭配),相同的值具有一样的序数(NULL计为相同),并不留空序数

FIRST_VALUE

一个组的第一个值

LAG(expr, <offset>, <default>)

访问之前的行,OFFSET是缺省为1 的正数,表示相对行数,DEFAULT是当超出选定窗范围时的返回值(如第一行不存在之前行)

LAST_VALUE

一个组的最后一个值

LEAD(expr, <offset>, <default>)

访问之后的行,OFFSET是缺省为1 的正数,表示相对行数,DEFAULT是当超出选定窗范围时的返回值(如最后行不存在之前行)

MAXexpr)

最大值

MIN(expr)

最小值

NTILE(expr)

按表达式的值和行在组中的位置编号,如表达式为4,则组分4份,分别为1 ~ 4的值,而不能等分则多出的部分在值最小的那组

PERCENT_RANK

类似CUME_DIST,1/(行的序数 - 1)

RANK

相对序数,允许并列,并空出随后序号

RATIO_TO_REPORT(expr)

表达式值 / SUM(表达式值)

REGR_ xxxx(expr, expr)

线性回归函数

ROW_NUMBER

排序的组中行的偏移

STDDEV(expr)

标准差

STDDEV_POP(expr)

总体标准差

STDDEV_SAMP(expr)

样本标准差

SUM(expr)

合计

VAR_POP(expr)

总体方差

VAR_SAMP(expr)

样本方差

VARIANCE(expr)

方差

12.2 例子

竖表转横表

一般形式为将一个列为C!, C2, … CN的表,以C1, C2, … CX为基准,将CX+1, … CN的不同值改为列。一般化的语法:

SELECT C1, C2, … CX,

MAX(DECODE(rn, 1, CX+1, NULL)) CX+1_1, … MAX(DECODE(rn, 1, CN, NULL)) CN_1

MAX(DECODE(rn, 2, CX+1, NULL)) CX+1_2, … MAX(DECODE(rn, 2, CN, NULL)) CN_2

MAX(DECODE(rn,N,CX+1, NULL)) CX+1_N, … MAX(DECODE(rn,N, CN, NULL)) CN_N

FROM

(SELECT C1, C2, … CN,

ROW_NUMBER() OVER (PARTITION BY C1, C2, … CX ORDER BY <something>) rn

FROM T

WHERE …)

GROUP BY C1, C2, … CX;

通用包:

CREATE OR REPLACE PACKAGE pkg_pivot

AS

TYPE refcursor IS REF CURSOR;

TYPE ARRAY IS TABLE OF VARCHAR2(30);

PROCEDURE pivot(p_max_cols IN NUMBER DEFAULT NULL,

p_max_cols_query IN VARCHAR2 DEFAULT NULL,

p_query IN VARCHAR2,

p_anchor IN ARRAY,

p_pivot IN ARRAY,

p_cursor IN OUT refcursor);

END;

CREATE OR REPLACE PACKAGE BODY pkg_pivot

AS

PROCEDURE pivot(p_max_cols IN NUMBER DEFAULT NULL,

p_max_cols_query IN VARCHAR2 DEFAULT NULL,

p_query IN VARCHAR2,

p_anchor IN ARRAY,

p_pivot IN ARRAY,

p_cursor IN OUT refcursor)

AS

l_max_cols NUMBER;

l_query LONG;

l_cnames ARRAY;

BEGIN

IF (p_max_cols IS NOT NULL)

THEN

EXECUTE IMMEDIATE p_max_cols_query INTO l_max_cols;

ELSE

RAISE_APPLICATION_ERROR(-20001, 'Cannot figure out max cols');

END IF;

l_query := 'select ';

FOR i IN 1 .. p_anchor.count

LOOP

l_query := l_query || p_anchor(i) || ',';

END LOOP;

FOR i IN 1 .. l_max_cols

LOOP

FOR j IN 1 .. p_pivot.count

LOOP

l_query := l_query || 'max(decode(rn,'||i||','||p_pivot(j)||',null)) '||p_pivot(j) || '_' || i || ',';

END LOOP;

END LOOP;

l_query := RTRIM(l_query,',') || ' from (' || p_query || ') group by ';

FOR i IN 1 .. p_anchor.count

LOOP

l_query := l_query || p_anchor(i) || ',';

END LOOP;

l_query := RTRIM(l_query,',');

EXECUTE IMMEDIATE 'alter session set cursor_sharing=force';

OPEN p_cursor FOR l_query;

EXECUTE IMMEDIATE 'alter session set cursor_sharing=exact';

END;

END;

其中:

p_max_cols_query为SELECT MAX(COUNT(*)) FROM TABLE_NAME GROUP BY C1, C2, … CX;

p_query为SELECT C1, C2, … CN ROW_NUMBER() OVER (PARTITION BY C1, C2, … CX ORDER BY <something>) rn FROM TABLE_NAME;

p_anchor为pkg_pivot.array(C1, C2, … CX)

p_pivot为pkg_pivot.array(CX+1, CX+2, … CN)

p_cursor为返回的游标。

12.3 最后说明

PL/SQL与分析函数

PL/SQL不支持分析函数的语法,可以通过以下两种方法解决:

1。使用动态游标;

2。将含分析函数的语句创建为视图。

WHERE子句中的分析函数

由于查询仅在最后的ORDER BY子句前执行分析函数,因此WHERE条件中无法使用分析函数,只能利用嵌套循环实现。

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
2023年上半年GDP全球前十五强
 百态   2023-10-24
美众议院议长启动对拜登的弹劾调查
 百态   2023-09-13
上海、济南、武汉等多地出现不明坠落物
 探索   2023-09-06
印度或要将国名改为“巴拉特”
 百态   2023-09-06
男子为女友送行,买票不登机被捕
 百态   2023-08-20
手机地震预警功能怎么开?
 干货   2023-08-06
女子4年卖2套房花700多万做美容:不但没变美脸,面部还出现变形
 百态   2023-08-04
住户一楼被水淹 还冲来8头猪
 百态   2023-07-31
女子体内爬出大量瓜子状活虫
 百态   2023-07-25
地球连续35年收到神秘规律性信号,网友:不要回答!
 探索   2023-07-21
全球镓价格本周大涨27%
 探索   2023-07-09
钱都流向了那些不缺钱的人,苦都留给了能吃苦的人
 探索   2023-07-02
倩女手游刀客魅者强控制(强混乱强眩晕强睡眠)和对应控制抗性的关系
 百态   2020-08-20
美国5月9日最新疫情:美国确诊人数突破131万
 百态   2020-05-09
荷兰政府宣布将集体辞职
 干货   2020-04-30
倩女幽魂手游师徒任务情义春秋猜成语答案逍遥观:鹏程万里
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案神机营:射石饮羽
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案昆仑山:拔刀相助
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案天工阁:鬼斧神工
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案丝路古道:单枪匹马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:与虎谋皮
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:李代桃僵
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:指鹿为马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:小鸟依人
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:千金买邻
 干货   2019-11-12
 
推荐阅读
 
 
 
>>返回首頁<<
 
靜靜地坐在廢墟上,四周的荒凉一望無際,忽然覺得,淒涼也很美
© 2005- 王朝網路 版權所有