一、数据库的一般函数
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 EMPWHERE 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