【个人学习笔记8之--关于OVER子句多属性的比较 PIVOT 聚合问题】

这篇讲些聚合的手段

1.OVER子句@H_502_9@ 我们所了解的OVER字句不仅仅是用于排序上,其实它还可以用于标量的聚合函数--为每一行计算聚合,不需要你去分组.(分组这个点我觉得很舒服）@H_502_9@ 我在上个笔记中其实已经用到了这个用法我再换个例子说说：@H_502_9@ create table #(id int,a int)@H_502_9@ insert # select@H_502_9@ 1,2 union all select @H_502_9@ 1,3 union all select @H_502_9@ 1,4 union all select @H_502_9@ 1,5 union all select @H_502_9@ 2,2 union all select @H_502_9@ 2,4 union all select @H_502_9@ 2,8 union all select@H_502_9@ 2,8@H_502_9@ select id,a,@H_502_9@ SUM(a) over(partition by id ),--分组总和@H_502_9@ AVG(a) over(partition by id ),--分组平均@H_502_9@ COUNT(a) over(partition by id ),--分组计数@H_502_9@ MAX(a) over (partition by id )--分组最大数@H_502_9@ from # @H_502_9@ /*@H_502_9@ id a @H_502_9@ ----------- ----------- ----------- ----------- ----------- -----------@H_502_9@ 1 2 14 3 4 5@H_502_9@ 1 3 14 3 4 5@H_502_9@ 1 4 14 3 4 5@H_502_9@ 1 5 14 3 4 5@H_502_9@ 2 2 22 5 4 8@H_502_9@ 2 4 22 5 4 8@H_502_9@ 2 8 22 5 4 8@H_502_9@ 2 8 22 5 4 8@H_502_9@ */@H_502_9@ @H_502_9@ -------------@H_502_9@ 上面的SUM(a) over(partition by id)等价于(select sum(a) from # group by id)@H_502_9@ 我在上篇学习笔记也提过了,用OVER()聚合的效率比子查询高非常多.

2.关于多属性的比较@H_502_9@ create table #test (id int,a int,b int,c int)@H_502_9@ insert #test select @H_502_9@ 1,2,3,5,7,8 union all select @H_502_9@ 2,1,9,9 @H_502_9@ --这个题目我们要求出根据ID分组,求出最大的一条记录..这个所谓的最大就是先比A 再比B 再比C 要求结果：@H_502_9@ /*@H_502_9@ 1,8@H_502_9@ 2,3@H_502_9@ */@H_502_9@ --如果有这么个写法 sql 可以支持就好了-- || 可惜没有@H_502_9@ select ID,MAX(a,b,C) @H_502_9@ from #test@H_502_9@ group by ID@H_502_9@ --普通做法,依次比较@H_502_9@ select * @H_502_9@ from #test k@H_502_9@ where not exists(select * from #test @H_502_9@ where 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))@H_502_9@ --把每个字符拼接起来,一次比完大小,这里注意将字段要转化成相同的长度,这个的优点是无论是否有好的索引,因为它只扫描一次@H_502_9@ select @H_502_9@ ID,@H_502_9@ a=SUBSTRING(COL,5),@H_502_9@ b=SUBSTRING(COL,6,@H_502_9@ c=SUBSTRING(COL,11,5)@H_502_9@ from (@H_502_9@ select ID,@H_502_9@ MAX(CAST(a as char(5))+CAST(b as char(5))+CAST(c as char(5))) as col@H_502_9@ from #test @H_502_9@ group by ID@H_502_9@ ) l

@H_502_9@ 3.PIVOT@H_502_9@ 这个基本语法大家都知道,我只说一点吧,你不能旋转多列属性,除非你提前在CTE 或者派生表里面处理好，如下：@H_502_9@ create table #p(empid int,name varchar(10),val int)@H_502_9@ insert #p select @H_502_9@ 1,'a','b',2 --drop table #p @H_502_9@ ;with cte as@H_502_9@ (@H_502_9@ select RTRIM(empid)+'_'+name as e_name,val from #p @H_502_9@ )@H_502_9@ select *@H_502_9@ from cte pivot@H_502_9@ (sum(val) for e_name in ([1_a],[1_b],[2_a],[2_b])) l@H_502_9@ /*@H_502_9@ 1_a 1_b 2_a 2_b@H_502_9@ ----------- ----------- ----------- -----------@H_502_9@ 9 4 7 2@H_502_9@ */@H_502_9@ -------------@H_502_9@ 至于Unpivot 简单理解就是Pivot的反向操作.当然注意一点：被旋转的数据必须具有相同的数据类型.

4。聚合问题

@H_502_9@ a.连接字符串问题:这个论坛里已经写烂了,2000用函数,2005用XML PATH 我就不写@H_502_9@ 这里提供个新的方法：@H_502_9@ create table #p(empid int,name varchar(10))@H_502_9@ insert #p select @H_502_9@ 1,'a'union all select @H_502_9@ 1,'b' union all select @H_502_9@ 1,'c'union all select @H_502_9@ 1,'d'union all select @H_502_9@ 2,'a' union all select @H_502_9@ 2,'t'union all select @H_502_9@ 2,'v' --drop table #p @H_502_9@ --前提是name不能在同一组里出现重复,且每组内最大记录数不是很大@H_502_9@ select @H_502_9@ empid,@H_502_9@ name=MAX(case when rn=1 then name else '' end)+MAX(case when rn=2 then ','+name else '' end)@H_502_9@ +MAX(case when rn=3 then ','+name else '' end)+MAX(case when rn=4 then ','+name else '' end)@H_502_9@ from(@H_502_9@ select empid,name,@H_502_9@ (select COUNT(*) from #p where k.empid=empid and k.name>=name) as rn @H_502_9@ from #p k )z @H_502_9@ group by empid@H_502_9@ /*@H_502_9@ empid name@H_502_9@ ----------- -------------------------------------------@H_502_9@ 1 a,c,d@H_502_9@ 2 a,t,v

b.组内数字连乘@H_502_9@ create table #p(empid int,val varchar(10))@H_502_9@ insert #p select @H_502_9@ 1,2union all select @H_502_9@ 1,6union all select @H_502_9@ 1,3union all select @H_502_9@ 1,4union all select @H_502_9@ 2,2union all select @H_502_9@ 2,3union all select @H_502_9@ 2,6 --drop table #p @H_502_9@ --loga(b)=x 等价于 power(a,x)=b@H_502_9@ --loga(v1*......*vn)=loga(v1)+.....+loga(vn)@H_502_9@ --v1*v2*....*vn=power(10,log10(v1*v2*....*vn))==POWER(10,sum(LOG10(val)))@H_502_9@ select empid,@H_502_9@ 乘积=POWER(10,sum(LOG10(val)))@H_502_9@ from #p @H_502_9@ group by empid@H_502_9@ /*@H_502_9@ empid 乘积@H_502_9@ ----------- -----------@H_502_9@ 1 144@H_502_9@ 2 36@H_502_9@ */

c.取中值(昨天正好有个这个问题的贴)@H_502_9@ --按rp分组、ddsj排序，选择出每组摆在正中间的记录行（若为偶数行的话，选择摆在正中间的两条记录行）

create table a(rq varchar(8),ddsj int)@H_502_9@ insert into a@H_502_9@ select@H_502_9@ '200805',30 union all select@H_502_9@ '200805',40 union all select @H_502_9@ '200805',50 union all select @H_502_9@ '200805',20 union all select @H_502_9@ '200806',250 union all select@H_502_9@ '200806',200 union all select@H_502_9@ '200806',310 union all select@H_502_9@ '200806',100 union all select@H_502_9@ '200806',130; @H_502_9@ --方法1：利用位置的收尾呼应~@H_502_9@ select rq,ddsj=AVG(ddsj)@H_502_9@ from@H_502_9@ (select ROW_NUMBER() over(partition by rq order by ddsj ) as rn1,@H_502_9@ ROW_NUMBER() over(partition by rq order by ddsj desc ) as rn2@H_502_9@,* from a ) k @H_502_9@ where abs(rn1-rn2)<=1@H_502_9@ --方法2：利用位置的收尾呼应~@H_502_9@ select rq,ddsj=AVG(ddsj)@H_502_9@ from@H_502_9@ (select ROW_NUMBER() over(partition by rq order by ddsj) as rn1,@H_502_9@ COUNT(*) over(partition by rq) as rn@H_502_9@,* from a ) k @H_502_9@ where abs(2*rn1-rn-1)<=1

/*@H_502_9@ rq ddsj@H_502_9@ -------- -----------@H_502_9@ 200805 35@H_502_9@ 200806 200

【个人学习笔记8之--关于OVER子句 多属性的比较 PIVOT 聚合问题】

猜你在找的设计模式相关文章

【个人学习笔记8之--关于OVER子句多属性的比较 PIVOT 聚合问题】