SQL需求：在row_num=1行新增列并关联max_idle对应数据

表结构及测试数据 #

CREATE TABLE nearest_location(
created_date TIMESTAMP,
device VARCHAR(255),
rake_device VARCHAR(255),
is_same_location INT,
rounded_geo_lat FLOAT,
rounded_geo_lng FLOAT,
idle_or_moving VARCHAR(255),
time_diff_idle_vs_moving INT,
max_idle INT,
row_num INT
);

INSERT INTO nearest_location(created_date, device, rake_device, is_same_location, 
rounded_geo_lat, rounded_geo_lng, idle_or_moving, time_diff_idle_vs_moving, max_idle, 
row_num)
VALUES
('2023-12-26 09:58', 'SLA16143', 'ARIL-05-SLA16143', 1, 22.78, 69.67, 'idle', 3, 31, 1),
('2023-12-26 09:55', 'SLA16143', 'ARIL-05-SLA16143', 1, 22.78, 69.67, 'idle', 19, 16, 
2),
('2023-12-26 09:36', 'SLA16143', 'ARIL-05-SLA16143', 1, 22.78, 69.67, 'idle', 60, 10, 
3),
('2023-12-26 08:36', 'SLA16143', 'ARIL-05-SLA16143', 1, 22.78, 69.67, 'idle', 60, 5, 4),
('2023-12-26 07:36', 'SLA16143', 'ARIL-05-SLA16143', 0, 22.78, 69.67, 'moving', 69, 2, 
5),
('2023-12-26 06:27', 'SLA16143', 'ARIL-05-SLA16143', 1, 22.77, 69.67, 'idle', 18, 4, 6),
('2023-12-26 06:09', 'SLA16143', 'ARIL-05-SLA16143', 0, 22.77, 69.67, 'moving', 12, 61, 
7),
('2023-12-26 05:57', 'SLA16143', 'ARIL-05-SLA16143', 1, 22.74, 69.67, 'idle', 17, 3, 8),
('2023-12-26 05:40', 'SLA16143', 'ARIL-05-SLA16143', 0, 22.74, 69.67, 'moving', 11, 60, 
9),
('2023-12-26 05:29', 'SLA16143', 'ARIL-05-SLA16143', 1, 22.74, 69.68, 'idle', 44, 1, 
10);

需求说明 #

• 针对目标表，在row_num=1的行新增多组扩展列：max_idle_latN、max_idle_lngN、time_diffN（N为1、2、3...）
• 扩展列需分别填充表中max_idle=N对应行的rounded_geo_lat、rounded_geo_lng、time_diff_idle_vs_moving值
• 非row_num=1的行，所有扩展列填充为NULL

期望输出 #

尝试的SQL（未达预期） #

SELECT
    mid.created_date,
    mid.device,
    mid.rake_device,
    mid.is_same_location,
    MAX(CASE WHEN mid.rn_idle = 1 AND mid.max_idle = 1 THEN mid.rounded_geo_lat END) AS max_idle_lat1,
    MAX(CASE WHEN mid.rn_idle = 1 AND mid.max_idle = 1 THEN mid.rounded_geo_lng END) AS max_idle_lng1,
    MAX(CASE WHEN mid.rn_idle = 1 AND mid.max_idle = 1 THEN mid.time_diff_idle_vs_moving END) AS time_diff1,
    MAX(CASE WHEN mid.rn_idle = 1 AND mid.max_idle = 2 THEN mid.rounded_geo_lat END) AS max_idle_lat2,
    MAX(CASE WHEN mid.rn_idle = 1 AND mid.max_idle = 2 THEN mid.rounded_geo_lng END) AS max_idle_lng2,
    MAX(CASE WHEN mid.rn_idle = 1 AND mid.max_idle = 2 THEN mid.time_diff_idle_vs_moving END) AS time_diff2,
    MAX(CASE WHEN mid.rn_idle = 1 AND mid.max_idle = 3 THEN mid.rounded_geo_lat END) AS max_idle_lat3,
    MAX(CASE WHEN mid.rn_idle = 1 AND mid.max_idle = 3 THEN mid.rounded_geo_lng END) AS max_idle_lng3,
    MAX(CASE WHEN mid.rn_idle = 1 AND mid.max_idle = 3 THEN mid.time_diff_idle_vs_moving END) AS time_diff3,
    MAX(CASE WHEN mid.rn_idle = 1 AND mid.max_idle = 4 THEN mid.rounded_geo_lat END) AS max_idle_lat4,
    MAX(CASE WHEN mid.rn_idle = 1 AND mid.max_idle = 4 THEN mid.rounded_geo_lng END) AS max_idle_lng4,
    MAX(CASE WHEN mid.rn_idle = 1 AND mid.max_idle = 4 THEN mid.time_diff_idle_vs_moving END) AS time_diff4,
    MAX(CASE WHEN mid.rn_idle = 1 AND mid.max_idle = 5 THEN mid.rounded_geo_lat END) AS max_idle_lat5,
    MAX(CASE WHEN mid.rn_idle = 1 AND mid.max_idle = 5 THEN mid.rounded_geo_lng END) AS max_idle_lng5,
    MAX(CASE WHEN mid.rn_idle = 1 AND mid.max_idle = 5 THEN mid.time_diff_idle_vs_moving END) AS time_diff5
