如何关联两表按日期范围统计订单完成用户的产品选择数

表结构说明 #

• Order表：存储5个固定配送日（周四、周五、周六、周日、周一）内的订单数据，字段包括order_id、cust_id、DeliveryDay、status（取值为completed、processing等）
• Selection表：存储订阅用户选择的当周配送产品数据，用户每周可从20款产品中选择，产品池每周更新，表中同时保留4-5周前已取消订阅用户的历史选品记录，字段包括Cust_id、Product_id、ProductSlot、startDate（每周从周四开始，该字段为当周周四的日期）

核心统计规则 #

1. 仅统计订单状态为Completed的用户对应的产品选择量
2. 需校验订单配送日属于选品表对应周的5个配送周期内，再按product_id统计选品数
3. 失败订单、处理中订单对应的选品记录全部不计入统计：例如选品表中cust_id为1239的用户选择了2款产品，但其订单在Order表中状态为非完成，该用户的选品不计入统计

原始SQL问题梳理 #

原SQL未考虑配送日期匹配，且存在语法、逻辑错误，原语句如下：

Select product_id, cust_id, startDate, Number 
from selections S
Group by product_id
join orders O on S.cust_id = O.cust_id
where orders.status in('completed', processing)

具体问题：

• JOIN语句位置错误，应放在FROM之后、WHERE和GROUP BY之前
• 未匹配订单配送日和选品所属周的范围，会把跨周的订单和选品错误关联
• 过滤条件包含了processing状态，不符合仅统计完成订单的要求
• GROUP BY逻辑错误，仅按product_id分组但SELECT了多个非聚合字段，不符合SQL语法规范

修正后实现SQL #

以下以MySQL语法为例，其他数据库仅需调整日期计算函数即可：

SELECT 
    S.Product_id,
    COUNT(DISTINCT S.ProductSlot) AS valid_selected_count
FROM 
    selections S
INNER JOIN 
    orders O 
    ON S.Cust_id = O.cust_id
    -- 匹配订单配送日属于选品对应的周配送周期（周四到下周一共5天）
    AND O.DeliveryDay BETWEEN S.startDate AND DATE_ADD(S.startDate, INTERVAL 4 DAY)
WHERE 
    O.status = 'completed'
    -- 若仅需统计当周数据，可放开以下注释
    -- AND S.startDate = (SELECT MAX(startDate) FROM selections)
GROUP BY 
    S.Product_id
ORDER BY 
    valid_selected_count DESC;

逻辑说明 #

• 用INNER JOIN关联两张表，自动排除没有对应完成订单的选品记录
• 关联条件增加配送日范围匹配，确保订单属于选品对应的周配送周期
• 仅保留status为completed的订单，过滤处理中、失败的订单
• 按Product_id分组，用DISTINCT避免同一用户同个产品多次选择导致的重复计数，统计每个产品的有效选品数

内容的提问来源于stack exchange，提问作者Manjeet Singh