如何用AWS Athena SQL实现列值转多行的数据格式转换？

要实现将每行中用/分隔的字段拆分为多行并对应匹配，可通过Athena的字符串拆分和行转列函数完成，具体SQL如下：

WITH split_data AS (
    SELECT
        ID,
        -- 拆分开始时间并记录位置索引
        UNNEST(SPLIT(start_time, '/')) WITH ORDINALITY AS st(time_val, idx),
        -- 拆分结束时间并记录位置索引
        UNNEST(SPLIT(end_time, '/')) WITH ORDINALITY AS et(time_val, idx),
        -- 拆分故障扇区并记录位置索引
        UNNEST(SPLIT(fault_sector, '/')) WITH ORDINALITY AS fs(sector_val, idx),
        -- 拆分循环次数并记录位置索引
        UNNEST(SPLIT(cycle_count, '/')) WITH ORDINALITY AS cc(count_val, idx)
    FROM your_table
)
SELECT
    sd.ID,
    sd.st.time_val AS 开始时间,
    sd.et.time_val AS 结束时间,
    -- 故障扇区空值替换为“空值”
    CASE WHEN TRIM(sd.fs.sector_val) = '' THEN '空值' ELSE sd.fs.sector_val END AS 故障扇区,
    -- 循环次数空值替换为“空值”
    CASE WHEN TRIM(sd.cc.count_val) = '' THEN '空值' ELSE sd.cc.count_val END AS 循环次数
FROM split_data sd
-- 通过ID和索引关联各拆分后的字段行
JOIN split_data sd_et ON sd.ID = sd_et.ID AND sd.st.idx = sd_et.et.idx
JOIN split_data sd_fs ON sd.ID = sd_fs.ID AND sd.st.idx = sd_fs.fs.idx
JOIN split_data sd_cc ON sd.ID = sd_cc.ID AND sd.st.idx = sd_cc.cc.idx
-- 过滤掉拆分后可能的空行（比如原字段末尾的/导致的空元素）
WHERE sd.st.time_val != ''
ORDER BY sd.ID, sd.st.idx;

代码说明： #

1. CTE split_data：使用SPLIT函数将每个用/分隔的字段拆分为数组，再通过UNNEST...WITH ORDINALITY将数组转为多行，同时保留每个元素的位置索引idx，确保同一行的各字段拆分后能按位置对应。
2. 关联查询：通过ID和idx将各字段拆分后的行关联，保证每个ID下的开始时间、结束时间、故障扇区、循环次数一一对应。
3. 空值处理：用CASE语句将拆分后的空字符串（原数据中/之间的空内容）替换为“空值”。
4. 过滤空行：排除原字段末尾/导致的空元素行（比如ID1的循环次数最后一个/产生的空值行）。

替换your_table为你的实际表名即可执行。

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