Hive中posexplode拆分字段产生重复行的去重方法咨询

• 拆分逻辑问题：使用split拆分|分隔的字符串时，连续分隔符会生成空字符串元素，posexplode会把所有元素（包括空字符串）全部展开，直接产生大量空值行
• 笛卡尔积问题：三个字段同时使用lateral view posexplode会产生笛卡尔积，三个字段各自展开的行数相乘，进一步放大重复空行的数量
• 语法错误：原SQL存在多处语法问题，包括字段定义间缺少逗号、replace函数参数缺少逗号分隔

单字段拆分场景（对应你给出的cofe字段示例） #

拆分后先过滤空字符串，同时保留原始字段全空的记录：

SELECT 
  id,
  COALESCE(ss.valid_cofe, '') AS cofe
FROM (
  SELECT 
    id, 
    sequence,  
    split(BMWA, '~')[15] AS CFEEA,
    split(BMWA, '~')[16] AS FTAAA,
    split(BMWA, '~')[17] AS CNTTA
  FROM ( 
    SELECT 
      id,  
      sequence,  
      replace(bmw, '^','~') AS BMWA
    FROM tablee
  ) rr 
) qq
LATERAL VIEW OUTER posexplode(
  -- 过滤拆分出的空值/纯空白元素
  FILTER(
    split(replace(qq.CFEEA, '|', '~'), '~'),
    x -> TRIM(x) <> ''
  )
) ss AS r, valid_cofe
-- 兼容原始字段全为空的情况，保留一条空记录
UNION ALL
SELECT id, '' AS cofe
FROM (
  SELECT 
    id, 
    split(BMWA, '~')[15] AS CFEEA
  FROM ( 
    SELECT 
      id,  
      replace(bmw, '^','~') AS BMWA
    FROM tablee
  ) rr 
) qq
WHERE TRIM(COALESCE(CFEEA, '')) = ''

多字段同位置对应场景（cofe/fnte/cnte拆分后同下标值为一组） #

按位置对齐取值，避免笛卡尔积，过滤全空的无效行：

SELECT 
  qq.id,
  COALESCE(c[idx], '') AS cofe,
  COALESCE(f[idx], '') AS fnte,
  COALESCE(cn[idx], '') AS cnte
FROM (
  SELECT 
    id, 
    sequence,  
    split(BMWA, '~')[15] AS CFEEA,
    split(BMWA, '~')[16] AS FTAAA,
    split(BMWA, '~')[17] AS CNTTA,
    -- 提前拆分三个字段
    split(replace(split(BMWA, '~')[15], '|', '~'), '~') AS c,
    split(replace(split(BMWA, '~')[16], '|', '~'), '~') AS f,
    split(replace(split(BMWA, '~')[17], '|', '~'), '~') AS cn
  FROM ( 
    SELECT 
      id,  
      sequence,  
      replace(bmw, '^','~') AS BMWA
    FROM tablee
  ) rr 
) qq
-- 生成最大长度的位置序列
LATERAL VIEW OUTER posexplode(
  sequence(0, greatest(size(c)-1, size(f)-1, size(cn)-1))
) pos AS r, idx
-- 过滤全空的无效位置
WHERE NOT (
  TRIM(COALESCE(c[idx], '')) = ''
  AND TRIM(COALESCE(f[idx], '')) = ''
  AND TRIM(COALESCE(cn[idx], '')) = ''
)
-- 保留三个字段全为空的原始记录
UNION ALL
SELECT id, '', '', ''
FROM (
  SELECT 
    id, 
    split(BMWA, '~')[15] AS CFEEA,
    split(BMWA, '~')[16] AS FTAAA,
    split(BMWA, '~')[17] AS CNTTA
  FROM ( 
    SELECT 
      id,  
      replace(bmw, '^','~') AS BMWA
    FROM tablee
  ) rr 
) qq
WHERE TRIM(COALESCE(CFEEA, '')) = ''
  AND TRIM(COALESCE(FTAAA, '')) = ''
  AND TRIM(COALESCE(CNTTA, '')) = ''

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