数据库分析函数解析

分析函数解析

分析函数的语法结构一般是：分析函数名(参数) 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窗口子句的用法，先看下面例子：

点击(此处)折叠或打开

1.SQL>edit

2.已写入file afiedt.buf

3.

4. 1 WITH t AS

5. 2 (SELECT(CASE

6. 3 WHEN LEVEL IN(1, 2)THEN

7. 4 1

8. 5 WHEN LEVEL IN(4, 5)THEN

9. 6 6

10. 7 ELSE

11. 8 LEVEL

12. 9 END) ID

13. 10 FROM dual

14. 11 CONNECT BY LEVEL< 10)

15. 12 SELECT id,

16. 13 SUM(ID) over(ORDER BY ID) default_sum,

17. 14 SUM(ID) over(ORDER BY ID RANGE BETWEEN unbounded preceding AND CURRENT ROW) ra

nge_unbound_sum,

18. 15 SUM(ID) over(ORDER BY ID ROWS BETWEEN unbounded preceding AND CURRENT ROW) row

s_unbound_sum,

19. 16 SUM(ID) over(ORDER BY ID RANGE BETWEEN 1 preceding AND 2 following) range_sum,

20. 17 SUM(ID) over(ORDER BY ID ROWS BETWEEN 1 preceding AND 2 following) rows_sum

21. 18*FROM t

22.SQL>/

23.

24. ID DEFAULT_SUM RANGE_UNBOUND_SUM ROWS_UNBOUND_SUM RANGE_SUM ROWS_SUM

25.---------- ----------- ----------------- ---------------- ---------- ----------

26.

27. 1 2 2 1 55

28. 1 2 2 2 5 11

29. 3 5 5 5 316

30. 6 23 23 11 33 21

31. 6 23 23 17 33 25

32. 6 23 23 23 33 27

33. 7 30 30 30 42 30

34. 8 38 38 38 24 24

35. 9 47 47 47 17 17

36.

37.已选择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,1,3);

当id=3时，是sum为3-1<=id<=3+2 的和，即sum=3（取id为3）；

当id=6时，是sum为6-1<=id<=6+2 的和，即sum=6+6+6+7+8=33（取id为6，6，6，7，8）;

以此类推下去，结果如上例中所示。

4、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；

以此类推下去，结果如上例所示。

注：行比较分析函数lead和lag无window(窗口)子句。

keep函数

1、概念：

keep是Oracle下的另一个分析函数，他的用法不同于通过over关键字指定的分析函数，可以用于这样一种场合下：取同一个分组下以某个字段排序后，对指定字段取最小或最大的那个值。

从这个前提出发，我们可以看到其实这个目标通过一般的row_number分析函数也可以实现，即指定rn=1。但是，该函数无法实现同时获取最大和最小值。或者说用first_value和last_value，结合row_number实现，但是该种方式需要多次使用分析函数，而且还需要套一层SQL。于是出现了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。

2、分析：

SQL> desc t

Name Null? Type

------------------------------------------------- ----------------------------

X VARCHAR2(10)

Y DATE

Z NUMBER

SQL> selectx,to_char(y,'yyyy-mm') y,z from t

2 order by x,y,z

3 /

X Y Z

---------- -----------------

a 2011-02 111

a 2011-02 222

a 2011-02 888

a 2011-03 333

a 2011-04 555

a 2011-04 666

b 2011-02 111

b 2011-02 222

b 2011-03 333

b 2011-04 555

10 rows selected.

SQL>

下面，我们来使用keep函数来看一些例子。

SQL> select x,

2 min(z) keep(dense_rank first order by trunc(y,'mm')) first_min,

3 min(z) keep(dense_rank last order by trunc(y,'mm')) last_min,

4 max(z) keep(dense_rank first order by trunc(y,'mm')) first_max,

5 max(z) keep(dense_rank last order by trunc(y,'mm')) last_max

6 fromt

7 group by x

8 /

X FIRST_MIN LAST_MIN FIRST_MAX LAST_MAX

