Oracle中将分隔数据转换为多行的实现方案咨询

针对你需要把单字段的键值对日志拆分成多行键值表的需求，我整理了几种常用场景下的实现方法，涵盖数据库SQL和Python数据处理两种主流方式：

一、数据库SQL实现（分数据库示例） #

1. MySQL 8.0+（递归CTE方式） #

MySQL 8.0及以上支持递归公用表表达式（CTE），可以轻松实现字符串的拆分和多行生成：

WITH RECURSIVE split_log AS (
    -- 初始行：拆分第一个键值对
    SELECT 
        ID,
        TRIM(SUBSTRING_INDEX(Log, '|', 1)) AS key_value,
        TRIM(SUBSTRING(Log, LENGTH(SUBSTRING_INDEX(Log, '|', 1)) + 2)) AS remaining_log
    FROM Source_Table
    WHERE Log IS NOT NULL AND Log != ''
    
    UNION ALL
    
    -- 递归拆分剩余的键值对
    SELECT 
        ID,
        TRIM(SUBSTRING_INDEX(remaining_log, '|', 1)) AS key_value,
        TRIM(SUBSTRING(remaining_log, LENGTH(SUBSTRING_INDEX(remaining_log, '|', 1)) + 2)) AS remaining_log
    FROM split_log
    WHERE remaining_log IS NOT NULL AND remaining_log != ''
)
-- 拆分键值对为Key和Value列
SELECT 
    ID,
    TRIM(SUBSTRING_INDEX(key_value, ':', 1)) AS `Key`,
    TRIM(SUBSTRING(key_value, LENGTH(SUBSTRING_INDEX(key_value, ':', 1)) + 2)) AS `Value`
FROM split_log
ORDER BY ID;

说明：

• 递归CTE先把每个Log字段拆分成单个的键值对条目
• 最后一步再把每个键值对按:分割，提取Key和Value，同时用TRIM()去掉多余空格

2. PostgreSQL #

PostgreSQL的字符串处理函数更简洁，结合string_to_array和unnest可以快速实现：

SELECT 
    st.ID,
    TRIM(split_part(kv_pair, ':', 1)) AS "Key",
    TRIM(split_part(kv_pair, ':', 2)) AS "Value"
FROM Source_Table st,
     unnest(string_to_array(st.Log, '|')) AS kv_pair
WHERE st.Log IS NOT NULL AND st.Log != ''
ORDER BY st.ID;

说明：

• string_to_array(st.Log, '|')把Log字段转成键值对数组
• unnest()把数组展开成多行
• split_part()拆分每个键值对为Key和Value

3. SQL Server #

SQL Server 2016+支持STRING_SPLIT函数，实现方式如下：

SELECT 
    st.ID,
    TRIM(LEFT(kv_pair, CHARINDEX(':', kv_pair) - 1)) AS [Key],
    TRIM(SUBSTRING(kv_pair, CHARINDEX(':', kv_pair) + 1, LEN(kv_pair))) AS [Value]
FROM Source_Table st
CROSS APPLY STRING_SPLIT(st.Log, '|') AS split
WHERE st.Log IS NOT NULL AND st.Log != ''
  AND CHARINDEX(':', split.value) > 0 -- 过滤掉无冒号的异常条目（如果有的话）
ORDER BY st.ID;

说明：

• CROSS APPLY STRING_SPLIT()把每个Log拆分成多行键值对
• 用CHARINDEX定位冒号位置，拆分Key和Value

二、Python Pandas实现（数据ETL/分析场景） #

如果是用Python做数据处理，Pandas的str.split和explode方法可以快速完成转换：

import pandas as pd

# 模拟源表数据
source_data = pd.DataFrame({
    'ID': [1, 2],
    'Log': ['Status : New | Assignment : 1 | Priority : Low', 'Status : In Progress']
})

# 步骤1：按|拆分Log列，生成列表
source_data['key_value'] = source_data['Log'].str.split('|')

# 步骤2：把列表展开成多行
exploded_df = source_data.explode('key_value').drop('Log', axis=1)

# 步骤3：拆分key_value为Key和Value列
exploded_df[['Key', 'Value']] = exploded_df['key_value'].str.split(':', expand=True).apply(lambda x: x.str.strip())

# 步骤4：整理最终结果
target_df = exploded_df.drop('key_value', axis=1)[['ID', 'Key', 'Value']]

print(target_df)

输出结果：

ID         Key          Value
0   1      Status            New
0   1  Assignment              1
0   1    Priority            Low
1   2      Status  In Progress

说明：

• str.split('|')把每个Log拆分成键值对列表
• explode()把列表行转成多行
• 再次用str.split(':', expand=True)拆分键值，并用str.strip()去掉多余空格

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