选读SQL经典实例笔记09_数值处理

1. 对于复杂的数值计算而言，SQL 并非首选工具

2. 求和

2.1. SUM函数会忽略Null，但是我们可能会遇到Null分组

2.2. sql

1. select deptno, comm
2.   from emp
3. where deptno in (10,30)
4. order by 1
5.    DEPTNO       COMM
6. ---------- ----------
7.         10
8.         10
9.         10
10.         30        300
11.         30        500
12.         30
13.         30          0
14.         30       1300
15.         30
16. select sum(comm)
17.   from emp
18. SUM(COMM)
19. ----------
20.       2100
21. select deptno, sum(comm)
22.   from emp
23. where deptno in (10,30)
24. group by deptno
25.    DEPTNO  SUM(COMM)
26. ---------- ----------
27.         10
28.         30       2100

复制代码

3. 行数

3.1. COUNT函数会忽略Null

3.2. 使用符号*或者常量参数的时候，就会包含Null

4. 累计求和

4.1. DB2

4.2. Oracle

4.3. 使用SUM函数的窗口函数版本进行累计求和

1. select ename, sal,
2.         sum(sal) over (order by sal,empno) as running_total
3.    from emp
4.    order by 2
5. ENAME             SAL RUNNING_TOTAL
6. ---------- ---------- -------------
7. SMITH             800           800
8. JAMES             950          1750
9. ADAMS            1100          2850
10. WARD             1250          4100
11. MARTIN           1250          5350
12. MILLER           1300          6650
13. TURNER           1500          8150
14. ALLEN            1600          9750
15. CLARK            2450         12200
16. BLAKE            2850         15050
17. JONES            2975         18025
18. SCOTT            3000         21025
19. FORD             3000         24025
20. KING             5000         29025

复制代码

4.4. PostgreSQL

4.5. MySQL

4.6. SQL Server

4.7. 使用标量子查询来进行累计求和

1. select e.ename, e.sal,
2.         (select sum(d.sal) from emp d
3.           where d.empno ＜= e.empno) as running_total
4.    from emp e
5.   order by 3
6. ENAME             SAL RUNNING_TOTAL
7. ---------- ---------- -------------
8. SMITH             800           800
9. ALLEN            1600          2400
10. WARD             1250          3650
11. JONES            2975          6625
12. MARTIN           1250          7875
13. BLAKE            2850         10725
14. CLARK            2450         13175
15. SCOTT            3000         16175
16. KING             5000         21175
17. TURNER           1500         22675
18. ADAMS            1100         23775
19. JAMES             950         24725
20. FORD             3000         27725
21. MILLER           1300         29025

复制代码

5. 累计乘积

5.1. DB2

5.2. Oracle

5.3. 使用窗口函数SUM OVER，并利用对数来模拟乘法

1. select empno,ename,sal,
2.         exp(sum(ln(sal))over(order by sal,empno)) as running_prod
3.    from emp
4.   where deptno = 10
5. EMPNO ENAME       SAL         RUNNING_PROD
6. ----- ---------- ---- --------------------
7. 7934 MILLER     1300                 1300
8. 7782 CLARK      2450              3185000
9. 7839 KING       5000          15925000000

复制代码

5.4. PostgreSQL

5.5. MySQL

5.6. SQL Server

5.7. 标量子查询

1. select e.empno,e.ename,e.sal,
2.          (select exp(sum(ln(d.sal)))
3.             from emp d
4.            where d.empno ＜= e.empno
5.              and e.deptno=d.deptno) as running_prod
6.    from emp e
7.    where e.deptno=10
8. EMPNO ENAME       SAL         RUNNING_PROD
9. ----- ---------- ---- --------------------
10. 7782 CLARK      2450                 2450
11. 7839 KING       5000             12250000
12. 7934 MILLER     1300          15925000000

复制代码

5.7.2. 对于SQL Server而言，还需要用LOG函数来替代LN函数

6. 累计差

6.1. DB2

6.2. Oracle

6.3. 使用窗口函数SUM OVER

1. select ename,sal,
2.          sum(case when rn = 1 then sal else -sal end)
3.          over(order by sal,empno) as running_diff
4.     from (
5.   select empno,ename,sal,
6.          row_number() over(order by sal,empno) as rn
7.     from emp
8.    where deptno = 10
9.           ) x

复制代码

6.4. PostgreSQL

6.5. MySQL

6.6. SQL Server

6.7. 使用标量子查询

1. select a.empno, a.ename, a.sal,
2.         (select case when a.empno = min(b.empno) then sum(b.sal)
3.                      else sum(-b.sal)
4.                 end
5.            from emp b
6.           where b.empno ＜= a.empno
7.             and b.deptno = a.deptno ) as rnk
8.    from emp a
9.   where a.deptno = 10

复制代码

7. 众数

7.1. 在一组数据里出现次数最多的那个数

7.2. DB2

7.3. SQL Server

7.4. 使用窗口函数DENSE_RANK

1. select sal
2.     from (
3.   select sal,
4.          dense_rank() over(order by cnt desc) as rnk
5.     from (
6.   select sal, count(*) as cnt
7.     from emp
8.    where deptno = 20
9.    group by sal
10.          ) x
11.          ) y
12.    where rnk = 1

复制代码

7.5. Oracle

1. select max(sal)
2.           keep(dense_rank first order by cnt desc) sal
3.     from (
4.   select sal, count(*) cnt
5.     from emp
6.    where deptno=20
7.    group by sal
8.          )

复制代码

7.6. PostgreSQL

7.7. MySQL

7.8. 使用子查询

