交易不为空的客户计数

Question

with hrk as (
select a.LBrCode,a.VcrAcctId,a.DrCr,a.FcyTrnAmt amt,
b.CustNo,b.AcctType,c.AMLRating x 
from vcr a 
inner join ac_mast b on a.LBrCode=b.LBrCode and substring(a.VcrAcctId,1,24)=substring(b.PrdAcctId,1,24)
left join cust_mast c on b.CustNo=c.CustNo
where a.entrydate  between '01-jan-2021' and '31-mar-2021'
)
select AcctType,
count(*) over() cust_cnt,
sum(case when (DrCr='C' and x=1)then amt else 0 end) low_credit,
sum(case when (DrCr='C' and x=1)then 1 else 0 end)   low_credit_cnt,
sum(case when (DrCr='D' and x=1)then amt else 0 end) low_debit,
sum(case when (DrCr='D' and x=1)then 1 else 0 end)   low_debit_cnt,

sum(case when (DrCr='C' and x=2)then amt else 0 end) Medium_credit,
sum(case when (DrCr='C' and x=2)then 1 else 0 end)   Medium_credit_cnt,
sum(case when (DrCr='D' and x=2)then amt else 0 end) Medium_debit,
sum(case when (DrCr='D' and x=2)then 1 else 0 end)   Medium_debit_cnt,

sum(case when (DrCr='C' and x=2)then amt else 0 end) high_credit,
sum(case when (DrCr='C' and x=2)then 1 else 0 end)   high_credit_cnt,
sum(case when (DrCr='D' and x=2)then amt else 0 end) high_debit,
sum(case when (DrCr='D' and x=2)then 1 else 0 end)   high_debit_cnt
from hrk
group by AcctType

--一个给定的客户可以有N个交易，AMLRating下可以有多个客户我想在每一个类别中都涉及Cusno的编号，即低、中、高，其中总和不为空

Answer 1

您有一个别名为cust_cnt的count(*) over()。更好的别名是acct_type_cnt，因为使用OVER子句可以计算结果中有多少行，即有多少AcctType。

如果你想统计客户，可以使用

count(*)

或使用

count(custono)

如果客户号码可以为空

或使用

count(distinct custono)

如果一个客户号码可以出现多次，而您只想数一次。

当然，您可以将此应用于条件聚合表达式，例如

count(distinct case when drcr = 'C' and x = 1 then custono end) low_credit_cnt,