關(guān)于OVER子句多屬性的比較PIVOT聚合問(wèn)題

1、1 .OVER子句我們所了解的OVER字句不僅僅是用于排序上，其實(shí)它還可以用于標(biāo)量的聚合函數(shù)-為每 行計(jì)算聚合 , 不需要你去分組 .( 分組這個(gè)點(diǎn)我覺(jué)得很舒服) 我在上個(gè)筆記中其實(shí)已經(jīng)用到了這個(gè)用法 我再換個(gè)例子說(shuō)說(shuō)：create table #(id int ， a int )2，8select id，a，SUM(a) over (partitionAVG(a) over (partition COUN(Ta) over (partition MAX(a) over (partition from #by id )，- 分組總和by id )，- 分組平均by id )，- 分組計(jì)數(shù)by

2、id ) - 分組最大數(shù)insert # select1，2unionallselect1，3unionallselect1，4unionallselect1，5unionallselect2，2unionallselect2，4unionallselect2，8unionallselect/*id a111122222345248814 3 414 3 414 3 414 3 422 5 422 5 422 5 422 5 455558888*/面的 SUM(a) over (partitionby id) 等價(jià)于 ( select sum(a) from # group by id)我在上

3、篇學(xué)習(xí)筆記也提過(guò)了 ，用OVER()聚合的效率比子查詢(xún) 高非常多.2. 關(guān)于多屬性的比較create table #test (id int ， a int ， b int ， c int )insert #test select1，2，3，4unionallselect1，3，5，4unionallselect1，3，7，4unionallselect1，3，7，8unionallselect2,2,3,4unionallselect2,5,3,8unionallselect2,5,3,8unionallselect2,7,3,8unionallselect2,1,9,9-這個(gè)題目我們要求出

4、根據(jù) ID 分組,求出最大的一條記錄 . 這個(gè)所謂的最大就是先比 A 再 比B再比C要求結(jié)果：/*1,3,7,82,7,3,3*/- 如果有這么個(gè)寫(xiě)法 SQL 可以支持就好了 - | 可惜沒(méi)有select ID, MAX(a,b,C)from #testgroup by ID- 普通做法 , 依次比較select *from #test kwhere not exists ( select * from #testwhere id =k.id and (a >k.a or a=k.a and b >k.b or a =k.a and b =k.b and c >k.c)- 把

5、每個(gè)字符拼接起來(lái) ,一次比完大小 ,這里注意將字段要轉(zhuǎn)化成相同的長(zhǎng)度 , 這個(gè)的優(yōu)點(diǎn)是 無(wú)論是否有好的索引 , 因?yàn)樗粧呙枰淮蝧electID,a=SUBSTRING(COL,1,5), b=SUBSTRING(COL,6,5), c=SUBSTRING(COL,11,5) from (select ID,MAX( CAST(a as char (5) +CAST(b as char (5) +CAST(c as char (5) as col from #test group by ID) l3. PIVOT這個(gè)基本語(yǔ)法大家都知道 , 我只說(shuō)一點(diǎn)吧 , 你不能旋轉(zhuǎn)多列屬性 , 除非你提前在

6、 CTE 或者派 生表里面處理好 ，如下：create table #p(empid int , name varchar ( 10),val int )insert#p select1, 'a',2unionallselect1, 'a',3unionallselect1, 'a',4unionallselect1, 'b',4unionallselect2, 'a' ,5 union all select2, 'a' ,2 union all select2, 'b' ,2 -d

7、rop table #p; with cte as(select RTRIM(empid) +'_' +name as e_name,val from #p )select *from cte pivot( sum(val) for e_name in ( 1_a , 1_b , 2_a , 2_b ) l /*1_a 1_b 2_a 2_b 9 4 7 2 */至于 Unpivot 簡(jiǎn)單理解就是 Pivot 的反向操作 . 當(dāng)然注意一點(diǎn)： 被旋轉(zhuǎn)的數(shù)據(jù)必須具有相同 的數(shù)據(jù)類(lèi)型 .4。聚合問(wèn)題a. 連接字符串問(wèn)題：這個(gè)論壇里已經(jīng)寫(xiě)爛了 ，2000用函數(shù)，2005用XML PAT

8、H我就不寫(xiě) 這里提供個(gè)新的方法：create table #p(empid int ， name varchar (10)insert #p select1， 'a' union all select1， 'b' union all select1， 'c' union all select1， 'd' union all select2， 'a' union all select2， 't' union all select2， 'v'-drop table #p- 前提是 nam

9、e 不能在同一組里出現(xiàn)重復(fù) ， 且每組內(nèi)最大記錄數(shù)不是很大selectempid，name=MAX( case when rn =1 then name else '' end) +MAX( case when rn =2 then '，' +name else '' end)+MAX(case whenrn =3 then '，' +name else '' end) +MAX( case whenrn =4 then '，' +nameelse '' end) from (sel

10、ect empid，name，( select COUN(T* ) from #p where k.empid =empid and >=name) as rn from #p k )zgroup by empid /*empid name1a,b,c,d2a,t,v*/b. 組內(nèi)數(shù)字連乘create table #p(empid int , val varchar (10) insert #p select1,2 unionallselect1,6 unionallselect1,3 unionallselect1,4 unionallselect2,2 unionall

11、select2,3 unionallselect2,6 -drop table #p-loga(b)=x 等價(jià)于 power(a,x)=b-loga(v1*vn)=loga(v1)+loga(vn)-v1*v2*vn=power(10,log10(v1*v2*vn)=POWER(10,sum(LOG10(val)select empid,乘積 =POWE(R10, sum( LOG10(val)from #pgroup by empid/*empid 乘積1 1442 36*/c. 取中值 ( 昨天正好有個(gè)這個(gè)問(wèn)題的貼 )- 按 rp 分組、 ddsj 排序，選擇出每組擺在正中間的記錄行(若為

12、偶數(shù)行的話，選擇擺在正 中間的兩條記錄行)create table a(rq varchar (8), ddsj int )insertintoaselect'200805',30unionallselect'200805',40unionallselect'200805',50unionallselect'200805',20unionallselect'200806',250unionallselect'200806',200unionallselect'200806',310unionallselect'200806',100unionallselect'200806',130;- 方法 1：利用位置的收尾呼應(yīng) select rq,ddsj from( selectROW_NUMBER(o) ver (partitionby rqorderby ddsj )as rn1,ROW_NUMBER(o)ver (partition by rqorderby ddsjdesc )as rn2, * from a ) kwhereabs(rn1 -rn2) <=1- 方法2：利用位