您可以取消嵌套字符串，然后应用一些聚合逻辑：
with t as (
select 'A >> B >> C' as items union all
select 'A >> A >> B' as items union all
select 'B >> B >> C >> C >> A' as items union all
select 'B >> B >> B >> C >> A >> D' as items
)
select t.*,
(select as struct max(case when n = 1 then item end) as item_1,
max(case when n = 1 then cnt end) as item_1_cnt,
max(case when n = 2 then item end) as item_2,
max(case when n = 2 then cnt end) as item_2_cnt,
max(case when n = 3 then item end) as item_3,
max(case when n = 3 then cnt end) as item_3_cnt
from (select item, dense_rank() over (order by min(n)) as n, count(*) as cnt
from unnest(split(t.items, ' >> ')) item with offset n
group by item
) x
).*
from t;
最里面的子查询使用计数器将字符串转换为行。然后将其汇总起来，以便在商品首次出现时对商品进行排序-以及商品的数量。
最后，这些汇总到所需的不同列中。
602392714
清零编程群