.SQL.查找购买相同品牌且每个品牌至少购买 2 种产品的客户

Question

我有两个表：

Sales
columns:  (Sales_id, Date, Customer_id, Product_id, Purchase_amount):
Product 
columns:  (Product_id, Product_Name, Brand_id,Brand_name)

我必须编写一个查询来查找购买品牌"X"和"Y"(两者(的客户以及每个品牌的至少 2 种产品。以下查询是否正确？有什么建议的更改吗？

SELECT S.Customer_id "Customer ID"
FROM Sales S LEFT JOIN Product P
ON S.Product_id = P.Product_id
AND P.Brand_Name IN ('X','Y')
GROUP BY S.Customer_id
HAVING COUNT(DISTINCT S.Product_id)>=2 -----at least 2 products in each brand
AND COUNT(S.Customer_id) =2 ---------------customers who bought both brands

任何帮助将不胜感激。提前致谢

Answer 1

使用COUNT()窗口函数来计算每个客户购买的不同品牌的数量和每个品牌的不同产品的数量。
然后过滤掉尚未购买两个品牌的客户，并使用HAVING子句过滤掉未购买每个品牌至少 2 种产品的客户GROUP BY客户。
此外，您的联接应该是INNER联接，而不是LEFT联接。

select t.customer_id "Customer ID" 
from (
select s.customer_id,
count(distinct p.brand_id) over (partition by s.customer_id) brands_counter,
count(distinct p.product_id) over (partition by s.customer_id, p.brand_id) products_counter
from sales s inner join product p
on p.product_id = s.product_id
where p.brand_name in ('X', 'Y')
) t
where t.brands_counter = 2
group by t.customer_id
having min(t.products_counter) >= 2

Answer 2

从现有查询开始，可以使用以下HAVING子句：

HAVING 
AND COUNT(DISTINCT CASE WHEN p.brand_name = 'X' then S.product_id end) >= 2
AND COUNT(DISTINCT CASE WHEN p.brand_name = 'Y' then S.product_id end) >= 2

这可确保客户至少购买两个品牌的两种产品。这隐含地保证了它在两个品牌中都订购了订单，因此不需要额外的逻辑。

你也可以用MIN()和MAX()来表达这一点：

HAVING 
AND MIN(CASE WHEN p.brand_name = 'X' THEN S.product_id END)
<> MAX(CASE WHEN p.brand_name = 'X' then S.product_id end)
AND MIN(CASE WHEN p.brand_name = 'Y' THEN S.product_id END)
<> MAX(CASE WHEN p.brand_name = 'Y' then S.product_id end)

Answer 3

可以使用两个级别的聚合：

SELECT Customer_id
FROM (SELECT S.Customer_id, S.Brand_Name, COUNT(DISTINCT S.Product_Id) as num_products
FROM Sales S LEFT JOIN
Product P
ON S.Product_id = P.Product_id
WHERE P.Brand_Name IN ('X', 'Y')
GROUP BY S.Customer_id, S.Product_Id
) s
GROUP BY Customer_Id
HAVING COUNT(*) = 2 AND MIN(num_products) >= 2;