按日展示数据并统计每月/每年唯一订单ID数的SQL查询需求

测试数据与表结构 #

CREATE TABLE sales (
 id int auto_increment primary key,
 orderID VARCHAR(255),
 sent_date DATE
);
INSERT INTO sales (orderID, sent_date ) VALUES
("Order_01", "2019-03-15"),
("Order_01", "2019-03-16"),
("Order_02", "2020-06-16"),
("Order_03", "2020-07-27"),
("Order_03", "2020-08-05"),
("Order_03", "2020-08-10");

需求回顾 #

我们需要按日展示数据，统计每日首次出现的唯一orderID数量：如果某个orderID在当月/当年的更早日期已经出现过，那么该日期对应的计数为0，否则记为1，最终得到每日的汇总数。

解决方案SQL #

SELECT 
    s.sent_date,
    COUNT(DISTINCT CASE WHEN s.sent_date = first_occur.first_sent THEN s.orderID END) AS `COUNT(distinct orderID)`
FROM 
    sales s
LEFT JOIN (
    -- 子查询：获取每个订单在对应年月的首次发送日期
    SELECT 
        orderID,
        YEAR(sent_date) AS order_year,
        MONTH(sent_date) AS order_month,
        MIN(sent_date) AS first_sent
    FROM sales
    GROUP BY orderID, order_year, order_month
) first_occur ON 
    s.orderID = first_occur.orderID 
    AND YEAR(s.sent_date) = first_occur.order_year 
    AND MONTH(s.sent_date) = first_occur.order_month
GROUP BY s.sent_date
ORDER BY s.sent_date;

逻辑拆解 #

• 子查询first_occur：为每个orderID按年份和月份分组，计算出该订单在对应年月里的最早发送日期。这一步是判断订单是否为首次出现的核心依据。
• 关联与条件判断：将原表与子查询结果关联，通过CASE语句筛选出当日是首次出现的订单——只有当当前行的sent_date等于该订单的首次发送日期时，才保留该orderID，否则返回NULL（COUNT函数会自动忽略NULL值）。
• 分组统计：最后按sent_date分组，统计每日符合条件的唯一orderID数量，就能得到预期结果。

预期输出 #

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