SQLite选择超过实际7天(周)的数据，如果不存在，则为null/零

Question

我正在构建一个金融应用程序，需要选择一些特定的数据，这是在实际过去7天，即使其中一个日期不存在，只是返回0或空

具体地说，我有一个transactions表，并且有一个值，我需要返回今天最后7天的每天事务总数，而不是数据库上最后7天的事务总数

所以我需要的是这样的东西！

-----------
total|date
-----------
150 | 22/9
50  | 21/9
0   | 20/9
100 | 19/9
800 | 18/9
1500| 17/9
0   | 16/9

我当前的查询返回每天的交易总数和当天的日期字段

SELECT SUM(value), date as value FROM transactions GROUP BY STRFTIME('%d', DATE(date, 'unixepoch'))

Answer 1

您需要一个返回前7个日期的递归CTE和一个到要聚合的表的LEFT连接：

WITH week(date) AS (
  SELECT date('now', '-7 day') 
  UNION ALL 
  SELECT date(date, '+1 day') 
  FROM week 
  WHERE date < date('now', '-1 day') 
)
SELECT TOTAL(t.value) total, 
       strftime('%d/%m', w.date) date
FROM week w LEFT JOIN transactions t
ON date(t.date, 'unixepoch') = w.date
GROUP BY w.date
ORDER BY w.date DESC;