小議分析函式中排序對結果的影響（二）

分析函式中經常會包括ORDER BY語句，而這個語句會對Oracle的執行結果產生影響。

繼續討論複雜一些的情況。

小議分析函式中排序對結果的影響（一）：http://yangtingkun.itpub.net/post/468/476949

 

分析函式中經常會包括ORDER BY語句，而這個語句會對Oracle的執行結果產生影響。

繼續討論複雜一些的情況。

小議分析函式中排序對結果的影響（一）：http://yangtingkun.itpub.net/post/468/476949

 

 

仍然沿用上面一篇文章的例子：

SQL> SELECT * FROM T;

        ID NAME
---------- ------------------------------
         1 A
         3 C
         2 B

如果不加任何的排序，直接查詢T表，Oracle根據記錄讀取的順序返回記錄，得到的結果是1、3、2。

上一篇討論瞭如果加上包括排序的分析函式，會對結果造成何種影響，下面描述一下，出現多個排序不同的分析函式，會如何影響查詢結果。

SQL> SELECT ID,
  2  NAME,
  3  SUM(ID) OVER(ORDER BY ID) SUM1,
  4  SUM(ID) OVER(ORDER BY ID DESC) SUM2
  5  FROM T;

        ID NAME                                 SUM1       SUM2
---------- ------------------------------ ---------- ----------
         3 C                                       6          3
         2 B                                       3          5
         1 A                                       1          6

SQL> SELECT ID,
  2  NAME,
  3  SUM(ID) OVER(ORDER BY ID DESC) SUM1,
  4  SUM(ID) OVER(ORDER BY ID) SUM2
  5  FROM T;

        ID NAME                                 SUM1       SUM2
---------- ------------------------------ ---------- ----------
         1 A                                       6          1
         2 B                                       5          3
         3 C                                       3          6

看樣子，Oracle採用了第二個出現的排序操作，作為最終結果返回的排序次序，而與排序是升序還是降序沒有關係。

如果再增加新的排序操作呢：

SQL> SELECT ID,
  2  NAME,
  3  SUM(ID) OVER(ORDER BY ID) SUM1,
  4  SUM(ID) OVER(ORDER BY ID DESC) SUM2,
  5  MAX(ID) OVER(ORDER BY ID) MAX1
  6  FROM T;

        ID NAME                                 SUM1       SUM2       MAX1
---------- ------------------------------ ---------- ---------- ----------
         3 C                                       6          3          3
         2 B                                       3          5          2
         1 A                                       1          6          1

SQL> SELECT ID,
  2  NAME,
  3  SUM(ID) OVER(ORDER BY ID DESC) SUM1,
  4  SUM(ID) OVER(ORDER BY ID) SUM2,
  5  MAX(ID) OVER(ORDER BY ID DESC) MAX1
  6  FROM T;

        ID NAME                                 SUM1       SUM2       MAX1
---------- ------------------------------ ---------- ---------- ----------
         1 A                                       6          1          3
         2 B                                       5          3          3
         3 C                                       3          6          3

這個現象無非是兩種情況，一種是SUM的排序優先順序比MAX高，另一種就是對於Oracle來說，總會選擇後出現的那種排序最為最終的排序結果。

SQL> SELECT ID,
  2  NAME,
  3  MAX(ID) OVER(ORDER BY ID) MAX1,
  4  MIN(ID) OVER(ORDER BY ID) MIN1,
  5  MAX(ID) OVER(ORDER BY ID DESC) MAX2,
  6  SUM(ID) OVER(ORDER BY ID) SUM1
  7  FROM T;

        ID NAME                                 MAX1       MIN1       MAX2       SUM1
---------- ------------------------------ ---------- ---------- ---------- ----------
         3 C                                       3          1          3          6
         2 B                                       2          1          3          3
         1 A                                       1          1          3          1

這個例子就可以充分說明問題了，對於當前的情況，只有兩種排序的可能性，ID升序和ID降序，Oracle選擇了最後出現的那種排序操作作為了最終的排序結果。

將問題擴充套件多多列的情況：

SQL> INSERT INTO T VALUES (4, 'E');

已建立 1 行。

SQL> INSERT INTO T VALUES (5, 'D');

已建立 1 行。

SQL> COMMIT;

提交完成。

SQL> SELECT * FROM T;

        ID NAME
---------- ------------------------------
         4 E
         5 D
         1 A
         3 C
         2 B

SQL> COL NAME FORMAT A5
SQL> SELECT ID,
  2  NAME,
  3  MAX(ID) OVER(ORDER BY ID) MAX1,
  4  MAX(ID) OVER(ORDER BY ID DESC) MAX2,
  5  MAX(ID) OVER(ORDER BY NAME) MAX3,
  6  MAX(ID) OVER(ORDER BY NAME DESC) MAX4
  7  FROM T;

        ID NAME        MAX1       MAX2       MAX3       MAX4
---------- ----- ---------- ---------- ---------- ----------
         4 E              4          5          5          4
         5 D              5          5          5          5
         3 C              3          5          3          5
         2 B              2          5          2          5
         1 A              1          5          1          5

Oracle總會選擇最後出現的分析函式中的排序方法，作為整個結果集的排序方法。