分析函數(shù)詳細(xì)解析

-.z.目錄一、概述 51.1什么是分析函數(shù) 51.2分析函數(shù)的作用 51.3分析函數(shù)的優(yōu)缺點 6二、分析函數(shù)語法說明 63.1語法格式 63.2語句說明 73.2.1FUNCTION子句 73.2.2PARTITION子句 73.2.3ORDERBY子句 93.2.4WINDOW子句 14三、分析函數(shù)的分類 283.1排名分析函數(shù) 283.2聚合分析函數(shù) 283.3行比擬分析函數(shù) 283.4統(tǒng)計分析函數(shù) 293.5行連接分析函數(shù) 293.6其他分析函數(shù) 29四、分析函數(shù)使用說明 294.1排名分析函數(shù) 294.1.1ROW_NUMBER函數(shù)說明 304.1.2RANK函數(shù)說明 314.1.3DENSE_RANK函數(shù)說明 324.1.4FIRST/LAST函數(shù)說明 334.1.5FIRST_VALUE/LAST_VALUE函數(shù)說明 364.2聚合分析函數(shù) 394.2.1SUM函數(shù)說明 394.2.2MA*/MIN函數(shù)說明 404.2.3AVG函數(shù)說明 414.2.4COUNT函數(shù)說明 424.3行比擬分析函數(shù) 444.3.1LAG/LEAD函數(shù)說明 444.4統(tǒng)計分析函數(shù) 464.4.1RATIO_TO_REPORT函數(shù)說明 464.5行連接分析函數(shù) 484.5.1LISTAGG函數(shù)說明 494.6其它分析函數(shù) 504.6.1CORR函數(shù) 504.6.2COVAR_POP函數(shù) 514.6.3COVAR_SAMP函數(shù) 514.6.4CUME_DIST函數(shù) 514.6.5NTILE函數(shù) 514.6.6PERCENT_RANK函數(shù) 524.6.7PERCENTILE_CONT函數(shù) 524.6.8PERCENTILE_DISC函數(shù) 524.6.9REGR_(LinearRegression)Functions函數(shù) 534.6.10STDDEV函數(shù) 534.6.11STDDEV_POP函數(shù) 534.6.12STDDEV_SAMP函數(shù) 544.6.13VAR_POP函數(shù) 544.6.14VAR_SAMP函數(shù) 544.6.15VARIANCE函數(shù) 54五、分析函數(shù)專題案例 545.1專題案例之連續(xù)存在問題 555.2專題案例之填充缺失行問題 595.3專題案例之模擬SQL*PLUSBREAK 615.4專題案例之刪除重復(fù)行問題 625.5專題案例之行列轉(zhuǎn)換問題 645.6專題案例之累計數(shù)不能求和問題 675.7專題案例之字符串連接問題 695.8專題案例之集中度問題 725.9專題案例之計算稅負(fù)問題 75總結(jié) 99附件 99一、概述1.1什么是分析函數(shù)隨著信息化的逐步開展，企事業(yè)單位的經(jīng)營管理決策由以前的人工管理逐步向信息化管理進(jìn)展轉(zhuǎn)變。信息化前期的一個重要特點是數(shù)據(jù)的收集，正所謂"無數(shù)據(jù)，不真實〞、"用數(shù)據(jù)說話〞，表達(dá)出數(shù)據(jù)的重要性；信息化后期的一個重要特點就是數(shù)據(jù)分析的需求越來越多，通過對客觀數(shù)據(jù)的加工與分析，對企事業(yè)單位的生產(chǎn)經(jīng)營、管理監(jiān)控、重大決策等提供了重要的客觀依據(jù)，數(shù)據(jù)分析相關(guān)的產(chǎn)品孕育而生，比方我們公司的稅源管理平臺相關(guān)系列產(chǎn)品。面對形形色色的數(shù)據(jù)查詢統(tǒng)計的需求時，比方連續(xù)存在問題、累計求和問題，缺失行填充的問題等等，通過數(shù)據(jù)庫傳統(tǒng)的SQL語言，越來越力不從心，ORACLE從版本開場提供了一系列的分析函數(shù)，顧名思義，就是與數(shù)據(jù)分析相關(guān)的一些功能函數(shù)，是對現(xiàn)有SQL語言進(jìn)展數(shù)據(jù)分析統(tǒng)計時的功能擴(kuò)展，以滿足各種各樣的個性化的查詢統(tǒng)計的需要。分析函數(shù)與聚合函數(shù)非常相似，不同于聚合函數(shù)的地方在于它們每個分組序列均返回多行，局部聚合函數(shù)和分析函數(shù)是同一個命令，如果從語法格式上區(qū)分的話，沒加over()的即是聚合函數(shù)，加了over()即是分析函數(shù)。分析函數(shù)分組查詢出的行被稱為"窗口(window)〞，在根據(jù)over()執(zhí)行過程中，針對每一行都會重新定義窗口，這點一定要理解清楚，它是分析函數(shù)生成數(shù)據(jù)的原理，后續(xù)章節(jié)會詳細(xì)介紹。1.2分析函數(shù)的作用分析函數(shù)為我們提供了一種簡單高效的處理方式，在分析函數(shù)出現(xiàn)之前，我們必須使用自聯(lián)查詢、子查詢或者內(nèi)聯(lián)視圖，甚至是復(fù)雜的存儲過程實現(xiàn)的語句，現(xiàn)在只需要一條簡單的SQL語句就可以實現(xiàn)了。分析函數(shù)最重要的一個作用是允許你對一個數(shù)據(jù)集進(jìn)排序和篩選，這是SQL從來不能實現(xiàn)的。1.3分析函數(shù)的優(yōu)缺點大局部分析函數(shù)都要求排序操作，會消耗一定的系統(tǒng)資源，當(dāng)然大多可以進(jìn)展優(yōu)化。除了最后的Orderby子句之外，分析函數(shù)是在查詢中執(zhí)行的最后的操作集，這樣的話就不能直接在謂詞中使用分析函數(shù)，即不能在上面使用WHERE或HAVING子句。分析函數(shù)的優(yōu)點在于減少表和索引的訪問次數(shù)，實現(xiàn)復(fù)雜的行間計算，復(fù)雜聚合等，邏輯讀和COST是常規(guī)方法的一半，執(zhí)行SQL簡單，但是語法構(gòu)造相對復(fù)雜，多數(shù)函數(shù)都具有一樣的語法構(gòu)造，所以在本篇文章中先進(jìn)展統(tǒng)一介紹，后續(xù)單個函數(shù)介紹時就不過多說明函數(shù)語法構(gòu)造了。二、分析函數(shù)語法說明3.1語法格式FUNCTION_NAME(<argument>,<argument>...)

