连接多个表以计算结果

r55awzrz 于 2021-07-26 发布在 Java

关注(0)|答案(2)|浏览(441)

对sql来说比较陌生，如果这是一个简单的解决方法，请原谅我的无知。我在标准sql中使用googlebigquery，我也尝试过阅读其他主题，但似乎没有一个适合我的需求。
我从两个不同的表中提取信息，并构建了单独的子查询，将信息提取为以下格式：
表a列出了所有服务的州和这些城市所有的餐馆
表b显示了每个州/餐馆最近的销售额，每个州/餐馆都有一个唯一的事务id，因此州/餐馆组合可能会出现多次

(Table A)                        (Table B)                              (Table C)
    State  | Restaurant         State | Restaurant  |Sale ID           State | Restaurant | Transactions
    -------------------         ----------------------------           ---------------------------------
      NY   |  Peter's            NY   | Peter's     |  111  |          NY    | Peter's    |     2
      NY   |  Burger Bin         NY   | Peter's     |  181  |          NY    | Burger Bin |     0
      NY   |  Al's               NY   | Al's        |  925  |          NY    | Al's       |     1
      CA   |  Peter's            CA   | Burger Bin  |  596  |          CA    | Peter's    |     0
      CA   |  Burger Bin         CA   | Burger Bin  |  231  |          CA    | Burger Bin |     2
      CA   |  Al's               IL   | Al's        |  87   |          CA    | Al's       |     0
      IL   |  Peter's            IL   | Burger Bin  |  714  |          IL    | Peter's    |     0
      IL   |  Burger Bin         IL   | Al's        |  12   |          IL    | Burger Bin |     1 
      IL   |  Al's                                                     IL    | Al's       |     2

我遇到的问题是如何将a&b合并到我希望在表c中看到的输出中。
我想看到所有的州和餐馆列出，与一列，显示次数的组合出现在表b。如果它们没有出现，我仍然希望它们被表示，但是值是0。
我尝试过使用不同的计数函数，但没有得到正确的结果，我认为是我没有正确加入。不知道我需要如何加入这些，或者我需要使用什么函数。
任何帮助都将不胜感激！

sql Join group-by google-bigquery

来源：https://stackoverflow.com/questions/62265023/joining-multiple-tables-to-count-results

2条答案

按热度按时间

qij5mzcb1#

您需要加入并分组。您可以将此查询用于此目的：

SELECT State, Restaurant, COUNT(TableB.SaleID) as Transactions
FROM TableA
LEFT JOIN TableB
 USING(State, Restaurant)
GROUP BY State, Restaurant

赞(0）回复(0）举报 2021-07-26

vhipe2zx2#

bigquery标准sql的另一个选项


# standardSQL

SELECT State, Restaurant, IFNULL(Transactions, 0) Transactions
FROM `project.dataset.tableA`
LEFT JOIN (
  SELECT State, Restaurant, COUNT(1) AS Transactions
  FROM `project.dataset.tableB`
  GROUP BY State, Restaurant
)
USING(State, Restaurant)

如果要应用于您问题中的样本数据，请参见下面的示例


# standardSQL

WITH `project.dataset.tableA` AS (
  SELECT 'NY' State, "Peter's" Restaurant UNION ALL
  SELECT 'NY', "Burger Bin" UNION ALL
  SELECT 'NY', "Al's" UNION ALL
  SELECT 'CA', "Peter's" UNION ALL
  SELECT 'CA', "Burger Bin" UNION ALL
  SELECT 'CA', "Al's" UNION ALL
  SELECT 'IL', "Peter's" UNION ALL
  SELECT 'IL', "Burger Bin" UNION ALL
  SELECT 'IL', "Al's" 
), `project.dataset.tableB` AS (
  SELECT 'NY' State, "Peter's" Restaurant, 111 SaleID UNION ALL
  SELECT 'NY', "Peter's", 181 UNION ALL
  SELECT 'NY', "Al's", 925 UNION ALL
  SELECT 'CA', "Burger Bin", 596 UNION ALL
  SELECT 'CA', "Burger Bin", 231 UNION ALL
  SELECT 'IL', "Al's", 87 UNION ALL
  SELECT 'IL', "Burger Bin", 714 UNION ALL
  SELECT 'IL', "Al's", 12 
)
SELECT State, Restaurant, IFNULL(Transactions, 0) Transactions
FROM `project.dataset.tableA`
LEFT JOIN (
  SELECT State, Restaurant, COUNT(1) AS Transactions
  FROM `project.dataset.tableB`
  GROUP BY State, Restaurant
)
USING(State, Restaurant)

输出为

Row State   Restaurant      Transactions     
1   NY      Peter's         2    
2   NY      Burger Bin      0    
3   NY      Al's            1    
4   CA      Peter's         0    
5   CA      Burger Bin      2    
6   CA      Al's            0    
7   IL      Peter's         0    
8   IL      Burger Bin      1    
9   IL      Al's            2

赞(0）回复(0）举报 2021-07-26

我来回答

连接多个表以计算结果

2条答案

相关问题

热门标签

最新问答