sql - 在 BigQuery 中交叉应用历史日期范围

Question

我有一个不断增长的订单表，看起来像这样：

units_sold	时间戳
1	2021-03-02 10:00:00
2	2021-03-02 11:00:00
4	2021-03-02 12:00:00
3	2021-03-03 13:00:00
9	2021-03-03 14:00:00

我试图将表格划分为每一天，并收集当天和前一天售出的单位的统计数据。我可以很容易地在今天和昨天买到今天和昨天的单位，但我需要为我的订单表中的每个日期交叉应用一个日期范围。

预期结果如下所示：

单位_已售_昨天	今日售出单位	日期测量
12	7	2021-03-02
无效的	12	2021-03-03

一种方法是每天创建订单数据或将订单数据附加到新表中。但是，这个表可能会变得非常大，而且我也需要历史数据。

在我看来，我知道我已经级联了数据，以便 BigQuery 将数据与“今天的日期”进行比较，该日期将跨越表中的所有日期。

我认为这种转变可能来自表中所有不同日期的交叉应用，所以我会得到每个日期的订单表的副本，但有一个不同的“今天日期”列，我可以推断units_sold_today通过使用该列将数据与销售日期进行日期差异。

但是，这仍然会创建大量数据来处理，我猜 BigQuery 或标准 SQL 语法中可能有一个简单的函数。

标签： sqlgoogle-bigquery

Answer 1

考虑下面

select date_measured, units_sold_today,
  lag(units_sold_today) over(order by date_measured) units_sold_yesterday,
from (
  select date(timestamp) date_measured,
    sum(units_sold) units_sold_today
  from `project.dataset.table` 
  group by date_measured
)    

如果应用于您问题中的样本数据 - 输出是