注,仅适用于MYSQL8以上,不然无法使用窗口函数
示例数据如下:
| lot_id | file_name | file_time | type |
|---|
| 13629111 | 13629111_20231101_165326.csv | 2023-11-01 16:53:18 | 1 |
| 13629111 | 13629111_20231110_095855.csv | 2023-11-10 09:58:31 | 2 |
| 13629111 | 13629111_20231110_084255.csv | 2023-11-10 08:41:56 | 2 |
| 13629111 | 13629111_20231110_093731.csv | 2023-11-10 08:41:56 | 2 |
| 1234 | 1234_20231102_134030.csv | 2023-11-02 13:40:21 | 1 |
| 1234 | 1234_20231102_171750.csv | 2023-11-02 17:17:31 | 1 |
| 1234 | 1234_20231106_122026.csv | 2023-11-06 12:18:36 | 2 |
若想取出数据,按lot_id和type分组,依据file_time最新的那条数据,获取到file_name. 即取出如下数据
| lot_id | file_name | file_time | type |
|---|
| 1234 | 1234_20231102_171750.csv | 2023-11-02 17:17:00 | 1 |
| 1234 | 1234_20231106_122026.csv | 2023-11-06 12:18:00 | 2 |
| 13629111 | 13629111_20231101_165326.csv | 2023-11-01 16:53:00 | 1 |
| 13629111 | 13629111_20231110_095855.csv | 2023-11-10 09:58:00 | 2 |
可使用如下sql完成
select lot_id,file_name,file_time,`type`,
ROW_NUMBER() over(PARTITION BY lot_id,`type` order by file_time desc) as `rank` FROM test;
select * from
(
select lot_id,file_name,file_time,`type`,
ROW_NUMBER() over(PARTITION BY lot_id,`type` order by file_time desc) as `rank` FROM test
)a where a.`rank` = 1
其中
- PARTITION BY lot_id,type. 语法类似与group by,指定分组要求.
- order by file_time desc即指定组内按什么排序.
- ROW_NUMBER()为窗口函数,记为组内的行号
注意:窗口函数不会改变原有的数据行数,可以认为是查看分析数据的一种方式,即在原数据上加上一列
上半句片段执行结果如下,rank表示按照需求的排序情况
| lot_id | file_name | file_time | type | rank |
|---|
| 13629111 | 13629111_20231101_165326.csv | 2023-11-01 16:53:18 | 1 | 1 |
| 13629111 | 13629111_20231110_095855.csv | 2023-11-10 09:58:31 | 2 | 1 |
| 13629111 | 13629111_20231110_084255.csv | 2023-11-10 08:41:56 | 2 | 2 |
| 13629111 | 13629111_20231110_093731.csv | 2023-11-10 08:41:56 | 2 | 3 |
| 1234 | 1234_20231102_134030.csv | 2023-11-02 13:40:21 | 1 | 2 |
| 1234 | 1234_20231102_171750.csv | 2023-11-02 17:17:31 | 1 | 1 |
| 1234 | 1234_20231106_122026.csv | 2023-11-06 12:18:36 | 2 | 1 |
