SQLite中如何对比客户不同日期的订单数量？

没问题，我来帮你搞定这个需求！首先先把你那半写完的每日订单统计语句补全，确保能正确生成每个客户每天的订单总量数据：

SELECT 
    A.customer_id, 
    B.name, 
    SUM(A.quantity) AS daily_total, 
    strftime('%Y-%m-%d', A.order_date) AS order_date
FROM orders A
LEFT JOIN customers B ON A.customer_id = B.customer_id
GROUP BY A.customer_id, B.name, strftime('%Y-%m-%d', A.order_date)
ORDER BY A.customer_id, order_date;

基于这个基础数据，我分两种常见场景给你提供对比方案：

1. 对比同一客户相邻日期的订单量（比如和前一天的差异） #

SQLite支持LAG()窗口函数，它能帮我们获取同一客户分组内上一个日期的订单数据，直接算出和前一天的订单量差值：

WITH daily_customer_orders AS (
    SELECT 
        A.customer_id, 
        B.name, 
        SUM(A.quantity) AS daily_total, 
        strftime('%Y-%m-%d', A.order_date) AS order_date
    FROM orders A
    LEFT JOIN customers B ON A.customer_id = B.customer_id
    GROUP BY A.customer_id, B.name, strftime('%Y-%m-%d', A.order_date)
)
SELECT 
    customer_id,
    name,
    order_date,
    daily_total,
    LAG(daily_total) OVER (PARTITION BY customer_id ORDER BY order_date) AS previous_day_total,
    daily_total - LAG(daily_total) OVER (PARTITION BY customer_id ORDER BY order_date) AS quantity_diff_from_prev_day
FROM daily_customer_orders
ORDER BY customer_id, order_date;

说明：PARTITION BY customer_id确保只在当前客户的范围内查找前一天数据，ORDER BY order_date保证日期按时间顺序排列，这样LAG()就能准确拿到上一个日期的订单总量，最后一列直接算出和前一天的数量差。

2. 对比同一客户任意两个指定日期的订单量 #

如果你需要对比特定两个日期（比如2023-10-01和2023-10-05）的订单量，可以用自连接的方式实现：

WITH daily_customer_orders AS (
    SELECT 
        A.customer_id, 
        B.name, 
        SUM(A.quantity) AS daily_total, 
        strftime('%Y-%m-%d', A.order_date) AS order_date
    FROM orders A
    LEFT JOIN customers B ON A.customer_id = B.customer_id
    GROUP BY A.customer_id, B.name, strftime('%Y-%m-%d', A.order_date)
)
SELECT 
    d1.customer_id,
    d1.name,
    d1.order_date AS date1,
    d1.daily_total AS total1,
    d2.order_date AS date2,
    d2.daily_total AS total2,
    d1.daily_total - d2.daily_total AS quantity_diff
FROM daily_customer_orders d1
JOIN daily_customer_orders d2 ON d1.customer_id = d2.customer_id
WHERE d1.order_date = '2023-10-01' AND d2.order_date = '2023-10-05' -- 替换成你要对比的日期
ORDER BY d1.customer_id;

如果有些客户在其中某一天没有订单，你可以用LEFT JOIN配合COALESCE()把NULL值转为0，避免计算错误：

WITH daily_customer_orders AS (
    SELECT 
        A.customer_id, 
        B.name, 
        SUM(A.quantity) AS daily_total, 
        strftime('%Y-%m-%d', A.order_date) AS order_date
    FROM orders A
    LEFT JOIN customers B ON A.customer_id = B.customer_id
    GROUP BY A.customer_id, B.name, strftime('%Y-%m-%d', A.order_date)
)
SELECT 
    d1.customer_id,
    d1.name,
    d1.order_date AS date1,
    COALESCE(d1.daily_total, 0) AS total1,
    '2023-10-05' AS date2, -- 替换成目标对比日期
    COALESCE(d2.daily_total, 0) AS total2,
    COALESCE(d1.daily_total, 0) - COALESCE(d2.daily_total, 0) AS quantity_diff
FROM daily_customer_orders d1
LEFT JOIN daily_customer_orders d2 
    ON d1.customer_id = d2.customer_id 
    AND d2.order_date = '2023-10-05' -- 替换成目标对比日期
WHERE d1.order_date = '2023-10-01' -- 替换成基准日期
ORDER BY d1.customer_id;

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