如何合并两张表并按timestamp小时维度统计操作行数？

我有两张表：

utilities表： #

items表： #

我需要将这两张表合并（避免ID冲突，可生成新ID），并通过date_trunc('hour', timestamp) as time, COUNT(*) as metric统计每小时的操作次数，期望得到如下结果：

期望结果： #

我尝试了以下SQL查询，但出现报错："Utilities.timestamp must appear in the GROUP BY ..."

WITH one AS (
  SELECT 
  date_trunc('hour', timestamp) as timeOne, 
  COUNT(*) as utilities_count
  FROM Utilities
  ORDER BY timeOne
),
two AS (
  SELECT 
  date_trunc('hour', timestamp) as timeTwo, 
  COUNT(*) as item_count
  FROM Items
  ORDER BY timeTwo
)
SELECT 
  SUM(utilities_count, item_count) as metric,
  timeOne as time
FROM one, two
ORDER BY 1;

请问如何实现正确的表合并与小时维度的操作次数统计？

1. 缺少GROUP BY子句：使用COUNT(*)聚合函数时，必须将date_trunc生成的时间列加入GROUP BY，否则数据库无法确定聚合维度。
2. 笛卡尔积连接：FROM one, two会生成两张表的笛卡尔积，完全偏离按小时合并统计的需求。
3. SUM函数用法错误：SUM仅接受单个参数，不能直接传入两个列相加，需用列值相加或分别求和后再合并。

方法一：先合并数据再统计（推荐） #

通过UNION ALL合并两张表的所有记录，用ROW_NUMBER()生成新ID避免冲突，再按小时分组统计，逻辑直观清晰：

WITH combined_data AS (
  SELECT 
    ROW_NUMBER() OVER (ORDER BY timestamp) AS new_id,
    timestamp,
    action
  FROM utilities
  UNION ALL
  SELECT 
    ROW_NUMBER() OVER (ORDER BY timestamp) + (SELECT COUNT(*) FROM utilities) AS new_id,
    timestamp,
    action
  FROM items
)
SELECT 
  date_trunc('hour', timestamp) AS time,
  COUNT(*) AS metric
FROM combined_data
GROUP BY time
ORDER BY time;

方法二：分别统计再合并 #

若需保留两张表各自的统计结果，可先分别按小时统计，再通过FULL JOIN按小时合并，最后求和：

WITH utilities_stats AS (
  SELECT 
    date_trunc('hour', timestamp) AS hour_time,
    COUNT(*) AS count
  FROM utilities
  GROUP BY hour_time
),
items_stats AS (
  SELECT 
    date_trunc('hour', timestamp) AS hour_time,
    COUNT(*) AS count
  FROM items
  GROUP BY hour_time
)
SELECT 
  COALESCE(u.hour_time, i.hour_time) AS time,
  COALESCE(u.count, 0) + COALESCE(i.count, 0) AS metric
FROM utilities_stats u
FULL JOIN items_stats i ON u.hour_time = i.hour_time
ORDER BY time;

内容的提问来源于stack exchange，提问作者Rafe