UNION ALL 日期在同一行

我在 Postgres 中將多個表聯合在一起，其中時間戳略有不同，但如果我四捨五入到小時，則相同。如果日期相同，是否可以讓每個工會顯示在同一行？目前我得到每個日期的重複行。

select date_trunc('hour', TO_TIMESTAMP(created / 1000)::timestamp), 
COALESCE(count(*), 0) as "1489",
null as "1616"
from actions
where group_id = 1489
and TO_TIMESTAMP(created / 1000)::timestamp >= current_date-7
group by 1
union all
select date_trunc('hour', TO_TIMESTAMP(created / 1000)::timestamp), 
null as "1489",
COALESCE(count(*), 0) as "1616"
from actions
where group_id = 1616
and TO_TIMESTAMP(created / 1000)::timestamp >= current_date-7
group by 1
order by 1

它返回：

您可以像這樣使用FULL JOIN：

SELECT
   COALESCE(a1.t, a2.t) as t,
   a1."1489",
   a2."1616"
FROM
(
   select 
       date_trunc('hour', TO_TIMESTAMP(created / 1000)::timestamp) as t, 
       COALESCE(count(*), 0) as "1489"
   from actions
   where group_id = 1489
       and TO_TIMESTAMP(created / 1000)::timestamp >= current_date-7
   group by 1
) as a1
FULL JOIN
(
   select 
       date_trunc('hour', TO_TIMESTAMP(created / 1000)::timestamp) as t, 
       COALESCE(count(*), 0) as "1616"
   from actions
   where group_id = 1616
       and TO_TIMESTAMP(created / 1000)::timestamp >= current_date-7
   group by 1
) as a2 ON (a1.t=a2.t)
ORDER BY 1;

或者只使用CASE：

SELECT
   date_trunc('hour', TO_TIMESTAMP(created / 1000)::timestamp),
   SUM(CASE WHEN group_id=1489 THEN 1 ELSE 0 END) as "1489",
   SUM(CASE WHEN group_id=1616 THEN 1 ELSE 0 END) as "1616"
FROM actions
WHERE 
   group_id in (1489, 1616)
   AND TO_TIMESTAMP(created / 1000)::timestamp >= current_date-7
GROUP BY 1
ORDER BY 1;

引用自：https://dba.stackexchange.com/questions/175119