如何在PostgreSQL中根据Did将宽表转换为一对多结构的窄表？

你可以通过两种实用方法实现这个需求：

方法一：使用UNION ALL（适合时间组数较少的场景） #

这种方法逻辑直观，直接将每组时间字段单独查询后合并结果：

SELECT Sid, Did, Time1s AS start_time, Time1e AS end_time
FROM original_table
UNION ALL
SELECT Sid, Did, Time2s AS start_time, Time2e AS end_time
FROM original_table
UNION ALL
SELECT Sid, Did, Time3s AS start_time, Time3e AS end_time
FROM original_table
ORDER BY Sid, start_time;

如果需要将结果存入新表，可执行：

CREATE TABLE transformed_table AS
SELECT Sid, Did, Time1s AS start_time, Time1e AS end_time
FROM original_table
UNION ALL
SELECT Sid, Did, Time2s AS start_time, Time2e AS end_time
FROM original_table
UNION ALL
SELECT Sid, Did, Time3s AS start_time, Time3e AS end_time
FROM original_table
ORDER BY Sid, start_time;

方法二：使用UNNEST结合数组（适合时间组数较多的场景） #

这种方法扩展性更强，后续新增Time4、TimeN组时，只需修改数组内容即可：

SELECT
    Sid,
    Did,
    start_time,
    end_time
FROM original_table,
UNNEST(
    ARRAY[Time1s, Time2s, Time3s],
    ARRAY[Time1e, Time2e, Time3e]
) AS t(start_time, end_time)
ORDER BY Sid, start_time;

补充说明 #

• UNNEST会将数组中的每个元素拆分为单独行，同时拆分两个数组时，对应位置的元素会一一匹配，避免时间错位。
• 若你的PostgreSQL版本低于9.4，可使用嵌套UNNEST写法：

SELECT
    Sid,
    Did,
    UNNEST(ARRAY[Time1s, Time2s, Time3s]) AS start_time,
    UNNEST(ARRAY[Time1e, Time2e, Time3e]) AS end_time
FROM original_table
ORDER BY Sid, start_time;

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