SQL PRESTO-交叉加入Unnest，条纹扁平

Question

为什么我的查询不会像预期的那样使数据变平？

我正在查询一个列包含数组的表。我的目标是不肯定阵列中的物品，以找到独特的物品并将其变成行。

SELECT
table1.tag_names,
table1.tag_ids, 
rank_position
FROM table1
CROSS JOIN UNNEST (tag_ids, tag_names)
   WITH ORDINALITY as T (tag_ids, tag_names, rank_position)
ORDER BY tag_ids

结果：

tag_names	tag_ids	rank_position
[“ red”，“ blue”，“ green”]	[111，222，333]	1
[“红色”，“蓝色”，“ Yellow”]	[111，222，444]	4

所需的结果：

tag_names	tag_ids	rank_position
“ red”	111	1
“蓝色”	222	2
“绿色”	333	3
“黄色”	444	4

我缺少什么？

更新以使用别名，下面的新结果：

tag_names	tag_ids	rank_position
“ red”	111	1
“红色”	111	10
“红色”	111	3
”	111	“ 111 12
12 “黄色”	444	4

Answer 1

您应该在交叉加入Unnest中使用的别名在选择：

-- sample data
WITH dataset (tag_names, tag_ids) AS (
    VALUES (array['red', 'blue', 'green'], array[111, 222, 444])
) 

-- query
select T.tag_names,
   T.tag_ids, 
   rank_position
from dataset
CROSS JOIN UNNEST (tag_ids, tag_names)
   WITH ORDINALITY as T (tag_ids, tag_names, rank_position)
ORDER BY tag_ids

输出：

tag_names	tag_ids	rank_posity
red	111	1
蓝色	222	2
绿色	444	3

upd>

upd> upd> upd> 准则不跨多行起作用，实现所需结果的一种方法是使阵列弄平，然后使用和 row_number ：

-- sample data
WITH dataset (tag_names, tag_ids) AS (
    VALUES (array['red', 'blue', 'green'], array[111, 222, 333]),
     (array['red', 'blue', 'yellow'], array[111, 222, 444])
) 

-- query
select *, row_number() over (order by tag_ids) rank_position
from (
        select T.tag_names,
            T.tag_ids
        from dataset
            CROSS JOIN UNNEST (tag_ids, tag_names) AS T (tag_ids, tag_names)
            GROUP BY T.tag_names, T.tag_ids
    )
ORDER BY tag_ids

output：

tag_names	tag_ids	rank_position
row_number row_number 红色	111	1
蓝色	222	2
绿色	333	3
黄色	444	4