第 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条件中无法使用分析函数,只能利用嵌套循环实现。