在snowflake/DBT中减去列

Question

我有两列，一列是选择对象，另一列是未选择对象。

我需要用最终对象创建一个列，例如:

Customer_ID已选。最后车衬衫、裤子、眼镜眼镜。衬衫,裤子衣服，裤子，眼镜裤子、裙子

不幸的是，dbt jinja对这个问题没有帮助，因为它(几乎总是)在SQL运行时之前编译，所以它无法访问这些值。

这个可以，但是可以用Snowflake SQL来解决。基本模式是将值平摊，然后进行减法，然后将它们重新聚集在一起。

下面是一些代码:

with carts as (
select 799235 as customer_id, array_construct('shirt', 'pants', 'glasses')  as picked, array_construct('glasses') as unpicked
union
select 799246, array_construct('dress', 'pants', 'glasses'), array_construct('pants', 'dress')
),
picked_expanded as (
select
carts.customer_id,
picked.value::varchar as item
from carts, lateral flatten ( input => carts.picked ) as picked
),
unpicked_expanded as (
select
carts.customer_id,
unpicked.value::varchar as item
from carts, lateral flatten ( input => carts.unpicked ) as unpicked
),
combined as (
select * from picked_expanded
except
select * from unpicked_expanded
),
agg as (
select
customer_id,
array_agg(item) as final_cart
from combined
group by 1
)
select * from agg

给出结果:

FINAL_CART799246"text-align:左;"("glasses"799235"text-align:左;"("shirt","pants"