SQL中两个表的聚合查询？ [英] Aggregate query across two tables in SQL?

问题描述

我在BigQuery工作。我有两张表：

  TABLE：orgs 
 code：STRING 
 group：STRING 
 
 TABLE：org_employees 
 code：STRING 
 employee_count：INTEGER 
  

<每个表中的代码实际上是一个外键。我想获得所有独特的 group s，并计算它们中的orgs数量，并且（这是棘手的一点）这些org中只有多少只有一名员工。看起来像这样的数据：

  group，orgs，single_handed_orgs 
 00Q，23,12 
 00K， 15,7 
  

我知道如何做第一位，得到独特的 group s和 orgs 表中关联组织的数量：

  SELECT 
 count（code），group 
 FROM 
 [orgs] 
 GROUP BY组
  

而且，我知道如何从练习表中获得单手org的数量：

<$ p $
代码，
（employee_count == 1）AS is_single_handed
FROM
[org_employees]

但我不确定如何将它们粘合在一起。任何人都可以提供帮助吗？

解决方案

对于BigQuery：传统SQL

  SELECT 
 [group]，
 COUNT（o.code）as orgs，
 SUM （employee_count = 1）as single_handed_orgs 
 FROM [orgs] AS o 
 LEFT JOIN [org_employees] AS e 
 ON e.code = o.code 
 GROUP BY [group] 
  

在org_employees表中缺少某些代码时使用LEFT JOIN

适用于BigQuery：标准SQL

  SELECT 
 grp，
 COUNT（o.code）AS orgs，
 SUM（CASE employee_count WHEN 1 THEN 1 ELSE 0 END）as single_handed_orgs 
 FROM orgs AS o 
 LEFT JOIN org_employees AS e 
 ON e.code = o.code 
 GROUP BY grp 
  

使用 grp vs group - 看起来像标准sql一样使用保留关键词，即使我在后面加上反斜杠

确认：

您可以使用带反引号的关键字

I'm working in BigQuery. I've got two tables:

TABLE: orgs
code: STRING
group: STRING

TABLE: org_employees
code: STRING
employee_count: INTEGER

The code in each table is effectively a foreign key. I want to get all unique groups, with a count of the orgs in them, and (this is the tricky bit) a count of how many of of those orgs only have a single employee. Data that looks like this:

group,orgs,single_handed_orgs
00Q,23,12
00K,15,7

I know how to do the first bit, get the unique groups and count of associated orgs from the orgs table:

SELECT
  count(code), group
FROM
  [orgs]
GROUP BY group

And, I know how to get the count of single-handed orgs from the practice table:

SELECT
  code,
  (employee_count==1) AS is_single_handed
FROM
  [org_employees]

But I'm not sure how to glue them together. Can anyone help?

解决方案

for BigQuery: legacy SQL

SELECT
  [group], 
  COUNT(o.code) as orgs, 
  SUM(employee_count = 1) as single_handed_orgs
FROM [orgs] AS o
LEFT JOIN [org_employees] AS e
ON e.code  = o.code
GROUP BY [group]

using LEFT JOIN in case if some codes are missing in org_employees tables

for BigQuery: standard SQL

SELECT
  grp, 
  COUNT(o.code) AS orgs , 
  SUM(CASE employee_count WHEN 1 THEN 1 ELSE 0 END) AS single_handed_orgs
FROM orgs AS o
LEFT JOIN org_employees AS e
ON e.code  = o.code
GROUP BY grp  

Note use of grp vs group - looks like standard sql does like use of Reserved Keywords even if i put backticks around

Confirmed:

you can use keyword with backticks around

这篇关于SQL中两个表的聚合查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！