填写BigQuery中联接表的缺失值

本文介绍了填写BigQuery中联接表的缺失值的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我在BigQuery中有一个表格，其中包含每天发生某些交易时的净库存量以及具有价格历史记录的表格，如下所示:

I have a table in BigQuery which contains net stock quantity for every day when some transaction occurs and table with price history, something like this:

WITH `trans` AS ( SELECT DATE '2018-10-02' trans_date, 10.0 quantity UNION ALL SELECT DATE '2018-10-03', 5.0 UNION ALL SELECT DATE '2018-10-05', 11.0 ), `prices` AS ( SELECT DATE '2018-10-01' price_date, 1.0 price UNION ALL SELECT DATE '2018-10-02', 2.0 UNION ALL SELECT DATE '2018-10-03', 3.0 UNION ALL SELECT DATE '2018-10-04', 4.0 UNION ALL SELECT DATE '2018-10-05', 5.0 UNION ALL SELECT DATE '2018-10-06', 6.0 UNION ALL SELECT DATE '2018-10-07', 7.0 ) SELECT price_date, quantity, price FROM ( SELECT price_date, quantity, price, trans_date FROM `trans` RIGHT JOIN `prices` ON trans.trans_date = prices.price_date ORDER BY price_date )

我不知道如何填写该日期没有交易的最后一个已知数量，以得到如下结果:

And I can't figure out how to fill in last known quantity where there is no transaction for that date, to get result something like this:

price_date quantity price 2018-10-01 0.0 1.0 2018-10-02 10.0 2.0 2018-10-03 5.0 3.0 2018-10-04 5.0 4.0 2018-10-05 11.0 5.0 2018-10-06 11.0 6.0 2018-10-07 11.0 7.0

下一步将是随着时间的推移计算值. 对于任何示例/建议，我将不胜感激.

next step would be then to calculate value over time. I will be grateful for any example / suggestion how to do it.

我添加了股票代号，以匹配更真实的示例

I added stock symbols, to match more real example

WITH `trans` AS ( SELECT DATE '2018-10-02' trans_date, 10.0 quantity, 'TX' symbol UNION ALL SELECT DATE '2018-10-03' trans_date, 5.0 quantity, 'TX' UNION ALL SELECT DATE '2018-10-05', 11.0, 'AX' ), `prices` AS ( SELECT DATE '2018-10-01' price_date, 1.0 price, 'TX' symbol UNION ALL SELECT DATE '2018-10-02', 2.0, 'TX' UNION ALL SELECT DATE '2018-10-03', 3.0, 'TX' UNION ALL SELECT DATE '2018-10-04', 4.0, 'TX' UNION ALL SELECT DATE '2018-10-05', 5.0, 'TX' UNION ALL SELECT DATE '2018-10-06', 6.0, 'TX' UNION ALL SELECT DATE '2018-10-07', 7.0, 'TX' UNION ALL SELECT DATE '2018-10-08', 8.0, 'AX' UNION ALL SELECT DATE '2018-10-09', 9.0, 'TX' UNION ALL SELECT DATE '2018-10-10', 10.0, 'AX' UNION ALL SELECT DATE '2018-10-11', 11.0, 'TX' UNION ALL SELECT DATE '2018-10-12', 12.0, 'TX' ) SELECT price_date, t.symbol AS symbol, IFNULL( ARRAY_AGG( IF(p.price_date >= t.trans_date AND p.symbol = t.symbol, quantity, NULL) IGNORE NULLS ORDER BY trans_date DESC LIMIT 1 )[OFFSET(0)], -1234567890) quantity, price FROM `prices` p CROSS JOIN `trans` t GROUP BY price_date, price, symbol HAVING quantity != -1234567890 ORDER BY price_date

推荐答案

以下是BigQuery标准SQL

Below is for BigQuery Standard SQL

#standardSQL WITH `trans` AS ( SELECT DATE '2018-10-02' trans_date, 10.0 quantity UNION ALL SELECT DATE '2018-10-03', 5.0 UNION ALL SELECT DATE '2018-10-05', 11.0 ), `prices` AS ( SELECT DATE '2018-10-01' price_date, 1.0 price UNION ALL SELECT DATE '2018-10-02', 2.0 UNION ALL SELECT DATE '2018-10-03', 3.0 UNION ALL SELECT DATE '2018-10-04', 4.0 UNION ALL SELECT DATE '2018-10-05', 5.0 UNION ALL SELECT DATE '2018-10-06', 6.0 UNION ALL SELECT DATE '2018-10-07', 7.0 ) SELECT price_date, IFNULL( ARRAY_AGG( IF(p.price_date >= t.trans_date, quantity, NULL) IGNORE NULLS ORDER BY trans_date DESC LIMIT 1 )[OFFSET(0)], 0) quantity, price FROM `prices` p CROSS JOIN `trans` t GROUP BY price_date, price -- ORDER BY price_date