OVER

(<Partition-Clause><Order-by-Clause><WindowingClause>)實例說明：查詢納稅人2021年度適應(yīng)稅率的銷售收入按征期的累計收入情況。查詢SQL：〔本文使用的測試表的構(gòu)造與數(shù)據(jù)除系統(tǒng)表外均在本文最后附件中〕

SELECTT.NSRSBH,

T.ZQ_ID,

T.SYSL_*SE,SUM(T.SYSL_*SE)OVER(PARTITIONBYT.NSRSBHORDERBYT.ZQ_IDROWSBETWEENUNBOUNDEDPRECEDINGANDCURRENTROW)SYSL_*SE_HJ

FROMTMP_F*HS_TESTT

WHERET.BCBZ='N'

ANDT.ZQ_ID>='2021011';上述腳本高亮局部即為分析函數(shù)，其中：SUM：就是分析函數(shù)名FUNCTION_NAME〔SUM不是聚集函數(shù)嗎？〕；(T.SYSL_*SE)：是分析函數(shù)的參數(shù)<argument>，每個函數(shù)有0~3個參數(shù)，參數(shù)也可以是表達(dá)式，如：SUM(T.SYSL_*SE+T.JYZS_*SE)；OVER：是一個關(guān)鍵字，用于標(biāo)識分析函數(shù)，否則查詢分析器不能區(qū)別SUM〔〕到底是聚集函數(shù)還是分析函數(shù)，所以SUM在此時為分析函數(shù)；PARTITIONBYT.NSRSBH：是可選的分區(qū)子句<Partition-Clause>，如果不存在任何分區(qū)子句，則全部的結(jié)果集可看作一個單一的大區(qū)；ORDERBYT.ZQ_ID：是可選的ORDERBY子句<Order-by-Clause>，依靠已經(jīng)排序后的數(shù)據(jù)的分析函數(shù)，如用于訪問結(jié)果集中前一行和后一行的LAG和LEAD函數(shù)，必須使用；其它函數(shù)如AVG則不需要。在使用了任何排序的開窗函數(shù)時，該子句是強(qiáng)制性的，它指定了在計算分析函數(shù)時一組內(nèi)的數(shù)據(jù)是如何排序的。ROWSBETWEENUNBOUNDEDPRECEDINGANDCURRENTROW：是可選的WINDOWS子句<WindowingClause>，如有顯示的WINDOWS子句時，必須得有ORDERBY子句。有些分析函數(shù)不能有顯示的WINDOW，如ROW_NUMBER、RANK、DENSE_RANK等。3.2語句說明3.2.1FUNCTION子句ORACLE提供了30個左右的分析函數(shù)，后續(xù)章節(jié)會重點介紹這些分析函數(shù)的具體功能及如何使用。3.2.2PARTITION子句通過PARTITIONBY子句，將一樣的行聚合到一起成為一組，之后當(dāng)前行的分析函數(shù)計算就是在這行對應(yīng)的PARTITION里。每個分析函數(shù)都可以使用PARTITIONBY子句。每行在對應(yīng)的窗口內(nèi)，應(yīng)用分析函數(shù)，然后計算得到當(dāng)前行對應(yīng)的分析函數(shù)值。PARTITIONBY子句可以沒有，如果也沒有ORDERBY子句，則表示當(dāng)前行對應(yīng)的窗口范圍是所有行。通過下述查詢SQL及結(jié)果的展示，很容易理解PARTITION的作用。PARTITIONBY子句實例查詢語句：SELECTT.NSRSBH,

T.ZQ_ID,

T.SYSL_*SE,

SUM(T.SYSL_*SE)OVER(PARTITIONBYT.NSRSBH)SYSL_*SE_HJ1,SUM(T.SYSL_*SE)OVER()SYSL_*SE_HJ2

FROMTMP_F*HS_TESTT

WHERET.BCBZ='N'

ANDT.ZQ_ID>='2021011';結(jié)果展示：NSRSBHZQ_IDSYSL_*SESYSL_*SE_HJ1SYSL_*SE_HJ2查詢結(jié)果集中所有的查詢結(jié)果集中所有的納稅人SYSL_*SE的合計數(shù)區(qū)域1202101112719.66848721.081793082.772021021295042.73848721.081793082.7720210410848721.081793082.7720210510848721.081793082.772021031513874.91848721.081793082.77202106127083.78848721.081793082.77區(qū)域22021011115943.9714595.731793082.77區(qū)域2202102135353.17714595.731793082.772021031111574.11714595.731793082.772021061112130.72714595.731793082.77202105198921.89714595.731793082.772021041240671.94714595.731793082.77區(qū)域3202101159526.93229765.961793082.77區(qū)域320210210229765.961793082.77202103131598.3229765.961793082.77202104155244.02229765.961793082.77202105183396.71229765.961793082.7720210610229765.961793082.77解釋說明：一、SUM(T.SYSL_*SE)OVER(PARTITIONBYT.NSRSBH)SYSL_*SE_HJ1通過PARTITIONBYT.NSRSBH語句將結(jié)果集按照NSRSBH分成3個區(qū)域；通過SUM(T.SYSL_*SE)分析函數(shù)計算每個區(qū)域內(nèi)的SYSL_*SE的合計數(shù)，并填充到每個區(qū)域內(nèi)的SYSL_*SE_HJ1列中。二、SUM(T.SYSL_*SE)OVER()SYSL_*SE_HJ21、假設(shè)不指定PARTITIONBY子句，也不指定ORDERBY子句，則當(dāng)前行對應(yīng)的窗口范圍為結(jié)果集的所有記錄，SUM(T.SYSL_*SE)OVER()的結(jié)果即為結(jié)果集中所有記錄數(shù)的合計數(shù)，并填充到SYSL_*SE_HJ2列中。3.2.3ORDERBY子句分析函數(shù)中ORDER

