bl双性强迫侵犯h_国产在线观看人成激情视频_蜜芽188_被诱拐的少孩全彩啪啪漫画

oracle的開窗函數(shù)

原創(chuàng)

創(chuàng)新互聯(lián)公司主要從事成都網(wǎng)站制作、做網(wǎng)站、外貿(mào)營銷網(wǎng)站建設(shè)、網(wǎng)頁設(shè)計、企業(yè)做網(wǎng)站、公司建網(wǎng)站等業(yè)務(wù)。立足成都服務(wù)仲巴,十余年網(wǎng)站建設(shè)經(jīng)驗,價格優(yōu)惠、服務(wù)專業(yè),歡迎來電咨詢建站服務(wù):18980820575

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

開窗函數(shù) 其實就是group by的另一種。它于group by的區(qū)別在于開窗函數(shù)可以分組列中排序其實就是加了一列影藏列。可以在group by中在分組的意思

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

翻譯

基本介紹:

Analytic_clause的語法如下：

[ query_partition_clause ] [ order_by_clause [ windowing_clause ] ]

這里：

query_partition_clause是查詢分組子句；

order_by_clause是分組排序子句；

windowing_clause是窗口范圍子句。

分析函數(shù)在查詢結(jié)果集確定之后才開始進行計算，Analytic_clause就是用來定義函數(shù)怎樣對查詢結(jié)果集進行分組計算的。

根據(jù)Oracle對查詢和分析函數(shù)的處理方法可知，在select和order by子句中都可以使用分析函數(shù)。

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)

分組，排序，窗口默認(rèn)為range between unbounded preceding and current row

3).        P

(query_partition_clause)

分組，不排序，沒有窗口

4).        Ow

(order_by_clause windowing_clause)

分組為整個查詢結(jié)果集，排序，定義窗口范圍

5).        O

(order_by_clause)

分組為整個查詢結(jié)果集，排序，窗口默認(rèn)為range between unbounded preceding and current row

6).        Null

()

分組為整個查詢結(jié)果集，不排序，沒有窗口

因為只有存在order_by_clause，才能有windowing_clause，故不存在如下兩種形式的組合：

pw(query_partition_clause windowing_clause)

w(windowing_clause)

總結(jié)：

1).        對于是否存在order_by_clause，分析函數(shù)可以分為兩類，含有order_by_clause的一般稱為windowing function，不含的稱為reporting function。

2).        Windowing function，對查詢結(jié)果集進行分組，排序，根據(jù)窗口范圍計算分組中每一行的函數(shù)結(jié)果。

3).        Reporting function，對查詢結(jié)果集進行分組，不排序，窗口范圍為整個分組，在每一個分組內(nèi)，計算整個分組的函數(shù)值，再將函數(shù)值分別賦給分組內(nèi)的每一行。

一、開窗函數(shù)

    開窗函數(shù)指定了分析函數(shù)工作的數(shù)據(jù)窗口大小，這個數(shù)據(jù)窗口大小可能會隨著行的變化而變化，舉例如下：

    1、over（order by salary） 按照salary排序進行累計，order by是個默認(rèn)的開窗函數(shù)。

    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)計小于等于當(dāng)前salary的salary總和。

    返回結(jié)果：

        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總和。

    返回結(jié)果：

     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)計各個部門內(nèi)部小于當(dāng)前SALARY的和。

    返回結(jié)果：

     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升序排序，匯總當(dāng)前SALARY到比當(dāng)前SALARY大100之間的SALARY總和。

    返回結(jié)果：

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行。                                                                                                                                

    解釋：返回前置行和當(dāng)前行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）-- 每行對應(yīng)的數(shù)據(jù)窗口是之前行幅度值不超過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;

    返回結(jié)果

        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行。

當(dāng)前題目：oracle的開窗函數(shù)
轉(zhuǎn)載源于：http://vcdvsql.cn/article34/pejgse.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)頁設(shè)計公司、、建站公司、網(wǎng)站建設(shè)、外貿(mào)建站、做網(wǎng)站

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)

網(wǎng)站托管運營