<每个表中的代码实际上是一个外键。我想获得所有独特的 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: INTEGERThe 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,7I 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 groupAnd, 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中两个表的聚合查询？
发布评论