分析函数的语法结构一般是:分析函数名() OVER (PARTITION BY子句 ORDER BY子句 ROWS/RANGE子句)
即由以下三部分组成:
分析函数名 如SUM()、MAX()、MIN()、COUNT()、AVG()等聚集函数以及LEAD()、LAG()行比较函数等
OVER 关键字,表示前面的函数是分析函数,不是普通的集合函数
分析子句 OVER关键字后面括号内的内容
分析子句又由下面三部分组成:
PARTITION BY 分组子句,表示分析函数的计算范围,不同的组互不相干
ORDER BY 排序子句,表示分组后,组内的排序方式
ROWS/RANGE 窗口子句,是在分组(PARTITION BY)后,组内的子分组(也称窗口),是分析函数的计算范围窗口,而不是PARTITION;窗口有两种,ROWS 和 RANGE
SELECT id,
SUM(ID)OVER(ORDER BY ID) AS default_sum,
SUM(ID)OVER(ORDER BY IDRANGE BETWEEN unbounded preceding AND CURRENT ROW)
AS range_unbound_sum,
SUM(ID)OVER(ORDER BY ID ROWS BETWEEN unbounded preceding AND CURRENT ROW)
AS rows_unbound_sum,
SUM(ID)OVER(ORDER BY ID RANGE BETWEEN 1 preceding AND 2 following)
AS range_sum,
SUM(ID)OVER(ORDER BY ID ROWS BETWEEN 1 preceding AND 2 following)
AS rows_sum
FROM table
1.窗口子句必须和 ORDER BY 子句同时使用。
如果指定了 ORDER BY 子句未指定窗口子句:
RANGE BETWEEN unbounded preceding AND CURRENT ROW
2.RANGE 是逻辑窗口,是指定当前行对应值的范围取值,列数不固定,只要行值在范围内,对应列都包含在内,
如上例中range_sum(即range 1 preceing and 2 following)例的分析结果:当id=1时,是sum为1-1<=id<=1+2 的和,即sum=1+1+3=5(取id为1,1,3)
当id=3时,是sum为3-1<=id<=3+2 的和,即sum=3(取id为3)
3.ROWS 是物理窗口,即根据 ORDER BY 子句排序后,取的前 N行及后N行的数据计算(与当前行的值无关,只与排序后的
行号相关),如上例中rows_sum例结果,是取前1行和后2行数据的求和,分析上例rows_sum的结果:
当id=1(第一个1时)时,前一行没数,后二行分别是1和3,sum=1+1+3=5
当id=3时,前一行id=1,后二行id都为6,则sum=1+3+6+6=16
累加效果実現
失敗例:
!!!!!这种情况下。体现不出来累加效果,因为1和999数值相同的会同时全部求和,没有累加效果。
select t.cust_count,t.rule_type ,sum(t.cust_count) OVER(ORDER BY
DECODE(t.rule_type,'SP',1,999))
from R_RULE t
WHERE cust_count <> 0
AND comp_no = 'C10681'
体现累加效果:
WITH t AS (
SELECT LEVEL AS lv ,
MOD(LEVEL,2) AS dep,
LEVEL+10 AS SS FROM dual
CONNECT BY LEVEL < 10
)
--按照dep分组求和
SELECT t.lv ,t.ss,dep,SUM(t.ss) OVER (PARTITION BY dep ORDER BY lv
--计算第一行到最后一行的总和
--默认是计算到当前行的和current row
ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM t
ORDER BY dep
注:行比较分析函数 LEAD 和 LAG 无window(窗口)子句。
ORACLE lag()与lead() 函数
lag与lead函数是跟偏移量相关的两个分析函数,通过这两个函数可以在一次查询中取出同一字段的前N行的数据(lag)和后N行的数据(lead)作为独立的列,从而更方便地进行进行数据过滤。这种操作可以代替表的自联接,并且LAG和LEAD有更高的效率。
over()表示 lag()与lead()操作的数据都在over()的范围内,他里面可以使用partition by 语句(用于分组) order by 语句(用于排序)。partition by a order by b表示以a字段进行分组,再 以b字段进行排序,对数据进行查询。
例如:lead(field, num, defaultvalue) field需要查找的字段,num往后查找的num行的数据,defaultvalue没有符合条件的默认值。
select t.id id ,
lead(t.id, 1, null) over (order by t.id) next_id,
t.cphm
from test t
order by t.id asc
列转行ーーpivot函数
with T1 AS(
select t.target_month,t.koufuri_junmiman,t.cnt,t.riskrank from SCORINGRESULTRESULT t
)
select *
from T1 pivot (max(cnt) for RISKRANK in ('S' as S,'A' AS A,'B' AS B,'C' AS C, 'D' AS D, 'E' AS E)) --转化为列名,并为其加上别名
order by target_month;
keep函数
语法:min | max(column1) keep (dense_rank first | lastorder by column2) over (partion by column3);
最前是聚合函数,可以是min、max、avg、sum。。。
column1为要计算的列;
dense_rank first,dense_rank last为keep 函数的保留属性,表示分组、排序结果集中第一个、最后一个;
解释:返回按照column3分组后,按照column2排序的结果集中第一个或最后一个最小值或最大值column1。