在使用联合all语句时，如何避免多次连接？

Question

我正在处理一个查询，我找到了一个点：

SELECT tpd.timestamp::Date,'Mon' AS Label,
       count(tpd.aggregated)
FROM tap.deving AS tpd INNER JOIN
  (select DATE_TRUNC('week', timestamp),  MAX(timestamp) AS max_timestamp
   from tap.deving
   group by DATE_TRUNC('week', timestamp)
  ) b
on tpd.timestamp = b.max_timestamp 
left JOIN ca.hardware AS ch ON tpd.dev = ch.name
left JOIN ca.sites AS css ON css.id = ch.id
WHERE (tpd.aggregated=TRUE)
  AND (css.country='USA') and (tpd.timestamp::date=now()::Date - interval '1 day') group by tpd.timestamp
UNION ALL
SELECT tpd.timestamp::date,'Tap but not' AS Label,
       count(tpd.tap)
FROM tap.deving AS tpd INNER JOIN
  (select DATE_TRUNC('week', timestamp),  MAX(timestamp) AS max_timestamp
   from tap.deving
   group by DATE_TRUNC('week', timestamp)
  ) b
on tpd.timestamp = b.max_timestamp 
left JOIN ca.hardware AS ch ON tpd.dev = ch.name
left JOIN ca.sites AS css ON css.id = ch.id
WHERE (tpd.tap=true)
  AND (tpd.aggregated=false) and (tpd.needs_to_be=true)
  AND (css.country='USA')  and (tpd.timestamp::date=now()::Date - interval '1 day') group by tpd.timestamp

我在许多SO帖子的帮助下写了这个查询，它变得相当混乱和超级慢。我不知道如何优化这个查询。

Answer 1

您可以尝试这个查询吗？

 SELECT tpd.timestamp::Date,CASE tpd.aggregated
      WHEN false THEN 'Tap but not'
      WHEN true THEN 'Mon' as Label,
       count(tpd.aggregated)
FROM tap.deving AS tpd INNER JOIN
  (select DATE_TRUNC('week', timestamp),  MAX(timestamp) AS max_timestamp
   from tap.deving
   group by DATE_TRUNC('week', timestamp)
  ) b
on tpd.timestamp = b.max_timestamp 
left JOIN ca.hardware AS ch ON tpd.dev = ch.name
left JOIN ca.sites AS css ON css.id = ch.id
WHERE ((tpd.aggregated=TRUE)  or ((tpd.tap=true)  AND (tpd.aggregated=false) and (tpd.needs_to_be=true)))
  AND (css.country='USA') and (tpd.timestamp::date=now()::Date - interval '1 day') group by tpd.timestamp;