【无标题】

一、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()函数可以给，结果集增加一个行号