Oracle--分析函数

分析函数的语法结构一般是：分析函数名() 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

１．窗口子句必须和 ORDER BY 子句同时使用。
如果指定了 ORDER BY 子句未指定窗口子句：

RANGE BETWEEN unbounded preceding AND CURRENT ROW

２．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）

３．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。