SQLite查询加入一系列日期？

我正在使用SQLite.

假设我有一个sales有两列的桌子,date并且count为了跟踪我在那一天卖了多少杯柠檬水.如果我在某一天没有出售任何柠檬水,我太沮丧了,无法在sales表格中创建新行.

我想知道在给定日期范围内销售的平均眼镜数量.我可以发出这样的查询:

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

但是,除非该范围内的所有日期都包含在表格中,否则结果将不准确.我需要一些方法告诉SQLite将丢失的行计为零.

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

正如我上面所说的,我正在使用SQLite,所以如果你向我推荐其他数据库,你就没有帮助.也就是说,如果您对其他数据库有更多经验,并认为您的解决方案可以在vanilla SQL中完成,我很乐意听到它.

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

这样的事情会做:

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有点生锈,所以我建议你查阅手册.

编辑:索引的代码:

create unique index ux_dates_date on dates (date);