SQL aggregate函数练习

常见统计函数

下面介绍几个常用统计函数:

Function	Description
COUNT(*), COUNT(column)	计数！COUNT(*) 统计数据行数，COUNT(column) 统计column非NULL的行数.
MIN(column)	找column最小的一行.
MAX(column)	找column最大的一行.
AVG(column)	对column所有行取平均值.
SUM(column)	对column所有行求和.

Table(表）: Employees

Role	Avg(Years_employed)
Artist	5
Engineer	2.8333333333333335
Manager	6

重置

SELECT role,avg(years_employed) from employees group by role;

练习 do it — 请完成如下任务

1. 找出就职年份最高的雇员(列出雇员名字+年份） ✓
2. 按角色(Role)统计一下每个角色的平均就职年份 ✓
3. 按办公室名字总计一下就职年份总和
4. 每栋办公室按人数排名,不要统计无办公室的雇员
5. 就职1,3,5,7年的人分别占总人数的百分比率是多少(给出年份和比率"50%" 记为 50)

select name,max(years_employed) from employees;

select role,avg(years_employed) from employees group by role;

select building,sum(years_employed) from employees group by building;

select building,count(name) as rank from employees
where building is not null group by building order by rank asc;或者
SELECT Building,COUNT(*) 
FROM Employees 
WHERE Building GROUP BY Building;


select years_employed, (count(name)* 100  / (select
count(*) from employees) ) 
as ratio from employees
where years_employed in (1,3,5,7)
group by years_employed

到目前为止，我们的Query查询已经有点复杂了，不过还好我们已经基本把查询语法介绍完了。在 GROUP BY 分组语法中，我们知道数据库是先对数据做WHERE，然后对结果做分组，如果我们要对分组完的数据再筛选出几条如何办？ （想一下按年份统计电影票房，要筛选出>100万的年份？）

一个不常用的语法 HAVING 语法将用来解决这个问题，他可以对分组之后的数据再做SELECT筛选.

用HAVING进行筛选

SELECT group_by_column, AGG_FUNC(column_expression) AS aggregate_result_alias, … FROM mytable WHERE condition GROUP BY column HAVING group_condition;

HAVING 和 WHERE 语法一样，只不过作用的结果集不一样. 在我们例子数据表数据量小的情况下可能感觉HAVING没有什么用，但当你的数据量成千上万属性又很多时也许能帮上大忙 .

我们将更加深入的分析 Employee 数据，综合运用不同的SQL语法来解决这些问题.

Table(表）: Employees

Role	Name	Building	Years_employed
Engineer	Becky A.	1e	4
Engineer	Dan B.	1e	2
Engineer	Sharon F.	1e	6
Engineer	Dan M.	1e	4
Engineer	Malcom S.	1e	1
Artist	Tylar S.	2w	2
Artist	Sherman D.	2w	8
Artist	Jakob J.	2w	6
Artist	Lillia A.	2w	7
Artist	Brandon J.	2w	7
Manager	Scott K.	1e	9
Manager	Shirlee M.	1e	3
Manager	Daria O.	2w	6
Engineer	Yancy I.	null	0
Artist	Oliver P.	null	0

练习 do it — 请完成如下任务

1. 统计一下Artist角色的雇员数量
2. 按角色统计一下每个角色的雇员数量
3. 算出Engineer角色的就职年份总计
4. 按角色分组算出每个角色按有办公室和没办公室的统计人数(列出角色，数量，有无办公室,注意一个角色如果部分有办公室，部分没有需分开统计）
5. 按角色和就职年份统计人数,年份按0-3，3-6，6-9这种阶梯分组，最后按角色+阶梯分组排序

1.
SELECT count(role) FROM employees group by role having role = 'Artist';
#或
SELECT COUNT() FROM Employees WHERE Role='Artist';

2.
select role,count() from employees  group by role;

3.
select sum(years_employed) from employees where role = 'Engineer';

4.#难
select role, case when building is not null 
then '1' else '0' end as have,
count()
from employees group by role,have

5.#难
SELECT Role,Years_employed/3 AS Year,
COUNT(Name) FROM Employees
GROUP BY Role,Year 
ORDER BY Year ASC;