1. select sal
2.     from emp
3.    where deptno = 20
4.    group by sal
5.   having count(*) ＞= all ( select count(*)
6.                              from emp
7.                             where deptno = 20
8.                             group by sal )

复制代码

8. 中位数

8.1. 按顺序排列的一组数据中居于中间位置的数

8.2. DB2

1. 'select avg(sal)
2.     from (
3.   select sal,
4.          count(*) over() total,
5.          cast(count(*) over() as decimal)/2 mid,
6.          ceil(cast(count(*) over() as decimal)/2) next,
7.          row_number() over (order by sal) rn
8.     from emp
9.    where deptno = 20
10.          ) x
11.    where ( mod(total,2) = 0
12.            and rn in ( mid, mid+1 )
13.          )
14.       or ( mod(total,2) = 1
15.            and rn = next
16.          )

复制代码

8.2.2. DB2则使用MOD函数

8.3. SQL Server

1. select avg(sal)
2.     from (
3.   select sal,
4.          count(*) over() total,
5.          cast(count(*) over() as decimal)/2 mid,
6.          ceiling(cast(count(*)over() as decimal)/2) next,
7.          row_number() over(order by sal) rn
8.     from emp
9.    where deptno = 20
10.          ) x
11.    where ( total%2 = 0
12.            and rn in ( mid, mid+1 )
13.          )
14.       or ( total%2 = 1
15.            and rn = next
16.          )

复制代码

8.3.2. SQL Server的取模运算符是%

8.4. Oracle

1. select median(sal)
2.   from emp
3. where deptno=20

复制代码

8.4.1.1. Oracle Database 10g

1. select percentile_cont(0.5)
2.         within group(order by sal)
3.   from emp
4. where deptno=20

复制代码

8.4.2.1. Oracle 9i

8.5. PostgreSQL

8.6. MySQL

8.7. 使用自连接查询

1. select avg(sal)
2.     from (
3.   select e.sal
4.     from emp e, emp d
5.    where e.deptno = d.deptno
6.      and e.deptno = 20
7.    group by e.sal
8.   having sum(case when e.sal = d.sal then 1 else 0 end)
9.                             ＞= abs(sum(sign(e.sal - d.sal)))
10.          )

复制代码

9. 百分比

9.1. 某一列的值占总和的百分比

9.2. DB2

9.3. Oracle

9.4. SQL Server

9.5. sql

1. select distinct (d10/total)*100 as pct
2.    from (
3. select deptno,
4.         sum(sal)over() total,
5.         sum(sal)over(partition by deptno) d10
6.    from emp
7.         ) x
8.   where deptno=10

复制代码

9.6. MySQL

9.7. PostgreSQL

9.8. sql

1. select (sum(
2.           case when deptno = 10 then sal end)/sum(sal)
3.          )*100 as pct
4.    from emp

复制代码

10. 聚合Null列

10.1. 使用聚合函数时一定要记住，Null值会被忽略

10.2. 一旦涉及聚合运算，就要相应地考虑如何处理Null值

1. select avg(coalesce(comm,0)) as avg_comm
2.     from emp
3.    where deptno=30

复制代码

11. 计算平均值时去掉最大值和最小值

11.1. DB2

11.2. Oracle

11.3. SQL Server

11.4. 窗口函数MAX OVER和MIN OVER

1. select avg(sal)
2.     from (
3.   select sal, min(sal) over()min_sal, max(sal)over() max_sal
4.     from emp
5.          ) x
6.    where sal not in (min_sal,max_sal)

复制代码

11.5. PostgreSQL

11.6. MySQL

11.7. 使用子查询去掉最大值和最小值

1. select avg(sal)
2.     from emp
3.    where sal not in (
4.       (select min(sal) from emp),
5.       (select max(sal) from emp)
6.    )

复制代码

11.7.2. 如果希望只去掉一个最大值和一个最小值，只需要把它们从合计值里先减掉，再做除法即可

1. select (sum(sal)-min(sal)-max(sal))/(count(*)-2)
2.   from emp

复制代码

12. 修改累计值

12.1.  示例

1. create view V (id,amt,trx)
2. as
3. select 1, 100, 'PR' from t1 union all
4. select 2, 100, 'PR' from t1 union all
5. select 3, 50,  'PY' from t1 union all
6. select 4, 100, 'PR' from t1 union all
7. select 5, 200, 'PY' from t1 union all
8. select 6, 50,  'PY' from t1
9. select * from V
10. ID        AMT TRX
11. -- ---------- ---
12. 1        100  PR
13. 2        100  PR
14. 3         50  PY
15. 4        100  PR
16. 5        200  PY
17. 6         50  PY

复制代码

12.2. DB2

12.3. Oracle

12.4. 使用窗口函数SUM OVER进行累计求和

1. select case when trx = 'PY'
2.               then 'PAYMENT'
3.               else 'PURCHASE'
4.           end trx_type,
5.           amt,
6.           sum(
7.            case when trx = 'PY'
8.               then -amt else amt
9.            end
10.          ) over (order by id,amt) as balance
11.     from V

复制代码

12.4.2. 使用CASE表达式来决定交易的类型

12.5. PostgreSQL

12.6. MySQL

12.7. SQL Server

12.8. 使用标量子查询进行累计求和

1. select case when v1.trx = 'PY'
2.               then 'PAYMENT'
3.               else 'PURCHASE'
4.           end as trx_type,
5.           v1.amt,
6.           (select sum(
7.                    case when v2.trx = 'PY'
8.                         then -v2.amt else v2.amt
9.                    end
10.                  )
11.             from V v2
12.            where v2.id ＜= v1.id) as balance
13.    from V v1

复制代码

12.8.2. 使用CASE表达式来决定交易的类型


免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！