我正在和SQLite一起工作。
假设我有一个表sales,它有两列date和count,用来记录我当天卖出了多少杯柠檬汁。如果我在给定的一天没有卖出任何柠檬汁,我会非常沮丧,以至于无法在sales表中创建新的行。
我想知道在给定的日期范围内售出的眼镜的平均数量。我可以像这样发出一个查询:
SELECT AVG(count) FROM sales WHERE date IS BETWEEN '2010-01-04' AND '2010-01-10';但是,除非该范围内的所有日期都包含在表中,否则结果不会准确。我需要一些方法来告诉SQLite将缺少的行计为零。
我想如果我可以在一个日期范围内加入sales表,那将是最理想的。这似乎是不可能的,所以从日期范围创建表的简单方法可能是下一个最好的方法。我不想修改销售表,这对于柠檬水摊位来说似乎很愚蠢,但在现实生活中更有意义。
正如我上面所说的,我使用的是SQLite,所以如果你向我推荐其他数据库,你是没有帮助的。也就是说,如果您对另一个数据库有更多的经验,并且认为您的解决方案可以用普通的SQL完成,我很乐意听到它。
发布于 2010-01-12 05:32:29
创建一个日程表并连接到该表上:
下面这样的代码就可以了:
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向dates表中的date列添加索引不会有什么坏处。我的sqlite有点生锈了,所以我建议你咨询一下the manual。
编辑:索引的代码:
create unique index ux_dates_date on dates (date);发布于 2010-01-12 05:41:45
我认为你需要这样的东西:
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.
发布于 2016-04-07 21:19:52
您可以使用递归公用表表达式生成一系列日期并将其联接:
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/
https://stackoverflow.com/questions/2045015
复制相似问题