选择带有“过度分区"的案例 [英] select case with "over partition by"
问题描述
正确的语法是什么,或者是否可以在选择和分区中使用 case?(使用 sql server 2012)
What's the correct syntax or is it possible to use case in a select and in it partition by? (using sql server 2012)
a = unique id
b = a string'xf%'
c = values
d = values
e = values
select
case
when b like 'xf%' then
(sum(c*e)/100*3423 over (partition by a))end as sumProduct
from #myTable
这是我需要解决的问题,这是我遇到的问题的一部分以前 sql 中的 sumProduct
this is something i need to solve which is a part of a problem i had previously sumProduct in sql
编辑:根据要求添加一些示例数据和预期结果创建表 #testing (b varchar (20), a date, c int, e int)
edit: upon request adding some sample data and expected result create table #testing (b varchar (20), a date, c int, e int)
b a c e sumProduct (expected)
xf1m 2015.03.02 1 3 (1*3 + 2*5 + 4*2 +3*6)*100/3423
xf3m 2015.03.02 2 5 (1*3 + 2*5 + 4*2 +3*6)/100*3423
xf5y 2015.03.02 4 2 (1*3 + 2*5 + 4*2 +3*6)/100*3423
xf10y 2015.03.02 3 6 (1*3 + 2*5 + 4*2 +3*6)/100*3423
adfe 2015.03.02 2 5 ---this is skipped because is not xf%
xf1m 2013.02.01 7 2 (7*2 + 1*8 + 10*1)/100*3423
xf15y 2013.02.01 1 8 (7*2 + 1*8 + 10*1)/100*3423
xf20y 2013.02.01 10 1 (7*2 + 1*8 + 10*1)/100*3423
我发现问题是这样的:即使不符合标准,也会将内容相加.在我的小提琴中,您可以看到 sumProduct 的结果是 49 而不是 39,因为添加了来自 adfe 的 2*5.我该怎么办?
I saw that the problem is this: stuff are being added up even if they don't correspond the criteria. In my fiddle you can see that the result for sumProduct is 49 instead of 39 because 2*5 from adfe is being added. What can I do about that?
create table #testing (b varchar (20), a date, c int, e int)
insert into #testing (b,a,c,e)
values
('xf1m','2015-03-02','1','3'),
('xf3m','2015-03-02','2','5'),
('xf5y','2015-03-02','4','2'),
('xf10y','2015-03-02','3','6'),
('adfe','2015-03-02','2','5'),
('xf1m','2013-02-01','7','2'),
('xf15y','2013-02-01','1','8'),
('xf20y','2013-02-01','10','1')
找到解决方案,写在下面作为答案
推荐答案
你不能在 Aggregate() OVER (PARTITION clause)
表达式内放置任意表达式 -所以把额外的计算移到外面:
You can't put arbitrary expressions within the Aggregate() OVER (PARTITION clause)
expression - so move the additional calculations outside:
select
case
when b like 'xf%' then
(sum(c*e) over (partition by a))/100*3423 end as sumProduct
from #myTable
这篇关于选择带有“过度分区"的案例的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!