如何在SQL中实现类似Pandas按组重采样时序数据的功能？

我有多传感器的时序数据，想在查询时按每个传感器单独重采样，用Pandas可以这么实现：

# df是pandas dataframe，索引为timestamp（datetime64类型）
df=df.groupby('group').resample('1H').mean()

我尝试用SQL写了类似逻辑：

SELECT  date_trunc('hour', timestamp) AS timestamp, avg(signal.value) AS value, source_name
FROM signal AS t_signal
GROUP BY(1, t_source.name)

但结果和Pandas不一致——Pandas的resample会为无数据的时段生成对应时间戳的行，而date_trunc只聚合已有数据的时段。想问SQL里有没有和Pandas resample功能完全一致的函数？

SQL本身没有和Pandas resample完全等效的内置函数，但可以通过生成完整时间序列+左连接的方式实现相同效果，核心思路是先为每个传感器生成所有需要的时间区间，再和原始数据关联聚合。

以PostgreSQL为例的实现步骤 #

1. 生成覆盖目标时间范围的连续小时序列
2. 获取所有传感器的唯一列表
3. 交叉连接生成每个传感器的完整时间轴，再左连接原始数据做聚合

完整代码：

WITH time_series AS (
  SELECT generate_series(
    (SELECT MIN(timestamp) FROM signal),
    (SELECT MAX(timestamp) FROM signal),
    '1 hour'::interval
  ) AS hour_timestamp
),
sensor_list AS (
  SELECT DISTINCT source_name FROM signal
)
SELECT
  ts.hour_timestamp AS timestamp,
  s.source_name,
  AVG(t_signal.value) AS value
FROM time_series ts
CROSS JOIN sensor_list s
LEFT JOIN signal t_signal
  ON ts.hour_timestamp = date_trunc('hour', t_signal.timestamp)
  AND s.source_name = t_signal.source_name
GROUP BY ts.hour_timestamp, s.source_name
ORDER BY s.source_name, ts.hour_timestamp;

其他SQL方言（如MySQL）的适配写法 #

用递归CTE生成时间序列，逻辑类似：

WITH RECURSIVE time_series AS (
  SELECT MIN(timestamp) AS hour_timestamp FROM signal
  UNION ALL
  SELECT hour_timestamp + INTERVAL 1 HOUR
  FROM time_series
  WHERE hour_timestamp < (SELECT MAX(timestamp) FROM signal)
),
sensor_list AS (
  SELECT DISTINCT source_name FROM signal
)
SELECT
  ts.hour_timestamp AS timestamp,
  s.source_name,
  AVG(t_signal.value) AS value
FROM time_series ts
CROSS JOIN sensor_list s
LEFT JOIN signal t_signal
  ON DATE_FORMAT(ts.hour_timestamp, '%Y-%m-%d %H:00:00') = DATE_FORMAT(t_signal.timestamp, '%Y-%m-%d %H:00:00')
  AND s.source_name = t_signal.source_name
GROUP BY ts.hour_timestamp, s.source_name
ORDER BY s.source_name, ts.hour_timestamp;

关键说明 #

• 先构建每个传感器+每个时间区间的全量组合，确保无数据的时段也有对应的行
• 左连接原始数据后，无数据时段的AVG会返回NULL，和Pandas resample的默认行为一致
• 如果需要填充默认值（比如0），可以将AVG(t_signal.value)改为COALESCE(AVG(t_signal.value), 0)

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