本文介绍了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]

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

解决方案



  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



  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?

解决方案
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

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

you can use keyword with backticks around

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

08-18 22:00