oracle窗口函数多列分区,oracle group by和窗口函数

一、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 HIREDATESALTOTAL1 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