sql - 计算每个商家 ID 的独占和共享 product_id

Question

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

并且计数product_id仅存在于store_a或store_b分别被分类为'exclusive_product_storeA'或'exclusive_product_storeB'

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

请不要只有store_a和store_b而是多个Mercer_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;

标签： sqlpresto

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;

这是一个 db<>fiddle。