您可以取消嵌套字符串，然后应用一些聚合逻辑：
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;
最里面的子查询使用计数器将字符串转换为行。然后将其汇总起来，以便在商品首次出现时对商品进行排序-以及商品的数量。
最后，这些汇总到所需的不同列中。
