如何生成日期系列以占用 google BiqQuery 中的缺席日期？

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 的系列，具体取决于一年中的月份

原文由 Manura Omal 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

您可以使用下面的动态生成给定范围内的所有日期（在下面的示例中，它是从 2015-06-01 到 CURRENT_DATE() 的所有日期 - 通过更改您可以控制要生成的日期范围的日期）

 SELECT DATE(DATE_ADD(TIMESTAMP("2015-06-01"), pos - 1, "DAY")) AS calendar_day
FROM (
     SELECT ROW_NUMBER() OVER() AS pos, *
     FROM (FLATTEN((
     SELECT SPLIT(RPAD('', 1 + DATEDIFF(TIMESTAMP(CURRENT_DATE()), TIMESTAMP("2015-06-01")), '.'),'') AS h
     FROM (SELECT NULL)),h
)))

所以，现在 - 您可以将它与 LEFT JOIN 与您的表格一起使用来计算所有日期。请参阅下面的潜在示例

SELECT
  calendar_day,
  IFNULL(sales, 0) AS sales
FROM (
  SELECT DATE(DATE_ADD(TIMESTAMP("2015-06-01"), pos - 1, "DAY")) AS calendar_day
  FROM (
       SELECT ROW_NUMBER() OVER() AS pos, *
       FROM (FLATTEN((
       SELECT SPLIT(RPAD('', 1 + DATEDIFF(TIMESTAMP(CURRENT_DATE()), TIMESTAMP("2015-06-01")), '.'),'') AS h
       FROM (SELECT NULL)),h
  )))
) AS all_dates
LEFT JOIN (
  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
)
ON DATE = calendar_day

我想获得前几个月的销售额

下面给出了上个月的所有日子

SELECT DATE(DATE_ADD(DATE_ADD(DATE_ADD(CURRENT_DATE(), -1, "MONTH"), 1 - DAY(CURRENT_DATE()), "DAY"), pos - 1, "DAY")) AS calendar_day
FROM (
     SELECT ROW_NUMBER() OVER() AS pos, *
     FROM (FLATTEN((
     SELECT SPLIT(RPAD('', 1 + DATEDIFF(DATE_ADD(CURRENT_DATE(), - DAY(CURRENT_DATE()), "DAY"), DATE_ADD(DATE_ADD(CURRENT_DATE(), -1, "MONTH"), 1 - DAY(CURRENT_DATE()), "DAY")), '.'),'') AS h
     FROM (SELECT NULL)),h
)))

原文由 Mikhail Berlyant 发布，翻译遵循 CC BY-SA 3.0 许可协议

Answer 2

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

要生成天数列表（每行一天）：

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

原文由 Cohen 发布，翻译遵循 CC BY-SA 4.0 许可协议