应用场景:查询一个注液系统运行过程数据中不同批次的最大液位,以及最大液位出现的时间。直接用GROUP BY的话,时间只返回每个分组的第一条,与最大液位出现的时间并不对应:
SELECT
batch,
level AS Max_Level,
collection_time AS Max_Level_Time
FROM
DataTable
GROUP BY
batch;
因此,采用构造窗口函数的方式重构一个查询目标表,增加字段rn,对原始记录进行行号标记,标记的依据是各分组按照特定方法的排序。这是目前最为简洁的方式。
可以采用RANK()或ROW_NUMBER()函数,RANK()可对值相同的行标记同样行号,适用于 可能有多条记录满足条件并需要全部返回的情况。
SELECT
batch,
level AS Max_Level,
collection_time AS Max_Level_Time
FROM (
SELECT
batch,
Level,
collection_time,
RANK() OVER(PARTITION BY batch ORDER BY level DESC) AS rn
FROM DataTable
) t
WHERE rn = 1;
当我们应用RANK() OVER(PARTITION BY batch ORDER BY level DESC, collection_time DESC)
,想象如下过程:
- 分区:首先,数据根据
batch
字段被分割成几个不同的“窗口”或“分区”。 - 排序:在每个分区内部,数据首先按
level
降序排序,这意味着液位最高的记录排在前面。如果有多个记录的液位相同,则这些记录再根据collection_time
降序排序,确保最新(时间最晚)的记录排在前面。 - 编号:接下来,对于每个分区内的行,从1开始给它们编号,这就是
ROW_NUMBER()
的功能。编号按照排序的顺序进行,液位最高且时间最新的记录得到编号1,之后的记录依次编号。
应用此逻辑后,上述表格的数据会附加一个rn
列,表示每行在所属批次内的排名情况:
batch | level | collection_time | rn |
101 | 50 | 2023-04-01 09:00 | 1 |
101 | 50 | 2023-04-01 08:00 | 2 |
102 | 75 | 2023-04-01 10:00 | 1 |
102 | 65 | 2023-04-01 11:00 | 2 |
103 | 90 | 2023-04-01 13:00 | 1 |
103 | 90 | 2023-04-01 12:00 | 2 |
通过这种方式,每个批次的最大液位及其对应的记录(如果有多条记录液位相同,则是时间最新的那条)被标记为rn=1
,从而便于后续查询直接筛选出这些记录。