代码之家 › 专栏 › 技术社区 › benzado

要在一系列日期上联接的sqlite查询?

sqlite datetime sql

benzado · 技术社区 · 16 年前

我在和sqlite合作。

假设我有一张桌子 sales 有两列, date 和 count 为了记录下那天我卖了多少杯柠檬水。如果我在某一天不卖柠檬水,我会很沮丧,无法在 出售 表。

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

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

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

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

正如我上面所说,我使用的是sqlite,所以如果您向我推荐其他数据库,您就没有任何帮助。也就是说,如果您对另一个数据库有更多的经验,并且认为您的解决方案可以用普通的SQL来完成,那么我很乐意听听。

3 回复 | 直到 10 年前

My Other Me 10 年前

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

这样做可以做到:

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为止在日期表的日期列中添加索引不会有任何影响。我的青石有点生锈,所以我建议你咨询一下 the manual 就这点而言。

编辑: 索引代码:

create unique index ux_dates_date on dates (date);

Nick Dandoulakis 16 年前

我想你需要这样的东西:

SELECT sum(count) / ((strftime('%s','2010-01-10')-strftime('%s','2010-01-04')+86400)/86400)
FROM sales
WHERE date IS BETWEEN '2010-01-04' AND '2010-01-10';

查询通过在给定日期之间用秒计算确切的天数。
除以86400(=24*3600)秒。

例如

SELECT (strftime('%s','2010-03-01')-strftime('%s','2010-01-01') + 86400) / 86400

输出 60 这是正确的值。

查看sqlite的 Date And Time Functions.

Brady Holt 10 年前

可以使用递归公用表表达式生成一系列日期并将其联接:

WITH RECURSIVE
  cnt(x) AS (
    SELECT 0
    UNION ALL
    SELECT x+1 FROM cnt
    LIMIT (SELECT ((julianday('2010-01-10') - julianday('2010-01-04'))) + 1)
), 
WITH date_series AS (
  SELECT date(julianday('2010-01-04'), '+' || x || ' days') as date 
FROM date_series ds
)
SELECT AVG(COALESCE(s.count, 0)) as avg_sales_count
FROM date_series ds
  LEFT JOIN sales s ON ds.date = s.date

我写了一篇博文,上面有更多信息: http://www.geekytidbits.com/generate-date-range-sqlite/