代码之家 › 专栏 › 技术社区 › Adam Hopkinson

BigQuery左联接正在使值加倍

google-bigquery join database sql

0

Adam Hopkinson · 技术社区 · 8 年前

我正在尝试合并两个数据集-一个是销售目标,另一个是按天和按市场(美国/英国)计算的实际销售。

为了做到这一点,我用的是第三张桌子 GENERATE_DATE_ARRAY 创建一个报告日期的主列表-这样我就不会在没有目标设置和没有报告销售的情况下出现差距。

我发现我的销售额被统计了两次,所以把我的数据和查询减少到了可重复的状态:

#standardSQL

WITH dates AS (
  SELECT day FROM UNNEST(GENERATE_DATE_ARRAY(DATE '2018-07-05', '2018-07-09', INTERVAL 1 DAY)) AS day
),
targets AS (
  SELECT DATE '2018-07-06' AS day, 'UK' AS Market, NUMERIC '2.4' AS quantity
  UNION ALL SELECT '2018-07-06', "US", 8.4
  UNION ALL SELECT '2018-07-06', "US", 1.2
  UNION ALL SELECT '2018-07-08', "UK", 3.0
  UNION ALL SELECT '2018-07-08', "US", 10.9
),
sales AS (
  SELECT DATE '2018-07-08' AS day, 'UK' AS Market, 4 AS quantity
  UNION ALL SELECT '2018-07-06', 'US', 15
)

SELECT 
  dates.day AS day,
  targets.market AS market,
  SUM(targets.quantity) AS targetQuantity,
  SUM(sales.quantity) AS quantity
FROM dates
LEFT JOIN targets
  ON dates.day = CAST(targets.day AS DATE)
LEFT JOIN sales
  ON dates.day = CAST(sales.day AS DATE) AND targets.market = sales.market
GROUP BY day, market
ORDER BY day, market

结果如下:

结果显示,7月6日(第3行)的报告销售额为30,尽管数据中有15个。

当有两行的日期和市场在 targets 数据,但我不知道如何为此编码。

谢谢你的帮助!

1 回复 | 直到 8 年前

1

Mikhail Berlyant 8 年前

下面应该有用。其想法是预先聚合销售和目标表以避免重复

#standardSQL
WITH dates AS (
  SELECT day FROM UNNEST(GENERATE_DATE_ARRAY(DATE '2018-07-05', '2018-07-09', INTERVAL 1 DAY)) AS day
), targets AS (
  SELECT DATE '2018-07-06' AS day, 'UK' AS Market, NUMERIC '2.4' AS quantity
  UNION ALL SELECT '2018-07-06', "US", 8.4
  UNION ALL SELECT '2018-07-06', "US", 1.2
  UNION ALL SELECT '2018-07-08', "UK", 3.0
  UNION ALL SELECT '2018-07-08', "US", 10.9
), sales AS (
  SELECT DATE '2018-07-08' AS day, 'UK' AS Market, 4 AS quantity
  UNION ALL SELECT '2018-07-06', 'US', 15
)
SELECT 
  dates.day AS day,
  t.market AS market,
  targetQuantity,
  quantity
FROM dates 
LEFT JOIN (SELECT day, market, SUM(quantity) AS targetQuantity FROM targets GROUP BY day, market) t
  ON dates.day = CAST(t.day AS DATE)
LEFT JOIN (SELECT day, market, SUM(quantity) AS quantity FROM sales GROUP BY day, market) s
  ON dates.day = CAST(s.day AS DATE) AND t.market = s.market
ORDER BY day, market