BigQuery 连接三个表

我正在尝试连接 BigQuery 中的三个表；表 1 有一个事件的记录（即每一行是一条记录），表 2 有第二个事件的记录，表 3 有类别名称。

我想要生成一个最终表，其中包含按类别和设备平台划分的表 1 和表 2 的计数。但是，每次我运行此命令时都会收到一条错误消息join.t3.category 不是连接中任一表的字段.

这是我当前的代码：

Select count(distinct joined.t1.Id) as t1_events, count(distinct t2.Id) as t2_events, joined.t1.Origin as platform, joined.t3.category as category

from 

(

SELECT 
        Id,
        Origin,
        CatId

    FROM [testing.table_1] as t1

JOIN (SELECT category,
            CategoryID

FROM [testing.table_3]) as t3

on t1.CatId = t3.CategoryID

) AS joined

JOIN (SELECT Id,
            CategoryId

FROM [testing.table_2]) as t2

ON (joined.t1.CatId = t2.CategoryId)    

Group by platform,category;

作为参考，下面是表 1 和表 2 之间的一个更简单的连接，效果非常好：

Select count(distinct t1.Id) as t1_event, count(distinct t2.Id) as t2_events, t1.Origin as platform

from testing.table_1 as t1

JOIN testing.table_2 as t2

on t1.CatId = t2.CategoryId

Group by platform;

简单的修复方法是添加category第一个内部字段SELECT- 否则最外层看不到SELECT- 因此错误！这就是问题所在！

此外，在 BigQuery Legacy SQL 中，您可以使用EXACT_COUNT_DISTINCT https://cloud.google.com/bigquery/query-reference#exact_count_distinct否则你会得到统计近似值 - 请参阅计数（[不同]） https://cloud.google.com/bigquery/query-reference#countdistinct

因此，对于旧版 SQL，您的查询可能如下所示：

SELECT
  EXACT_COUNT_DISTINCT(joined.t1.Id) AS t1_events,
  EXACT_COUNT_DISTINCT(t2.Id) AS t2_events,
  joined.t1.Origin AS platform,
  joined.t3.category AS category
FROM (
  SELECT
    Id, Origin, CatId, category
  FROM [testing.table_1] AS t1
  JOIN (SELECT category, CategoryID FROM [testing.table_3]) AS t3
  ON t1.CatId = t3.CategoryID 
) AS joined
JOIN (SELECT Id, CategoryId FROM [testing.table_2]) AS t2
ON joined.t1.CatId = t2.CategoryId
GROUP BY platform, category

此外，我觉得你可以进一步简化它（假设不会有歧义字段）

SELECT
  EXACT_COUNT_DISTINCT(joined.t1.Id) AS t1_events,
  EXACT_COUNT_DISTINCT(t2.Id) AS t2_events,
  joined.t1.Origin AS platform,
  joined.t3.category AS category
FROM (
  SELECT
    Id, Origin, CatId, category
  FROM [testing.table_1] AS t1
  JOIN [testing.table_3] AS t3
  ON t1.CatId = t3.CategoryID 
) AS joined
JOIN [testing.table_2] AS t2
ON joined.t1.CatId = t2.CategoryId
GROUP BY platform, category

当然，如果您将使用它的标准 SQL 版本，您将需要执行相同的修复（正如 Elliott 建议的那样：

SELECT
  COUNT(DISTINCT joined.t1.Id) AS t1_events,
  COUNT(DISTINCT t2.Id) AS t2_events,
  joined.t1.Origin AS platform,
  joined.t3.category AS category
FROM (
  SELECT 
    Id, Origin, CatId, category
  FROM `testing.table_1` AS t1
  JOIN `testing.table_3` AS t3
  ON t1.CatId = t3.CategoryID
) AS joined
JOIN `testing.table_2` AS t2
ON joined.t1.CatId = t2.CategoryId
GROUP BY platform, category