如何用SQL高效获取每个ID的各属性最新值并转宽表？

问题描述 #

现有一张Attribute_history表，结构及数据如下：

ID   Attribute_Name  Attribute_Val  Time Stamp
---  --------------  -------------  ----------
1    Color           Red            2022/09/28 01:00
2    Color           Blue           2022/09/28 01:30
1    Length          3              2022/09/28 01:00
2    Length          4              2022/09/28 01:30
1    Diameter        5              2022/09/28 01:00
2    Diameter        10             2022/09/28 01:30
2    Diameter        11             2022/09/28 01:32

需要生成一张宽表，展示每个ID对应的各属性值，若同一ID和属性存在多条记录，取Time Stamp最新的那条，目标表结构如下：

ID    Color   Length  Diameter
----  ------  ------- -------- 
1     Red     3       5 
2     Blue    4       11 

当前采用多层嵌套SELECT语句实现，需多次查询同一张表，效率低下且无法批量处理所有属性，原查询示例如下：

SELECT
    COLOR, DIAMETER, DATE_
FROM
(
    SELECT
        COLORS.COLOR, ATTR.ATTRIBUTE_NAME AS DIAMETER, ATTR.TIME_STAMP AS DATE_, RANK() OVER (PARTITION BY COLORS.COLOR ORDER BY ATTR.TIME_STAMP DESC) DATE_RANK
    FROM
    (
        SELECT
            ATTRIBUTE_HISTORY.ATTRIBUTE_VAL
        FROM
            ATTRIBUTE_HISTORY
        WHERE
            ATTRIBUTE_HISTORY.ATTRIBUTE_NAME = 'Color'
        GROUP BY ATTRIBUTE_HISTORY.ID
    ) COLORS
    INNER JOIN ATTRIBUTE_HISTORY ATTR ON COLORS.ID = ATTR.ID
    WHERE
        ATTR.ATTRIBUTE_NAME = 'DIAMETER' 
)
WHERE
    DATE_RANK = 1

优化方案 #

以下两种方法只需扫描原表一次，高效简洁且支持批量处理属性：

方法1：窗口函数+CASE WHEN行转列（兼容性强） #

WITH latest_attr AS (
    SELECT 
        ID,
        Attribute_Name,
        Attribute_Val,
        -- 按ID和属性分组，时间倒序排号，最新记录为rn=1
        ROW_NUMBER() OVER (PARTITION BY ID, Attribute_Name ORDER BY [Time Stamp] DESC) AS rn
    FROM Attribute_history
)
SELECT 
    ID,
    -- 把不同属性转为列，MAX确保取唯一有效记录
    MAX(CASE WHEN Attribute_Name = 'Color' THEN Attribute_Val END) AS Color,
    MAX(CASE WHEN Attribute_Name = 'Length' THEN Attribute_Val END) AS Length,
    MAX(CASE WHEN Attribute_Name = 'Diameter' THEN Attribute_Val END) AS Diameter
FROM latest_attr
WHERE rn = 1 -- 只保留最新记录
GROUP BY ID
ORDER BY ID;

方法2：窗口函数+PIVOT（适用于支持PIVOT的数据库，如SQL Server、Oracle） #

WITH latest_attr AS (
    SELECT 
        ID,
        Attribute_Name,
        Attribute_Val,
        ROW_NUMBER() OVER (PARTITION BY ID, Attribute_Name ORDER BY [Time Stamp] DESC) AS rn
    FROM Attribute_history
)
SELECT ID, Color, Length, Diameter
FROM latest_attr
WHERE rn = 1
-- 行转列，将Attribute_Name的取值转为列名
PIVOT (
    MAX(Attribute_Val)
    FOR Attribute_Name IN (Color, Length, Diameter)
) AS pivot_table
ORDER BY ID;

方案优势 #

• 性能更优：仅扫描Attribute_history表一次，避免多次重复查询。
• 扩展性强：新增属性时，只需在CASE WHEN分支或PIVOT的IN列表中添加对应属性名即可。
• 逻辑清晰：通过CTE先筛选最新记录，再行转列，代码结构直观易维护。

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