-------------------- ---------- ---------- ----------

a 111 555 888 666

b 111 555 222 555

这是显示的效果。

我们来分析一下，SQL里面的groupby 是作用于聚会函数的，可以这么理解一下。还有，这里我们头脑里应该有这么一个概念，排序即分组。

结合结果集与keep函数里orderby 子句，我们来看一下表中的数据，然后把表里的数据进行如下划分，

以X为维度，按值a进行划分：

a 2011-02 111

a 2011-02 222

a 2011-02 888

---------------------------------------这些数据划分为第一组。

因为order by是按日期（截止到月）进行排序的，上面的日期都一样。

a 2011-03 333

----------------------------------------这些数据划分为第二组。

a 2011-04 555

a 2011-04 666

----------------------------------------这些数据划分为第三组。

以X为维度，按值b进行划分：

b 2011-02 111

b 2011-02 222

-----------------------------------------第一组

b 2011-03 333

----------------------------------------第二组

b 2011-04 555

----------------------------------------第三组

好，有了上述的划分，我们就不难理解SQL输出的结果集了。下面我们来分析一下SQL中输入的值。结合SQL，

?min(z) keep(dense_rank first order by trunc(y,'mm')) first_min 该子句用来显示，以a为维度，以y为序，显示第一组（因为keep函数里指定是的first子句）中的最小值，所以，它显示为111（结合上述的划分结果来看）。

?min(z) keep(dense_rank last order by trunc(y,'mm')) last_min 该子句用来显示，以a为维度，以y为序，显示最后一组（因为keep函数里指定是的last子句）中的最小值，所以，这显示为555。

下面，我们来再分析一下以X为b的结果。

我们来看一下x=b的那一行，last_min与last_max的值一样。这是因为x为b的最后一组，它只有一行数据，对于一行数据而言，它的最大值与最小值肯定是同一个值了，所以last_min 与last_max一样。

使用其他聚合函数也非常好理解了，比如我使用sum函数。

SQL> select x,

2 sum(z) keep(dense_rank first order by trunc(y,'mm')) first_sum,

3 sum(z) keep(dense_rank last order by trunc(y,'mm')) last_sum

4 fromt

5 group by x

6 /

X FIRST_SUM LAST_SUM

-------------------- ----------

a 1221 1221

b 333 555

最近在工作中，碰到一个问题，后来在处理过程中接触到了KEEP，DENSE_RANK

分析函数，问题瞬间被简单的解决，否则按照常规写法，肯定会写出嵌套语句，但是网上对这个方面的介绍比较少，现在特整理了一下：

从目前查到的来看：DENSE_RANK是和KEEP结合使用的，网上给出的解释是Returns the row ranked first using DENSE_RANK。我的理解是返回 DENSE_RANK函数的第一结果。一般写法是 MIN [ MAX ] (A) KEEP(DENSE_RANK FIRST [ LAST ] ORDER BY B)，这里引用别人说的明的解释一下：

DENSE_RANK

功能描述：根据ORDER BY子句中表达式的值，从查询返回的每一行，计算它们与其它行的相对位置。组内的数据按ORDER BY子句排序，然后给每一行赋一个号，从而形成一个序列，该序列从1开始，往后累加。每次ORDER BY表达式的值发生变化时，该序列也随之增加。有同样值的行得到同样的数字序号（认为null时相等的）。密集的序列返回的时没有间隔的数。

FIRST

功能描述：从DENSE_RANK返回的集合中取出排在最前面的一个值的行（可能多行，因为值可能相等），因此完整的语法需要在开始处加上一个集合函数以从中取出记录。LAST

功能描述：从DENSE_RANK返回的集合中取出排在最后面的一个值的行（可能多行，因为值可能相等），因此完整的语法需要在开始处加上一个集合函数以从中取出记录。

所以默认排序下，FIRST可以理解是取小值，LAST取大值。而前面的MIN或者MAX则是在KEEP的结果集中取某一字段的最大值或最小值。