填补另一张桌子上瀑布的空白
填补另一张桌子上瀑布的空白
提问于 2014-11-21 01:31:28
我有两张桌子：
1.预报表中的原始预测数据，由快照日期提取，我使用这些数据创建了瀑布，如下所示
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的周数。
然后我有表2，消费表中的消费数据。我将使用消费表中的周与预测表中的周相匹配，以插入消费量以填补瀑布中的空白。
如果我在excel中手动操作，它将被预测为第8/25周，周数为35，然后我从消费表中找到第35周，并在这里插入。因此，所有快照日期的第35周都是相同的。
它看起来是这样的：
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但这里的问题在于我的预测表中，例如，第一次快照日期将预测第一周至第十周，而第二次快照日期只会预测第二周至第十周。我不知道如何以及是否有可能使这个过程自动化bigquery sql，因为空白基本上意味着没有数据，也没有预测周。
如果有人能给我一些想法，我会非常感激的。
这是我的剧本：
//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回答 1
Stack Overflow用户
发布于 2014-11-25 06:48:15
我对此很感兴趣。
这两个查询生成带有示例中值的表。假设第一个查询的输出被写入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.。
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);然后，下面的查询生成类似于您想要的内容：
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;这个查询的关键是交叉连接生成所有想要的输出行，其中已经包含了消耗的数量。然后左联接保留所有这些行，并在可用时选择预测数量。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/27052735
复制相关文章
相似问题
腾讯云开发者
