计算每个merchant_id的独占和共享product_ids

Question

对于每个merchant_id，我想计算store_a和store_b中都存在的product_id，然后将其归类为'shared_product

并分别计算仅存在于store_a或store_b中的product_id，然后将其归类为'exclusive_product_storeA'或'exclusive_product_storeB'

我的查询不显示shared_product，它只显示'exclusive_product_storeA'和'exclusive_product_storeB'

请不要只有store_a和store_b，而是多个merchant_id

桌子

merchant_id   product_id    store_id    product_status
1             8328          store_a     new_product
1             4234          store_a     new_product
1             8328          store_b     new_product 
1             4234          store_b     new_product
1             5943          store_b     old_product
2             1244          store_a     old_product
2             1244          store_b     old_product
2             2353          store_a     old_product 
2             2353          store_b     old_product
2             5943          store_a     new_product
3             9838          store_a     old_product
3             9838          store_b     old_product
3             1244          store_a     old_product
3             1244          store_b     old_product
3             6544          store_a     old_product
3             6544          store_b     old_product
3             3443          store_a     old_product 

输出表

merchant_id    product_status   product_state              count_product
1              new_product      shared_products             2
1              old_product      exclusive_product_storeB    1
2              old_product      shared_products             2
2              new_product      exclusive_product_storeB    1
3              old_product      shared_products             3
3              old_product      exclusive_product_storeA    1

查询

select merchant_id, product_status, product_state, count(*) as cnt
from (select merchant_id, product_id, product_status,
(case when max(store_id) <> min(store_id) then 'shared'
when max(store_id) = 'store_a' then 'only store_a'
else 'only store_b'
end) as product_state
from t
where store_id in ('store_a', 'store_b)
group by merchant_id, product_id, product_status
) mp
group by merchant_id, product_status, product_state;

Answer 1

您可以使用窗口函数对产品进行分类，然后聚合：

select merchant_id, product_status, product_state, count(*) as cnt
from (select t.*,
(case when min(store_id) over (partition by product_id) = 
max(store_id) over (partition by product_id)
then min(store_id) over (partition by product_id) || ' only'
else 'both'
end) as product_state
from t
where store_id in ('store_a', 'store_b')
) mp
group by merchant_id, product_status, product_state;

这是一个数据库<>小提琴。