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_idle)
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);
上述数据包含名为max_idle和row_num的2列，因此无论其中的row_num=1，我都要创建新列，并追加max_idle1的ROUND_GEO_LAT、ROUND_GEO_LNG的值
所以新的模式应该是这样的.
这是我要查找的输出:
| created_date | device | rake_device | is_same_location | rounded_geo_lat | rounded_geo_lng | idle_or_moving | time_diff_idle_vs_moving | max_idle | rn_idle | max_idle_lat1 | max_idle_lng1 | time_diff1 | max_idle_lat2 | max_idle_lng2 | time_diff2 |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 25-12-2023 21:22 | SLA11502 | TXDP-12-SLA11502 | 1 | 20.47 | 82.9 | idle | 23 | 10 | 1 | 21.31 | 84.1 | 61 | 21.47 | 83.97 | 62 |
| 25-12-2023 20:59 | SLA11502 | TXDP-12-SLA11502 | 1 | 20.47 | 82.9 | idle | 61 | 3 | 2 | null | null | null | null | null | null |
| 25-12-2023 19:58 | SLA11502 | TXDP-12-SLA11502 | 1 | 20.47 | 82.9 | idle | 5 | 2 | 3 | null | null | null | null | null | null |
| 25-12-2023 19:53 | SLA11502 | TXDP-12-SLA11502 | 1 | 20.47 | 82.9 | idle | 27 | 1 | 4 | null | null | null | null | null | null |
我try 了这个查询，但没有按预期工作:
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;