PostgreSQL去噪查询

Question

我使用的是AWS红移，它使用PostgreSQL作为sql语言。在将数据还原为一行的查询中，我需要一些帮助。

我的图式

Milestone
  -ItemId
  -LocationType //mapped to enum X, Y, Z
  -EventTime

里程碑可以有三种类型的位置X，Y或Z。我想把这个变成一行。

ItemId EventTime_X EventTime_Y EventTime_Z

我曾经尝试过这样的方法，但这只是需要很长一段时间才能运行。

select x.itemId, X.eventTime as EventTime_X, Y.eventTime as EventTime_Y, z.eventTime as EventTime_Z
from (select * from milestone where LocationType = 'X') as X
left outer join (select * from milestone where LocationType = 'Y') as Y on x.itemId=y.itemId
left outer join (select * from milestone where LocationType = 'Z') as Z on x.itemId=z.itemId

Answer 1

假设X、Y和Z有一个里程碑，您可以使用条件聚合来完成这一任务：

select m.itemId,
       max(case when m.LocationType = 'X' then eventTime end) as eventTime_X,
       max(case when m.LocationType = 'Y' then eventTime end) as eventTime_Y,
       max(case when m.LocationType = 'Z' then eventTime end) as eventTime_Z
from Milestone m
where LocationType in ('X', 'Y', 'Z')
group by m.itemId;