【SQL开发实战技巧】系列（十四）：计算消费后的余额&计算银行流水累计和&计算各部门工资排名前三位的员工

系列文章目录

【SQL开发实战技巧】系列（一）:关于SQL不得不说的那些事
【SQL开发实战技巧】系列（二）：简单单表查询
【SQL开发实战技巧】系列（三）：SQL排序的那些事
【SQL开发实战技巧】系列（四）：从执行计划讨论UNION ALL与空字符串&UNION与OR的使用注意事项
【SQL开发实战技巧】系列（五）：从执行计划看IN、EXISTS 和 INNER JOIN效率，我们要分场景不要死记网上结论
【SQL开发实战技巧】系列（六）：从执行计划看NOT IN、NOT EXISTS 和 LEFT JOIN效率，记住内外关联条件不要乱放
【SQL开发实战技巧】系列（七）：从有重复数据前提下如何比较出两个表中的差异数据及对应条数聊起
【SQL开发实战技巧】系列（八）：聊聊如何插入数据时比约束更灵活的限制数据插入以及怎么一个insert语句同时插入多张表
【SQL开发实战技巧】系列（九）：一个update误把其他列数据更新成空了？Merge改写update！给你五种删除重复数据的写法！
【SQL开发实战技巧】系列（十）：从拆分字符串、替换字符串以及统计字符串出现次数说起
【SQL开发实战技巧】系列（十一）：拿几个案例讲讲translate|regexp_replace|listagg|wmsys.wm_concat|substr|regexp_substr常用函数
【SQL开发实战技巧】系列（十二）：三问(如何对字符串字母去重后按字母顺序排列字符串？如何识别哪些字符串中包含数字？如何将分隔数据转换为多值IN列表?)
【SQL开发实战技巧】系列（十三）：讨论一下常用聚集函数&通过执行计划看sum()over()对员工工资进行累加
【SQL开发实战技巧】系列（十四）：计算消费后的余额&计算银行流水累计和&计算各部门工资排名前三位的员工
【SQL开发实战技巧】系列（十五）：查找最值所在行数据信息及快速计算总和百之max/min() keep() over()、fisrt_value、last_value、ratio_to_report
【SQL开发实战技巧】系列（十六）：数据仓库中时间类型操作（初级）日、月、年、时、分、秒之差及时间间隔计算
【SQL开发实战技巧】系列（十七）：数据仓库中时间类型操作（初级）确定两个日期之间的工作天数、计算—年中周内各日期出现次数、确定当前记录和下一条记录之间相差的天数
【SQL开发实战技巧】系列（十八）：数据仓库中时间类型操作（进阶）INTERVAL、EXTRACT以及如何确定一年是否为闰年及周的计算
【SQL开发实战技巧】系列（十九）：数据仓库中时间类型操作（进阶）如何一个SQL打印当月或一年的日历？如何确定某月内第一个和最后—个周内某天的日期？
【SQL开发实战技巧】系列（二十）：数据仓库中时间类型操作（进阶）获取季度开始结束时间以及如何统计非连续性时间的数据
【SQL开发实战技巧】系列（二十一）：数据仓库中时间类型操作（进阶）识别重叠的日期范围，按指定10分钟时间间隔汇总数据
【SQL开发实战技巧】系列（二十二）：数仓报表场景☞ 从分析函数效率一定快吗聊一聊结果集分页和隔行抽样实现方式
【SQL开发实战技巧】系列（二十三）：数仓报表场景☞ 如何对数据排列组合去重以及通过如何找到包含最大值和最小值的记录这个问题再次用执行计划给你证明分析函数性能不一定高
【SQL开发实战技巧】系列（二十四）：数仓报表场景☞通过案例执行计划详解”行转列”,”列转行”是如何实现的
【SQL开发实战技巧】系列（二十五）：数仓报表场景☞结果集中的重复数据只显示一次以及计算部门薪资差异高效的写法以及如何对数据进行快速分组
【SQL开发实战技巧】系列（二十六）：数仓报表场景☞聊聊ROLLUP、UNION ALL是如何分别做分组合计的以及如何识别哪些行是做汇总的结果行

文章目录

前言

本篇文章讲解的主要内容是：通过模拟计算消费流水账及计算银行流水累计和讲解sum()over()函数使用场景、通过计算各部门工资排名前三位的员工小案例来介绍ROW_NUMBER、RANK、DENSE_RANK使用方法及区别
【SQL开发实战技巧】这一系列博主当作复习旧知识来进行写作，毕竟SQL开发在数据分析场景非常重要且基础，面试也会经常问SQL开发和调优经验，相信当我写完这一系列文章，也能再有所收获，未来面对SQL面试也能游刃有余~。

一、模拟计算消费流水账

首先生成样例数据：

SQL>with t as(2select1000as 编号,'余额'as 项目,30000as 金额 from dual
  3unionall4select empno,to_char(rownum),sal from emp where deptno=105)6select*from t;

        编号 项目                                             金额
---------- ---------------------------------------- ----------1000 余额                                          30000778212450783925000793431300

这是模拟的一个消费流水账，账户余额初始是30000，后面是三次消费的数据，现在有个需求：得到每笔费用消费后的余额。
如何做这个需求呢？
这里给出解决方案，如下：

