SQLite 查询连接日期范围？

Question

我正在使用 SQLite。

假设我有一个表 sales，其中包含两列:date 和 count，用于跟踪我售出了多少杯柠檬水那个日期。如果我在某一天没有卖出任何柠檬水，我会非常沮丧，无法在 sales 表中创建新行。

我想了解给定日期范围内销售的平均眼镜数量。我可以发出这样的查询:

SELECT AVG(count) FROM sales WHERE date IS BETWEEN '2010-01-04' AND '2010-01-10';

但是，除非该范围内的所有日期都包含在表中，否则结果将不准确。我需要某种方法来告诉 SQLite 将缺失的行计为零。

我想如果我可以在某个日期范围内加入销售表，那将是理想的。这似乎不可能，因此从日期范围创建表的简单方法可能是下一个最好的方法。我不想修改销售表，这对于柠檬水摊来说似乎很愚蠢，但在现实生活中更有意义。

正如我上面所说，我正在使用 SQLite，因此如果您向我推荐其他数据库，那对您没有帮助。也就是说，如果您对其他数据库有更多经验，并且认为您的解决方案可以用普通 SQL 完成，我很想听听。

Answer 1

创建一个日历表并加入其中:

像这样的事情就可以了:

create table dates (id integer primary key);
insert into dates default values;
insert into dates default values;
insert into dates select null from dates d1, dates d2, dates d3 , dates d4;
insert into dates select null from dates d1, dates d2, dates d3 , dates d4;
alter table dates add date datetime;
update dates set date=date('2000-01-01',(-1+id)||' day');

这将覆盖您直到 2287-05-20向日期表中的日期列添加索引不会有什么坏处。我的sqlite有点生锈了，建议你咨询the manual关于这一点。

编辑:索引代码:

create unique index ux_dates_date on dates (date);