检查其他列中是否存在条目，然后分类为exists_in_both_col; exists_in_colA;exists_

Question

• 对于每个merchant_id计数product_id，这些计数同时存在于store_a和store_b然后被归类为'shared_product
• 对于每个merchant_id计数product_id并找到哪些product_id分别存在于store_a或store_b中，然后将其分类为'exclusive_product_storeA'或'exclusive_product_storeB'

我的查询抛出重复的product_id

桌子

merchant_id   product_id    store_id    product_status
1             8328          store_a     new_product
2             9392          store_b     new_product
3             3828          store_b     old_product
1             8328          store_a     new_product
2             9392          store_b     new_product
3             3828          store_a     old_product

输出表

merchant_id    product_status   product_state              count_product
1              new_product      shared_products             2
3              new_product      exclusive_product_storeA    1
3              new_product      exclusive_product 1_storeB  2
2              old_product      shared_products             2
3              old_product      exclusive_product_storeA    1
1              old_product      exclusive_product 1_storeB  2

Answer 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;

这不包括结果集中的store_id- 它没有意义，因为product_state具有该信息。