sql - 如何生成日期系列以占用谷歌 BigQuery 中缺失的日期？

Question

我试图从谷歌大查询表中获取每日销售额。我为此使用了以下代码。

select Day(InvoiceDate) date, Sum(InvoiceAmount) sales from test_gmail_com.sales 
where year(InvoiceDate) = Year(current_date()) and
Month(InvoiceDate) = Month(current_date())
group by date order by date

从上面的查询中，它只给出了表中每天的销售额总和。有可能有些日子没有任何销售。对于这种情况，我需要获取日期，并且总和应该是 0。例如，每个月应该有 30 0r 31 行销售总和。示例如下所示。每月的第 4 天没有销售。所以它的总和应该是0。

date | sales
-----+------
1    |   259
-----+------
2    |   359
-----+------
3    |   45
-----+------
4    |    0
-----+------
5    |  156

可以在大查询中做吗？基本上日期列应该是 1 - 28/29/30 或 31 日的系列，具体取决于一年中的月份

Answer 1

生成日期列表，然后加入您需要的任何表格似乎是最简单的。我用了 generate_date_array + unnest而且看起来很干净。

生成天数列表(每行一天):

  SELECT
  *
  FROM 
    UNNEST(GENERATE_DATE_ARRAY('2018-10-01', '2020-09-30', INTERVAL 1 DAY)) AS example