SQL>with t as(2select1000as 编号,'余额'as 项目,30000as 金额 from dual
  3unionall4select empno,to_char(rownum),sal from emp where deptno=105)6select t.*,sum(casewhen 项目='余额'then 金额 else-金额 end)over(orderby 编号)as 余额
  7from t;

        编号 项目                                             金额         余额
---------- ---------------------------------------- ---------- ----------1000 余额                                          3000030000778212450275507839250002255079343130021250SQL>

二、计算银行流水累计和

有如下临时表：

SQL>with t as2(select1as id,100as amt,'PR'as trx
  3from dual
  4unionall5select2as id,100as amt,'PR'as trx
  6from dual
  7unionall8select3as id,50as amt,'PY'as trx
  9from dual
 10unionall11select4as id,100as amt,'PR'as trx
 12from dual
 13unionall14select5as id,200as amt,'PY'as trx
 15from dual
 16unionall17select6as id,50as amt,'PY'as trx
 18from dual)19select*from t;

        ID        AMT TRX
---------- ---------- ---1100 PR
         2100 PR
         350 PY
         4100 PR
         5200 PY
         650 PY

6rows selected

这是一个存取／款列表。

• id是唯一列
• amt列表示每次事务处理（取款或存款）涉及的金额
• trx列定义了事务处理的类型，取款是"PY",存款是"PR"

现在有一个需求：计算每次存／取款后的余额：如果TRX是"PR",则加上amt值代表的金额，否则要减去amt代表的金额。

怎么做呢？
看下面解决方案：

SQL>with t as2(select1as id,100as amt,'PR'as trx
  3from dual
  4unionall5select2as id,100as amt,'PR'as trx
  6from dual
  7unionall8select3as id,50as amt,'PY'as trx
  9from dual
 10unionall11select4as id,100as amt,'PR'as trx
 12from dual
 13unionall14select5as id,200as amt,'PY'as trx
 15from dual
 16unionall17select6as id,50as amt,'PY'as trx
 18from dual)19select id,20case21when trx ='PR'then22'存款'23else24'取款'25endas 存取类型,26         amt,27sum(case28when trx ='PR'then29            amt
 30else31-amt
 32end)over(orderby id)as 余额
 33from t
 34orderby id;

        ID 存取类型        AMT         余额
---------- ---- ---------- ----------1 存款        1001002 存款        1002003 取款         501504 存款        1002505 取款        200506 取款         5006rows selected

三、返回各部门工资排名前三位的员工

有经验的人一看到标题，马上就会指出：这个需求太模糊了。为什么？先看下面的示
例。

SQL>select deptno,2         empno,3         sal,4         row_number()over(partitionby deptno orderby sal desc)as row_number,5         rank()over(partitionby deptno orderby sal desc)as rank,6         dense_rank()over(partitionby deptno orderby sal desc)as dense_rank
  7from emp
  8where deptno in(20,30)9orderby1,3desc;

DEPTNO EMPNO       SAL ROW_NUMBER       RANK DENSE_RANK
------ ----- --------- ---------- ---------- ----------2077883000.001112079023000.002112075662975.003322078761100.00443207369800.005543076982850.001113074991600.002223078441500.003333075211250.004443076541250.00544307900950.0066511rows selected

该示例用了

PARTITION BY

子句，通过这个子句可以把主查询返回的数据分组进行分析。在查询中分别用了三个分析函数

ROW_NUMBER、RANK、DENSE_RANK

来分组

(PARTITION BY deptno)

生成序号。
注意粗体标识的部分，当排序列（工资）有重复数据时，会出现以下情况。

ROW_NUMBER

仍然会生成序号1、2、3。

RANK

相同的工资会生成同样的序号，而且其后的序号与

ROW_NUMBER

相同(empno=7566,生成的序号是3)。

DENSE_RANK

相同的工资会生成同样的序号，而且其后的序号递增(empno=7566,生成的序号是2)。
这里如果用

ROW_NUMBER

取排名第一的员工，显然会漏掉7902这名员工。如果用

DENSE_RANK

取排名前两位的员工，很明显会返回三条记录。所以具体要分析清楚需求，再决定用哪一个函数。
这里选用

DENSE_RANK

(因需求不定，所以随意选取了一个）取排名前三的员工，返回数据如下：

SQL>select*from(2select deptno,3         empno,4         sal,5         row_number()over(partitionby deptno orderby sal desc)as row_number,6         rank()over(partitionby deptno orderby sal desc)as rank,7         dense_rank()over(partitionby deptno orderby sal desc)as dense_rank
  8from emp
  9where deptno in(20,30)10orderby1,3desc11)where dense_rank<=3;

DEPTNO EMPNO       SAL ROW_NUMBER       RANK DENSE_RANK
------ ----- --------- ---------- ---------- ----------2077883000.001112079023000.002112075662975.003322078761100.004433076982850.001113074991600.002223078441500.003337rows selected

总结

上面文章是sum()over() 、ROW_NUMBER、RANK、DENSE_RANK几个分析函数常用的场景，这里博主拿了三个工作案例场景给大家讲解。