时间戳

首页 > 解决方案 > 尽管独立正确，JOIN 还是让我的总数增加了

sql - 尽管独立正确，JOIN 还是让我的总数增加了

问题描述

我将从两个独立的查询开始，每个查询都为我提供正确的结果：

SELECT
  DATE_TRUNC(ga.traffic_date, WEEK(MONDAY)) week_start,
  SUM(traffic) traffic
FROM
  `ga.daily_traffic` 
  WHERE traffic_date >= '2019-03-04'

回报：

+--------------+---------+
| traffic_week | traffic |
+--------------+---------+
| 2019-03-04   |   66572 |
+--------------+---------+

第二个查询：

  SELECT
  week_start,
  SUM(traffic) traffic
FROM
 `marketing.channel_spend`
 WHERE week_start = '2019-03-04'

回报：

+------------+----------+
| week_start |  spend   |
+------------+----------+
| 2019-03-04 | 80143.07 |
+------------+----------+

我应该注意第二个查询：该字段week_start已经以每周增量存储，这可能是导致此（？）当我将两者连接在一起时的原因，如下所示：

SELECT
  week_start,
  SUM(spend) spend,
  SUM(traffic) traffic
FROM
  `ga.daily_traffic` ga
LEFT JOIN `marketing.channel_spend` chan
ON DATE_TRUNC(ga.traffic_date, WEEK(MONDAY)) = chan.week_start
WHERE week_start = '2019-03-04'
GROUP BY 1
ORDER BY 1 DESC

产生以下内容：

+------------+---------+-----------+
| week_start | traffic |   spend   |
+------------+---------+-----------+
| 2019-03-04 |  153115 | 561001.49 |
+------------+---------+-----------+

是什么导致流量和支出总额激增？

标签： sqlgoogle-bigquery

解决方案

你可以使用 cte

with cte as
(
SELECT
  DATE_TRUNC(ga.traffic_date, WEEK(MONDAY)) week_start,
  SUM(traffic) traffic
FROM
  `ga.daily_traffic` 
  WHERE traffic_date >= '2019-03-04'
),cte2 as
(
SELECT
  week_start,
  SUM(traffic) traffic
FROM
 `marketing.channel_spend`
 WHERE week_start = '2019-03-04'
) select cte.week_start,cte.traffic,cte2.traffic as chanel_traffic  from  cte left join cte2 on cte.week_start=cte2.week_start

推荐阅读