标量-聚集(scalar-aggregate) 函数。这些函数像标量函数,因为它们也是在每一行返回单个的值,但是它们也像聚集函数,因为它们要对一个集合中多个行中的值执行计算,以计算出结果。下面的标量-聚集函数执行的是与 sum 聚集函数一样的计算,但是这个函数返回的是没有合并行的结果:
select dept, salary,
sum(salary) over (partition by dept) as deptsum,
avg(salary) over (partition by dept) as avgsal,
count(*) over (partition by dept) as deptcount,
max(salary) over (partition by dept) as maxsal
from emptab;
DEPT SALARY DEPTSUM AVGSAL DEPTCOUNT MAXSAL
----- ------- -------- ------- --------- --------
1 50000 383000 63833 6 78000
1 75000 383000 63833 6 78000
1 52000 383000 63833 6 78000
1 78000 383000 63833 6 78000
1 75000 383000 63833 6 78000
1 53000 383000 63833 6 78000
2 - 51000 51000 2 51000
2 51000 51000 51000 2 51000
3 79000 209000 69666 3 79000
3 55000 209000 69666 3 79000
3 75000 209000 69666 3 79000
- - 84000 84000 2 84000
- 84000 84000 84000 2 84000
展示 SUM 报告函数的例子 注意,该查询没有包含 GROUP BY 子句。相反,该查询使用了 OVER 子句来对数据分区,以便 sum 函数对同一部门中的行执行计算,并在每一个部门内的每一行中返回该部门所有薪水的总和。按惯例,为了在每一行中包括那样的聚集结果,我们需要使用一个联合,但是现在 OLAP 函数为此提供了更简易的模式。我们推荐使用这种类型的函数作为报告 函数,因为这种函数是对集合计算总和,并在每一行中都报告一次结果的。我曾经在前面和后面的例子中使用了 SUM,但是大部分聚集函数(例如 AVG、MIN、MAX、STDEV,等等)都使用 OVER 子句。在 DEPTSUM 列右边的其他列显示了平均薪水、部门中雇员的人数以及部门中的最高薪水。惟一不支持作为标量-聚集函数的聚集函数是线性回归函数。这些报告函数一个强大的用处就是计算比率和百分比。要计算某个雇员的薪水占整个部门薪水总和的百分比,只需简单地用报告的薪水总和去除该雇员的薪水。select empnum, dept, salary,
sum(salary) over (partition by dept) as deptsum,
decimal(salary,10,2) /
sum(salary) over(partition by dept)as percentage
from emptab;
EMPNUM DEPT SALARY DEPTSUM PERCENTAGE
------ ----- -------- ----------- ----------
1 1 50000 383000 0.1305
2 1 75000 383000 0.1958
5 1 52000 383000 0.1357
6 1 78000 383000 0.2036
7 1 75000 383000 0.1958
11 1 53000 383000 0.1383
4 2 - 51000
9 2 51000 51000 1.0000
8 3 79000 209000 0.3779
10 3 55000 209000 0.2631
12 3 75000 209000 0.3588
0 - - 84000
3 - 84000 84000 1.0000
百分比的例子
果我们在要进行聚集的集合中引入一个排序方式,会出现什么情况呢?答案是,我们不处理一个报告(reporting) 函数,而是处理一个累加(cumulative)函数。累加函数是一种标量-聚集函数,它对当前行以及集合中当前行之前(相对排序方式而言)的所有行进行操作。让我们为这个例子使用一个不同的表。假设我们有一个这样的表,它记有当前历年的每月销售业绩。那么,我们如何计算每个月的年至今日(year-to-date) 销售数字呢?这里,我们要计算每月销售的累加和。我们可以这样做:
select date, sales,
sum(sales) over (order by date) as cume_sum,
count(*) over (order by date) as setcount
from sales
where year(date) = 2000;
DATE SALES CUME_SUM SETCOUNT
---------- ------------ ------------ ---------
01/01/2000 968871.12 968871.12 1
02/01/2000 80050.05 1048921.17 2
03/01/2000 757866.14 1806787.31 3
04/01/2000 58748.13 1865535.44 4
05/01/2000 40711.69 1906247.13 5
06/01/2000 241187.78 2147434.91 6
07/01/2000 954924.16 3102359.07 7
08/01/2000 502822.96 3605182.03 8
09/01/2000 97201.45 3702383.48 9
10/01/2000 853999.45 4556382.93 10
11/01/2000 358775.59 4915158.52 11
12/01/2000 437513.35 5352671.87 12
计算累加和的例子 让我们看一下结果。对于第一行,累加和就等于这一行的销售量。对于第二行,累加和等于一月份和二月份销售量的和(968871.12 + 80050.05 = 1048921.17)。类似地,第三行的结果是一月份、二月份和三月份销售量的和。在 CUME_SUM 列右边的列执行一个累加计数,给出在集合中行的数量。例如,第一行只有一行被求和(也就是该行本身),第二行有两行被求和(该行本身以及前一行),依此类推。上面的图给出了销售数字以及在前面的查询中计算出的累加和的图形化表示。如果我们有多年的数据,并且想计算每一年内 到当月的累加和,那么我们也可以像下面这样使用 PARTITION BY 子句:
select date, sales,
sum(sales) over (partition by year(date)
order by month(date)) as cume_sum
from sales
where year(date) >= 2000;
DATE SALES CUME_SUM
---------- ------------ -----------
01/01/2000 968871.12 968871.12
02/01/2000 80050.05 1048921.17
03/01/2000 757866.14 1806787.31
04/01/2000 58748.13 1865535.44
05/01/2000 40711.69 1906247.13
06/01/2000 241187.78 2147434.91
07/01/2000 954924.16 3102359.07
08/01/2000 502822.96 3605182.03
09/01/2000 97201.45 3702383.48
10/01/2000 853999.45 4556382.93
11/01/2000 358775.59 4915158.52
12/01/2000 437513.35 5352671.87 --------------------------------------------------------------------------------
01/01/2001 476851.71 476851.71
02/01/2001 593768.12 1070619.83
03/01/2001 818597.97 1889217.80
...
使用 PARTITION BY 子句计算累加和
select dept, salary,
sum(salary) over (partition by dept) as deptsum,
avg(salary) over (partition by dept) as avgsal,
count(*) over (partition by dept) as deptcount,
max(salary) over (partition by dept) as maxsal
from emptab;
DEPT SALARY DEPTSUM AVGSAL DEPTCOUNT MAXSAL
----- ------- -------- ------- --------- --------
1 50000 383000 63833 6 78000
1 75000 383000 63833 6 78000
1 52000 383000 63833 6 78000
1 78000 383000 63833 6 78000
1 75000 383000 63833 6 78000
1 53000 383000 63833 6 78000
2 - 51000 51000 2 51000
2 51000 51000 51000 2 51000
3 79000 209000 69666 3 79000
3 55000 209000 69666 3 79000
3 75000 209000 69666 3 79000
- - 84000 84000 2 84000
- 84000 84000 84000 2 84000
展示 SUM 报告函数的例子 注意,该查询没有包含 GROUP BY 子句。相反,该查询使用了 OVER 子句来对数据分区,以便 sum 函数对同一部门中的行执行计算,并在每一个部门内的每一行中返回该部门所有薪水的总和。按惯例,为了在每一行中包括那样的聚集结果,我们需要使用一个联合,但是现在 OLAP 函数为此提供了更简易的模式。我们推荐使用这种类型的函数作为报告 函数,因为这种函数是对集合计算总和,并在每一行中都报告一次结果的。我曾经在前面和后面的例子中使用了 SUM,但是大部分聚集函数(例如 AVG、MIN、MAX、STDEV,等等)都使用 OVER 子句。在 DEPTSUM 列右边的其他列显示了平均薪水、部门中雇员的人数以及部门中的最高薪水。惟一不支持作为标量-聚集函数的聚集函数是线性回归函数。这些报告函数一个强大的用处就是计算比率和百分比。要计算某个雇员的薪水占整个部门薪水总和的百分比,只需简单地用报告的薪水总和去除该雇员的薪水。select empnum, dept, salary,
sum(salary) over (partition by dept) as deptsum,
decimal(salary,10,2) /
sum(salary) over(partition by dept)as percentage
from emptab;
EMPNUM DEPT SALARY DEPTSUM PERCENTAGE
------ ----- -------- ----------- ----------
1 1 50000 383000 0.1305
2 1 75000 383000 0.1958
5 1 52000 383000 0.1357
6 1 78000 383000 0.2036
7 1 75000 383000 0.1958
11 1 53000 383000 0.1383
4 2 - 51000
9 2 51000 51000 1.0000
8 3 79000 209000 0.3779
10 3 55000 209000 0.2631
12 3 75000 209000 0.3588
0 - - 84000
3 - 84000 84000 1.0000
百分比的例子
果我们在要进行聚集的集合中引入一个排序方式,会出现什么情况呢?答案是,我们不处理一个报告(reporting) 函数,而是处理一个累加(cumulative)函数。累加函数是一种标量-聚集函数,它对当前行以及集合中当前行之前(相对排序方式而言)的所有行进行操作。让我们为这个例子使用一个不同的表。假设我们有一个这样的表,它记有当前历年的每月销售业绩。那么,我们如何计算每个月的年至今日(year-to-date) 销售数字呢?这里,我们要计算每月销售的累加和。我们可以这样做:
select date, sales,
sum(sales) over (order by date) as cume_sum,
count(*) over (order by date) as setcount
from sales
where year(date) = 2000;
DATE SALES CUME_SUM SETCOUNT
---------- ------------ ------------ ---------
01/01/2000 968871.12 968871.12 1
02/01/2000 80050.05 1048921.17 2
03/01/2000 757866.14 1806787.31 3
04/01/2000 58748.13 1865535.44 4
05/01/2000 40711.69 1906247.13 5
06/01/2000 241187.78 2147434.91 6
07/01/2000 954924.16 3102359.07 7
08/01/2000 502822.96 3605182.03 8
09/01/2000 97201.45 3702383.48 9
10/01/2000 853999.45 4556382.93 10
11/01/2000 358775.59 4915158.52 11
12/01/2000 437513.35 5352671.87 12
计算累加和的例子 让我们看一下结果。对于第一行,累加和就等于这一行的销售量。对于第二行,累加和等于一月份和二月份销售量的和(968871.12 + 80050.05 = 1048921.17)。类似地,第三行的结果是一月份、二月份和三月份销售量的和。在 CUME_SUM 列右边的列执行一个累加计数,给出在集合中行的数量。例如,第一行只有一行被求和(也就是该行本身),第二行有两行被求和(该行本身以及前一行),依此类推。上面的图给出了销售数字以及在前面的查询中计算出的累加和的图形化表示。如果我们有多年的数据,并且想计算每一年内 到当月的累加和,那么我们也可以像下面这样使用 PARTITION BY 子句:
select date, sales,
sum(sales) over (partition by year(date)
order by month(date)) as cume_sum
from sales
where year(date) >= 2000;
DATE SALES CUME_SUM
---------- ------------ -----------
01/01/2000 968871.12 968871.12
02/01/2000 80050.05 1048921.17
03/01/2000 757866.14 1806787.31
04/01/2000 58748.13 1865535.44
05/01/2000 40711.69 1906247.13
06/01/2000 241187.78 2147434.91
07/01/2000 954924.16 3102359.07
08/01/2000 502822.96 3605182.03
09/01/2000 97201.45 3702383.48
10/01/2000 853999.45 4556382.93
11/01/2000 358775.59 4915158.52
12/01/2000 437513.35 5352671.87 --------------------------------------------------------------------------------
01/01/2001 476851.71 476851.71
02/01/2001 593768.12 1070619.83
03/01/2001 818597.97 1889217.80
...
使用 PARTITION BY 子句计算累加和
over()在 pl/sql是可以使用的。
SQL> select * from test_a;NAME AGE DEPT
---------- ---------- ----------
qwq 12 1
sdd 13 1
sf 15 1
fsdf 25 2
sff 26 2
sffdf 23 16 rows selectedSQL> select name,age,dept,sum(age) over(partition by dept order by age) from test_a;NAME AGE DEPT SUM(AGE)OVER(PARTITIONBYDEPTOR
---------- ---------- ---------- ------------------------------
qwq 12 1 12
sdd 13 1 25
sf 15 1 40
sffdf 23 1 63
fsdf 25 2 25
sff 26 2 516 rows selected