原創
select *
from (select province,
commodity,
sum(price),
ROW_NUMBER() OVER(PARTITION BY province order by sum(price) desc) rn
from test141211
group by province, commodity
-- order by province desc, sum(price) desc
)
where rn <= 5
開窗函數 其實就是group by的另一種。它于group by的差別在于開窗函數可以分組列中排序其實就是加了一列影藏列。可以在group by中在分組的意思
---------------------------------------------------------------------------------------
翻譯
基本介紹:
Analytic_clause的文法如下：
[ query_partition_clause ] [ order_by_clause [ windowing_clause ] ]
這裡：
query_partition_clause是查詢分組子句；
order_by_clause是分組排序子句；
windowing_clause是視窗範圍子句。
分析函數在查詢結果集确定之後才開始進行計算，Analytic_clause就是用來定義函數怎樣對查詢結果集進行分組計算的。
根據Oracle對查詢和分析函數的處理方法可知，在select和order by子句中都可以使用分析函數。
query_partition_by、order_by_clause和windowing_clause三個子句是可選的，将三個子句分别簡記為p，o，w。
合法的組合方式有如下6種：
1). Pow
(query_partition_clause order_by_clause windowing_clause)
分組，排序，定義視窗範圍
2). Po
(query_partition_clause order_by_clause)
分組，排序，視窗預設為range between unbounded preceding and current row
3). P
(query_partition_clause)
分組，不排序，沒有視窗
4). Ow
(order_by_clause windowing_clause)
分組為整個查詢結果集，排序，定義視窗範圍
5). O
(order_by_clause)
分組為整個查詢結果集，排序，視窗預設為range between unbounded preceding and current row
6). Null
()
分組為整個查詢結果集，不排序，沒有視窗
因為隻有存在order_by_clause，才能有windowing_clause，故不存在如下兩種形式的組合：
pw(query_partition_clause windowing_clause)
w(windowing_clause)
總結：
1). 對于是否存在order_by_clause，分析函數可以分為兩類，含有order_by_clause的一般稱為windowing function，不含的稱為reporting function。
2). Windowing function，對查詢結果集進行分組，排序，根據視窗範圍計算分組中每一行的函數結果。
3). Reporting function，對查詢結果集進行分組，不排序，視窗範圍為整個分組，在每一個分組内，計算整個分組的函數值，再将函數值分别賦給分組内的每一行。
一、開窗函數
開窗函數指定了分析函數工作的資料視窗大小，這個資料視窗大小可能會随着行的變化而變化，舉例如下：
1、over（order by salary） 按照salary排序進行累計，order by是個預設的開窗函數。
SELECT
EMPLOYEE_ID, SALARY, MANAGER_ID, DEPARTMENT_ID,
SUM(SALARY) OVER (ORDER BY SALARY) DD
FROM INFA_TEST.EMPLOYEES EMP
ORDER BY SALARY
功能：按salary升序排序，統計小于等于目前salary的salary總和。
傳回結果：
EMPLOYEE_ID SALARY MANAGER_ID DEPARTMENT_ID DD
132 2100 121 50 2100
128 2200 120 50 6500
136 2200 122 50 6500
127 2400 120 50 11300
135 2400 122 50 11300
119 2500 114 30 26300
140 2500 123 50 26300
144 2500 124 50 26300
191 2500 122 50 26300
182 2500 120 50 26300
注意 SALARY為2200、2400和2500行的DD值
2、over（partition by DEPARTMENT_ID）按照部門分區。
SELECT
SUM(SALARY) OVER (PARTITION BY DEPARTMENT_ID) DD
ORDER BY DEPARTMENT_ID
功能：按DEPARTMENT_ID分區，彙總各個部門的SALARY總和。
EMPLOYEE_ID SALARY MANAGER_ID DEPARTMENT_ID DD
200 4400 101 10 4400
201 13000 100 20 19000
202 6000 201 20 19000
114 11000 100 30 24900
115 3100 114 30 24900
116 2900 114 30 24900
119 2500 114 30 24900
118 2600 114 30 24900
117 2800 114 30 24900
注意 DEPARTMENT_ID為20，30的DD值
3、over（partition by DEPARTMENT_ID order by SALARY）按照部門分區。
SUM(SALARY) OVER (PARTITION BY DEPARTMENT_ID ORDER BY SALARY) DD
功能：按DEPARTMENT_ID分區，按SALARY升序排序，統計各個部門内部小于目前SALARY的和。
201 13000 100 20 6000
114 11000 100 30 2500
115 3100 114 30 5100
116 2900 114 30 7900
119 2500 114 30 10800
118 2600 114 30 13900
注意 DEPARTMENT_ID為20、30的DD值和2中的差別
4、over（order by salary range between 50 preceding and 150 following）
SQL> select
empno,sal,mgr,deptno,
sum(sal) over (partition by deptno order by sal
RANGE BETWEEN 0 PRECEDING AND 100 FOLLOWING) dd
from emp;
功能：按DEPARTMENT_ID分區，按SALARY升序排序，彙總目前SALARY到比目前SALARY大100之間的SALARY總和。
EMPNO SAL MGR DEPTNO DD
----- ------ ------ ------- --------
7934 1300 7782 10 1300
7782 2450 7839 10 2450
7839 5000 10 5000
7369 800 7902 20 800
7566 2975 7839 20 5975 3000在2975和（2975+100）之間，故求2975與3000的和
7902 3000 7566 20 3000
7900 950 7698 30 950
7521 1250 7698 30 2500
7654 1250 7698 30 2500
7844 1500 7698 30 3100
7499 1600 7698 30 1600
7698 2850 7839 30 2850
已選擇12行。
解釋：傳回前置行和目前行SALARY相等，後續行比他大100的記錄，在SALARY列上求和。
上下邊界沒有限制：OVER (PARTITION BY DEPARTMENT_ID ORDER BY SALARY RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
SQL> select empno,sal,mgr,deptno,
RANGE BETWEEN unbounded PRECEDING AND unbounded FOLLOWING) dd
from emp;
EMPNO SAL MGR DEPTNO DD
---------- ---------- ---------- ---------- ----------
7934 1300 7782 10 8750
7782 2450 7839 10 8750
7839 5000 10 8750
7369 800 7902 20 6775
7566 2975 7839 20 6775
7902 3000 7566 20 6775
7900 950 7698 30 9400
7521 1250 7698 30 9400
7654 1250 7698 30 9400
7844 1500 7698 30 9400
7499 1600 7698 30 9400
7698 2850 7839 30 9400
已選擇12行。
5、over（order by salary rows between 1 preceding and 2 following）-- 每行對應的資料視窗是之前行幅度值不超過1，之後行幅度值不超過2
sum(sal) over (partition by deptno order by sal
rows BETWEEN 1 PRECEDING AND 2 FOLLOWING) dd
傳回結果
EMPNO SAL MGR DEPTNO DD
7839 5000 10 7450
7902 3000 7566 20 5975
7900 950 7698 30 3450
7521 1250 7698 30 4950
7654 1250 7698 30 5600
7844 1500 7698 30 7200
7499 1600 7698 30 5950
![oracle中的start with connect by用法[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)