MySQL查询连续3天订单额超$50客户时遇Error 3588问题排查

需要从sales.mytable表中找出连续3天有订单且每笔订单金额大于$50的客户，编写的SQL语句如下：

SELECT T.Customer_ID, T.Customer_Name, T.Order_Date
 FROM (SELECT T.*, COUNT(*) OVER(ORDER BY Order_Date RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) cnt_days
          FROM sales.mytable T
         WHERE Sales>'50')T
 WHERE cnt_days = 3;

执行时收到错误：

Error Code: 3588. Window '' with RANGE frame has ORDER BY expression of datetime type. Only INTERVAL bound value allowed

示例表数据：

# Row_ID, Order_ID, Order_Date, Ship_Date, Ship_Mode, Customer_ID, Customer_Name, Segment, Country, City, State, Postal_Code, Region, Product_ID, Category, Sub_Category, Product_Name, Sales
'1', 'CA-2017-152156', '2017-11-08', '2017-11-11', 'Second Class', 'CG-12520', 'Claire Gute', 'Consumer', 'United States', 'Henderson', 'Kentucky', '42420.0', 'South', 'FUR-BO-10001798', 'Furniture', 'Bookcases', 'Bush Somerset Collection Bookcase', '261.9600'

你写的RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING存在核心问题：当窗口函数的ORDER BY字段是日期/时间类型时，MySQL要求RANGE的边界必须用INTERVAL关键字明确指定时间间隔，不能直接写数字1。直接写1会被MySQL当作数值处理，而非天数，因此触发错误。

另外原语句还有两处逻辑漏洞：

1. 未按客户分组，会把所有客户的订单混在一起统计，无法准确判断单个客户的连续订单情况；
2. COUNT(*)会统计窗口内所有订单数，若同一客户同一天有多笔订单，会导致计数大于3，无法精准判断是否为连续3天。

方案1：适配多订单场景的连续日期判断 #

WITH filtered_orders AS (
    SELECT Customer_ID, Customer_Name, Order_Date
    FROM sales.mytable
    WHERE Sales > 50  -- Sales为数值类型时无需加引号
),
ranked_orders AS (
    SELECT 
        *,
        ROW_NUMBER() OVER(PARTITION BY Customer_ID ORDER BY Order_Date) AS rn,
        DATE_SUB(Order_Date, INTERVAL ROW_NUMBER() OVER(PARTITION BY Customer_ID ORDER BY Order_Date) DAY) AS group_date
    FROM filtered_orders
)
SELECT Customer_ID, Customer_Name, MIN(Order_Date) AS start_date, MAX(Order_Date) AS end_date
FROM ranked_orders
GROUP BY Customer_ID, Customer_Name, group_date
HAVING DATEDIFF(MAX(Order_Date), MIN(Order_Date)) >= 2;  -- 连续3天的日期差至少为2天

方案2：修正窗口函数写法（适用于客户单日仅一笔订单场景） #

若能确保每个客户每天最多一笔订单，可使用以下简化写法：

SELECT DISTINCT T.Customer_ID, T.Customer_Name
FROM (
    SELECT 
        *,
        COUNT(*) OVER(
            PARTITION BY Customer_ID 
            ORDER BY Order_Date 
            RANGE BETWEEN INTERVAL 1 DAY PRECEDING AND INTERVAL 1 DAY FOLLOWING
        ) AS cnt_days
    FROM sales.mytable
    WHERE Sales > 50
) T
WHERE cnt_days = 3;

• 必须用PARTITION BY Customer_ID将数据按客户拆分，保证统计的是单个客户的订单连续性；
• 日期类型的RANGE窗口边界必须用INTERVAL X DAY格式指定；
• 若客户同一天有多笔订单，方案1更准确，它通过日期分组逻辑避免重复计数干扰连续天数判断。

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