Sqlite获取最近30天的列表，每天一行

Question

我将日期存储在int字段中，当我选择它时，我使用

strftime('%Y-%m-%d', table.date/1000, 'unixepoch') as date

我需要有过去30天的数据用于制图，这意味着我需要在最后的查询中有这样的数据：

2017-01-18，2

2017-01-17，0

2017-01-16，0

2017-01-15，1

在我的表中，我只有1月18日和15日的行。因此，对它运行查询，将只返回存在日期的行，

2017-01-18，2

2017-01-15，1

然而，作为最终结果，我需要在过去30天中有值为0的行，这些行没有任何值。为了做到这一点，我需要一种方法将我的数据与返回的查询结合起来

现在-1天，0

现在2天，0

现在-3天，0……

过去30天。这样，当我组合这两个查询时，将得到所需的结果。

使用来自https://stackoverflow.com/a/32987070/379865

我设法得到了的数据

WITH RECURSIVE dates(date) AS (
VALUES(date('now'))
UNION ALL
SELECT date(date, '-1 day')
FROM dates
limit 30
)
SELECT date, 0 as value FROM dates;

然而，我不知道如何将它与我的另一个查询联合起来，因为with似乎并不是联合的一部分——所有

Answer 1

我不完全确定你在追求什么，但我会试试的。

按strftime('%Y-%m-%d', table.date/1000, 'unixepoch')聚合，然后进行计数。

例如

SELECT COUNT(*), strftime('%Y-%m-%d', table.date/1000, 'unixepoch') as date FROM table GROUP BY strftime('%Y-%m-%d', table.date/1000, 'unixepoch')

希望这就是你想要的。。

Answer 2

WITH确实支持复合查询：

WITH ...
SELECT date, 0 AS value FROM dates
UNION ALL
SELECT ...;

但是，要删除现有日期的伪行，应该使用join:

WITH ...
SELECT ...
FROM dates
LEFT JOIN (SELECT ...your actual query...)
USING (date);