Oracle及窗口分析函数用法实例

窗口与分析函数用法实例一．现状与需求当前数据库表为各单位各年月下的统计分数值与案件数量。ixkeynamenyscorecount_l1caaab一支队201301981002caaac一支队2013021103caaad一支队201303961204caaae一支队201304901305caaaf一支队201305911406caaag一支队201306751507caaah一支队201307741608caaaj一支队201309731709caaak一支队2013107118010caaal一支队2013118819011caaam一支队2013128920012aaaab一支队20140195.521013aaaac一支队20140291.222014aaaad一支队2014039323015aaaae一支队2014049424016aaaaf一支队20140592.525017aaaag一支队2014069326018aaaah一支队2014079427019aaaai一支队2014089628020aaaaj一支队2014099329021aaaak一支队2014109130022aaaal一支队2014118531023aaaam一支队2014128632024caaan二支队2013019233025daaao二支队2013029334026daaa二支队2013038735027daaag二支队2013048636028daaah二支队2013058537029daaai二支队2013068438030daaaj二支队2013078339031daaak二支队2013088240032daaab二支队2013098141033daaac二支队2013108042034daaad二支队2013119943035daaae二支队2013129344036aaaan二支队2014018745037aaaao二支队2014028846038baaa二支队2014038647039baaag二支队2014048148040baaah二支队2014058449041baaai二支队2014068350042baaaj二支队2014078751043baaak二支队2014088252044baaab二支队2014098153045baaac二支队2014108054046baaad二支队2014117855047baaae二支队20141276560表-1[temp_score]上表中是一支队、二支队在2013年，2014年各月份的分数统计值与对应的案件数量。实际的生产库表中出现了这样的情况，为了进行验证，在一支队2013年的2月份分数为null，去掉了2013年8月份的数据记录。同时，在实际的库表中，每个月份有对应多类案件的考核分数与案件数量，这里只考虑一种案件类型。数据说明案件数量说明该单位在考核月份受理的案件总数量L。且该案件需要填写的数据字段个数C是一个定值(与案件类型有关)，故案件数量乘单个案件所需个数Y=L×C是所需要填写字段总数。分数生成说明该单位在考核月份受理的案件中，实际总填写的字段总数T占应填写字段总数的百分比为分数。𝑆=𝑇𝑌单条记录说明当前数据表记录的是某单位某月份对应的考核得分S及案件数量L。改造需求考核该单位从考核月所处年的第一个月到当前考核月份的累计分数。二．方法范例Oracle-PL/SQLselectts.name,ts.ny,ts.score,ts.count_l,sum(decode(ts.score,null,0,nvl(ts.count_l,0)))over(orderbyts.namedesc,ts.nyascrowsbetween(selectcount(*)fromtemp_scoretwheret.name=ts.nameandt.nylike''||substr(ts.ny,1,4)||'%'andt.nyts.ny)precedingand0following)total_l,sum(nvl(ts.score,0)*nvl(ts.count_l,0))over(orderbyts.namedesc,ts.nyascrowsbetween(selectcount(*)fromtemp_scoretwheret.name=ts.nameandt.nylike''||substr(ts.ny,1,4)||'%'andt.nyts.ny)precedingand0following)astotal_s,round((sum(ts.score*ts.count_l)over(orderbyts.namedesc,ts.nyascrowsbetween(selectcount(*)fromtemp_scoretwheret.name=ts.nameandt.nylike''||substr(ts.ny,1,4)||'%'andt.nyts.ny)precedingand0following))/(sum(decode(ts.score,null,0,nvl(ts.count_l,0)))over(orderbyts.namedesc,ts.nyascrowsbetween(selectcount(*)fromtemp_scoretwheret.name=ts.nameandt.nylike''||substr(ts.ny,1,4)||'%'andt.nyts.ny)precedingand0following)),2)fromtemp_scorets;三．方法说明1.求累计值的算法单月的分数统计𝑆=𝑇L×C累计月的统计，例如统计第i个月的分数，记该月的分数𝑆𝑖记该月的案件数量𝐿𝑖该单个案件需填写的字段个数C每个月的实际填写字段总数𝑇𝑖为𝑆𝑖·𝐶·𝐿𝑖100每个月应当填写的总字段数𝑌𝑖为𝐶·𝐿𝑖累计第i月的分支计算公式累计月分数=∑𝑇𝑖𝑖=𝑖𝑖=0∑𝑌𝑖𝑖=𝑖𝑖=0·100带入Ti、Yi表达式𝐶100∑𝑆𝑖·𝐿𝑖𝑖=𝑖𝑖=0𝐶∑𝐿𝑖𝑖=𝑖𝑖=0·100化简得∑𝑆𝑖·𝐿𝑖𝑖=𝑖𝑖=0∑𝐿𝑖𝑖=𝑖𝑖=0即：当年第一月到当月的累计得分是以案件数量为权，对各月份的分数进行加权平均当月累计得分=各月分数与案件数据乘积的累计求和各月案件数量的累计求和·100这样的好处就是避免重新计算各月字段的实际填写数和应该填写总数，其累计得分不再与某一类型的案件应填字段个数(C)有关，而仅仅依靠已有的案件数量和单月得分就可以求出累计得分，比较简捷。在二中的解决方案中利用的就是各单月分数与案件数量来进行乘积累加相除求分数。示例中即是采用这种求均值方式：sum(nvl(ts.score,0)*nvl(ts.count_l,0))over(...)/sum(decode(ts.score,null,0,nvl(ts.count_l,0)))over(...)2.求累计值的SQL语句序号单位年月案件数量案件累计1一支队2014012102102一支队2014022204303一支队2014032306604一支队2014042409005一支队20140525011506一支队20140626014107二支队2014014504508二支队2014024609109二支队201403470138010二支队201404480186011二支队201405490235012二支队2014065002850对月份分数的加权累计，对案件数量的累计都用到了月份的累计和。在数学上当然是容易处理的，但是在SQL语句中的处理需要用到窗口函数、分析函数sum(求和量)over(orderby分组且排序量，rows–-以行序号范围为窗口between窗口范围的起始边界precedingand窗口范围的结束边界following)其中sum内是目标求和量，是一行内的某一列或任意各列的计算表达式，窗口一旦限定之后，那么对窗口范围内所有数据行实施累加运算。orderby的排序功能为窗口函数的范围边界做好的铺垫，对于有多个层次的数据采用多个字段的orderby那么即可对数据进行合理的分层，窗口才好选定起、止边界。如果已经确知每个单位必然在每一年都会是规则year01,year02,year03,…year12各月份，那么一旦按照单位、月份的分组顺序之后,可以设定窗口，左（起）边界是当前月份数-1，右边界是0。即从当前月份（如果是201405）的前（5-1）=4个月开始，前四个月就是当年1月份，到右边界当前月份5月份止，累积求和。可以采用下面的语句selectts.name,ts.ny,ts.score,sum(ts.count_l)over(orderbyts.name,ts.nyascrowsbetweensubstr(ts.ny,5,6)-1precedingand0following)fromtemp_scorets;在实际的生产库中，为了防止某一年考核没有完整的12个月的情况，应该对左边界的限定值采用总数限定，而非直接从月份的显示数减去。只写窗口函数如下：over(orderbyts.namedesc,ts.nyascrowsbetween(selectcount(*)fromtemp_scoretwheret.name=ts.nameandt.nylike''||substr(ts.ny,1,4)||'%'andt.nyts.ny)precedingand0following)右边界仍为0左边界为查找到的与当前年份一致，月份比考核月小的总数量。3.求和量的限定在实际的表数据中，有出现分数为null的情况，这样在加权总数将略掉此项，而实际的案件总数却仍会加上，应该对此进行限定，如果分数为null的应该不再考虑该月该类型案件总数，且对为null的值应该算0sum(decode(ts.score,null,0,nvl(ts.count_l,0)))四．结果如图序号单位年月分数案件数量案件累计分数加权累计累计均值1一支队201301981001009800982一支队2013021101009800983一支队201303961202202132096.914一支队201304901303503302094.345一支队201305911404904576093.396一支队201306751506405701089.087一支队201307741608006885086.068一支队201309731709708126083.779一支队2013107118011509404081.7710一支队20131188190134011076082.6611一支队20131289200154012856083.4812一支队20140195.52102102005595.513一支队20140291.22204304011993.314一支队201403932306606150993.215一支队201404942409008406993.4116一支队20140592.5250115010719493.2117一支队20140693260141013137493.1718一支队20140794270168015675493.31