数据库分析函数解析.docxVIP

分析函数解析分析函数的语法结构一般是：分析函数名(参数)?OVER?(PARTITION?BY子句?ORDER?BY子句?ROWS/RANGE子句)。即由以下三部分组成：分析函数名：如sum、max、min、count、avg等聚集函数以及lead、lag行比较函数等；over： 关键字，表示前面的函数是分析函数，不是普通的集合函数；分析子句：over关键字后面挂号内的内容；分析子句又由下面三部分组成：partition by ：分组子句，表示分析函数的计算范围，不同的组互不相干；ORDER?BY：?排序子句，表示分组后，组内的排序方式；ROWS/RANGE：窗口子句，是在分组(PARTITION?BY)后，组内的子分组（也称窗口），此时分析函数的计算范围窗口，而不是PARTITON。窗口有两种，ROWS和RANGE；下面分析rows与range窗口子句的用法，先看下面例子：点击(此处)折叠或打开SQL?edit已写入?file?afiedt.buf?1?WITH?t?AS?2?(SELECT?(CASE?3 WHEN?LEVEL?IN?(1,?2)?THEN?4 1?5 WHEN?LEVEL?IN?(4,?5)?THEN?6 6?7?ELSE?8?LEVEL?9?END)?ID?10?FROM?dual?11?CONNECT?BY?LEVEL??10)?12?SELECT?id,?13?SUM(ID)?over(ORDER?BY?ID)?default_sum,?14?SUM(ID)?over(ORDER?BY?ID RANGE?BETWEEN?unbounded preceding?AND?CURRENT?ROW)?range_unbound_sum,?15?SUM(ID)?over(ORDER?BY?ID?ROWS?BETWEEN?unbounded preceding?AND?CURRENT?ROW)?rows_unbound_sum,?16?SUM(ID)?over(ORDER?BY?ID RANGE?BETWEEN?1 preceding?AND?2 following)?range_sum,?17?SUM(ID)?over(ORDER?BY?ID?ROWS?BETWEEN?1 preceding?AND?2 following)?rows_sum?18*?FROM?tSQL?/?ID DEFAULT_SUM RANGE_UNBOUND_SUM ROWS_UNBOUND_SUM RANGE_SUM ROWS_SUM---------- ----------- ----------------- ---------------- ---------- ----------?1 2???????????2???????????????? 15?5?1 2?????????? 2???????????????? 2??????????????? 5???????? 11?3 5?????????? 5???????????????? 53?16?6 23????????? 23??????????????? 1133?21?6 23????????? 23??????????????? 17?????????????? 33????????25?6 23????????? 23??????????????? 23?????????????? 33??????? 27?7 30????????? 30??????????????? 30?????????????? 42??????? 30?8 38????????? 38??????????????? 38?????????????? 24????????24?9 47????????? 47??????????????? 47?????????????? 17????????17已选择9行。从上面的例子可知：1、窗口子句必须和order by 子句同时使用，且如果指定了order by 子句未指定窗口子句，则默认为RANGE?BETWEEN?unbounded preceding?AND?CURRENT?ROW，如上例结果集中的defult_sum等于range_unbound_sum；2、如果分析函数没有指定ORDER?BY子句，也就不存在ROWS/RANGE窗口的计算；3、range是逻辑窗口，是指定当前行对应值的范围取值，列数不固定，只要行值在范围内，对应列都包含在内，如上例中range_sum（即range 1 preceing and 2 following）例的分析结果：当id=1时，是sum为1-1=id=1+2 的和，即sum=1+1+3=5(取id为1