一、数据库的一般函数
1.求和一般要去除null字段的值
sum(NVL('字段',0));
2. CASE WHEN ‘字段’='合计' THEN '合计' ELSE ‘字段’ END AS '别名'----------这个语法的含义和if else的意思一样
3.
sql语句片段:
select '3' flag, 'all' aa, V_ORG_CODE_PRV, count(1) all_cnt, sum(case when cnt >=2 then 1 else 0 end) total_cnt
from (
select V_ORG_CODE_PRV, v_cust_code, sum(cnt) cnt //统计客户的购买次数
from t_a //(cnt是该表的统计好的次数)
group by V_ORG_CODE_PRV, v_cust_code //这里是分组的条件
)
group by V_ORG_CODE_PRV
sum(case when cnt >=2 then 1 else 0 end) -----合计次数大于2的户数
4.decode函数
sql语句片段:DECODE(‘变量’,2,D_DEVELOPMENT_CUSTNUM+D_LOYAL_CUSTNUM,3,D_LOYAL_CUSTNUM,4,D_SELFCIG_CUSTNUM_4+D_SELFCIG_CUSTNUM_5,5,D_SELFCIG_CUSTNUM_5)
解释:如果该--‘变量’-- 为2,那么就选择2后面的为该字段的值,,如果为3,,那就选择3后面的值为该字段的值。。。。。。。。以此类推
-----(decode(b.zhs,0,0,a.jhhs2*1.000/b.zhs))*100 sgl,
--decode函数的作用是,b.zhs如果为0,则该字段的值为0,不是为(a.jhhs2*1.000/b.zhs)
5.DB2的行转列函数的使用案例
select listagg(BASICS_CLASS_NAME,',') within group(order by a ) cla from tem2 ---listagg(字段,‘分隔符’)
6.DB2的递归示例
WITH base (BASICS_CLASS_ID, PARENT_BASICS_CLASS_ID, BASICS_CLASS_CODE, BASICS_CLASS_NAME, PATH , PATH_NAME)--------------//里面是字段,和查询的字段要一一对应
AS (SELECT BASICS_CLASS_ID,
PARENT_BASICS_CLASS_ID,
BASICS_CLASS_CODE,
BASICS_CLASS_NAME,
cast(BASICS_CLASS_ID as VARCHAR(2000)) PATH,
cast(BASICS_CLASS_NAME as VARCHAR(2000)) PATH_NAME
FROM PURCHASEMGR.CG_BASICS_CLASS
WHERE IN_USED = '1' AND PARENT_BASICS_CLASS_ID IS NULL OR PARENT_BASICS_CLASS_ID = ''
UNION ALL
SELECT c.BASICS_CLASS_ID,
c.PARENT_BASICS_CLASS_ID,
c.BASICS_CLASS_CODE,
c.BASICS_CLASS_NAME,
B.PATH || '/' || c.BASICS_CLASS_ID PATH,
B.PATH_NAME || '/' || c.BASICS_CLASS_NAME PATH_NAME
FROM PURCHASEMGR.CG_BASICS_CLASS c, base b
WHERE c.IN_USED = '1' AND c.PARENT_BASICS_CLASS_ID = b.BASICS_CLASS_ID
),temp as(
select * from base
)
select * from temp
二、数据库的分析函数
1.分析函数和聚合函数的不同之处是什么?
普通的聚合函数用group by分组,每个分组返回一个统计值,而分析函数采用partition by分组,并且每组每行都可以返回一个统计值。
2.分析函数的形式
分析函数带有一个开窗函数over(),包含三个分析子句:分组(partition by), 排序(order by), 窗口(rows) ,他们的使用形式如下:over(partition by xxx order by yyy rows between zzz)。
注意:窗口子句不能单独出现,必须有order by子句时才能出现。以下是ROWS窗口的作用范围总结
ROWS BETWEEN 1 preceding AND current row 是指当前行的上一行(rownum-1)到当前行的汇总
ROWS BETWEEN 1 preceding AND 1 following 是指当前行的上一行(rownum-1)到当前行的下辆行(rownum+2)的汇总
ROWS BETWEEN current row AND unbounded following 指当前行到最后一行的汇总
ROWS BETWEEN unbounded preceding AND current row 是指第一行至当前行的汇总
2.1 ROWS与RANG的区别举例:
2.1.1开窗的窗口范围:
over(order by salary range between 5 preceding and 5 following):窗口范围为当前行数据幅度减5加5后的-----------------范围内的。
举例:sum(s)over(order by s range between 2 preceding and 2 following) 表示加2或2的范围内的求和
adf 3 45 45 --45加2减2即43到47,但是s在这个范围内只有45
asdf 3 55 55
cfe 2 74 74
3dd 3 78 158 --78在76到80范围内有78,80,求和得158
fda 1 80 158
gds 2 92 92
ffd 1 95 190
dss 1 95 190
ddd 3 99 198
gf 3 99 198
举例:sum(s)over(order by s rows between 2 preceding and 2 following)表示在上下两行之间的范围内
adf 3 45 174 (45+55+74=174)
asdf 3 55 252 (45+55+74+78=252)
cfe 2 74 332 (74+55+45+78+80=332)
3dd 3 78 379 (78+74+55+80+92=379)
fda 1 80 419
gds 2 92 440
ffd 1 95 461
dss 1 95 480
ddd 3 99 388
gf 3 99 293
3.分析函数的实例
分析函数例子(在scott用户下模拟)
3.1示例目的:显示各部门员工的工资,并附带显示该部分的最高工资。--显示各部门员工的工资,并附带显示该部分的最高工资。
1
2
3
4
5
6
7
8
9
10
11
12
13
|
SELECT
E.DEPTNO,
E.EMPNO,
E.ENAME,
E.SAL,
LAST_VALUE(E.SAL)
OVER(PARTITION
BY
E.DEPTNO
ORDER
BY
E.SAL
ROWS
--unbounded preceding and unbouned following针对当前所有记录的前一条、后一条记录,也就是表中的所有记录
--unbounded:不受控制的,无限的
--preceding:在...之前
--following:在...之后
BETWEEN
UNBOUNDED PRECEDING
AND
UNBOUNDED FOLLOWING) MAX_SAL
FROM
EMP E;
|
运行结果:
解释:这里是的 ---
LAST_VALUE(E.SAL):函数是取最后一个值,
PARTITION
BY
E.DEPTNO:指以部门号分组,
ORDER
BY
E.SAL
:以工资排序,ROWS BETWEEN
UNBOUNDED PRECEDING
AND
UNBOUNDED FOLLOWING:表示窗体的范围为第一行到最后一行
注意:分析函数是在sql数据查出来的情况下再进行分析函数的操作的,sql语句的排序order by本身就是一个窗体函数,所以sql语句的排序功能实在分析函数之后进行的,也就是说sql的排序是最后一步执行的
sql语句查出数据-----分析函数执行-----sql的排序执行(order by是默认的窗体函数)
3.2示例目的:按照deptno分组,然后计算每组值的总和
1
2
3
4
5
6
|
SELECT
EMPNO,
ENAME,
DEPTNO,
SAL,
SUM
(SAL) OVER(PARTITION
BY
DEPTNO
ORDER
BY
ENAME) max_sal
FROM
SCOTT.EMP;
|
运行结果:
解释:
如果over()函数中没有窗口子句即---ROWS或RANG语句时,
3.2.1 如果省略分组,则把全部记录当成一个组。
3.2.2 如果存在order by则默认的窗口是unbounded preceding and current row --当前组的第一行到当前行,即在当前组中,第一行到当前行
3.2.3 如果同时省略order by则默认的窗口是unbounded preceding and unbounded following --整个组
依照规则,此语句的求和是求得是第一行到当前行的总和
三、理解over()函数
1.1、两个order by的执行时机
分析函数(以及与其配合的开窗函数over())是在整个sql查询结束后(sql语句中的order by的执行比较特殊)再进行的操作, 也就是说sql语句中的order by也会影响分析函数的执行结果:
a) 两者一致:如果sql语句中的order by满足与分析函数配合的开窗函数over()分析时要求的排序,即sql语句中的order by子句里的内容和开窗函数over()中的order by子句里的内容一样,
那么sql语句中的排序将先执行,分析函数在分析时就不必再排序;
b) 两者不一致:如果sql语句中的order by不满足与分析函数配合的开窗函数over()分析时要求的排序,即sql语句中的order by子句里的内容和开窗函数over()中的order by子句里的内容不一样,
那么sql语句中的排序将最后在分析函数分析结束后执行排序。
1.2、开窗函数over()分析函数中的分组/排序/窗口
开窗函数over()分析函数包含三个分析子句:分组子句(partition by), 排序子句(order by), 窗口子句(rows)
窗口就是分析函数分析时要处理的数据范围,就拿sum来说,它是sum窗口中的记录而不是整个分组中的记录,因此我们在想得到某个栏位的累计值时,我们需要把窗口指定到该分组中的第一行数据到当前行, 如果你指定该窗口从该分组中的第一行到最后一行,那么该组中的每一个sum值都会一样,即整个组的总和。
窗口子句在这里我只说rows方式的窗口,range方式和滑动窗口也不提。
窗口子句中我们经常用到指定第一行,当前行,最后一行这样的三个属性:
第一行是 unbounded preceding,
当前行是 current row,
最后一行是 unbounded following,
注释:
当开窗函数over()出现分组(partition by)子句时,
unbounded preceding即第一行是指表中一个分组里的第一行, unbounded following即最后一行是指表中一个分组里的最后一行;
当开窗函数over()省略了分组(partition by)子句时,
unbounded preceding即第一行是指表中的第一行, unbounded following即最后一行是指表中的最后一行。
窗口子句不能单独出现,必须有order by子句时才能出现,
例如:
1
2
3
|
last_value(sal) over(partition
by
deptno
order
by
sal
rows
between
unbounded preceding
and
unbounded following)
|
以上示例指定窗口为整个分组。而出现order by子句的时候,不一定要有窗口子句,但效果会很不一样,此时的窗口默认是当前组的第一行到当前行!
如果省略分组,则把全部记录当成一个组。a) 如果存在order by则默认窗口是unbounded preceding and current row --当前组的第一行到当前行
b) 如果这时省略order by则窗口默认为unbounded preceding and unbounded following --整个组
而无论是否省略分组子句,如下结论都是成立的:
1、窗口子句不能单独出现,必须有order by子句时才能出现。
2、当省略窗口子句时:
a) 如果存在order by则默认的窗口是unbounded preceding and current row --当前组的第一行到当前行,即在当前组中,第一行到当前行
b) 如果同时省略order by则默认的窗口是unbounded preceding and unbounded following --整个组
所以,
lag(sal) over(order by sal) 解释
over(order by salary)表示意义如下:
首先,我们要知道由于省略分组子句,所以当前组的范围为整个表的数据行,
然后,在当前组(此时为整个表的数据行)这个范围里执行排序(即order by salary),
最后,我们知道分析函数lag(sal)在当前组(此时为整个表的数据行)这个范围里的窗口范围为当前组的第一行到当前行,即分析函数lag(sal)在这个窗口范围执行。
参见:
Oracle的LAG和LEAD分析函数
Oracle分析函数ROW_NUMBER()|RANK()|LAG()使用详解
1.3、帮助理解over()的实例
例1:关注点:sql无排序,over()排序子句省略
1
2
3
|
SELECT
DEPTNO, EMPNO, ENAME, SAL,
LAST_VALUE(SAL) OVER(PARTITION
BY
DEPTNO)
FROM
EMP;
|
运行结果:
例2:关注点:sql无排序,over()排序子句有,窗口省略
1
2
3
4
5
6
7
|
SELECT
DEPTNO,
EMPNO,
ENAME,
SAL,
LAST_VALUE(SAL) OVER(PARTITION
BY
DEPTNO
ORDER
BY
SAL
DESC
)
FROM
EMP;
|
运行结果:
例3:关注点:sql无排序,over()排序子句有,窗口也有,窗口特意强调全组数据
1
2
3
4
5
6
7
8
9
|
SELECT
DEPTNO,
EMPNO,
ENAME,
SAL,
LAST_VALUE(SAL)
OVER(PARTITION
BY
DEPTNO
ORDER
BY
SAL
ROWS
BETWEEN
UNBOUNDED PRECEDING
AND
UNBOUNDED FOLLOWING) MAX_SAL
FROM
EMP;
|
运行结果:
例4:关注点:sql有排序(正序),over()排序子句无,先做sql排序再进行分析函数运算
1
2
3
4
5
6
7
8
9
|
SELECT
DEPTNO,
MGR,
ENAME,
SAL,
HIREDATE,
LAST_VALUE(SAL) OVER(PARTITION
BY
DEPTNO) LAST_VALUE
FROM
EMP
WHERE
DEPTNO = <strong>30</strong>
ORDER
BY
DEPTNO, MGR;
|
运行结果:
例5:关注点:sql有排序(倒序),over()排序子句无,先做sql排序再进行分析函数运算
1
2
3
4
5
6
7
8
9
|
SELECT
DEPTNO,
MGR,
ENAME,
SAL,
HIREDATE,
LAST_VALUE(SAL) OVER(PARTITION
BY
DEPTNO) LAST_VALUE
FROM
EMP
WHERE
DEPTNO = <strong>30</strong>
ORDER
BY
DEPTNO, MGR
DESC
;
|
运行结果:
例6:关注点:sql有排序(倒序),over()排序子句有,窗口子句无,此时的运算是:sql先选数据但是不排序,而后排序子句先排序并进行分析函数处理(窗口默认为第一行到当前行),最后再进行sql排序
1
2
3
4
5
6
7
8
9
|
SELECT
DEPTNO,
MGR,
ENAME,
SAL,
HIREDATE,
MIN
(SAL) OVER(PARTITION
BY
DEPTNO
ORDER
BY
SAL
ASC
) LAST_VALUE
FROM
EMP
WHERE
DEPTNO = <strong>30</strong>
ORDER
BY
DEPTNO, MGR
DESC
;
|
运行结果:
SELECT DEPTNO,
1
2
3
4
5
6
7
8
|
MGR,
ENAME,
SAL,
HIREDATE,
MIN
(SAL) OVER(PARTITION
BY
DEPTNO
ORDER
BY
SAL
DESC
) LAST_VALUE
FROM
EMP
WHERE
DEPTNO = <strong>30</strong>
ORDER
BY
DEPTNO, MGR
DESC
;
|
运行结果:
三、常见分析函数详解
为了方便进行实践,特将演示表和数据罗列如下:
一、创建表
1
2
3
4
5
6
|
create
table
t(
bill_month varchar2(<strong>12</strong>) ,
area_code number,
net_type
varchar
(<strong>2</strong>),
local_fare number
);
|
二、插入数据
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
|
insert
into
t
values
(
'200405'
,<strong>5761</strong>,
'G'
, <strong>7393344.04</strong>);
insert
into
t
values
(
'200405'
,<strong>5761</strong>,
'J'
, <strong>5667089.85</strong>);
insert
into
t
values
(
'200405'
,<strong>5762</strong>,
'G'
, <strong>6315075.96</strong>);
insert
into
t
values
(
'200405'
,<strong>5762</strong>,
'J'
, <strong>6328716.15</strong>);
insert
into
t
values
(
'200405'
,<strong>5763</strong>,
'G'
, <strong>8861742.59</strong>);
insert
into
t
values
(
'200405'
,<strong>5763</strong>,
'J'
, <strong>7788036.32</strong>);
insert
into
t
values
(
'200405'
,<strong>5764</strong>,
'G'
, <strong>6028670.45</strong>);
insert
into
t
values
(
'200405'
,<strong>5764</strong>,
'J'
, <strong>6459121.49</strong>);
insert
into
t
values
(
'200405'
,<strong>5765</strong>,
'G'
, <strong>13156065.77</strong>);
insert
into
t
values
(
'200405'
,<strong>5765</strong>,
'J'
, <strong>11901671.70</strong>);
insert
into
t
values
(
'200406'
,<strong>5761</strong>,
'G'
, <strong>7614587.96</strong>);
insert
into
t
values
(
'200406'
,<strong>5761</strong>,
'J'
, <strong>5704343.05</strong>);
insert
into
t
values
(
'200406'
,<strong>5762</strong>,
'G'
, <strong>6556992.60</strong>);
insert
into
t
values
(
'200406'
,<strong>5762</strong>,
'J'
, <strong>6238068.05</strong>);
insert
into
t
values
(
'200406'
,<strong>5763</strong>,
'G'
, <strong>9130055.46</strong>);
insert
into
t
values
(
'200406'
,<strong>5763</strong>,
'J'
, <strong>7990460.25</strong>);
insert
into
t
values
(
'200406'
,<strong>5764</strong>,
'G'
, <strong>6387706.01</strong>);
insert
into
t
values
(
'200406'
,<strong>5764</strong>,
'J'
, <strong>6907481.66</strong>);
insert
into
t
values
(
'200406'
,<strong>5765</strong>,
'G'
, <strong>13562968.81</strong>);
insert
into
t
values
(
'200406'
,<strong>5765</strong>,
'J'
, <strong>12495492.50</strong>);
insert
into
t
values
(
'200407'
,<strong>5761</strong>,
'G'
, <strong>7987050.65</strong>);
insert
into
t
values
(
'200407'
,<strong>5761</strong>,
'J'
, <strong>5723215.28</strong>);
insert
into
t
values
(
'200407'
,<strong>5762</strong>,
'G'
, <strong>6833096.68</strong>);
insert
into
t
values
(
'200407'
,<strong>5762</strong>,
'J'
, <strong>6391201.44</strong>);
insert
into
t
values
(
'200407'
,<strong>5763</strong>,
'G'
, <strong>9410815.91</strong>);
insert
into
t
values
(
'200407'
,<strong>5763</strong>,
'J'
, <strong>8076677.41</strong>);
insert
into
t
values
(
'200407'
,<strong>5764</strong>,
'G'
, <strong>6456433.23</strong>);
insert
into
t
values
(
'200407'
,<strong>5764</strong>,
'J'
, <strong>6987660.53</strong>);
insert
into
t
values
(
'200407'
,<strong>5765</strong>,
'G'
, <strong>14000101.20</strong>);
insert
into
t
values
(
'200407'
,<strong>5765</strong>,
'J'
, <strong>12301780.20</strong>);
insert
into
t
values
(
'200408'
,<strong>5761</strong>,
'G'
, <strong>8085170.84</strong>);
insert
into
t
values
(
'200408'
,<strong>5761</strong>,
'J'
, <strong>6050611.37</strong>);
insert
into
t
values
(
'200408'
,<strong>5762</strong>,
'G'
, <strong>6854584.22</strong>);
insert
into
t
values
(
'200408'
,<strong>5762</strong>,
'J'
, <strong>6521884.50</strong>);
insert
into
t
values
(
'200408'
,<strong>5763</strong>,
'G'
, <strong>9468707.65</strong>);
insert
into
t
values
(
'200408'
,<strong>5763</strong>,
'J'
, <strong>8460049.43</strong>);
insert
into
t
values
(
'200408'
,<strong>5764</strong>,
'G'
, <strong>6587559.23</strong>);
insert
into
t
values
(
'200408'
,<strong>5764</strong>,
'J'
, <strong>7342135.86</strong>);
insert
into
t
values
(
'200408'
,<strong>5765</strong>,
'G'
, <strong>14450586.63</strong>);
insert
into
t
values
(
'200408'
,<strong>5765</strong>,
'J'
, <strong>12680052.38</strong>);
commit
;
|
三、first_value()与last_value():求最值对应的其他属性
问题、取出每月通话费最高和最低的两个地区。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
SELECT
BILL_MONTH,
AREA_CODE,
SUM
(LOCAL_FARE) LOCAL_FARE,
FIRST_VALUE(AREA_CODE)
OVER(PARTITION
BY
BILL_MONTH
ORDER
BY
SUM
(LOCAL_FARE)
DESC
ROWS
BETWEEN
UNBOUNDED PRECEDING
AND
UNBOUNDED FOLLOWING) FIRSTVAL,
LAST_VALUE(AREA_CODE)
OVER(PARTITION
BY
BILL_MONTH
ORDER
BY
SUM
(LOCAL_FARE)
DESC
ROWS
BETWEEN
UNBOUNDED PRECEDING
AND
UNBOUNDED FOLLOWING) LASTVAL
FROM
T
GROUP
BY
BILL_MONTH, AREA_CODE
ORDER
BY
BILL_MONTH
|
运行结果:
四、rank(),dense_rank()与row_number():求排序
rank,dense_rank,row_number函数为每条记录产生一个从1开始至n的自然数,n的值可能小于等于记录的总数。这3个函数的唯一区别在于当碰到相同数据时的排名策略。
①row_number:
row_number函数返回一个唯一的值,当碰到相同数据时,排名按照记录集中记录的顺序依次递增。
②dense_rank:
dense_rank函数返回一个唯一的值,当碰到相同数据时,此时所有相同数据的排名都是一样的。
③rank:
rank函数返回一个唯一的值,当碰到相同的数据时,此时所有相同数据的排名是一样的,同时会在最后一条相同记录和下一条不同记录的排名之间空出排名。
演示数据在Oracle自带的scott用户下:
1、rank()值相同时排名相同,其后排名跳跃不连续
1
2
3
4
5
6
7
|
SELECT
*
FROM
(
SELECT
DEPTNO,
RANK() OVER(PARTITION
BY
DEPTNO
ORDER
BY
SAL
DESC
) RW,
ENAME,
SAL
FROM
SCOTT.EMP)
WHERE
RW <= <strong>4</strong>;
|
运行结果:
2、dense_rank()值相同时排名相同,其后排名连续不跳跃
1
2
3
4
5
6
7
|
SELECT
*
FROM
(
SELECT
DEPTNO,
DENSE_RANK() OVER(PARTITION
BY
DEPTNO
ORDER
BY
SAL
DESC
) RW,
ENAME,
SAL
FROM
SCOTT.EMP)
WHERE
RW <= <strong>4</strong>;
|
运行结果:
3、row_number()值相同时排名不相等,其后排名连续不跳跃
1
2
3
4
5
6
7
|
SELECT
*
FROM
(
SELECT
DEPTNO,
ROW_NUMBER() OVER(PARTITION
BY
DEPTNO
ORDER
BY
SAL
DESC
) RW,
ENAME,
SAL
FROM
SCOTT.EMP)
WHERE
RW <= <strong>4</strong>;
|
运行结果:
五、lag()与lead():求之前或之后的第N行
lag和lead函数可以在一次查询中取出同一字段的前n行的数据和后n行的值。这种操作可以使用对相同表的表连接来实现,不过使用lag和lead有更高的效率。
lag(arg1,arg2,arg3)
第一个参数是列名,
第二个参数是偏移的offset,
第三个参数是超出记录窗口时的默认值。
举例如下:
SQL> select * from kkk;
ID NAME
---------- --------------------
1 1name
2 2name
3 3name
4 4name
5 5name
SQL> select id,name,lag(name,1,0) over(order by id) from kkk;
ID NAME LAG(NAME,1,0)OVER(ORDERBYID)
---------- -------------------- ----------------------------
1 1name 0
2 2name 1name
3 3name 2name
4 4name 3name
5 5name 4name
SQL> select id,name,lead(name,1,0) over(order by id) from kkk;
ID NAME LEAD(NAME,1,0)OVER(ORDERBYID)
---------- -------------------- -----------------------------
1 1name 2name
2 2name 3name
3 3name 4name
4 4name 5name
5 5name 0
SQL> select id,name,lead(name,2,0) over(order by id) from kkk;
ID NAME LEAD(NAME,2,0)OVER(ORDERBYID)
---------- -------------------- -----------------------------
1 1name 3name
2 2name 4name
3 3name 5name
4 4name 0
5 5name 0
SQL> select id,name,lead(name,1,'linjiqin') over(order by id) from kkk;
ID NAME LEAD(NAME,1,'ALSDFJLASDJFSAF')
---------- -------------------- ------------------------------
1 1name 2name
2 2name 3name
3 3name 4name
4 4name 5name
5 5name linjiqin
---------------------------------------------------------------------------------------
六、rollup()与cube():排列组合分组
1)、group by rollup(a, b, c):
首先会对(a、b、c)进行group by,
然后再对(a、b)进行group by,
其后再对(a)进行group by,
最后对全表进行汇总操作。
2)、group by cube(a, b, c):
则首先会对(a、b、c)进行group by,
然后依次是(a、b),(a、c),(a),(b、c),(b),(c),
最后对全表进行汇总操作。
1、生成演示数据:
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as ds_trade
SQL> conn system/oracle as sysdba
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.3.0
Connected as SYS
SQL> create table scott.t as select * from dba_indexes;
Table created
SQL> connect scott/oracle
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.3.0
Connected as scott
SQL>
2、普通group by体验
sql> select owner, index_type, status, count(*) from t where owner like 'SY%' group by owner, index_type, status;
3、group by rollup(A,B,C)
GROUP BY ROLLUP(A, B, C):
首先会对(A、B、C)进行GROUP BY,
然后再对(A、B)进行GROUP BY,
其后再对(A)进行GROUP BY,
最后对全表进行汇总操作。
sql> select owner, index_type, status, count(*) from t where owner like 'SY%' group by ROLLUP(owner, index_type, status);
4、group by cube(A,B,C)
GROUP BY CUBE(A, B, C):
则首先会对(A、B、C)进行GROUP BY,
然后依次是(A、B),(A、C),(A),(B、C),(B),(C),
最后对全表进行汇总操作。
sql> select owner, index_type, status, count(*) from t where owner like 'SY%' group by cube(owner, index_type, status);
七、max(),min(),sum()与avg():求移动的最值总和与平均值
问题:计算出各个地区连续3个月的通话费用的平均数(移动平均值)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
SELECT
AREA_CODE,
BILL_MONTH,
LOCAL_FARE,
SUM
(LOCAL_FARE) OVER(PARTITION
BY
AREA_CODE
ORDER
BY
TO_NUMBER(BILL_MONTH)
RANGE
BETWEEN
<strong>1</strong> PRECEDING
AND
<strong>1</strong> FOLLOWING)
"3month_sum"
,
AVG
(LOCAL_FARE) OVER(PARTITION
BY
AREA_CODE
ORDER
BY
TO_NUMBER(BILL_MONTH)
RANGE
BETWEEN
<strong>1</strong> PRECEDING
AND
<strong>1</strong> FOLLOWING)
"3month_avg"
,
MAX
(LOCAL_FARE) OVER(PARTITION
BY
AREA_CODE
ORDER
BY
TO_NUMBER(BILL_MONTH)
RANGE
BETWEEN
<strong>1</strong> PRECEDING
AND
<strong>1</strong> FOLLOWING)
"3month_max"
,
MIN
(LOCAL_FARE) OVER(PARTITION
BY
AREA_CODE
ORDER
BY
TO_NUMBER(BILL_MONTH)
RANGE
BETWEEN
<strong>1</strong> PRECEDING
AND
<strong>1</strong> FOLLOWING)
"3month_min"
FROM
(
SELECT
T.AREA_CODE, T.BILL_MONTH,
SUM
(T.LOCAL_FARE) LOCAL_FARE
FROM
T
GROUP
BY
T.AREA_CODE, T.BILL_MONTH)
|
运行结果:
问题:求各地区按月份累加的通话费
1
2
3
4
5
6
7
8
9
|
SELECT
AREA_CODE,
BILL_MONTH,
LOCAL_FARE,
SUM
(LOCAL_FARE) OVER(PARTITION
BY
AREA_CODE
ORDER
BY
BILL_MONTH
ASC
)
"last_sum_value"
FROM
(
SELECT
T.AREA_CODE, T.BILL_MONTH,
SUM
(T.LOCAL_FARE) LOCAL_FARE
FROM
T
GROUP
BY
T.AREA_CODE, T.BILL_MONTH)
ORDER
BY
AREA_CODE, BILL_MONTH
|
运行结果:
--------------------------------------------------------------------------
7.1--再数据一样的情况下使用 ------sum()over()
select name,class,s, sum(s)over(partition by class order by s desc) mm from t2 --根据班级进行分数求和
dss 1 95 190 --由于两个95都是第一名,所以累加时是两个第一名的相加
ffd 1 95 190
fda 1 80 270 --第一名加上第二名的
gds 2 92 92
cfe 2 74 166
gf 3 99 198
ddd 3 99 198
3dd 3 78 276
asdf 3 55 331
adf 3 45 376
注意:本来该函数执行的默认窗口是第一行到当前行(在分组的范围内),但是第一行的数据和第二行的数据一样时,就会进行累加
文本来自参考---------------------------------https://www.2cto.com/database/201701/590484.html