如何查询TYPE-NAME唯一组合的最新导入日期数据并倒序排列？

表结构与数据 #

DATA表 #

+---------+------------+-----------+--------+--------
| ID      |   FILES_ID |  TYPE     |  NAME  |  VALUE
+---------+------------+-----------+--------+--------
| 1       |      1     |  TYPE_A   |  A1    |  10
| 2       |      1     |  TYPE_A   |  A1    |  15
| 3       |      2     |  TYPE_B   |  B1    |  10
| 4       |      3     |  TYPE_C   |  CA    |  1
| 5       |      4     |  TYPE_B   |  BL    |  54
| 6       |      5     |  TYPE_C   |  CA    |  28
| 7       |      6     |  TYPE_B   |  B1    |  44

FILES表 #

+---------+---------------------
| ID      |  CREATED_DATE
+---------+---------------------
| 1       | 2023-03-10 19:52:41     
| 2       | 2023-03-11 19:52:41    
| 3       | 2023-03-12 19:52:41     
| 4       | 2023-03-13 19:52:41   
| 5       | 2023-03-14 19:52:41     
| 6       | 2023-03-15 19:52:41   

需求说明 #

需要获取DATA表中TYPE与NAME的唯一组合，同时得到该组合对应的最新导入日期（基于FILES表的CREATED_DATE字段），并按CREATED_DATE倒序排列。期望结果如下：

+---------+------------+---------------------
| TYPE    |   NAME     |  CREATED_DATE
+---------+------------+---------------------
| TYPE_B  |      B1    |  2023-03-15 19:52:41
| TYPE_C  |      CA    |  2023-03-14 19:52:41 
| TYPE_B  |      BL    |  2023-03-13 19:52:41   
| TYPE_A  |      A1    |  2023-03-11 19:52:41

可行解决方案 #

方案一：使用窗口函数 #

SELECT TYPE, NAME, CREATED_DATE
FROM (
    SELECT 
        d.TYPE, 
        d.NAME, 
        f.CREATED_DATE,
        ROW_NUMBER() OVER (PARTITION BY d.TYPE, d.NAME ORDER BY f.CREATED_DATE DESC) AS rn
    FROM DATA d
    JOIN FILES f ON d.FILES_ID = f.ID
) t
WHERE rn = 1
ORDER BY CREATED_DATE DESC;

方案二：分组聚合关联（性能更优） #

SELECT d.TYPE, d.NAME, f.CREATED_DATE
FROM (
    SELECT TYPE, NAME, MAX(FILES_ID) AS max_files_id
    FROM DATA
    GROUP BY TYPE, NAME
) d
JOIN FILES f ON d.max_files_id = f.ID
ORDER BY f.CREATED_DATE DESC;

原理说明 #

方案一（窗口函数） #

1. 将DATA与FILES表通过FILES_ID关联，获取每条数据对应的导入日期；
2. 用ROW_NUMBER()窗口函数按TYPE和NAME分组，在分组内按CREATED_DATE倒序排序，为每条记录分配序号rn；
3. 筛选出每个分组中rn=1的记录，即为该TYPE+NAME组合的最新导入日期记录；
4. 最后按CREATED_DATE倒序输出结果。

方案二（分组聚合） #

1. 观察FILES表可知，ID与CREATED_DATE呈递增对应关系（ID越大，日期越新），因此先在DATA表中按TYPE和NAME分组，提取每个组合对应的最大FILES_ID；
2. 将分组结果与FILES表关联，通过最大FILES_ID获取对应的最新CREATED_DATE；
3. 按CREATED_DATE倒序排序结果。该方案避免了窗口函数的全表扫描，在数据量较大时性能更优。

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