我正在尝试为Amazon Redshift中的多个状态计数生成一条记录(每个帐户)。基本上,我想要一个数据透视表。但是我不喜欢这种方法,也不知道是否存在更好的方法。
这就是我在做什么:
Select g1.account,
sum(case when g1.status = 'PASS' then 1 else 0 end) as status_cnt_pass,
sum(case when g1.status = 'NEW' then 1 else 0 end) as cnt_new,
sum(case when g1.status = 'FAIL' then 1 else 0 end) as cnt_fail,
sum(case when g1.status = 'TEST' then 1 else 0 end) as cnt_test,
sum(case when coalesce(TRIM(g1.status ), '') != '' and g1.status not in('PASS', 'NEW', 'FAIL','TEST') then 1 else 0 end) as cnt_other,
sum(case when coalesce(TRIM(g1.status ), '') = '' then 1 else 0 end) as cnt_none
输出:
account cnt_new cnt_fail cnt_test cnt_other cnt_none
foo 41 3 16 2 0
bar 105 17 5 1 1
有没有一种更好的方法可以在不维护列表的情况下获得其他值的计数?
不在('PASS','NEW','FAIL','TEST')
另外,如果先前的条件为true或status = null(或空白),我想停止检查其他状态值。
我没有这个表,也无法更改数据的填充方式。
我认为没有办法。但是您可以使用::
以下命令简化语法:
select g1.account,
sum( (g1.status = 'PASS')::int ) as status_cnt_pass,
sum( (g1.status = 'NEW')::int ) as cnt_new,
sum( (g1.status = 'FAIL')::int ) as cnt_fail,
sum( (g1.status = 'TEST')::int ) as cnt_test,
sum( trim(g1.status) not in ('', 'PASS', 'NEW', 'FAIL', 'TEST')::int ) as cnt_other,
sum( (coalesce(trim(g1.status ), '') = '')::int ) as cnt_none
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句