选读SQL经典实例笔记09_数值处理（sql数值计算）
sinye56 2024-09-22 08:26 7 浏览 0 评论
1. 对于复杂的数值计算而言，SQL 并非首选工具
2. 求和
2.1. SUM函数会忽略Null，但是我们可能会遇到Null分组
2.2. sql
select deptno, comm
from emp
where deptno in (10,30)
order by 1
DEPTNO COMM
---------- ----------
10
10
10
30 300
30 500
30
30 0
30 1300
30
select sum(comm)
from emp
SUM(COMM)
----------
2100
select deptno, sum(comm)
from emp
where deptno in (10,30)
group by deptno
DEPTNO SUM(COMM)
---------- ----------
10
30 21003. 行数
3.1. COUNT函数会忽略Null
3.2. 使用符号*或者常量参数的时候，就会包含Null
4. 累计求和
4.1. DB2
4.2. Oracle
4.3. 使用SUM函数的窗口函数版本进行累计求和
select ename, sal,
sum(sal) over (order by sal,empno) as running_total
from emp
order by 2
ENAME SAL RUNNING_TOTAL
---------- ---------- -------------
SMITH 800 800
JAMES 950 1750
ADAMS 1100 2850
WARD 1250 4100
MARTIN 1250 5350
MILLER 1300 6650
TURNER 1500 8150
ALLEN 1600 9750
CLARK 2450 12200
BLAKE 2850 15050
JONES 2975 18025
SCOTT 3000 21025
FORD 3000 24025
KING 5000 290254.4. PostgreSQL
4.5. MySQL
4.6. SQL Server
4.7. 使用标量子查询来进行累计求和
select e.ename, e.sal,
(select sum(d.sal) from emp d
where d.empno ＜= e.empno) as running_total
from emp e
order by 3
ENAME SAL RUNNING_TOTAL
---------- ---------- -------------
SMITH 800 800
ALLEN 1600 2400
WARD 1250 3650
JONES 2975 6625
MARTIN 1250 7875
BLAKE 2850 10725
CLARK 2450 13175
SCOTT 3000 16175
KING 5000 21175
TURNER 1500 22675
ADAMS 1100 23775
JAMES 950 24725
FORD 3000 27725
MILLER 1300 290255. 累计乘积
5.1. DB2
5.2. Oracle
5.3. 使用窗口函数SUM OVER，并利用对数来模拟乘法
select empno,ename,sal,
exp(sum(ln(sal))over(order by sal,empno)) as running_prod
from emp
where deptno = 10
EMPNO ENAME SAL RUNNING_PROD
----- ---------- ---- --------------------
7934 MILLER 1300 1300
7782 CLARK 2450 3185000
7839 KING 5000 159250000005.4. PostgreSQL
5.5. MySQL
5.6. SQL Server
5.7. 标量子查询
select e.empno,e.ename,e.sal,
(select exp(sum(ln(d.sal)))
from emp d
where d.empno ＜= e.empno
and e.deptno=d.deptno) as running_prod
from emp e
where e.deptno=10
EMPNO ENAME SAL RUNNING_PROD
----- ---------- ---- --------------------
7782 CLARK 2450 2450
7839 KING 5000 12250000
7934 MILLER 1300 159250000005.7.2. 对于SQL Server而言，还需要用LOG函数来替代LN函数
6. 累计差
6.1. DB2
6.2. Oracle
6.3. 使用窗口函数SUM OVER
select ename,sal,
sum(case when rn = 1 then sal else -sal end)
over(order by sal,empno) as running_diff
from (
select empno,ename,sal,
row_number() over(order by sal,empno) as rn
from emp
where deptno = 10
) x6.4. PostgreSQL
6.5. MySQL
6.6. SQL Server
6.7. 使用标量子查询
select a.empno, a.ename, a.sal,
(select case when a.empno = min(b.empno) then sum(b.sal)
else sum(-b.sal)
end
from emp b
where b.empno ＜= a.empno
and b.deptno = a.deptno ) as rnk
from emp a
where a.deptno = 107. 众数
7.1. 在一组数据里出现次数最多的那个数
7.2. DB2
7.3. SQL Server
7.4. 使用窗口函数DENSE_RANK
select sal
from (
select sal,
dense_rank() over(order by cnt desc) as rnk
from (
select sal, count(*) as cnt
from emp
where deptno = 20
group by sal
) x
) y
where rnk = 17.5. Oracle
select max(sal)
keep(dense_rank first order by cnt desc) sal
from (
select sal, count(*) cnt
from emp
where deptno=20
group by sal
)7.6. PostgreSQL
7.7. MySQL
7.8. 使用子查询
select sal
from emp
where deptno = 20
group by sal
having count(*) ＞= all ( select count(*)
from emp
where deptno = 20
group by sal )8. 中位数
8.1. 按顺序排列的一组数据中居于中间位置的数
8.2. DB2
'select avg(sal)
from (
select sal,
count(*) over() total,
cast(count(*) over() as decimal)/2 mid,
ceil(cast(count(*) over() as decimal)/2) next,
row_number() over (order by sal) rn
from emp
where deptno = 20
) x
where ( mod(total,2) = 0
and rn in ( mid, mid+1 )
)
or ( mod(total,2) = 1
and rn = next
)8.2.2. DB2则使用MOD函数
8.3. SQL Server
select avg(sal)
from (
select sal,
count(*) over() total,
cast(count(*) over() as decimal)/2 mid,
ceiling(cast(count(*)over() as decimal)/2) next,
row_number() over(order by sal) rn
from emp
where deptno = 20
) x
where ( total%2 = 0
and rn in ( mid, mid+1 )
)
or ( total%2 = 1
and rn = next
)8.3.2. SQL Server的取模运算符是%
8.4. Oracle
select median(sal)
from emp
where deptno=208.4.1.1. Oracle Database 10g
select percentile_cont(0.5)
within group(order by sal)
from emp
where deptno=208.4.2.1. Oracle 9i
8.5. PostgreSQL
8.6. MySQL
8.7. 使用自连接查询
select avg(sal)
from (
select e.sal
from emp e, emp d
where e.deptno = d.deptno
and e.deptno = 20
group by e.sal
having sum(case when e.sal = d.sal then 1 else 0 end)
＞= abs(sum(sign(e.sal - d.sal)))
)9. 百分比
9.1. 某一列的值占总和的百分比
9.2. DB2
9.3. Oracle
9.4. SQL Server
9.5. sql
select distinct (d10/total)*100 as pct
from (
select deptno,
sum(sal)over() total,
sum(sal)over(partition by deptno) d10
from emp
) x
where deptno=109.6. MySQL
9.7. PostgreSQL
9.8. sql
select (sum(
case when deptno = 10 then sal end)/sum(sal)
)*100 as pct
from emp10. 聚合Null列
10.1. 使用聚合函数时一定要记住，Null值会被忽略
10.2. 一旦涉及聚合运算，就要相应地考虑如何处理Null值
select avg(coalesce(comm,0)) as avg_comm
from emp
where deptno=3011. 计算平均值时去掉最大值和最小值
11.1. DB2
11.2. Oracle
11.3. SQL Server
11.4. 窗口函数MAX OVER和MIN OVER
select avg(sal)
from (
select sal, min(sal) over()min_sal, max(sal)over() max_sal
from emp
) x
where sal not in (min_sal,max_sal)11.5. PostgreSQL
11.6. MySQL
11.7. 使用子查询去掉最大值和最小值
select avg(sal)
from emp
where sal not in (
(select min(sal) from emp),
(select max(sal) from emp)
)11.7.2. 如果希望只去掉一个最大值和一个最小值，只需要把它们从合计值里先减掉，再做除法即可
select (sum(sal)-min(sal)-max(sal))/(count(*)-2)
from emp12. 修改累计值
12.1. 示例
create view V (id,amt,trx)
as
select 1, 100, 'PR' from t1 union all
select 2, 100, 'PR' from t1 union all
select 3, 50, 'PY' from t1 union all
select 4, 100, 'PR' from t1 union all
select 5, 200, 'PY' from t1 union all
select 6, 50, 'PY' from t1
select * from V
ID AMT TRX
-- ---------- ---
1 100 PR
2 100 PR
3 50 PY
4 100 PR
5 200 PY
6 50 PY12.2. DB2
12.3. Oracle
12.4. 使用窗口函数SUM OVER进行累计求和
select case when trx = 'PY'
then 'PAYMENT'
else 'PURCHASE'
end trx_type,
amt,
sum(
case when trx = 'PY'
then -amt else amt
end
) over (order by id,amt) as balance
from V12.4.2. 使用CASE表达式来决定交易的类型
12.5. PostgreSQL
12.6. MySQL
12.7. SQL Server
12.8. 使用标量子查询进行累计求和
select case when v1.trx = 'PY'
then 'PAYMENT'
else 'PURCHASE'
end as trx_type,
v1.amt,
(select sum(
case when v2.trx = 'PY'
then -v2.amt else v2.amt
end
)
from V v2
where v2.id ＜= v1.id) as balance
from V v112.8.2. 使用CASE表达式来决定交易的类型
相关推荐
- 6个接私活的网站，你有技术就有钱
-
如果觉得有帮助，还请大家帮忙多多转发，点个关注作者：发哥链接：GitHubDaily本篇文章会向大家推荐国内外几个接外包比较靠谱的平台，主旨是贵精不贵多。因此，像「猪x戒」这种会让程序员自贬身价，扰乱...
- Java开源可商用的CMS建站系统_java建站源码
-
Java研发的CMS内容管理系统具有许多优势和特点，包括以下几个方面：跨平台性：Java是一种跨平台的编程语言，可以在不同的操作系统上运行，包括Windows、Linux、Mac等。这意味着Java...
- SEO新手建站必看"干货"优质空间和功能选择技巧!
-
一.空间的分类服务器：远程的高级大型计算机。vps:虚拟服务器。虚拟空间：也称虚拟主机云主机：是在一组集群主机上虚拟出多个类似独立主机的部分，集群中每个主机上都有云主机的一个镜像，从而大大提高了虚拟主...
- 千字长文教你使用 宝塔面板 快速搭建网站
-
本文将教大家使用宝塔面板快速搭建网站，云服务器购买以及域名注册部分请自行上网搜索了解，亦可留言联系小编进行咨询。如果是和下方一样本地搭建演示的话，则不需要付费购买域名和主机。宝塔面板的是...
- BlueHost香港虚拟主机建站的5个优点
-
应该是从2006年左右开始，如果我们建站选择国内的主机需要备案手续，而且比较繁琐，且根据各地的不同政策还需要到接入点拍照登记个人信息等，一来比较繁琐，二来我们担心万一网站可能存在的信息问题导致不必要的...
- 10款好用的Linux服务器网站管理面板推荐
-
如今在建站时，很多人都会使用管理面板来辅助建站，因为相对于手动安装软件，面板更加简单而且高效，即使新手也能很快学会搭建网站，在本文中我们来推荐几款好用的网站管理面板宝塔面板宝塔面板是一款简单好用的网站...
- 小白拥有一台云服务器到底能干些什么?成就感爆棚的简单方案!
-
?云服务器是什么？云服务器（比如阿里云、腾讯云等）是提供给用户的一种虚拟服务器资源，你可以把它看作一台“rent的电脑”，只需要支付少量费用就可以拥有一个功能强大的网络设备。对于小白来说，拥有一...
- 苹果CMS，苹果CMS采集插件，苹果CMS快速建站(图文教程)
-
苹果CMS，有着强大的管理功能，管理后台界面大方、操作简单、功能齐全、模块众多、双端管理。苹果CMS加上丰富的系统标签，系统内置了丰富的cms标签并支持thinkphp框架标签完美融合，可以调取系统内...
- 新手搭建网站、小程序、APP等系统，如何选择服务器?
-
今天和小蔡和大家说说，新手搭建网站，如何选择服务器？废话不多说，直接来干货。服务器是存放网站源代码的容器，也是运行网站程序的工具，所以是不可或缺的。新手刚接触搭建网站，若不知道怎么去选择一台适合自己...
- 在海外VPS服务器(Hostinger)上配置宝塔面板的操作步骤
-
不得不说，宝塔面板是真的好用啊~用上就放不下了，一些海外的免费开源的服务器集成面板（比如CloudPanel）我也用了，不喜欢，真的不如宝塔面板方便耐用。今天聊一下在海外服务器（也包括国内服务器，没有...
- 干货盘点:每个wordpress站长都推荐完成的60个任务清单
-
构建和运营wordpress网站包含了很多重要任务，遗漏哪一方面都可能造成或大或小的不良后果，因此我们特别整理了这个任务清单，为你查漏补缺，希望能对您现在运营或者即将开始构建的wordpress网站有...
- 为什么站长喜欢选择BlueHost主机建站
-
BlueHost正式成立于2003年，从事主机(虚拟主机)业务至今已经将近十余年，无论从口碑还是用户的评价，我们基本很少看到关于Bluehost主机产品和商家负面的评论信息。从2014年开始，Blue...
- 自助建站时代来临 半小时成建站达人
-
“H5”意为第五代HTML，即第五代网页编写语言。自从1991年第一代HTML开始研发以来，网页编写、网站建设一直都属于高端技术行业，网站建设人员都是一些专业型人才，这也意味着网站的建设和维护都需要不...
- 现代化、开源的 Linux 服务器运维管理面板
-
1Panel是一个现代化、开源的Linux服务器运维管理面板。1Panel的功能和优势包括：快速建站：深度集成Wordpress和Halo，域名绑定、SSL证书配置等一键搞定；高效管理...
- [1Panel]开源，现代化，新一代的 Linux 服务器运维管理面板
-
测评介绍本期测评试用一下1Panel这款面板。1Panel是国内飞致云旗下开源产品。整个界面简洁清爽，后端使用GO开发，前端使用VUE的Element-Plus作为UI框架，整个面板的管理都是基于do...
欢迎 你 发表评论:
- 一周热门
- 最近发表
- 标签列表
-
- oracle忘记用户名密码 (59)
- oracle11gr2安装教程 (55)
- mybatis调用oracle存储过程 (67)
- oracle spool的用法 (57)
- oracle asm 磁盘管理 (67)
- 前端 设计模式 (64)
- 前端面试vue (56)
- linux格式化 (55)
- linux图形界面 (62)
- linux文件压缩 (75)
- Linux设置权限 (53)
- linux服务器配置 (62)
- mysql安装linux (71)
- linux启动命令 (59)
- 查看linux磁盘 (72)
- linux用户组 (74)
- linux多线程 (70)
- linux设备驱动 (53)
- linux自启动 (59)
- linux网络命令 (55)
- linux传文件 (60)
- linux打包文件 (58)
- linux查看数据库 (61)
- linux获取ip (64)
- linux进程通信 (63)