从其他桌子填充瀑布的空白 [英] fill the blanks of waterfall from other table
问题描述
我有两张表：
1。来自预测表的原始预测数据，由快照日期提取，并且我使用数据创建瀑布，如下所示：
I have two tables:
item/snapshot forecast weeks
123 | 8/25/14 | 9/1/14 | 9/8/14 | 9/15/14
--------------------------------------------
8/24/14| 7661 | 4980 | 588 | 2232
8/31/14| | 8319 | 1968 | 2760
9/7/14 | | | 6931 | 684
9/14/14| | | | 9328
行标签是快照日期和列标签预测数周。
基本上，数据有很多快照日期，每个快照将提供从此快照日期开始的预测数据，并且将在预测周内显示。第一个快照日期x的预测周数将超出x，第二个快照日期y的预测周数只会超过y。
Row labels are snapshot dates, and column labels are forecast weeks. Basically, there are bunch of snapshot dates of the data, and each snapshot will provide forecast data starting from this snapshot date, and it will show in forecast weeks. The first snapshot date x will have forecast weeks beyond x, second snapshot date y will only have forecast weeks beyond y.
然后我有消费表中的消费数据表2。我将使用消费表中的星期来匹配预测表中的星期来插入消耗以填充瀑布中的空白。
Then I have table 2, consumption data from consumption table. I will use the week in consumption table to match the week in forecast table to insert consumptions to fill those blanks in waterfall.
如果我在Excel中手动执行该操作，这将是预测周8/25，周数是35，然后我发现从消费表35周，并插入这里。因此，所有快照日期的第35周将是相同的。
If I'm doing it manually in excel, it would be forecast week 8/25, week number is 35, then I find week 35 from consumption table and insert here. So week 35 for all snapshot dates will be the same.
item/snapshot forecast weeks
123 | 8/25/14 | 9/1/14 | 9/8/14 | 9/15/14
--------------------------------------------
8/24/14| 7661 | 4980 | 588 | 2232
8/31/14| 2222 | 8319 | 1968 | 2760
9/7/14 | 2222 | 333 | 6931 | 684
9/14/14| 2222 | 333 | 444 | 9328
但是这里的问题出现在我的预测表中，例如，第一个快照日期将预测第1周到第10周，但第二个快照日期只会预测第2周到第10周。
我不知道如何使这个过程自动执行bigquery SQL，因为空白基本上意味着没有数据，没有预测周。
But the problem here is in my forecast table, for example, first snapshot date will have forecast week 1 to week 10, but second snapshot date will only have forecast week 2 to week 10. I don't know how and if it's possible to make this process automated bigquery sql, since the blanks basically means there is no data, no forecast weeks.
如果有人可以给我想法，我将非常感激。
I would be really appreciated if someone can give me ideas
以下是我的脚本：
And here's my script:
//Get item info from forecast table
DEFINE INLINE TABLE t1
SELECT CONCAT(SUBSTR(snapshot_date, -4, 4),'-',SUBSTR(snapshot_date, -10, 2),'-', SUBSTR(snapshot_date, -7, 2)) snapshot,
item_name,
type,
item_description,
CONCAT(SUBSTR(forecast_week_start_date, -4, 4),'-',SUBSTR(forecast_week_start_date, -10, 2),'-', SUBSTR(forecast_week_start_date, -7, 2)) forecast_week_start_date,
SUM(quantity) qty,
forecast_week_number,
forecast_year_number,
CONCAT(STRING(forecast_year_number),'-',STRING(forecast_week_number) year_week
FROM forecast
WHERE
concat(SUBSTR(snapshot_date, -4, 4),'-',SUBSTR(snapshot_date, -10, 2),'-', SUBSTR(snapshot_date, -7, 2)) >=
strftime_usec(date_add(TIME_USEC_TO_WEEK(date_add(now(),-84 ,'DAY'),1),-1,'DAY'),'%Y-%m-%d')
GROUP BY snapshot,
item_name,
type,
item_description,
forecast_week_start_date,
forecast_week_number,
forecast_year_number,
year_week
ORDER BY sdm_week_start_date
//Get min year_week to use later
DEFINE INLINE TABLE t2
SELECT MIN(year_week) min_year_week
FROM t1
//Get consumption data and apply using dc deploy week
SELECT
snapshot,
item_name,
type,
item_description,
forecast_week_start_date,
qty,
forecast_week_number,
forecast_year_number,
year_week
IF(t2.min_year_week!= year_week, qty+ABS(consumption_qty),qty)) quantity,
FROM t1
LEFT JOIN ALL
(SELECT item_name, week,sum(transaction_quantity) consumption_qt
FROM consumption
GROUP BY item_name,week) inv
ON t1.year_week=inv.week AND t1.item_name=inv.item_name
CROSS JOIN t2
推荐答案
破解在这。
这两个查询产生表中的值与你的例子。假设第一个查询的
输出写入 consumption_table 。
These two queries produce tables with the values in your example. Suppose the output of the first query is written to consumption_table.
SELECT *
FROM
(SELECT 123 AS item, '8/25/14' AS date, 2222 AS quantity),
(SELECT 123 AS item, '9/1/14' AS date, 333 AS quantity),
(SELECT 123 AS item, '9/8/14' AS date, 444 AS quantity),
(SELECT 123 AS item, '9/15/14' AS date, 0 AS quantity);
第二个查询的输出写入 forecast_table 。 p>
And the output of the second query is written to forecast_table.
SELECT *
FROM
(SELECT 123 AS item, '8/24/14' AS snapshot, '8/25/14' AS forecast, 7661 AS quantity),
(SELECT 123 AS item, '8/24/14' AS snapshot, '9/1/14' AS forecast, 4980 AS quantity),
(SELECT 123 AS item, '8/24/14' AS snapshot, '9/8/14' AS forecast, 588 AS quantity),
(SELECT 123 AS item, '8/24/14' AS snapshot, '9/15/14' AS forecast, 2232 AS quantity),
(SELECT 123 AS item, '8/31/14' AS snapshot, '9/1/14' AS forecast, 8319 AS quantity),
(SELECT 123 AS item, '8/31/14' AS snapshot, '9/8/14' AS forecast, 1968 AS quantity),
(SELECT 123 AS item, '8/31/14' AS snapshot, '9/15/14' AS forecast, 2760 AS quantity),
(SELECT 123 AS item, '9/7/14' AS snapshot, '9/8/14' AS forecast, 6931 AS quantity),
(SELECT 123 AS item, '9/7/14' AS snapshot, '9/15/14' AS forecast, 684 AS quantity),
(SELECT 123 AS item, '9/14/14' AS snapshot, '9/15/14' AS forecast, 9328 AS quantity);
然后下面的查询产生类似你想要的东西：
Then the following query produces something like what you want:
SELECT
Consumed.item AS item,
Consumed.snapshot AS snapshot,
Consumed.date AS date,
IF (Forecast.quantity IS NULL, Consumed.quantity, Forecast.quantity) AS quantity
FROM
(SELECT
C.item AS item,
S.snapshot AS snapshot,
C.date AS date,
C.quantity AS quantity
FROM
(SELECT *
FROM
(SELECT '8/24/14' AS snapshot),
(SELECT '8/31/14' AS snapshot),
(SELECT '9/7/14' AS snapshot),
(SELECT '9/14/14' AS snapshot)) AS S
CROSS JOIN
consumption_table AS C) AS Consumed
LEFT JOIN
forecast_table AS Forecast
ON Consumed.item = Forecast.item AND
Consumed.snapshot = Forecast.snapshot AND
Consumed.date = Forecast.forecast;
此查询的关键在于CROSS JOIN会生成所有需要的输出行已经在他们中。然后，LEFT JOIN保留所有这些行，并在可用时选择预测数量。
The key to this query is that the CROSS JOIN produces all of the desired output rows with the consumed quantity already in them. Then the LEFT JOIN preserves all of those rows and chooses the forecast quantity when it's available.
这篇关于从其他桌子填充瀑布的空白的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
