一、group by
1、分组的实质就是一行（或多行）中的某一列（或多列）具有相同值。
2、组是非空的，如果分组成功，组至少包含一个成员（或行）。
3、组是独特的，意味着，当查询中使用group by时，select列表中就不必使用distinct关键字。
4、当针对非空表的查询（包含group by）中使用聚集函数count时，它绝对不会返回0。至少会返回一个非0值。
二、count(*)和count(列名)
create table fruits (name varchar2(10));
insert into fruits values ('Oranges');
insert into fruits values ('Oranges');
insert into fruits values ('Oranges');
insert into fruits values ('Apple');
insert into fruits values ('Peach');
insert into fruits values (null);
insert into fruits values (null);
insert into fruits values (null);
insert into fruits values (null);
insert into fruits values (null);
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;
我理解为相当于在每一行后面开了一个窗口，窗口中能够访问聚集函数的结果。
ENAME DEPTNO CNT ---------- ---------- ---------- MILLER 10 19 CLARK 10 19 KING 10 19 JONES 20 19 TOM 20 19 SMITH 20 19 FORD 20 19 ADAMS 20 19 SCOTT 20 19 BLAKE 30 19 WARD 30 19 ALLEN 30 19 JAMES 30 19 MARTIN 30 19 TURNER 30 19 EAST 19 Jonathan 19 JACK 19 APPLE 19
在这个例子中，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子句。
select ename, deptno, count(*) over(partition by deptno) as cnt from emp order by 2; ENAME DEPTNO CNT ---------- ---------- ---------- MILLER 10 3 CLARK 10 3 KING 10 3 JONES 20 6 TOM 20 6 SMITH 20 6 FORD 20 6 ADAMS 20 6 SCOTT 20 6 BLAKE 30 6 WARD 30 6 ALLEN 30 6 JAMES 30 6 MARTIN 30 6 TURNER 30 6 EAST 4 Jonathan 4 JACK 4 APPLE 4
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()中的使用
select deptno,
ename,
hiredate,
sal,
sum(sal)over(partition by deptno) as total1,
sum(sal)over() as total2,
sum(sal)over(order by hiredate) as running_total
from emp;
DEPTNO ENAME HIREDATE SAL TOTAL1 TOTAL2 RUNNING_TOTAL
---------- ---------- ------------------ ---------- ---------- ---------- -------------
20 SMITH 17-DEC-80 800 13875 32025 800
30 ALLEN 20-FEB-81 1600 9400 32025 2400
30 WARD 22-FEB-81 1250 9400 32025 3650
20 JONES 02-APR-81 2975 13875 32025 6625
30 BLAKE 01-MAY-81 2850 9400 32025 9475
10 CLARK 09-JUN-81 2450 8750 32025 11925
30 TURNER 08-SEP-81 1500 9400 32025 13425
30 MARTIN 28-SEP-81 1250 9400 32025 14675
10 KING 17-NOV-81 5000 8750 32025 19675
20 FORD 03-DEC-81 3000 13875 32025 23625
30 JAMES 03-DEC-81 950 9400 32025 23625
10 MILLER 23-JAN-82 1300 8750 32025 24925
20 TOM 01-JAN-86 3000 13875 32025 27925
20 SCOTT 19-APR-87 3000 13875 32025 30925
20 ADAMS 23-MAY-87 1100 13875 32025 32025
EAST 32025 32025
Jonathan 32025 32025
JACK 32025 32025
APPLE 32025 32025
从上面的例子可以看出：
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()函数可以给，结果集增加一个行号
select empno, row_number()over(order by sal) rn from emp;
EMPNO RN
---------- ----------
7369 1
7900 2
7876 3
7521 4
7654 5
7934 6
7844 7
7499 8
7782 9
7698 10
7566 11
7788 12
7902 13
100 14
7839 15
9527 16
98 17
99 18
1 19