假设我有下表:
+ ------------ + ------------- + | 产品编号| customer_id | + ------------ + ------------- + | 一个| c1 | | 一个| c2 | | 一个| c3 | | 一个| c4 | | b | c1 | | c | c1 | | b | c2 | | d | c2 | + ------------ + ------------- +
我想查找每个客户购买(a,b,c)产品的数量以及每个客户购买(a,b,d)产品的数量。我尝试与一起使用COUNT
,GROUP BY
但只设法找到每个客户FIDDLE的购买数量。我需要使用CASE WHEN
还是DECODE
?我该如何实现?
预期的输出如下所示:
+ ------------- + ------------- + ------------- + | CUSTOMER_ID | ABC_PRODUCT | ABD_PRODUCT | + ------------- + ------------- + ------------- + | c1 | 1 | 0 | | c2 | 0 | 1 | | c3 | 0 | 0 | | c4 | 0 | 0 | + ------------- + ------------- + ------------- +
您可以使用单个聚合来执行此操作,而无需子查询。关键是使用带有聚合的嵌套case语句为每个客户计算每个产品的数量。以下确定客户是否拥有每个“捆绑销售商品”:
SELECT CUSTOMER_ID,
(case when max(case when product_id = 'a' then 1 else 0 end) +
max(case when product_id = 'b' then 1 else 0 end) +
max(case when product_id = 'c' then 1 else 0 end) = 3
then 1
else 0
end) as ABC,
(case when max(case when product_id = 'a' then 1 else 0 end) +
max(case when product_id = 'b' then 1 else 0 end) +
max(case when product_id = 'd' then 1 else 0 end) = 3
then 1
else 0
end) as ABD
FROM CUSTOMERS_SALES
GROUP BY CUSTOMER_ID;
现在,您的问题实际上是关于此类购买的数量。因此,我假设一个客户可以购买两次每个商品,而您希望它们计算两次。如果是这样,那么数量是最少的任何计数的值。您也可以得到以下信息:
SELECT CUSTOMER_ID,
least(sum(case when product_id = 'a' then 1 else 0 end),
sum(case when product_id = 'b' then 1 else 0 end),
sum(case when product_id = 'c' then 1 else 0 end)
) as ABC,
least(sum(case when product_id = 'a' then 1 else 0 end),
sum(case when product_id = 'b' then 1 else 0 end),
sum(case when product_id = 'd' then 1 else 0 end)
) as ABD
FROM CUSTOMERS_SALES
GROUP BY CUSTOMER_ID;
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句