MySQL查询：各产品两次有效订单记录间的最大间隔天数

假设存在如下orders_daily_data表，用于存储各产品每日的订单数量：

ID ProductType      Orders     OrderDate    
1   sugar           5          2023-01-04   
2   flour           0          2023-01-04   
3   pepper          7          2023-01-04
4   sugar           1          2023-01-05
5   flour           32         2023-01-05
6   pepper          0          2023-01-05
7   sugar           null       2023-01-06
8   pepper          1          2023-01-06
9   flour           0          2023-01-07
10  flour           0          2023-01-10
11  sugar           1          2023-01-11
12  pepper          1          2023-01-19
13  flour           1          2023-01-19

需求 #

查询每种产品两次有订单（Orders>0，null视为0）记录之间的最长未下单间隔天数。

预期结果 #

• sugar最长未下单间隔为2023-01-05至2023-01-11，共6天
• flour最长未下单间隔为2023-01-05至2023-01-19，共14天
• pepper最长未下单间隔为2023-01-06至2023-01-19，共13天

注意事项 #

• 表中可能存在日期间隙，即部分日期无产品记录
• null值视为0

用户尝试的SQL（未得到预期结果） #

select MAX(DATEDIFF(higher.OrderDate, lower.OrderDate)) from
orders_daily_data higher join orders_daily_data lower
on higher.ProductType = lower.ProductType and lower.OrderDate < higher.OrderDate
where higher.Orders > 0 and lower.Orders > 0
group by lower.ProductType 

解决方案 #

问题分析 #

原SQL的核心问题是：通过JOIN关联所有更早的有效订单记录，会计算非连续有效订单之间的间隔（比如sugar的2023-01-04和2023-01-11间隔7天），但我们需要的是连续两次有效订单之间的间隔，因此必须用窗口函数获取每个有效订单的上一个相邻有效订单日期。

正确SQL #

WITH valid_orders AS (
    -- 筛选所有有效订单记录（null视为0，只保留Orders>0的）
    SELECT 
        ProductType,
        OrderDate
    FROM orders_daily_data
    WHERE COALESCE(Orders, 0) > 0
),
order_intervals AS (
    -- 为每个有效订单匹配上一个有效订单日期，并计算间隔天数
    SELECT 
        ProductType,
        OrderDate AS current_order_date,
        LAG(OrderDate) OVER (PARTITION BY ProductType ORDER BY OrderDate) AS previous_order_date,
        DATEDIFF(OrderDate, LAG(OrderDate) OVER (PARTITION BY ProductType ORDER BY OrderDate)) AS interval_days
    FROM valid_orders
)
-- 筛选每个产品间隔天数最大的记录
SELECT 
    ProductType,
    previous_order_date AS 间隔起始日期,
    current_order_date AS 间隔结束日期,
    interval_days AS 最长间隔天数
FROM order_intervals
WHERE interval_days = (
    SELECT MAX(interval_days) 
    FROM order_intervals oi 
    WHERE oi.ProductType = order_intervals.ProductType
)
ORDER BY ProductType;

逻辑说明 #

1. valid_orders：先过滤出所有有效订单的日期，排除Orders为0或null的记录。
2. order_intervals：使用LAG()窗口函数，按产品分组、日期排序，获取每个有效订单的上一个有效订单日期，同时计算两个日期的间隔天数。
3. 外层查询：针对每个产品，找到间隔天数最大的那条记录，展示起止日期和间隔天数。

执行结果 #

该SQL会输出与预期完全一致的结果：

内容的提问来源于stack exchange，提问作者Michal W