溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
原創(chuàng)
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的區(qū)別在于開窗函數可以分組列中排序其實就是加了一列影藏列。可以在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升序排序，統(tǒng)計小于等于當前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）按照部門分區(qū)。
SELECT
EMPLOYEE_ID, SALARY, MANAGER_ID, DEPARTMENT_ID,
SUM(SALARY) OVER (PARTITION BY DEPARTMENT_ID) DD
FROM INFA_TEST.EMPLOYEES EMP
ORDER BY DEPARTMENT_ID
功能：按DEPARTMENT_ID分區(qū)，匯總各個部門的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）按照部門分區(qū)。
SELECT
EMPLOYEE_ID, SALARY, MANAGER_ID, DEPARTMENT_ID,
SUM(SALARY) OVER (PARTITION BY DEPARTMENT_ID ORDER BY SALARY) DD
FROM INFA_TEST.EMPLOYEES EMP
ORDER BY DEPARTMENT_ID
功能：按DEPARTMENT_ID分區(qū)，按SALARY升序排序，統(tǒng)計各個部門內部小于當前SALARY的和。
返回結果：
EMPLOYEE_ID SALARY MANAGER_ID DEPARTMENT_ID DD
200 4400 101 10 4400
201 13000 100 20 6000
202 6000 201 20 19000
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
117 2800 114 30 24900
注意 DEPARTMENT_ID為20、30的DD值和2中的區(qū)別
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分區(qū)，按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相等，后續(xù)行比他大100的記錄，在SALARY列上求和。
上下邊界沒有限制：OVER (PARTITION BY DEPARTMENT_ID ORDER BY SALARY RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
SQL> select empno,sal,mgr,deptno,
sum(sal) over (partition by deptno order by sal
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
SQL> select empno,sal,mgr,deptno,
sum(sal) over (partition by deptno order by sal
rows BETWEEN 1 PRECEDING AND 2 FOLLOWING) dd
from emp;
返回結果
EMPNO SAL MGR DEPTNO DD
---------- ---------- ---------- ---------- ----------
7934 1300 7782 10 8750
7782 2450 7839 10 8750
7839 5000 10 7450
7369 800 7902 20 6775
7566 2975 7839 20 6775
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
7698 2850 7839 30 4450
已選擇12行。
免責聲明：本站發(fā)布的內容（圖片、視頻和文字）以原創(chuàng)、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
