一、group by
1、分组的实质就是一行(或多行)中的某一列(或多列)具有相同值。
2、组是非空的,如果分组成功,组至少包含一个成员(或行)。
3、组是独特的,意味着,当查询中使用group by时,select列表中就不必使用distinct关键字。
4、当针对非空表的查询(包含group by)中使用聚集函数count时,它绝对不会返回0。至少会返回一个非0值。
二、count(*)和count(列名)
create table fruits (name varchar2(10));
select name, count(name) from fruits group by name;
select name, count(*) from fruits group by name;
从例子可以看出,count(name)会忽略列的空值行,count(*)不会忽略空值行。
当使用count时,需要考虑是否希望包含NULL。使用count(column),不会计算NULL。
如果希望包含NULL,则使用count(*)对行计数。
三、select列表和group by子句
1、当使用聚集函数(如count)时,对于select列表中的项,如果没有把它当作聚集函数的参数使用,那么它必须是组的一部分。
2、对于可能对更改组(或更改聚集函数返回的值)的select列表项,一定包含于group by子句中。
3、问:select列表中的哪些项会更改组及聚集函数返回的值?
答:select对象表的其他列。不包含在select对象表中的列,可以出现在select后面。
四、窗口
与聚集函数一样,窗口函数也针对定义的行集(组)执行聚集,但它不像聚集函数那样每组只返回一个值,窗口函数可以为每组返回多个值。执行聚集的行组是窗口(因此命名为“窗口函数”)。
简单来说,窗口函数就是可以在每一行结果集后面加上一列,这一列就是聚集函数的执行结果。
如何使用窗口函数从细节行(每个员工一行)访问聚集数据(员工总数):
select ename,
deptno,
count(*) over() as cnt
from emp order by 2;
我理解为相当于在每一行后面开了一个窗口,窗口中能够访问聚集函数的结果。
在这个例子中,orver关键字表明:把count的调用看作窗口函数,而不是聚集函数。窗口函数count(*)over()对于查询返回的每一行,都返回了表中所有行的计数。
窗口函数是最后一步执行,(where和group by之后)仅在order by之前。所以where条件不同,窗口函数计算结果也不同。
五、分区
1、partition by可以在over关键字内表示分区或分组,以完成聚集。如果使用了空括号,那么整个结果集就是分区。
2、partition by对定义的行组计算聚集时,当遇到新组的时候会复位,并对每个组中的每个成员返回一个聚集的结果。
3、partition by字句和group by子句的运行方式一样,只是它不受select子句中的其他项影响,而且不需要编写group by子句。
partition by子句的优点是:在同一个select语句中,一个窗口函数的计算独立于按其他列分区的其他窗口函数的计算。
例如上面这个sql语句中,部门为10的单独计算了员工数,部分为20、30、空的同样单独计算了员工数。
查询sql,返回每个员工、他的部门、他的部门中的员工数、他的职位以及跟他相同职位的员工数:
select ename,
deptno,
count(*)over(partition by deptno) as dept_cnt,
job,
count(*)over(partition by job) as job_cnt
from emp;
4、与group by子句一样,partition by子句把所有的NULL合并为组或分区。
六、窗口函数和order by小子句
1、窗口函数可以在over子句内使用order by小子句,用于指定分区中行的排序方式。
2、如果没有partition by子句,就意味着处理整个结果集。也可以先partition by再order by。
3、有些窗口函数强制要求对分区中的行排序。因此,对于有些窗口函数,order by子句是必需的。
七、partition by和order by在over()中的使用
从上面的例子可以看出:
partition by子句先按deptno分区,每个区都计算自己的sal总和。
order by子句,会在分区内指定一个默认的“移动”或“滑动”窗口。这里的分区是所有结果集,会按照当前行的hiredate位置终止总和的计算。形成累计的效果。
sum(sal)over(order by hiredate)
可以写成
sum(sal)over(order by hiredate
range between unbounded preceding and current row
)
八、其它
row_number()over()函数可以给,结果集增加一个行号