FROM
    MaxIdleData mid
WHERE
    mid.rn_idle = 1
GROUP BY
    mid.created_date, mid.device, mid.rake_device, mid.is_same_location;

解决方案 #

原SQL问题在于：仅筛选了rn_idle=1的行，无法保留原表所有行；且CASE条件要求同一行同时满足rn_idle=1和max_idle=N，逻辑矛盾无法匹配到对应数据。

正确做法是先将不同max_idle对应的数据转置为列，再左连接到原表，仅让row_num=1的行匹配扩展数据：

SELECT
    nl.created_date,
    nl.device,
    nl.rake_device,
    nl.is_same_location,
    nl.rounded_geo_lat,
    nl.rounded_geo_lng,
    nl.idle_or_moving,
    nl.time_diff_idle_vs_moving,
    nl.max_idle,
    nl.row_num AS rn_idle,
    -- 扩展列
    pivoted.max_idle_lat1,
    pivoted.max_idle_lng1,
    pivoted.time_diff1,
    pivoted.max_idle_lat2,
    pivoted.max_idle_lng2,
    pivoted.time_diff2,
    pivoted.max_idle_lat3,
    pivoted.max_idle_lng3,
    pivoted.time_diff3,
    pivoted.max_idle_lat4,
    pivoted.max_idle_lng4,
    pivoted.time_diff4,
    pivoted.max_idle_lat5,
    pivoted.max_idle_lng5,
    pivoted.time_diff5
FROM
    nearest_location nl
LEFT JOIN (
    -- 子查询：按设备聚合，将不同max_idle的字段转置为列
    SELECT
        device,
        rake_device,
        MAX(CASE WHEN max_idle = 1 THEN rounded_geo_lat END) AS max_idle_lat1,
        MAX(CASE WHEN max_idle = 1 THEN rounded_geo_lng END) AS max_idle_lng1,
        MAX(CASE WHEN max_idle = 1 THEN time_diff_idle_vs_moving END) AS time_diff1,
        MAX(CASE WHEN max_idle = 2 THEN rounded_geo_lat END) AS max_idle_lat2,
        MAX(CASE WHEN max_idle = 2 THEN rounded_geo_lng END) AS max_idle_lng2,
        MAX(CASE WHEN max_idle = 2 THEN time_diff_idle_vs_moving END) AS time_diff2,
        MAX(CASE WHEN max_idle = 3 THEN rounded_geo_lat END) AS max_idle_lat3,
        MAX(CASE WHEN max_idle = 3 THEN rounded_geo_lng END) AS max_idle_lng3,
        MAX(CASE WHEN max_idle = 3 THEN time_diff_idle_vs_moving END) AS time_diff3,
        MAX(CASE WHEN max_idle = 4 THEN rounded_geo_lat END) AS max_idle_lat4,
        MAX(CASE WHEN max_idle = 4 THEN rounded_geo_lng END) AS max_idle_lng4,
        MAX(CASE WHEN max_idle = 4 THEN time_diff_idle_vs_moving END) AS time_diff4,
        MAX(CASE WHEN max_idle = 5 THEN rounded_geo_lat END) AS max_idle_lat5,
        MAX(CASE WHEN max_idle = 5 THEN rounded_geo_lng END) AS max_idle_lng5,
        MAX(CASE WHEN max_idle = 5 THEN time_diff_idle_vs_moving END) AS time_diff5
    FROM
        nearest_location
    GROUP BY
        device, rake_device
) pivoted ON nl.row_num = 1 AND nl.device = pivoted.device AND nl.rake_device = pivoted.rake_device;

逻辑说明 #

1. 子查询通过CASE+MAX聚合，将每个设备对应的不同max_idle值的经纬度、时间差转置为固定列
2. 主查询用原表左连接子查询，仅当row_num=1时建立关联，确保只有该行填充扩展列，其他行自动为NULL
3. 若需按更细维度（如日期）聚合，可在子查询的GROUP BY中添加对应字段

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