BY的功能是添加一個默認(rèn)的開窗子句，這意味著計算中所使用的行的集合是當(dāng)前分區(qū)中當(dāng)前行和前面所有的行，即當(dāng)前行默認(rèn)窗口是當(dāng)前行所屬的PARTITION第1行到當(dāng)前行〔根據(jù)ORDER

BY順序指定〕，沒有ORDERBY子句時，默認(rèn)的窗口就是對應(yīng)所屬PARTITION的所有行，假設(shè)也無PARTITIONBY子句，則為查詢結(jié)果集中所有的行。ORDERBY默認(rèn)是RANGE類型的窗口，對應(yīng)邏輯窗口，保證分析函數(shù)值的唯一性，但是對與排名類的分析函數(shù)比擬特殊，因為排名函數(shù)不能帶WINDOW子句。ORDERBY如果有多個排序鍵且是RANGE窗口，則必須要求對應(yīng)的窗口是當(dāng)前PARTITION所有行、第1行到當(dāng)前行、當(dāng)前行到當(dāng)前PARTITION最后一行或當(dāng)前行到當(dāng)前行，在WINDOW子句說明中詳細(xì)介紹。通過下述查詢SQL及結(jié)果的展示，很容易理解ORDERBY的作用。1、帶PARTITIONBY子句與ORDERBY子句的分析函數(shù)實例1、查詢語句：--帶PARTITIONBY子句與ORDERBY子句的分析函數(shù)SELECTT.NSRSBH,T.ZQ_ID,T.SYSL_*SE,SUM(T.SYSL_*SE)OVER(PARTITIONBYT.NSRSBHORDERBYT.ZQ_ID)SYSL_*SE_HJ1,SUM(T.SYSL_*SE)OVER(PARTITIONBYT.NSRSBHORDERBYT.ZQ_IDRANGEBETWEENUNBOUNDEDPRECEDINGANDCURRENTROW)SYSL_*SE_HJ2FROMTMP_F*HS_TESTTWHERET.BCBZ='N'ANDT.ZQ_ID>='2021011';1、結(jié)果展示：NSRSBHZQ_IDSYSL_*SESYSL_*SE_HJ1SYSL_*SE_HJ2區(qū)域1區(qū)域1202101112719.6612719.6612719.662021021295042.73307762.39307762.392021031513874.91821637.3821637.320210410821637.3821637.320210510821637.3821637.3202106127083.78848721.08848721.08區(qū)域區(qū)域22021011115943.9115943.9115943.9202102135353.17151297.07151297.072021031111574.11262871.18262871.182021041240671.94503543.12503543.12202105198921.89602465.01602465.012021061112130.72714595.73714595.73區(qū)域區(qū)域3202101159526.9359526.9359526.932021021059526.9359526.93202103131598.391125.2391125.23202104155244.02146369.25146369.25202105183396.71229765.96229765.9620210610229765.96229765.961、解釋說明：SUM(T.SYSL_*SE)OVER(PARTITIONBYT.NSRSBHORDERBYT.ZQ_ID)SYSL_*SE_HJ1此分析函數(shù)的意思為先按照.NSRSBH分為3個區(qū)域，然后按照ZQ_ID從小到大的順序排序，按照上述排序順序，取本區(qū)域內(nèi)第一行到當(dāng)前行SYSL_*SE的累計數(shù)。如的SYSL_*SE_HJ1：SYSL_*SE_HJ1〔2021011〕=SYSL_*SE〔2021011〕=12719.66；SYSL_*SE_HJ1〔2021021〕=SYSL_*SE〔2021011〕+SYSL_*SE〔2021021〕=12719.66+295042.73=307762.39；SYSL_*SE_HJ1〔2021031〕=SYSL_*SE〔2021011〕+SYSL_*SE〔2021021〕+SYSL_*SE〔2021031〕=12719.66+295042.73+513874.91=821637.3…………………….SYSL_*SE_HJ1〔2021061〕=SYSL_*SE〔2021011〕+SYSL_*SE〔2021021〕+SYSL_*SE〔2021031〕+SYSL_*SE〔2021041〕+SYSL_*SE〔2021051〕+SYSL_*SE〔2021061〕=12719.66+295042.73+513874.91+0+0+27083.78=848721.08不同的分區(qū)內(nèi)，利用此分析函數(shù)重新累計就和，互不干預(yù)，如區(qū)域1內(nèi)的SYSL_*SE_HJ1字段是根據(jù)區(qū)域1內(nèi)的順序?qū)YSL_*SE累計求和，區(qū)域2內(nèi)的SYSL_*SE_HJ1字段是根據(jù)區(qū)域2內(nèi)的順序?qū)YSL_*SE累計求和，區(qū)域3內(nèi)的SYSL_*SE_HJ1字段是根據(jù)區(qū)域3內(nèi)的順序?qū)YSL_*SE累計求和；二、SUM(T.SYSL_*SE)OVER(PARTITIONBYT.NSRSBHORDERBYT.ZQ_IDRANGEBETWEENUNBOUNDEDPRECEDINGANDCURRENTROW)SYSL_*SE_HJ21、顯示的WINDOW子句RANGEBETWEENUNBOUNDEDPRECEDINGANDCURRENTROW，此語句根據(jù)字面含義很容易理解，即取當(dāng)前窗口內(nèi)記錄集最前面的記錄到當(dāng)前記錄所在行這一塊區(qū)域，分析函數(shù)利用此區(qū)域進(jìn)展計算此行記錄的分析函數(shù)值，并填充到SYSL_*SE_HJ2；2、仔細(xì)觀察，不難發(fā)現(xiàn)SYSL_*SE_HJ1與SYSL_*SE_HJ2字段值完全一樣，這就證明了ORDERBY子句默認(rèn)的開窗規(guī)則即按照當(dāng)前區(qū)域內(nèi)排序后的結(jié)果的第一條記錄到當(dāng)前行所在記錄的這一塊區(qū)域；3、上述WINDOW子句只是顯示開窗子句中的一種方法，下一章節(jié)重點介紹WINDOWS開窗子句；2、不帶PARTITIONBY子句，帶ORDERBY子句的分析函數(shù)實例我們再擴(kuò)展一下，如果將上述查詢語句中的PARTITIONBY子句去掉，會是什么結(jié)果呢？2、修改后的查詢SQL：--不帶PARTITIONBY子句，帶ORDERBY子句的分析函數(shù)SELECTT.NSRSBH,T.ZQ_ID,T.SYSL_*SE,SUM(T.SYSL_*SE)OVER(ORDERBYT.ZQ_ID)SYSL_*SE_HJ1,SUM(T.SYSL_*SE)OVER(ORDERBYT.ZQ_IDRANGEBETWEENUNBOUNDEDPRECEDINGANDCURRENTROW)SYSL_*SE_HJ2,SUM(T.SYSL_*SE)OVER(ORDERBYT.ZQ_IDROWSBETWEENUNBOUNDEDPRECEDINGANDCURRENTROW)SYSL_*SE_HJ3FROMTMP_F*HS_TESTTWHERET.BCBZ='N'ANDT.ZQ_ID>='2021011';2、結(jié)果展示：NSRSBHZQ_IDSYSL_*SESYSL_*SE_HJ1SYSL_*SE_HJ2SYSL_*SE_HJ3ZQ_ID為ZQ_ID為2021011的SYSL_*SE的合計數(shù)2021011115943.9188190.49188190.49175470.83202101112719.66188190.49188190.49188190.49ZQ_ID為ZQ_ID為2021011、2021021的SYSL_*SE的合計數(shù)2021021295042.73518586.39518586.39483233.22202102135353.17518586.39518586.39518586.39ZQ_ID為ZQ_ID為2021011、2021021、2021031的SYSL_*SE的合計數(shù)2021031111574.111175633.711175633.71661758.82021031513874.911175633.711175633.711175633.71…202104155244.021471549.671471549.671230877.73…202104101471549.671471549.671230877.732021041240671.941471549.671471549.671471549.67…202105183396.711653868.271653868.271554946.38…202105101653868.271653868.271554946.38202105198921.891653868.271653868.271653868.27…202106101793082.771793082.771653868.27…202106127083.781793082.771793082.771680952.052021061112130.721793082.771793082.771793082.772、解釋說明：去掉PARTITIONBY子句后，ORDERBY子句的排序集合為查詢結(jié)果中所有記錄，即按照ZQ_ID將所有記錄進(jìn)展排序；SYSL_*SE_HJ1、SYSL_*SE_HJ2在每個征期內(nèi)數(shù)據(jù)都是一樣的，是按照征期的排序順序進(jìn)展累計求和的，不是按照每條記錄的順序進(jìn)展累計求和的，即：SYSL_*SE_HJ1〔2021011〕=SYSL_*SE_HJ2〔2021011〕=SUM〔SYSL_*SE〔2021011〕〕=59526.93+115943.9+12719.66=188190.49；SYSL_*SE_HJ1〔2021021〕=SYSL_*SE_HJ2〔2021021〕=SUM〔SYSL_*SE〔2021011〕〕+SUM〔SYSL_*SE〔2021021〕〕=188190.49+0+295042.73+35353.17=518586.39………………SYSL_*SE_HJ1〔2021061〕=SYSL_*SE_HJ2〔2021061〕=SUM〔SYSL_*SE〔2021011〕〕+SUM〔SYSL_*SE〔2021021〕〕+SUM〔SYSL_*SE〔2021031〕〕+SUM〔SYSL_*SE〔2021041〕〕+SUM〔SYSL_*SE〔2021051〕〕+SUM〔SYSL_*SE〔2021061〕〕=1793082.773、SYSL_*SE_HJ3是按照排序后的每一行記錄進(jìn)展累加求和，與SYSL_*SE_HJ2的區(qū)別一個是RANGE類型的開窗函數(shù)，一個是ROWS類型的開窗函數(shù)，在下一章節(jié)會重點介紹這兩種方式的區(qū)別。3、不帶PARTITIONBY子句，帶ORDERBY子句，有多個排序鍵的分析函數(shù)實例我們再擴(kuò)展一下，如果的ORDERBY子句上有多個排序鍵，會是什么結(jié)果呢？3、修改后的查詢SQL：--不帶PARTITIONBY子句，帶ORDERBY子句，有多個排序鍵的分析函數(shù)SELECTT.NSRSBH,T.ZQ_ID,T.SYSL_*SE,--按照NSRSBH與ZQ_ID進(jìn)展排序SUM(T.SYSL_*SE)OVER(ORDERBYT.NSRSBH,T.ZQ_ID)SYSL_*SE_HJ1,--只按照NSRSBH進(jìn)展排序SUM(T.SYSL_*SE)OVER(ORDERBYT.NSRSBH)SYSL_*SE_HJ2FROMTMP_F*HS_TESTTWHERET.BCBZ='N'ANDT.ZQ_ID>='2021011';3、結(jié)果展示：NSRSBHZQ_IDSYSL_*SESYSL_*SE_HJ1SYSL_*SE_HJ2202101112719.6612719.66848721.082021021295042.73307762.39848721.082021031513874.91821637.3848721.0820210410821637.3848721.0820210510821637.3848721.08202106127083.78848721.08848721.082021011115943.9964664.981563316.81202102135353.171000018.151563316.812021031111574.111111592.261563316.812021041240671.941352264.21563316.81202105198921.891451186.091563316.812021061112130.721563316.811563316.81202101159526.931622843.741793082.77202102101622843.741793082.77202103131598.31654442.041793082.77202104155244.021709686.061793082.77202105183396.711793082.771793082.77202106101793082.771793082.773、解釋說明：SUM(T.SYSL_*SE)OVER(ORDERBYT.NSRSBH,T.ZQ_ID)SYSL_*SE_HJ1因為結(jié)果集中，通過NSRSBH與ZQ_ID能夠唯一確定一條記錄，所以SYSL_*SE_HJ1的結(jié)果即為按照結(jié)果集中的順序?qū)YSL_*SE進(jìn)展累計求和，并將數(shù)據(jù)填充到相應(yīng)的行次中。SUM(T.SYSL_*SE)OVER(ORDERBYT.NSRSBH)SYSL_*SE_HJ2因為結(jié)果集中，通過NSRSBH不能夠唯一確定一條記錄，假設(shè)按NSRSBH進(jìn)展排序進(jìn)展分析函數(shù)計算時，是按照稅號進(jìn)展累計求和的，一樣的稅號SUM(T.SYSL_*SE)，不同的稅號進(jìn)展累加，詳細(xì)計算過程見第2個SQL的解釋說明?？偨Y(jié)：我們可以這樣理解ORDERBY子句的含義，即將ORDERBY想象為GROUPBY，如果GROUPBY后記錄唯一，則默認(rèn)的窗口范圍就是當(dāng)前區(qū)域結(jié)果集記錄的第一行到當(dāng)前行；如果不唯一，即GROUPBY后有兩條一樣的記錄，則先將一樣的記錄合并，比方例子中的SUM(T.SYSL_*SE)，然后再按照結(jié)果集中的順序〔相當(dāng)于GROUPBY后剔除重復(fù)的順序〕，進(jìn)展分析函數(shù)的計算。上述理解只適用于ORDERBY默認(rèn)的排序方法，因為有一種顯示的開窗方法方法比擬特殊，即ROWS類型的開窗方式，它是按照物理行進(jìn)展分析函數(shù)計算的，可以參考第2個SQL。3.2.4WINDOW子句WINDOW子句給出了一個定義變化或固定的數(shù)據(jù)窗口的方法，分析函數(shù)將對這些數(shù)據(jù)進(jìn)展操作，默認(rèn)的窗口是一個固定的窗口，僅僅在一組的第一行開場，一直繼續(xù)到當(dāng)前行。如果有顯式WINDOW子句，必須有ORDERBY子句。有的分析函數(shù)不能有顯式WINDOW，如ROW_NUMBER、RANK,DENSE_RANK等。WINDOW可以指定是邏輯窗口還是物理窗口，邏輯窗口用RANGE關(guān)鍵字進(jìn)展限定，物理窗口用ROWS關(guān)鍵字進(jìn)展限定。RANGE窗口是默認(rèn)窗口，反響的是行與行之間的邏輯關(guān)系〔當(dāng)前行的排序鍵的VALUE加或減邏輯偏移量得到當(dāng)前行對應(yīng)的邏輯窗口的范圍〕。ROWS窗口是物理窗口，也就是排序后，按排序結(jié)果的行號對應(yīng)確定窗口，指定ROWS窗口的話，必須要求排序鍵唯一，否則結(jié)果可能不穩(wěn)定。當(dāng)前行對應(yīng)的分析函數(shù)結(jié)果是根據(jù)當(dāng)前行對應(yīng)的窗口〔邏輯OR物理〕范圍計算的。理解窗口的含義對分析函數(shù)的使用至關(guān)重要。窗口只有起點的，起點必須是CURRENTROW之前〔包含CURRENTROW〕。窗口內(nèi)數(shù)據(jù)移動順序必須是按照排序順序，從上到下，否則要么報錯，要么結(jié)果有問題。3.2.4.1WINDOW子句之窗口范圍語法格式：ROWS/RANGE<指定窗口起點子句1><指定窗口終點子句2>考前須知：WINDOW子句可以只指定窗口起點子句，不指定窗口終點子句；WINDOW子句不能只指定窗口終點子句，而不指定窗口起點子句，即指定窗口子句假設(shè)只有一個，則SQL編譯器默認(rèn)將此語句做為指定起點的窗口語句；指定窗口起點時，窗口的起點必須是CURRENTROW之前〔包含CURRENTROW〕；窗口內(nèi)數(shù)據(jù)移動順序必須是按照排序好的順序從上到下；WINDOW子句提供了多種指定窗口起點和終點范圍的形式，具體見下表：序號例如說明1ROWS/RANGEUNBOUNDEDPRECEDING終點CURRENTROW，相當(dāng)于BETWEEN起點ANDCURRENTROW2ROWS/RANGECURRENTROW相當(dāng)于BETWEENCURRENTROWANDCURRENTROW3ROWS/RANGEvalue_e*prPRECEDING相當(dāng)于BETWEENvalue_e*prPRECEDINGANDCURRENTROW4ROWS/RANGEBETWEENUNBOUNDEDPRECEDINGANDUNBOUNDEDFOLLOWING和無ORDERBY一樣，等價于沒有寫ORDERBY和WINDOW子句5ROWS/RANGEBETWEENUNBOUNDEDPRECEDINGANDCURRENTROW同ROWS/RANGEUNBOUNDEDPRECEDING，即相當(dāng)于BETWEEN起點ANDCURRENTROW6ROWS/RANGEBETWEENCURRENTROWANDvalue_e*prFOLLOWING當(dāng)前行到對應(yīng)value_e*p偏移量的行7ROWS/RANGEBETWEENvalue_e*prFOLLOWINGANDvalue_e*pr1FOLLOWING起點是FOLLOWING,終點必須是FOLLOWING8ROWS/RANGEBETWEENvalue_e*prPRECEDINGANDvalue_e*pr1FOLLOWING終點是PRECEDING,起點必須是PRECEDING9ROWS/RANGEvalue_e*prFOLLOWING報錯，無效窗口，因為窗口的起點必須在當(dāng)前行之前10ROWS/RANGEBETWEENCURRENTROWANDvalue_e*prPRECEDING報錯，無效窗口，窗口的順序必須是從上到下，不能終點的位置比起點還要靠上下面通過實例的方式，介紹一下各種開窗方式的窗口范圍，實例都以ROWS方式進(jìn)展展示。1、ROWS/RANGEUNBOUNDEDPRECEDING1.1、查詢SQL：SELECTT.NSRSBH,T.LRRQ,T.SYSL_*SE,SUM(T.SYSL_*SE)OVER(ORDERBYT.LRRQROWSUNBOUNDEDPRECEDING)WINDOW_ROWSFROMTMP_F*HS_TEST_DATET;1.2、結(jié)果展示UNBOUNDEDPRECEDINGNSRSBHLRRQSYSL_*SEWINDOW_ROWSUNBOUNDEDPRECEDING-2021/1/1115943.9115943.92021/1/235353.17151297.072021/1/3111574.11262871.18CURRENTROW2021/1/4240671.94503543.12CURRENTROW2021/1/598921.89602465.012021/1/6112130.72714595.731.3、解釋說明ROWS/RANGEUNBOUNDEDPRECEDING：只指定窗口起點，不指定窗口終點，根據(jù)UNBOUNDEDPRECEDING字面意思理解為，向上無限制，轉(zhuǎn)義為向上查找一直到第一條記錄為止，窗口的終點假設(shè)不指定，默認(rèn)是CURRENTROW，窗口的范圍為結(jié)果集中記錄的第一行到當(dāng)前行這個區(qū)域；查詢的結(jié)果中LRRQ為2021/1/5的記錄〔第5行〕的WINDOW_ROWS字段，計算過程為WINDOW_ROWS(2021/5/1)=SYSL_*SE(2021/1/1)+SYSL_*SE(2021/1/2)+SYSL_*SE(2021/1/3)+SYSL_*SE(2021/1/4)+SYSL_*SE(2021/1/5)=115943.9+35353.17+111574.11+240671.94+98921.89+112130.722、ROWS/RANGECURRENTROW2.1、查詢SQL：SELECTT.NSRSBH,T.LRRQ,T.SYSL_*SE,SUM(T.SYSL_*SE)OVER(ORDERBYT.LRRQROWSCURRENTROW)WINDOW_ROWSFROMTMP_F*HS_TEST_DATET;2.2、結(jié)果展示NSRSBHLRRQSYSL_*SEWINDOW_ROWSCURRENTROW到CURRENTROW到CURRENTROWCURRENTROW到CURRENTROW2021/1/1115943.9115943.9CURRENTROW到CURRENTROW2021/1/235353.1735353.172021/1/3111574.11111574.112021/1/4240671.94240671.94CURRENTROW到CURRENTROW到CURRENTROW2021/1/6112130.72112130.722.3、解釋說明ROWS/RANGECURRENTROW：通過CURRENTROW指定窗口的起點，不指定窗口的終點〔默認(rèn)為CURRENTROW〕，即每條記錄的窗口為本條記錄本身；每條記錄的的計算過程為：WINDOW_ROWS(2021/1/1)=SYSL_*SE(2021/1/1)=115943.9WINDOW_ROWS(2021/1/2)=SYSL_*SE(2021/1/2)=35353.17WINDOW_ROWS(2021/1/3)=SYSL_*SE(2021/1/3)=111574.11WINDOW_ROWS(2021/1/4)=SYSL_*SE(2021/1/4)=240671.94WINDOW_ROWS(2021/1/5)=SYSL_*SE(2021/1/5)=98921.89WINDOW_ROWS(2021/1/6)=SYSL_*SE(2021/1/6)=112130.723、ROWS/RANGEvalue_e*prPRECEDING3.1、查詢SQL：SELECTT.NSRSBH,T.LRRQ,T.SYSL_*SE,SUM(T.SYSL_*SE)OVER(ORDERBYT.LRRQROWS1PRECEDING)WINDOW_ROWSFROMTMP_F*HS_TEST_DATET;3.2、結(jié)果展示NSRSBHLRRQSYSL_*SEWINDOW_ROWS第1行第1行+第2行第2行+第3第2行+第3行2021/1/3111574.11146927.282021/1/4240671.94352246.05第5行+第5行+第6行2021/1/6112130.72211052.613.3、解釋說明ROWS/RANGEvalue_e*prPRECEDING：1、通過ROWS1PRECEDING指定窗口的起點為當(dāng)前記錄的上一條記錄，不指定窗口的終點〔默認(rèn)為CURRENTROW〕，即每條記錄的窗口為本條記錄的上一條記錄到當(dāng)前記錄這個區(qū)域；ROWS/RANGEvalue_e*prPRECEDING中的value_e*pr可以任意指定；2、每條記錄的的計算過程為：WINDOW_ROWS(2021/1/1)=SYSL_*SE(2021/1/1)=115943.9WINDOW_ROWS(2021/1/2)=SYSL_*SE(2021/1/1)+SYSL_*SE(2021/1/2)=151297.07WINDOW_ROWS(2021/1/3)=SYSL_*SE(2021/1/2)+SYSL_*SE(2021/1/3)=146927.28……WINDOW_ROWS(2021/1/6)=SYSL_*SE(2021/1/5)+SYSL_*SE(2021/1/6)=211052.614、ROWS/RANGEBETWEENUNBOUNDEDPRECEDINGANDUNBOUNDEDFOLLOWING4.1、查詢SQL：SELECTT.NSRSBH,T.LRRQ,T.SYSL_*SE,SUM(T.SYSL_*SE)OVER(ORDERBYT.LRRQROWSBETWEENUNBOUNDEDPRECEDINGANDUNBOUNDEDFOLLOWING)WINDOW_ROWSFROMTMP_F*HS_TEST_DATET;4.2、結(jié)果展示UNBOUNDEDPRECEDINGNSRSBHLRRQSYSL_*SEWINDOW_ROWSUNBOUNDEDPRECEDING2021/1/1115943.9714595.732021/1/235353.17714595.732021/1/3111574.11714595.732021/1/4240671.94714595.732021/1/598921.89714595.73UNBOUNDEDFOLLOWING2021/1/6112130.72714595.73UNBOUNDEDFOLLOWING4.3、解釋說明ROWS/RANGEBETWEENUNBOUNDEDPRECEDINGANDUNBOUNDEDFOLLOWING：通過上述語句指定窗口的起點和終點均為無限制，相當(dāng)于窗口的范圍為整個結(jié)果集，此語法的結(jié)果等同于沒有ORDERBY的分析函數(shù)，即等同于以下SQL：SELECTT.NSRSBH,T.LRRQ,T.SYSL_*SE,SUM(T.SYSL_*SE)OVER()WINDOW_ROWSFROMTMP_F*HS_TEST_DATET;2、每條記錄的的計算過程為：WINDOW_ROWS(2021/1/1)=WINDOW_ROWS(2021/1/2)=WINDOW_ROWS(2021/1/3)=WINDOW_ROWS(2021/1/4)=WINDOW_ROWS(2021/1/5)=WINDOW_ROWS(2021/1/6)=SYSL_*SE(2021/1/1)+SYSL_*SE(2021/1/2)+SYSL_*SE(2021/1/3)+SYSL_*SE(2021/1/4)+SYSL_*SE(2021/1/5)+SYSL_*SE(2021/1/6)=714595.735、ROWS/RANGEBETWEENUNBOUNDEDPRECEDINGANDCURRENTROW5.1、查詢SQL：SELECTT.NSRSBH,T.LRRQ,T.SYSL_*SE,SUM(T.SYSL_*SE)OVER(ORDERBYT.LRRQROWSBETWEENUNBOUNDEDPRECEDINGANDCURRENTROW)WINDOW_ROWSFROMTMP_F*HS_TEST_DATET;5.2、結(jié)果展示UNBOUNDEDPRECEDINGNSRSBHLRRQSYSL_*SEWINDOW_ROWSUNBOUNDEDPRECEDING-2021/1/1115943.9115943.92021/1/235353.17151297.072021/1/3111574.11262871.18CURRENTROW2021/1/4240671.94503543.12CURRENTROW2021/1/598921.89602465.012021/1/6112130.72714595.735.3、解釋說明ROWS/RANGEBETWEENUNBOUNDEDPRECEDINGANDCURRENTROW:1、通過上述語句指定窗口的起點和終點與通過ROWSUNBOUNDEDPRECEDING指定窗口范圍的結(jié)果是一樣的，證明了ROWSUNBOUNDEDPRECEDING不指定終點的情況，終點默認(rèn)為CURRENTROW；2、每條記錄的的計算過程為：WINDOW_ROWS(2021/5/1)=SYSL_*SE(2021/1/1)+SYSL_*SE(2021/1/2)+SYSL_*SE(2021/1/3)+SYSL_*SE(2021/1/4)+SYSL_*SE(2021/1/5)=115943.9+35353.17+111574.11+240671.94+98921.89+112130.72=714595.736、ROWS/RANGEBETWEENCURRENTROWANDvalue_e*prFOLLOWING6.1、查詢SQL：SELECTT.NSRSBH,T.LRRQ,T.SYSL_*SE,SUM(T.SYSL_*SE)OVER(ORDERBYT.LRRQROWSBETWEENCURRENTROWAND1FOLLOWING)WINDOW_ROWSFROMTMP_F*HS_TEST_DATET;6.2、結(jié)果展示NSRSBHLRRQSYSL_*SEWINDOW_ROWS第1第1行+第2行第2行+第3行第2行+第3行2021/1/235353.17146927.282021/1/3111574.11352246.05第5行第5行+第6行第6行2021/1/598921.89211052.61第6行2021/1/6112130.72112130.726.3、解釋說明ROWSBETWEENCURRENTROWAND1FOLLOWING:1、通過上述語句指定窗口的起點為CURRENTROW，終點為CURRENTROW的下一條記錄；2、每條記錄的的計算過程為：WINDOW_ROWS(2021/1/1)=SYSL_*SE(2021/1/1)+SYSL_*SE(2021/1/2)=151297.07WINDOW_ROWS(2021/1/2)=SYSL_*SE(2021/1/2)+SYSL_*SE(2021/1/3)=146927.28WINDOW_ROWS(2021/1/3)=SYSL_*SE(2021/1/3)+SYSL_*SE(2021/1/4)=352246.05……WINDOW_ROWS(2021/1/6)=SYSL_*SE(2021/1/6)=112130.727、ROWS/RANGEBETWEENvalue_e*prFOLLOWINGANDvalue_e*pr1FOLLOWING7.1、查詢SQL：SELECTT.NSRSBH,T.LRRQ,T.SYSL_*SE,SUM(T.SYSL_*SE)OVER(ORDERBYT.LRRQROWSBETWEEN1FOLLOWINGAND2FOLLOWING)WINDOW_ROWSFROMTMP_F*HS_TEST_DATET;7.2、結(jié)果展示NSRSBHLRRQSYSL_*SEWINDOW_ROWS第2第2行+第3行第3行+第4行第3行+第4行第4行第4行+第5行第5第5行+第6行第6第6行NULL2021/1/598921.89112130.72NULL2021/1/6112130.727.3、解釋說明ORDERBYT.LRRQROWSBETWEEN1FOLLOWINGAND2FOLLOWING:1、通過上述語句指定窗口的起點為CURRENTROW向下偏移1行的記錄，終點為CURRENTROW向下偏移2行的記錄；2、每條記錄的的計算過程為：WINDOW_ROWS(2021/1/1)=SYSL_*SE(2021/1/2)+SYSL_*SE(2021/1/3)=146927.28WINDOW_ROWS(2021/1/2)=SYSL_*SE(2021/1/3)+SYSL_*SE(2021/1/4)=352246.05WINDOW_ROWS(2021/1/3)=SYSL_*SE(2021/1/4)+SYSL_*SE(2021/1/5)=339593.83WINDOW_ROWS(2021/1/4)=SYSL_*SE(2021/1/5)+SYSL_*SE(2021/1/6)=211052.61WINDOW_ROWS(2021/1/5)=SYSL_*SE(2021/1/6)=112130.72WINDOW_ROWS(2021/1/6)=NULL8、ROWS/RANGEBETWEENvalue_e*prPRECEDINGANDvalue_e*pr1FOLLOWING8.1、查詢SQL：SELECTT.NSRSBH,T.LRRQ,T.SYSL_*SE,SUM(T.SYSL_*SE)OVER(ORDERBYT.LRRQROWSBETWEEN1PRECEDINGAND1FOLLOWING)WINDOW_ROWSFROMTMP_F*HS_TEST_DATET;8.2、結(jié)果展示NSRSBHLRRQSYSL_*SEWINDOW_ROWS1PRECEDING1PRECEDINGCURRENTROW2021/1/1115943.9151297.07CURRENTROW1FOLLOWING2021/1/235353.17262871.181FOLLOWING2021/1/3111574.11387599.222021/1/4240671.94451167.942021/1/598921.89451724.552021/1/6112130.72211052.618.3、解釋說明ROWSBETWEEN1PRECEDINGAND1FOLLOWING:1、通過上述語句指定窗口的起點為CURRENTROW向上偏移1條記錄，終點為CURRENTROW的下一條記錄；2、每條記錄的的計算過程為：WINDOW_ROWS(2021/1/1)=SYSL_*SE(2021/1/1)+SYSL_*SE(2021/1/2)=151297.07WINDOW_ROWS(2021/1/2)=SYSL_*SE(2021/1/1)+SYSL_*SE(2021/1/2)+SYSL_*SE(2021/1/3)=262871.18WINDOW_ROWS(2021/1/3)=SYSL_*SE(2021/1/2)+SYSL_*SE(2021/1/3)+SYSL_*SE(2021/1/4)=352246.05=387599.22……WINDOW_ROWS(2021/1/6)=SYSL_*SE(2021/1/5)+SYSL_*SE(2021/1/6)=211052.61WINDOW子句之窗口范圍總結(jié)：我們可以利用關(guān)鍵字PRECEDING〔向上指定范圍〕、FOLLOWING〔向下指定范圍〕、CURRENTROW〔當(dāng)前行〕、UNBOUNDED〔無限制〕實現(xiàn)幾乎任意范圍的窗口定義?！沧⒁獠贿B續(xù)的窗口無法指定〕3.2.4.2WINDOW子句之RANGE、ROWSWINDOW子句有兩種類型，分別為RANGE類型和ROWS類型，RANGE窗口是默認(rèn)窗口，反響的是行與行之間的邏輯關(guān)系〔當(dāng)前行的排序鍵的VALUE加或減邏輯偏移量得到當(dāng)前行對應(yīng)的邏輯窗口的范圍〕，是邏輯窗口。ROWS窗口是物理窗口，也就是排序后，按排序結(jié)果的行號對應(yīng)確定窗口，指定ROWS窗口的話，必須要求排序鍵唯一，否則結(jié)果可能不穩(wěn)定。RANGE窗口僅對數(shù)據(jù)類型為NUMBER型和DATE型的起作用，因為不可能從VARCHAR2中增加或減去N個單元，另外的限制是ORDERBY中只能有一列，因而范圍實際上是一維的，不能在N維空間中。利用ROW分區(qū)，就沒有RANGE分區(qū)那樣的限制了，數(shù)據(jù)可以是任何類型，且ORDERBY可以包括很多列。我們通過實例看一下RANGE和ROWS的區(qū)別。1、RANGE、ROWS區(qū)別說明實例之一1.1、查詢SQL：SELECTT.NSRSBH,T.ZQ_ID,T.SYSL_*SE,SUM(T.SYSL_*SE)OVER(ORDERBYT.ZQ_ID)SYSL_*SE_HJ1,SUM(T.SYSL_*SE)OVER(ORDERBYT.ZQ_IDRANGEBETWEENUNBOUNDEDPRECEDINGANDCURRENTROW)SYSL_*SE_HJ2,SUM(T.SYSL_*SE)OVER(ORDERBYT.ZQ_IDROWSBETWEENUNBOUNDEDPRECEDINGANDCURRENTROW)SYSL_*SE_HJ3FROMTMP_F*HS_TESTTWHERET.BCBZ='N'ANDT.ZQ_ID>='2021011';1.2、結(jié)果展示：NSRSBHZQ_IDSYSL_*SESYSL_*SE_HJ1SYSL_*SE_HJ2SYSL_*SE_HJ3物理第1物理第1行物理第2行邏輯第一行物理第2行邏輯第一行物理第3行物理第3行202101112719.66188190.49188190.49188190.49邏輯第二行…………………20210210518586.39518586.39188190.49邏輯第二行…………………2021021295042.73518586.39518586.39483233.22202102135353.17518586.39518586.39518586.39邏輯第三行202103131598.31175