sql语句常⽤的函数总结
⼀、数据库的⼀般函数
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开窗的窗⼝范围：value函数什么意思
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的范围内的求和
select name,class,s, sum(s)over(order by s range between 2 preceding and 2 following) mm from t2
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
over （order by salary rows between 5 preceding and 5 following ）：窗⼝范围为当前⾏前后各移动5⾏。
举例：sum(s)over(order by s rows between 2 preceding and 2 following)表⽰在上下两⾏之间的范围内
select name,class,s, sum(s)over(order by s rows between 2 preceding and 2 following) mm from t2
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
2345678910111213
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 分组，然后计算每组值的总和
123456SELECT 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 3last_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()排序⼦句省略
123SELECT DEPTNO, EMPNO, ENAME, SAL,
LAST_VALUE(SAL) OVER(PARTITION BY DEPTNO)
FROM EMP;
运⾏结果：
例2：关注点：sql ⽆排序，over()排序⼦句有，窗⼝省略
1234567SELECT DEPTNO, EMPNO, ENAME,
SAL,
LAST_VALUE(SAL) OVER(PARTITION BY DEPTNO
ORDER BY SAL DESC)
FROM EMP;
运⾏结果：
例3：关注点：sql ⽆排序，over()排序⼦句有，窗⼝也有，窗⼝特意强调全组数据
1234567
89SELECT 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 排序再进⾏分析函数运算
123456789SELECT 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 排序再进⾏分析函数运算
12345678
9SELECT 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 排序
123456789SELECT 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;
运⾏结果：
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论