row_number()不能直接在where中使用，因其执行顺序在where之后，必须通过子查询或cte先生成序号再过滤；适用于需保留整行原始数据的去重场景，如按时间戳和设备id分组取最早/最新记录。
ROW_NUMBER() 为什么不能直接在 WHERE 中用？
因为 ROW_NUMBER() 是窗口函数，执行顺序在 WHERE 之后、ORDER BY 之前。你写 WHERE ROW_NUMBER() OVER (...) = 1 会报错：Invalid column name 'ROW_NUMBER()' 或类似提示。必须把它放到子查询或 CTE 里先算出序号，再外层过滤。
按时间戳 + 设备ID分组去重的典型写法
传感器数据常有毫秒级重复（同一 sensor_id 在 timestamp 相差 ≤10ms 内多次上报），需要保留最早/最新一条。常见做法是先用 ROW_NUMBER() 标记每组内的顺序，再取序号为 1 的记录：
SELECT sensor_id, timestamp, value, unit
FROM (
SELECT *,
ROW_NUMBER() OVER (
PARTITION BY sensor_id,
CAST(timestamp AS DATE),
DATEPART(HOUR, timestamp),
FLOOR(DATEPART(SECOND, timestamp) / 10.0)
ORDER BY timestamp ASC
) AS rn
FROM sensor_raw
WHERE timestamp >= '2024-06-01'
) t
WHERE rn = 1;
说明：
-
PARTITION BY按天+小时+每10秒切片，把高频噪声限制在小窗口内处理，避免全表排序拖慢性能 -
ORDER BY timestamp ASC保证取最早时间点；若要留最新，改用DESC - 注意
CAST(timestamp AS DATE)和DATEPART在 SQL Server 中有效；PostgreSQL 请用DATE(timestamp)和EXTRACT(HOUR FROM timestamp)
和 GROUP BY + MIN/MAX 对比：什么情况必须用 ROW_NUMBER()？
当你需要整行原始数据（比如同时保留 value 和 unit 字段），而不仅仅是聚合值时，GROUP BY 就不够用了——它无法保证 MIN(timestamp) 对应的那行的 unit 值也被一起取出。ROW_NUMBER() 能绑定整行上下文。
容易踩的坑：
- 没加
PARTITION BY导致全表只排一个序号，结果只剩一条数据 - 排序字段含 NULL（如某些传感器缺失
value），会让ROW_NUMBER()把 NULL 排前面或后面，影响“最早有效数据”的判断；建议加ORDER BY ISNULL(timestamp, '9999-12-31') ASC - SQL Server 中
datetime2和datetime精度不同，直接比较可能漏掉微秒级重复；用CONVERT(char(23), timestamp, 121)截断到毫秒再分组更稳
实时流式去重场景下 ROW_NUMBER() 的局限性
如果数据持续写入、需近实时去重（比如接 Kafka + Flink 或 SQL Server 的 CDC），单纯靠 ROW_NUMBER() 子查询无法增量维护。这时候它只能用于批处理清洗。真正要跑在流上，得配合物化视图、临时表缓存最近 N 分钟的 sensor_id + 时间窗口哈希，或者改用 LAG() 比较相邻行。
别指望一条 SQL 解决所有噪声——传感器协议层是否带序列号、硬件是否已做滤波、数据库是否有索引支撑 PARTITION BY 字段，这些都比窗口函数本身更影响最终效果。
