我有一个存储销售目标的表格 - 这些通常按月设置,但按天输入 - 这意味着每日目标是月目标除以天数。
这是输入目标的劳动密集型方式,因此我想重新创建具有开始和结束日期的表:
WITH targets AS (
SELECT DATE '2018-01-01' AS dateStart, DATE '2018-01-31' AS dateEnd, 'uk' AS market, NUMERIC '1550' AS quantity
UNION ALL SELECT '2018-02-01', '2018-02-28', "uk", 560
)
在我的查询中,我需要生成一个日期数组 ( dateStart
to dateEnd
),然后对于数组中的每个日期,应用市场并将目标除以数组中的日期数 - 但我无法让它工作。我正在做类似的事情:
SELECT
*,
(SELECT market FROM targets WHERE dr IN GENERATE_DATE_ARRAY(targets.dateStart, targets.dateEnd, INTERVAL 1 DAY)) AS market,
(SELECT SAFE_DIVIDE(budget, COUNT(GENERATE_DATE_ARRAY(targets.dateStart, targets.dateEnd, INTERVAL 1 DAY)) FROM targets WHERE dr IN GENERATE_DATE_ARRAY(targets.dateStart, targets.dateEnd, INTERVAL 1 DAY)) AND targets.market = market AS budget
FROM UNNEST(GENERATE_DATE_ARRAY(targets.dateStart, targets.dateEnd, INTERVAL 1 DAY)) AS dr
这意味着源表中的数据条目和行数更少(这是一个 Google 表格,因此最终会达到限制)。谢谢你的帮助。
下面是 BigQuery 标准 SQL
#standardSQL
WITH targets AS (
SELECT DATE '2018-01-01' AS dateStart, DATE '2018-01-31' AS dateEnd, 'uk' AS market, NUMERIC '1550' AS quantity
UNION ALL SELECT '2018-02-01', '2018-02-28', "uk", 560
)
SELECT market, day, quantity / days AS target
FROM targets,
UNNEST(GENERATE_DATE_ARRAY(dateStart, dateEnd)) day,
UNNEST([DATE_DIFF(dateEnd, dateStart, DAY) + 1]) days
ORDER BY market, day
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句