选择日期范围内的计数

Question

我希望能够统计两个日期之间（包括上周的今天）所有条目的数量，并且如果没有条目，则选择0。当前输出结果如下：

+-------+------------+
| items | SellDate   |
+-------+------------+
| 1     | 2017-01-01 |
+-------+------------+
| 3     | 2017-01-02 |
+-------+------------+
| 1     | 2017-01-03 |
+-------+------------+
| 5     | 2017-01-06 |
+-------+------------+

然而，我需要像这样打印的内容：

+-------+------------+
| items | SellDate   |
+-------+------------+
| 1     | 2017-01-01 |
+-------+------------+
| 3     | 2017-01-02 |
+-------+------------+
| 1     | 2017-01-03 |
+-------+------------+
| 0     | 2017-01-04 |
+-------+------------+
| 0     | 2017-01-05 |
+-------+------------+
| 5     | 2017-01-06 |
+-------+------------+
| 0     | 2017-01-07 |
+-------+------------+

我的查询看起来像这样：

SELECT 
    COUNT(Item.id) AS Items,
    DATE(Item.sold_at) AS SellDate
FROM Item
WHERE Item.sold_at IS NOT NULL AND Item.sold_at BETWEEN DATE(DATETIME('now', 'localtime', '-6 days')) AND DATE(DATETIME('now', 'localtime', '+1 day'))
GROUP BY SellDate

我做错了什么？

- Chax

---

你没有做错任何事情。SQL不会为结果集生成日期。源数据中不存在零日期。 - Gordon Linoff

请查看此链接：https://dev59.com/mU3Sa4cB1Zd3GeqPtTdT - Sate Wedos

Answer 1

据我所知，在SQLite 3.8.3及更高版本中，如果支持递归公共表达式，则不可能实现此操作。使用相应的版本，您可以通过将日期范围与项目列表连接来实现：

WITH RECURSIVE dates(date) AS (
  VALUES(DATE(DATETIME('now', 'localtime', '-6 days')))
  UNION ALL
  SELECT date(date, '+1 day')
  FROM dates
  WHERE date < DATE(DATETIME('now', 'localtime', '+1 day'))
)
SELECT 
    date,
    COUNT(Item.id) AS Items
FROM 
    dates
LEFT JOIN
    Item
ON
    dates.date = Item.SellDate
GROUP BY
    SellDate

- Oleh Rybalchenko

---

这个解决方案效果非常好且性能良好。我稍微调整的一件事是 +1 day，因为它给了我明天，所以我完全删除了它。这行代码是 WHERE date < DATE(DATETIME('now', 'localtime'))。我还在 GROUP BY 后面添加了 ORDER BY dates.date，否则日期就不会按顺序排列。任何计数为零的值都在最上面。其他部分都很完美。谢谢，今天我学习到了可以使用 RECURSIVE，我将尽快阅读相关文献。 - Chax