如何生成日期系列以占用 google BiqQuery 中的缺席日期? [英] How to generate date series to occupy absent dates in google BiqQuery?

问题描述

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

从 test_gmail_com.sales 中选择 Day(InvoiceDate) 日期，Sum(InvoiceAmount) 销售额其中 year(InvoiceDate) = Year(current_date()) 和月(发票日期)= 月(当前日期())按日期分组 按日期排序

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

日期 |销售量-----+------1 |259-----+------2 |359-----+------3 |45-----+------4 |0-----+------5 |156

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

解决方案

您可以使用下面的方式生成给定范围内的所有日期(在下面的示例中，它是从 2015-06-01 到 CURRENT_DATE() 的所有日期 - 通过改变那些你可以控制的日期范围来生成)

SELECT DATE(DATE_ADD(TIMESTAMP("2015-06-01"), pos - 1, "DAY")) AS calendar_day从 (SELECT ROW_NUMBER() OVER() AS pos, *来自(扁平化((SELECT SPLIT(RPAD('', 1 + DATEDIFF(TIMESTAMP(CURRENT_DATE()), TIMESTAMP("2015-06-01")), '.'),'') AS hFROM (SELECT NULL)),h)))

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

SELECT日历日，IFNULL(sales, 0) AS 销售额从 (SELECT DATE(DATE_ADD(TIMESTAMP("2015-06-01"), pos - 1, "DAY")) AS calendar_day从 (SELECT ROW_NUMBER() OVER() AS pos, *来自(扁平化((SELECT SPLIT(RPAD('', 1 + DATEDIFF(TIMESTAMP(CURRENT_DATE()), TIMESTAMP("2015-06-01")), '.'),'') AS hFROM (SELECT NULL)),h)))) AS all_dates左加入 (SELECT DAY(InvoiceDate) DATE, SUM(InvoiceAmount) 销售额来自 test_gmail_com.salesWHERE YEAR(InvoiceDate) = YEAR(CURRENT_DATE()) ANDMONTH(InvoiceDate) = MONTH(CURRENT_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从 (SELECT ROW_NUMBER() OVER() AS pos, *来自(扁平化((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 hFROM (SELECT NULL)),h)))

I am trying to get daily sum of sales from a google big-query table. I used following code for that.

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

From the above query it gives only the sum of sales daily which were in the table. There is a chance that some days do not have any sales. For those kind of situations, I need to get the date and sum should be 0. As an example, in every month should 30 0r 31 rows with sum of sales. Examples show below. 4th day of the month does not have a sales. So its sum should be 0.

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

Is it possible to do in Big-query? Basically date column should be a series from 1 - 28/29/30 or 31st depending on the month of the year

解决方案

You can use below to generate on fly all dates in given range (in below example it is all dates from 2015-06-01 till CURRENT_DATE() - by changing those you can control which dates range to generate)

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
)))

so, now - you can use it with LEFT JOIN with your table to have all dates accounted. See potential example below

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  

I wanna need to get previous months sales

Below gives all days of previous month

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
)))

这篇关于如何生成日期系列以占用 google BiqQuery 中的缺席日期?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！