我的问题是,正常查询,假设说是A表,数据查询出的结果为临时表1,从临时表1可以看出,Card字段有两个一样的,100001,那么我想查询出来的结果是把 SeqNum等于2和3的两个Card值为100001的这两个数据合并起来,统一为一条记录,这条新字段的Sndri是原来两条查询结果的Sndri之和,Rcvrit同理,SndAmt则是:例如原查询结果第2条 (2.5 * 8 + 0.2 * 2) / (2 + 8)
也就是(SndAmt1 * Sndri1 + SndAmt2 *Sndri2 ) /(Sndri1 + Sndri2 ) RcvAmt同理,其条数据不能变,那么我这条sql该怎么写select ID DATE SeqNum Card SndAmt Sndri RcvAmt Rcvri rs1 rs2 rs3 rs4 from A where DATE = '20160504' 正常的查询结果 如临时表1 我要查出临时表2的效果临时表1
ID DATE SeqNum Card SndAmt Sndri RcvAmt Rcvri rs1 rs2 rs3 rs4
1212 20160504 1
1213 20160504 2 100001 2.5 8 3.5 6
1214 20160504 3 100001 0.2 2 0.2 5
1215 20160504 4 200001 1.2 3 4.5 2 转变成如下所示:
临时表2
ID DATE SeqNum Card SndAmt Sndri RcvAmt Rcvri rs1 rs2 rs3 rs4
1212 20160504 1
1212 20160504 2 100001 2.04 10 2.0 11
1212 20160504 3 200001 1.2 3 4.5 2
默认天在组内,如果可以跨天把group by里的date去掉select first_value(min(id)) over(order by min(id)) id, date, card,
count(sum(1)) over(order by min(id)) seqnum, card,
sum(sndamt * sndri) / sum(sndri) sndamt, sum(sndri) sndri,
sum(rcvamt * rcvri) / sum(rcvri) rcvamt, sum(rcvri) rcvri
from t
group by date, card
ID DATE SeqNum Card SndAmt Sndri RcvAmt Rcvri rs1 rs2 rs3 rs4
1212 20160504 1
1212 20160504 2 100001 2.5 8 3.5 6
1212 20160504 3 100001 0.2 2 0.2 5
1212 20160504 4 200001 1.2 3 4.5 2 临时表2
ID DATE SeqNum Card SndAmt Sndri RcvAmt Rcvri rs1 rs2 rs3 rs4
1212 20160504 1
1212 20160504 2 100001 2.04 10 2.0 11
1212 20160504 3 200001 1.2 3 4.5 2