SQL如何将conversion列斜杠分隔字符串按属性拆分为多列

问题原因 #

你之前的写法只返回单个字符的核心问题是'[^\/]'正则仅匹配单个非斜杠字符，所以只取到了source值的首字母。

正确实现方案 #

默认适配PostgreSQL（你当前用到的split_part是PG原生函数），提供两种适配不同场景的写法：

方案1：正则提取（写法最简洁） #

直接用substring的正则捕获组匹配对应属性的值，规则是匹配/属性名:后面到下一个斜杠/字符串末尾的所有内容，匹配不到属性时会自动返回NULL，也不限制属性的出现顺序，完全符合需求：

SELECT
  split_part(a."conversion", '/', 1)  as id,
  split_part(a."conversion", '/', 2) as brand,
  split_part(a."conversion", '/', 3) as code,
  substring(a."conversion" FROM '/source:([^/]*)') as source,
  substring(a."conversion" FROM '/campaign:([^/]*)') as campaign,
  substring(a."conversion" FROM '/content:([^/]*)') as content,
  substring(a."conversion" FROM '/term:([^/]*)') as term,
  substring(a."conversion" FROM '/medium:([^/]*)') as medium
FROM your_table a

方案2：拆成键值对后聚合（适合属性非常多的场景） #

如果后续属性数量不固定，可以先把字符串拆成单个键值对，再行转列提取，扩展性更强：

WITH split_kv AS (
  SELECT
    split_part(a."conversion", '/', 1)  as id,
    split_part(a."conversion", '/', 2) as brand,
    split_part(a."conversion", '/', 3) as code,
    -- 拆分所有斜杠分隔的片段
    unnest(string_to_array(a."conversion", '/')) as kv_str
  FROM your_table a
  -- 过滤掉前三个固定字段
  WHERE kv_str NOT IN (split_part(a."conversion", '/', 1), split_part(a."conversion", '/', 2), split_part(a."conversion", '/', 3))
),
kv_parse AS (
  SELECT
    id,brand,code,
    split_part(kv_str, ':', 1) as k,
    split_part(kv_str, ':', 2) as v
  FROM split_kv
)
SELECT
  id,brand,code,
  max(v) FILTER (WHERE k = 'source') as source,
  max(v) FILTER (WHERE k = 'campaign') as campaign,
  max(v) FILTER (WHERE k = 'content') as content,
  max(v) FILTER (WHERE k = 'term') as term,
  max(v) FILTER (WHERE k = 'medium') as medium
FROM kv_parse
GROUP BY id,brand,code

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