SQLite查询加入一系列日期？ [英] SQLite query to join on a range of dates?

问题描述

我正在使用SQLite。

I am working with SQLite.

假设我有一个表 sales ，有两列，日期和计数，以跟踪在该日期卖出的几杯柠檬水。如果我在某一天不出售任何柠檬水，我太沮丧，不能在销售表中创建一个新的行。

Suppose I have a table sales with two columns, date and count, to keep track of how many glasses of lemonade I sold on that date. If I don't sell any lemonade on a given day, I am too depressed to create a new row in the sales table.

我想知道在特定日期范围内出售的平均眼镜数量。我可以发出这样的查询：

I'd like to find out the average number of glasses sold over a given date range. I could issue a query like this:

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

但是，除非该范围内的所有日期都包含在表。我需要一些方法来告诉SQLite将丢失的行计算为零。

However, the result won't be accurate unless all of the dates in that range are included in the table. I need some way to tell SQLite to count the missing rows as zeroes.

我想像如果我可以在日期范围内加入销售表，那将是理想的。这似乎是不可能的，所以从日期范围创建表的简单方法可能是次要的事情。我不想修改销售表，这似乎是一个柠檬水立场愚蠢，但在现实生活中更有意义。

I imagine if I could join the sales table on a date range, that would be ideal. That doesn't seem to be possible, so a simple way of creating a table from a date range is probably the next best thing. I don't want to modify the sales table, which seems silly for a lemonade stand but makes more sense in the real life counterpart.

如上所述，我是使用SQLite，所以如果你推荐其他数据库给我，你没有帮助。也就是说，如果您有更多的经验与另一个数据库，并认为您的解决方案可以在香草SQL，我很乐意听到。

As I said above, I am using SQLite, so you are not being helpful if you recommend other databases to me. That said, if you have more experience with another database and think your solution can be done in vanilla SQL, I'd love to hear it.

推荐答案

创建一个日历表，并加入到这里：

Create a calendar table and join onto that:

这样做可以做到：

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有点生锈，所以建议您参考手册。

That will cover you till 2287-05-20 Adding an index to the date column in the dates table won't hurt. My sqlite is little rusty, so I suggest you consult the manual on that.

编辑：索引代码：

create unique index ux_dates_date on dates (date);

这篇关于SQLite查询加入一系列日期？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！