如何按周期对比销售数据？SQLite查询结果不符问题排查

尝试对比不同日期区间的销售数据增长率，在SQLite v3.39环境下基于VENTAS表编写查询语句后，返回结果不符合预期：查询计算了全表数据的总和，而非对应日期区间内各code分组的数据。

表结构与数据 #

CREATE TABLE "VENTAS" (
    "date" TEXT,
    "code"  TEXT,
    "qty"   REAL,
    "cost"  REAL,
    "price" REAL
);

INSERT INTO "VENTAS" VALUES
("2022-01-01","MARIO", 1, 1.00, 2.00),
("2022-01-05","MARIO", -1, -1.00, -2.00),
("2022-01-09","LUIGI", 1, 1.00, 2.00),
("2022-01-23","LUIGI", 1, 1.00, 2.00),
("2022-01-30","PEACH", -1, -1.00, -2.00),
("2022-02-01","MARIO", 1, 1.00, 2.00),
("2022-02-11","MARIO", -1, -1.00, -2.00),
("2022-02-19","LUIGI", 1, 1.00, 2.00),
("2022-02-28","LUIGI", 1, 1.00, 2.00),
("2022-03-01","PEACH", -1, -1.00, -2.00),
("2022-03-15","MARIO", 1, 1.00, 2.00),
("2022-03-20","MARIO", -1, -1.00, -2.00),
("2022-03-29","LUIGI", 1, 1.00, 2.00),
("2022-04-09","LUIGI", 1, 1.00, 2.00),
("2022-04-12","PEACH", -1, -1.00, -2.00),
("2022-04-18","MARIO", 1, 1.00, 2.00),
("2022-04-22","MARIO", -1, -1.00, -2.00),
("2022-04-22","LUIGI", 1, 1.00, 2.00),
("2022-05-13","LUIGI", 1, 1.00, 2.00),
("2022-05-25","PEACH", -1, -1.00, -2.00);

原查询SQL #

SELECT code,
(SELECT SUM(qty) WHERE date BETWEEN '2022-01-01' AND '2022-01-31') as qty,
(SELECT SUM(qty) WHERE date BETWEEN '2022-02-01' AND '2022-02-28') as qty2,
(SELECT SUM((price * ABS(qty))) WHERE date BETWEEN '2022-01-01' AND '2022-01-31') as sale,
(SELECT SUM((price * ABS(qty))) WHERE date BETWEEN '2022-02-01' AND '2022-02-28') as sale2
FROM VENTAS
WHERE qty != 0
GROUP BY code;

实际查询结果 #

期望结果 #

问题原因与解决方案 #

问题原因 #

原查询中的子查询未关联外层的code字段，且未指定查询表，导致子查询实际计算的是全表对应日期区间的总和，而非当前code分组下的数据；同时未处理无匹配数据的情况，导致qty2、sale2字段为空。

正确查询SQL #

使用条件聚合（SUM结合CASE WHEN）实现分组内的日期区间统计，同时保证无匹配数据时返回0：

SELECT 
    code,
    SUM(CASE WHEN date BETWEEN '2022-01-01' AND '2022-01-31' THEN qty ELSE 0 END) AS qty,
    SUM(CASE WHEN date BETWEEN '2022-02-01' AND '2022-02-28' THEN qty ELSE 0 END) AS qty2,
    SUM(CASE WHEN date BETWEEN '2022-01-01' AND '2022-01-31' THEN price * ABS(qty) ELSE 0 END) AS sale,
    SUM(CASE WHEN date BETWEEN '2022-02-01' AND '2022-02-28' THEN price * ABS(qty) ELSE 0 END) AS sale2
FROM VENTAS
WHERE qty != 0
GROUP BY code;

说明 #

• 条件聚合会在每个code分组内，仅对符合日期区间的行进行求和，确保数据是分组专属的；
• ELSE 0保证无匹配数据时返回0，而非空值，完全匹配期望结果格式；
• 无需子查询，语法简洁高效，适配SQLite 3.39版本。

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