presto将单个列值拆分为多行

Question

我在presto上有一个表，它有多个记录的记录。在该记录中,我使用了这个简单的SQL查询,

select id, data from my_table where id IN (1,7)

id        data
1   ('A', 0.0, 12)
7   ('B', 0.0, 20) ('A', 0.0, 30) ('C', 0.0, 40)

name value age
A    0.0   12
B    0.0   20
A    0.0   30
C    0.0   40

SELECT data
FROM my_table
    CROSS APPLY STRING_SPLIT(data, ' ') where id IN (1,7); 

Answer 1

使用split、unnest、replace和try我可以执行查询，现在只想知道有没有其他方法可以执行/修复它？因为我对这种方式并不完全满意：）

WITH t AS (
select data  from my_table where id IN (1,7)
)

SELECT
  try(trim(data_parts[1])) AS name,
  try(trim(data_parts[2])) AS age,
  try(trim(data_parts[3])) AS value
FROM (
  SELECT split(replace(split_Col2,'('),',') as data_parts 
FROM t
CROSS JOIN UNNEST(SPLIT(data,')')) AS t (split_Col2) 
) WHERE length(try(trim(data_parts[1]))) > 0

结果

     name   age  value
34  'AAAA'  0.0 'NNNNN' 
36  'BBBB'  0.0 'NNNNN'     
38  'CCCC'  0.0 'MMMMM'
39  'AAAA'  0.0 'CCCCC'