Oracle如何查询今日为其历史最常下单日的客户列表

实现思路 #

• 先统计每个客户历史上每个月内日期的下单次数（保留原有逻辑）
• 用窗口函数对每个客户的所有下单日期按下单次数降序排名
• 过滤出每个客户排名第一的高频下单日期，再匹配今日的月内日期数值，即可得到需要联系的客户列表

通用SQL实现（支持CTE和窗口函数的数据库通用） #

WITH customer_order_day_stats AS (
    -- 统计每个客户各日期的下单量
    SELECT 
        p.IDCOMPANY AS IDCOMPANY,
        EXTRACT(DAY FROM p.DATEORDER) AS order_day,
        COUNT(*) AS order_quantity
    FROM ORDERS p 
    GROUP BY p.IDCOMPANY, EXTRACT(DAY FROM p.DATEORDER)
),
customer_top_order_day AS (
    -- 对每个客户的下单日期按下单量排名
    SELECT 
        IDCOMPANY,
        order_day,
        RANK() OVER (PARTITION BY IDCOMPANY ORDER BY order_quantity DESC) AS day_rank
    FROM customer_order_day_stats
)
-- 过滤出今日为最高频下单日的客户
SELECT DISTINCT IDCOMPANY
FROM customer_top_order_day
WHERE day_rank = 1 
AND order_day = EXTRACT(DAY FROM CURRENT_DATE)

低版本数据库兼容写法（无CTE支持） #

SELECT DISTINCT IDCOMPANY
FROM (
    SELECT 
        IDCOMPANY,
        order_day,
        RANK() OVER (PARTITION BY IDCOMPANY ORDER BY order_quantity DESC) AS day_rank
    FROM (
        SELECT 
            p.IDCOMPANY AS IDCOMPANY,
            EXTRACT(DAY FROM p.DATEORDER) AS order_day,
            COUNT(*) AS order_quantity
        FROM ORDERS p 
        GROUP BY p.IDCOMPANY, EXTRACT(DAY FROM p.DATEORDER)
    ) t1
) t2
WHERE day_rank = 1 
AND order_day = EXTRACT(DAY FROM CURRENT_DATE)

注意事项 #

• 若存在多个日期下单量一致且同为最高值的场景，希望仅返回一个优先日期时，可以将RANK()替换为ROW_NUMBER()，并在排序规则后追加自定义优先级，例如ORDER BY order_quantity DESC, order_day ASC会优先保留更早的日期
• 不同数据库的日期函数可能存在差异，例如SQL Server获取今日日份可以用DAY(GETDATE())，MySQL可以用DAY(CURDATE())，根据实际使用的数据库调整即可
• 若业务规则的判定维度是周几而非月内几号，将所有EXTRACT(DAY FROM 日期)替换为EXTRACT(DOW FROM 日期)（不同数据库周几的取值范围可能为0-6或1-7，注意校准）即可

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