使用如下数据集-是否可以查询多个列来确定预定义的值?
例如,查询数据将根据每个eventId
的状态返回'Complete‘或'Pending’状态,即,如果所有eventId
status = 'Approved‘-事件的状态为'Complete’。否则,事件的状态将为“挂起”。
eventId
= 1
有三种状态-草稿、草稿、已提交-状态=待处理。
eventId
= 2
有两种状态-已批准,已批准-状态=完成。
eventId
= 3
有一个status - Submitted - Status = Pending。
eventId
= 4
有两种状态-草稿,草稿-状态=待定。
| activityId | eventId | status |
| ------------- | ------------- | ----- |
| 1 | 1 | Draft |
| 2 | 1 | Draft |
| 3 | 1 | Submitted |
| 4 | 2 | Approved |
| 5 | 2 | Approved |
| 6 | 3 | Submitted |
| 7 | 4 | Draft |
| 8 | 4 | Draft |
发布于 2018-07-24 03:41:40
对于每个事件,您可以计算状态为'Approved‘的行数,并将其与所有行数进行比较:
select
eventid,
string_agg(status, ', ') as statuses,
case when count(*) = count(case status when 'Approved' then 1 end)
then 'Complete'
else 'Pending' end as event_status
from my_table
group by 1
order by 1
eventid | statuses | event_status
---------+-------------------------+--------------
1 | Draft, Draft, Submitted | Pending
2 | Approved, Approved | Complete
3 | Submitted | Pending
4 | Draft, Draft | Pending
(4 rows)
当然,如果不需要第二列,也可以将其删除。
带有boolean aggregate:的更优雅的版本
select
eventid,
string_agg(status, ', ') statuses,
case when bool_and(status = 'Approved')
then 'Complete'
else 'Pending' end as event_status
from my_table
group by 1
order by 1
https://stackoverflow.com/questions/51485165
复制相似问题