工作SQL问题解决 需求：各类商品互斥人群

计算各类商品购买人数，并按最小人群去逐一进行互斥

互斥名词解释：例如现在是购买常温饮料（G1）人数最少，面包（G2）人数第二少，咖啡（G3）人数第三少

常温饮料互斥人数为 G1的总人数

面包互斥人数应为 G2-(G2*G1)  [面包总人数-既买过面包又买过常温饮料的人数]

咖啡互斥人数应为 G3-（G2*G3+G1*G3+G1*G2*G3）[......]

根据这个思路，问题主要就是怎么去标记这些Group组合

可以根据用户id进行sum开窗，然后用1，2，4进行标记，各类用户

SUM(case
when material_class_desc_large = '常温饮料' then 1
when material_class_desc_large = '面包' then 2 end
) over (partition by super_id) as group_bread,
SUM(case
when material_class_desc_large = '常温饮料' then 1
when material_class_desc_large = '面包' then 2
when material_class_desc_large = '咖啡' then 4
end
) over (partition by super_id) as group_ice_drink

然后计算人数时给上大于最小人群和目前匹配人群标记号值的和

select count(distinct super_id) as `人口`, '面包' as divide_logo
from tmp4_divide_cate
where group_bread >= 3
union
select count(distinct super_id) as `人口`, '冷藏饮料' as divide_logo
from tmp4_divide_cate
where group_cafe >= 5

然后用各类总人数减去对应的各类互斥组合人数，就可以得到总人数