技术问询：查询2007年7月每日首次预约的新客户数量

需求说明 #

找出2007年7月每日进行首次预约的新客户。

涉及表信息 #

• 表1.A - order_item：按日展示订单级别的交易记录
• 表1.B - first_appointment：展示每位客户的首次预约日期（每人一条记录）

关键字段与说明 #

• customer_id：客户唯一标识
• order_item.create_datetime：客户预约的时间
• first_appointment.result_first_appointment_time：客户首次预约的时间

我写的SQL（存在问题） #

SELECT 
    DATE(order_item.create_datetime) AS appointment_date,
    COUNT(DISTINCT oi.customer_id) AS new_customers
FROM 
    order_item
JOIN 
    first_appointment ON order_item.customer_id = first_purchase.customer_id
WHERE 
    DATE(order_item.create_datetime) BETWEEN '2021-07-01' AND '2021-07-30'
GROUP BY 
    DATE(order_item.create_datetime);

问题分析 #

1. 表名笔误：JOIN语句里的first_purchase是错误表名，实际应为first_appointment
2. 时间范围错误：需求是统计2007年7月的数据，但WHERE条件写的是2021年
3. 逻辑偏差：统计的是「首次预约日期在目标月份」的客户，应以first_appointment.result_first_appointment_time作为日期依据，而非order_item.create_datetime（该表可能包含客户后续预约记录）
4. 别名未定义：SELECT中使用了oi.customer_id，但未给order_item设置oi别名

修正后的SQL #

基础版（直接用首次预约表统计） #

SELECT 
    DATE(fa.result_first_appointment_time) AS create_date,
    COUNT(DISTINCT fa.customer_id) AS num_of_first_appointment_customers
FROM 
    first_appointment fa
WHERE 
    DATE(fa.result_first_appointment_time) BETWEEN '2007-07-01' AND '2007-07-31'
GROUP BY 
    DATE(fa.result_first_appointment_time)
ORDER BY 
    create_date;

关联订单表版（确保首次预约有对应订单记录） #

如果需要验证首次预约确实产生了订单，可关联order_item表并匹配日期：

SELECT 
    DATE(fa.result_first_appointment_time) AS create_date,
    COUNT(DISTINCT fa.customer_id) AS num_of_first_appointment_customers
FROM 
    first_appointment fa
JOIN 
    order_item oi ON fa.customer_id = oi.customer_id
    AND DATE(fa.result_first_appointment_time) = DATE(oi.create_datetime)
WHERE 
    DATE(fa.result_first_appointment_time) BETWEEN '2007-07-01' AND '2007-07-31'
GROUP BY 
    DATE(fa.result_first_appointment_time)
ORDER BY 
    create_date;

预期结果 #

内容的提问来源于stack exchange，提问作者Da Rorikstead