1.问题来源
遇到存储过程的一段sql,其定义了cursor供后面便利使用,如下:
/*定义游标,将全网支付未对完帐的业务线信息放入结果集中*/
CURSOR CUR_UNFINISHED_BUSI IS
SELECT BUSILINE, STATE
FROM (SELECT BUSILINE,
STATE,
ROW_NUMBER() OVER(PARTITION BY BUSILINE ORDER BY CREATETIME DESC) ROWNUMBER
FROM AF_FLOW
WHERE SETTLEDATE = P_DATE
AND PROCESSID LIKE P_PROCESS_ID_FNP || '%'
AND BUSILINE IS NOT NULL)
WHERE ROWNUMBER = 1 AND STATE <> 'Finish';
2.over语法
以前根本没注意到over的用法,特地查询了以下:
链接:http://www.xuebuyuan.com/3179906.html
- OVER子句用于为行为定义一个窗口(windows),以便进行特定的运算。可以把行的窗口简单地认为是运算将要操作的一个行的集合(如聚合函数和排名函数)。
- 由于OVER子句为这些函数提供了一个行的窗口,所以这些函数也称之为开窗函数。
听起来特别绕,理解也费劲儿,但是给个实在的例子也就清晰明了了:
- 假设有这样一个表Sales.OrderValues,表的每一行代表一个订单,包含订单ID(orderid),客户ID(custid)、雇员ID(empid)、承运人ID(shipperid)、订单日期(orderdate),以及订单价格(val)。
- 统计每个客户(同一个custid)的总订单价格。
分析:
- 如果查询在SELECT阶段之前没有过滤数据,也没有应用其他的逻辑阶段,这个表达式将返回OrderValues视图中所有行的总价格。
- 如果想对行进行限制或分区,则可以使用PARTITION BY子句。例如,现在不想返回OrderValues中所有行的总价格,而只是想返回当前客户(和当前行具有相同custid的所有行)的总价格,则可以指定SUM(val) OVER(PARTITION BY custid)。
解决方案:
SELECT orderid,custid,val,
SUM(val) OVER() AS totalvalue, --未使用over,直接为所有记录的val求和
SUM(val) OVER(PARTITION BY custid) AS custtotalvalue --指定按照custid来统计val的和
FROM OrderValues;
本质来讲:聚合开窗函数使用OVER子句提供窗口作为上下文,对窗口中的一组值进行操作,而不是使用GROUP BY子句提供的上下文。
3.学以致用
- 选出指定账期符合条件的流程记录,通过over指定业务线形成每个业务线一条记录
SELECT BUSILINE,
STATE,
ROW_NUMBER() OVER(PARTITION BY BUSILINE ORDER BY CREATETIME DESC) ROWNUMBER
FROM AF_FLOW
WHERE SETTLEDATE = P_DATE -- 指定账期的记录
AND PROCESSID LIKE P_PROCESS_ID_FNP || '%' -- 符合条件的流程(模糊查询)
AND BUSILINE IS NOT NULL -- 按业务线区分、保证业务线字段不为空
OVER(PARTITION BY BUSILINE ORDER BY CREATETIME DESC)就是针对选出符合账期、流程名、业务线非空条件的记录开窗,按照BUSILINE 字段分别形成记录,ORDER BY CREATETIME DESC只是为了时间顺序。
- 筛选流程非成功的记录
SELECT BUSILINE, STATE
FROM (SELECT BUSILINE,
STATE,
ROW_NUMBER() OVER(PARTITION BY BUSILINE ORDER BY CREATETIME DESC) ROWNUMBER
FROM AF_FLOW
WHERE SETTLEDATE = P_DATE
AND PROCESSID LIKE P_PROCESS_ID_FNP || '%'
AND BUSILINE IS NOT NULL)
WHERE ROWNUMBER = 1 AND STATE <> 'Finish'; -- 将不是Finish的记录选出,就知道哪些业务线未完成对账。
``
##4. 附:
- 建表和插入数据语句
```sql
create table OrderValues(orderid number,custid number,val number);
insert into OrderValues values(10643,1,814.50);
insert into OrderValues values(10692,1,66.50);
insert into OrderValues values(10702,1,85.50);
insert into OrderValues values(10835,1,1000.50);
insert into OrderValues values(10365,1,34.50);
insert into OrderValues values(10666,1,784.50);
insert into OrderValues values(11642,2,66.50);
insert into OrderValues values(11752,2,85.50);
insert into OrderValues values(11855,2,1000.50);
insert into OrderValues values(11335,2,34.50);
insert into OrderValues values(11636,2,784.50);
insert into OrderValues values(13855,3,1000.50);
insert into OrderValues values(13435,3,34.50);
insert into OrderValues values(15636,3,784.50);