求与pandas指定1小时滚动窗口唯一值计数代码等效的SQL实现

当然有等效的SQL实现啦！不过具体写法会根据你使用的SQL数据库（比如PostgreSQL、BigQuery、MySQL 8.0+等）略有不同，下面是几个常见场景的实现，完全匹配你pandas代码的逻辑——也就是对每一行的时间点，计算过去1小时内（包含当前行）目标列的唯一值数量：

1. PostgreSQL 实现 #

PostgreSQL暂时不支持在窗口函数中直接使用COUNT(DISTINCT)，所以我们可以用关联子查询的方式实现：

SELECT
  timestamp_col,
  x,
  (SELECT COUNT(DISTINCT t2.x)
   FROM your_table t2
   WHERE t2.timestamp_col >= t1.timestamp_col - INTERVAL '1 hour'
     AND t2.timestamp_col <= t1.timestamp_col) AS unique_count
FROM your_table t1
ORDER BY timestamp_col;

逻辑说明 #

对于每一行t1，我们查询所有时间落在t1时间前1小时到t1时间范围内的行，统计其中x列的唯一值数量。这和你pandas代码里rolling('1h')的窗口范围完全一致。

2. BigQuery 实现 #

BigQuery支持在窗口函数中使用COUNT(DISTINCT)，并且可以直接指定时间范围的滚动窗口：

SELECT
  timestamp_col,
  x,
  COUNT(DISTINCT x) OVER (
    ORDER BY UNIX_SECONDS(timestamp_col)
    RANGE BETWEEN 3600 PRECEDING AND CURRENT ROW
  ) AS unique_count
FROM your_table
ORDER BY timestamp_col;

逻辑说明 #

我们把时间列转换为秒级时间戳，用RANGE BETWEEN 3600 PRECEDING AND CURRENT ROW定义“当前时间往前推3600秒（1小时）”的滚动窗口，然后直接统计窗口内x的唯一值数量，写法更简洁。

3. MySQL 8.0+ 实现 #

MySQL 8.0及以上版本支持窗口函数，但同样在窗口中使用COUNT(DISTINCT)有一定限制，推荐用关联子查询的方式：

SELECT
  t1.timestamp_col,
  t1.x,
  (SELECT COUNT(DISTINCT t2.x)
   FROM your_table t2
   WHERE t2.timestamp_col >= DATE_SUB(t1.timestamp_col, INTERVAL 1 HOUR)
     AND t2.timestamp_col <= t1.timestamp_col) AS unique_count
FROM your_table t1
ORDER BY timestamp_col;

逻辑说明 #

和PostgreSQL的逻辑完全一致，只是用MySQL的DATE_SUB函数来计算1小时前的时间点。

关键注意事项 #

• 你的时间列（对应pandas里的时间索引）必须是timestamp/datetime类型，不能是字符串格式，否则无法正确计算时间范围。
• 对于你示例中同一时间点有多行的情况，上述SQL都会正确统计：比如05:20:19的第一行x=4，窗口内只有它自己，所以unique_count=1；第二行x=5，窗口包含前一行，所以unique_count=2，和你给出的示例结果完全匹配。

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