【数据分析面试】16.查找会员的发货记录（SQL：JOIN vs LEFT JOIN）

题目

创建一个报告，显示哪些货物在客户的会员期间被送达给了客户。

如果交易在会员期间内发货，is_member列的值应为Y。否则，该列的值应为N。

提示：可以假设customer_id是唯一的，并且只向已注册客户发货。

输入：
customers表

shipments表

输出：

答案

解题思路

1. 将shipments表和customers表通过customer_id关联起来
2. 选择需要的shipments字段
3. 增加条件判断字段is_member，使用CASE WHEN语句判断ship_date是否在会员期间。

答案代码

SELECT 
	s.shipment_id
	,s.ship_date
	,s.customer_id
	,CASE WHEN s.ship_date 
		BETWEEN c.membership_start_date AND c.membership_end_date 
		THEN 'Y' ELSE 'N' END 
	AS is_member
	,s.quantity 
FROM 
	shipments s 
JOIN 
	customers c 
ON s.customer_id = c.customer_id;

Join 和 Left Join的区别

Join要求两边都有匹配，Left Join则保证左边全部显示。

题目假设是：customer_id是唯一的，并且只向已注册客户发货

因此这里使用JOIN或LEFT JOIN都是正确的。

因为题目只要求会员的发货记录，所以使用JOIN（或INNER JOIN）更加合适。如果不是发货给会员，那么这些发货记录不应包含在结果集中。

如果前提假设取消……

1. customer_id不是唯一的，并且存在向未注册客户发货的情况

这时，使用LEFT JOIN更合适，因为它会返回shipments表中的所有记录，即使某些记录没有与customers表中的记录匹配。对于没有匹配会员的发货，customers表的相关字段将为NULL。

SELECT 
	s.shipment_id
	, s.ship_date
	, s.customer_id
	, CASE WHEN 
		c.customer_id IS NOT NULL 
		AND 
		s.ship_date BETWEEN c.membership_start_date AND c.membership_end_date 
		THEN 'Y' ELSE 'N' END 
		AS is_member
	, s.quantity 
FROM 
	shipments s 
LEFT JOIN 
	customers c ON s.customer_id = c.customer_id;

• 在CASE语句中，增加判断条件c.customer_id IS NOT NULL 。
• 如果c.customer_id为NULL，说明这笔发货没有对应的会员记录，因此is_member直接为’N’。
• 如果不为NULL，说明有配对的会员记录，然后再检查发货日期是否在会员期间内。如果两个条件都满足，则is_member为’Y’；否则为’N’。

2. customer_id不是唯一的，而且有重复记录

当存在多个具有相同customer_id的会员记录时（例如用户在有的月份续费了会员，在有的月份取消续费），使用LEFT JOIN会导致对于同一个shipment_id返回多条记录，因为每个匹配的customer_id都会生成一条结果。

可以采取的策略有：

1. 使用聚合函数和分组
2. 使用子查询和EXISTS
3. 清理错误数据

如果多个相同的customer_id不应该存在，或者它们代表不同的概念（比如历史记录或误输入），那么可能需要清理数据或重新设计数据库结构来确保customer_id的唯一性。这通常涉及到与业务团队沟通以确定正确的数据处理策略。

更多详细答案可关注公众号查阅。