oracle的sql查詢分析函式-高階部分-分析函授over()子句

oracle的分析函式，應該是有一個格式的
function(argu1,argu2...)
over(partition by 
      order by 
      windowing-clause)
這是一個完整的分析函式的格式。
我之前用的分析函式，實際是省略了後面的over部分，也就是高階部分。
over分為3個部分：分組子句，partition by，這是指定對整體分析資料的分組依據；排序子句，order by，也就是對每個分組內的資料進行排序，同時也是一個預設的windowing函式；開窗子句，windowing-clause，這是一個資料視窗確定子句。
一個一個說明：
1、分組子句，partition by，這個分組部分，有點類似於group by子句，就是將整體資料，按照by後面提供的分組依據，按照依據值的不同，分成很多組，讓分析函式在每個組內工作，比如計算每個組的組內成員的sum值，這樣說，其實就是group by。
2、排序子句，order by，在order by之前，如果有分組子句，那麼就在每個分組內按照排序依據進行排序，如果沒有分組子句，那就是整個資料集進行排序。order by，是一個預設的開窗函式，也就是預設的windowing子句，其表示的意思就是：從分組或者整個資料集的第一行開始到當前行，所以排序子句，可以寫成這樣,order by 1，根據數字1進行排序，其實際就是不用排序，我只要確定每一個分組裡面有一個順序就可以了，不用確定按照那個欄位進行排序，這樣拿到的資料，估計是oracle按照內在的rowid給的排序。
3、視窗子句，windowing子句，這也是重點，也是難點。視窗子句，是指定每個分組內，分析函式要處理的資料行的範圍，也就是資料視窗，也就是說，用這個視窗子句，來確定在每個分組內，那些資料行要用分析函式來處理，比如sum函式，就是每個分組內，那些資料行的數值加入到sum函式計算的範圍內。再強調一次，視窗子句，就是指定那些資料行進入分析函式。視窗子句，透過兩種方式來指定進行分析的資料行，1是range，數值範圍，2是rows，資料行的偏移量，就是當前行的前面幾行到當前行的後面幾行。
視窗子句，必須和排序子句一起使用，也就是必須跟在order by子句後面，這樣，同時也指定了視窗子句語句中計算的依據欄位是那個，也就是視窗子句，開始時前面的那個指定欄位。
視窗子句具體寫法：
當前行的前面行   preceding 
當前行的後面行 following
rows和range的寫法一樣，只是標記不同
range between 1 proceding and 1 following
rows between 1 proceding and 1 following
寫法是一樣的，只是一個range，一個rows。但是意識差別很大。
order by name desc,age desc range between 1 proceding and 1 following，指的是當前行的age欄位的數值，加1和減1之間的範圍，比如當前行的age欄位數值是3，那麼這個視窗指定的範圍應該是2到4，這樣說，也就是當前行的分析函式，要計算這個分組裡面，age欄位在2和4之間的資料行。而且，視窗子句的依據欄位，可以是日期，但是寫法不一樣，要這樣寫，range between interval '2' day proceding and interval '2' day following，這樣就是計算當前行的日期欄位的前兩天和後兩天之間的範圍，也就是5天，也就是當前天加上前兩天再加上後兩天。
order by name desc,age desc rows between 1 proceding and 1 following
是指，當前行的前面一行和後面一行之間的。注意這裡的兩個1，實際是指定了偏移量，也可以用其他寫法：
（1）、rows between UNBOUNDED  proceding and UNBOUNDED  following     ：UNBOUNDED PRECEDING:從當前分組的每一行開始到最後一行
（2）、rows between UNBOUNDED  proceding and  CURRENT ROW    :CURRENT ROW：從當前分組的每一行開始，到當前行(並結束)
（3）、rows between CURRENT ROW and  UNBOUNDED  following      :CURRENT ROW：從當前分組的當前行開始，到最後一行(並結束)

從上面的表述，可以看出，windowing子句，是最複雜的，要是覺得煩的，也可以不這樣算，可以用pl方式來做，只是需要一張臨時表而已，相對來說，可能PL方式更為複雜一些。

over函式，實際，就是給分析函式，確定分組，確定計算範圍的一個函式，是分析函式的附加部件。
加上over函式，可以完成一些超級複雜的計算要求。
下面我舉一個例子：
票據管理
表1，是每個票據的票面記錄，比如票據號，出票人，金額等，一個票據對應一個記錄。
表2，是每個票據的詳細記錄，比如每個票據內包含了那些商品，記錄商品的程式碼，數量，金額等。

題目1：我有個商品，程式碼是AB，要求計算出上個月出售這個商品（AB）的票據和票據總量比率，也就是佔比。
表1和表2，是一對多的關係，表1和表2，交叉配對，加入條件：表2程式碼=AB，這時候，還是一對多的關係，很難得出表1中的唯一記錄，就是，等到的表1記錄，表1.*這樣的寫法，得到的是很多重複項，可能有人想到distinct的寫法，如果票據的唯一依據是多個欄位，那麼很難用distinct來做，那麼我們就用over函式來做。
想法：
表1和表2的卡迪爾乘積，加上表2程式碼=AB的條件，表1.*得到的記錄，是很多的重複項，但是按照原定設計，表1裡面應該有一個唯一的依據欄位，也就是PK欄位，而且肯定是2個欄位以上，或者多個欄位，那麼我們就以表1的PK欄位，為partition by的分組依據，也就是partition by 表1.PK欄位1，表1.PK欄位2，這樣，表1.*得到的重複項，重複的資料行，就被分組到同一分組內，然後隨便用order by排序一下，用row_number給出一個序號，然後在外面一層的sql中，指定序號=1，這樣就直接所有重複項全部去除掉了。
實際：
select * from (select row_number()over(partition by a.PK欄位1,a.PK欄位2 order by 1) as item_rows,a.* from 表1 a，表2 b where a.no=b.no and b.code='AB') where item_rows=1;

我再做一個更復雜，同時也更為符合實際的需求。
題目2：要求查詢上個月購買了特定商品的顧客，佔比情況，要求統計出特定商品，特定商品是全部商品的佔比。
題目再說明：計算出，購買了商品AB的顧客佔比，這個簡單，但是我要求，不是單單一個商品AB，我要求一次性統計出，所有商品，商品A購買的顧客佔比，商品B購買的顧客佔比。
想法：
還是從表1和表2的卡迪爾乘積開始，只是和上面的過程不同，上面的分組依據是表1，現在我們以表2為分組依據，我們以表2中的商品程式碼為分組依據，這樣每個分組內，同樣有表1.*的資料，然後將表1.*的資料進行排序，同樣的操作，但是得到的結果不一樣。
實際：