时间戳

首页 > 解决方案 > postgres 9.2 按序数计算状态数

sql - postgres 9.2 按序数计算状态数

问题描述

假设我有这个结果集：

Location|Company|Account Number|First Check Date|First Status|Second Check Date|Second Status|Third Check Date|Third Status|Fourth Check Date|Fourth Status|Fifth Check Date|Fifth Status
Westeros|Acme Corp.|1014541|8/23/2018|Denied||||||||
Westeros|Acme Corp.|1014544|8/23/2018|Pending||||||||
Westeros|Acme Corp.|1014561|8/23/2018|Pending||||||||
Westeros|Sirius Cybernetics Corp|1014562|8/22/2018|Finalized||||||||
Westeros|Sirius Cybernetics Corp|1014573|8/23/2018|Pending||||||||
Westeros|MomCorp|1014579|8/22/2018|Denied||||||||
Dorne|MomCorp|1018984|8/20/2018|Pending||||||||
Dorne|Sirius Cybernetics Corp|1019017|8/22/2018|Pending||||||||
The North|MomCorp|1033591|8/16/2018|Pending||||||||
The North|MomCorp|1033910|8/16/2018|Not Found||||||||
The North|Amerigroup|1033964|8/16/2018|Partial Payment||||||||
The North|MomCorp|1034036|8/22/2018|Paid||||||||
The North|MomCorp|1034041|8/23/2018|Partial Payment||||||||
Iron Islands|AG White|1175033|8/22/2018|Pending||||||||
Stormlands|foobar|1220179|8/14/2018|Not Found|8/21/2018|Not Found||||||

这是从可以归结为的查询返回的

select Location,Company,Account Number,First Check Date,First Status,Second Check Date,Second Status,Third Check Date,Third Status,Fourth Check Date,Fourth Status,Fifth Check Date,Fifth Status
from subselect as results

我试图做的是在每个状态列中生成元素计数。例如，第一个状态计数为 15，第二个为 1，其余为 0。

我曾尝试使用over()并使用 a 来执行此操作，sum(case when 'first_status' then1 else 0 end)但似乎没有给出正确的结果。

关于如何做到这一点的任何建议？

标签： sqlpostgresql

解决方案

假设缺失值为NULL，您可以执行以下操作：

select r.*,
       count(status1) over () as status1_cnt,
       count(status2) over () as status2_cnt,
       . . .
from subselect as results

但是，空状态可能是其他状态，例如''. 如果是这样，一个简单的方法是：

select r.*,
       count(nullif(status1, '')) over () as status1_cnt,
       count(nullif(status2, '')) over () as status2_cnt,
       . . .
from subselect as results

推荐阅读