如何关联两张表并按box_id分组获取最新时间戳对应的状态？

我有两张表：

1. connector_status表（结构及数据）：

2. connector表（结构及数据）：

需求：按box_id分组，获取每个box_id下所有connector对应的最新status_timestamp记录的状态，最终期望结果：

我写了一段SQL，但不知道怎么通过关联box_id获取最大时间戳，同时疑惑：如果先从connector_status表获取最新时间戳再关联box_id，能不能确保拿到对应box_id下每个connector的最新记录？

现有SQL：

SELECT IF(connector_status.status = 'Charging','Charging', IF(connector_status.status ='Available','Not Occupied', IF(connector_status.status = 'Faulted','Faulted','Occupied'))) AS group_status, connector.connector_id, connector.box_id, status_timestamp 
FROM connector_status  
JOIN connector ON connector_status.connector = connector.connector  
GROUP BY connector.box_id  
ORDER BY connector.box_id

步骤1：先获取每个connector的最新状态记录 #

要拿到每个connector的最新状态，先对connector_status按connector分组，取出每个组的最大status_timestamp，再关联回原表拿到对应的状态：

WITH latest_connector_status AS (
    SELECT 
        connector,
        MAX(status_timestamp) AS latest_ts
    FROM connector_status
    GROUP BY connector
)
SELECT 
    cs.connector,
    cs.status_timestamp,
    cs.status
FROM connector_status cs
JOIN latest_connector_status lcs 
    ON cs.connector = lcs.connector 
    AND cs.status_timestamp = lcs.latest_ts;

这段代码会得到每个connector的最新状态记录，结果如下：

步骤2：关联connector表拿到box_id，并按box_id聚合状态 #

把上面的结果和connector表关联，拿到每个connector对应的box_id，然后按照状态优先级（Charging > Available > Faulted）聚合每个box的最终状态：

WITH latest_connector_status AS (
    SELECT 
        connector,
        MAX(status_timestamp) AS latest_ts
    FROM connector_status
    GROUP BY connector
),
connector_latest_status AS (
    SELECT 
        cs.connector,
        cs.status,
        c.box_id
    FROM connector_status cs
    JOIN latest_connector_status lcs 
        ON cs.connector = lcs.connector 
        AND cs.status_timestamp = lcs.latest_ts
    JOIN connector c ON cs.connector = c.connector
)
SELECT 
    box_id,
    -- 按优先级映射状态：Charging > Available > Faulted，其他归为Occupied
    CASE
        WHEN MAX(CASE WHEN status = 'charging' THEN 3 ELSE 0 END) = 3 THEN 'Charging'
        WHEN MAX(CASE WHEN status = 'available' THEN 2 ELSE 0 END) = 2 THEN 'Available'
        WHEN MAX(CASE WHEN status = 'faulted' THEN 1 ELSE 0 END) = 1 THEN 'Faulted'
        ELSE 'Occupied'
    END AS status
FROM connector_latest_status
GROUP BY box_id
ORDER BY box_id;

这段代码会输出你期望的结果：

关于你的疑问 #

先从connector_status获取每个connector的最新时间戳，再关联box_id是完全可靠的。第一步已经确保每个connector只保留最新的那条记录，关联box_id后，每个box下的所有connector都是各自的最新状态，再按box聚合就能得到正确结果。

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