复杂的HANASQL RANK和ROW_NUMBER函数的应用

一

前言

基于HANA的内存数据库的强大性能, SAP建议把业务逻辑下沉到HANA中计算.以便减去应用服务器的负担,让程序性能更好一些.

SAP本身的一些复杂的业务逻辑比如MRP运算(MD01)也有了新的事务 MD01N (MRP LIVE)

报表类的数据分析程序尤其适用. 

动态报表强化了这个方式

详见链接

无峰，公众号：ABAP 技巧与实战动态报表D-Query简介

复杂的HANASQL系列,主要介绍在项目中一些复杂业务逻辑的SQL实现

本文主要介绍怎么使用RANK 和ROW_NUMBER 函数实现某些特定的业务查询

二

RANK

语法

RANK() OVER (PARTITION BY ORDER BY ASC/DESC)

1、此函数根据分组和排序子句计算数据集的排名。

2、当我们必须从源集中的多人记录或前N个或后N个记录中选择最新记录时，这将非常有用

三

ROW_NUMBER

语法：ROW_NUMBER() OVER(PARTITION BY COLUMN ORDER BY COLUMN ASC/DESC )

row_number() OVER() 从1开始，为每一条分组记录返回一个数字，这里的ROW_NUMBER() OVER (ORDER BY c1 DESC) 是先把c1列降序，再为降序以后的没条c1记录返回一个序号。

row_number() OVER (PARTITION BY COL1 ORDER BY COL2) 表示根据COL1分组，在分组内部根据 COL2排序，而此函数计算的值就表示每组内部排序后的顺序编号（组内连续的唯一的)

四

比较

RANK VS ROW_NUMBER

通过如下数据及SQL语句中rank 和 row_number的结果呈现, 可以看出

二者都是给分组的数据按指定顺序给出排名, 只是RANK 在分组字段和排序字段内容一致时,给出相同的排名. 而 ROW_NUMBER 则会给出不同的排名 ,需要根据具体的应用场景判断使用哪一个函数

五

应用实例一

获取有效的采购信息记录

系统的采购信息记录分别维护在三个层级,如下图所示,

• 商品+供应商  表EINA

• 商品+供应商+采购组织  表EINE 地点为空

• 商品+供应商+采购组织+地点  表EINE 地点非空

当系统创建采购订单读取采购信息记录时, 需要判断商品+供应商+采购组织+地点是否存在采购信息记录,此时系统标准逻辑会判断采购信息记录的后两个层级

• 如果有商品+供应商+采购组织+地点的记录,则采用该记录

• 如果没有,则采用商品+供应商+采购组织+地点为空的记录

如果我们希望通过一个查询获取商品+供应商+采购组织+地点是否存在采购信息记录,并且标记该采购信息记录的来源,则可以使用RANK(因为这里不会出现重复的数据,使用RANK和ROW_NUMBER没有区别)

SQL过程

先把地点为空的记录扩展到所有地点(扩展时需要排除那些无用的工厂),零售行业可以按T001W-EKORG = EINE-EKORG 扩展到采购组织相关的地点(工厂). 非零售行业没有强调采购组织和工厂的关系,需要全部扩展,给出一个数据源 ZSOURCE = ‘1’

把地点为空和非空的组合到一起

对组合后的表,使用rank 按 MATNR,LIFNR,EKORG,WERKS 分组, 按ZSOURCE 倒排序(基于之前给每个源设置的值,确定使用正排序,还是倒排序),给出排名

只获取排名为1的记录.这个结果集就是可以固化下来的采购信息记录查询视图.

我们可以通过限制条件,获取商品+供应商在每个采购组织,地点的采购信息记录信息及来源

如下图所示,

1598工厂存在EINE记录,来源为2.

5981工厂不存在EINE记录,采用了WERKS=’’的记录.来源为1

六

应用实例二

价格解析.

比如采购价PB00 ,有很多层级, 使用最多的就是下图的两个层级

• 带有地点的采购信息记录 A017

• 不带地点的采购信息记录 A018

定价逻辑会根据指定供应商,物料,采购组织,工厂,日期先查询A017,如果没有获取到价格,再指定供应商,物料,采购组织,日期查询A018

通过视图把采购价逻辑固定的方法与固定采购信息记录的方法类似, 只是这里需要增加一个日期作为查询的输入参数.

通过CDS视图ZVQ_CGJ_DAT .联合A017,与展开到地点的A018数据,

通过CDS TABLE FUNCTION 调用HANA SQL 获取RANK后的数据(CDS视图的SQL语法不支持RANK函数,所以只能实用该方法. 示例中默认获取当前日期的采购价.没有通过视图的参数传递采购价.

该方法的详细步骤详见链接

无峰，公众号：ABAP 技巧与实战ABAP基础知识 怎么访问HANA数据库中的其它CATALOG的表

最后再通过一个CDS视图读取TABLE FUNCTION 的结果.这样对ZVQ_CGJ_S的视图访问可以获取供应商,商品,采购组织,采购信息类型,地点的解析后的定价.

TABLE FUNCTION 可以直接在ABAP程序中使用 ,但不能通过SE16N使用. 再次封装的目的是为了SE16N也能使用该视图

七

总结

RANK ROW_NUMBER 属于窗口函数

窗口函数，也叫OLAP函数（Online Anallytical Processing，联机分析处理），可以对数据库数据进行实时分析处理。

窗口函数的语法规则:

<窗口函数> over (partition by <用于分组的列名>

order by <用于排序的列名>)

使用窗口函数可以把一些SAP中存在优先关系业务逻辑呈现并固定在特定的视图中.

本文给出的采购信息记录解析及价格解析就是如此. 通过对固定逻辑的视图访问,可以快速获取采购信息记录相关信息及采购价信息.

在采购信息记录解析中,使用了嵌套的HANA语句. 方便大家理解数据获取的过程. 

在价格解析中,则使用了CDS 及 AMDP 把逻辑通过CDS视图固定. 其它相关程序或视图可以方便的调用该视图获取当前日期的采购价.

最新的ABAP SQL语法中也支持RANK ROW_NUMBER 函数. 详见语法帮助及示例程序 DEMO_SELECT_OVER

THE

END

约定

如果你对这篇文章感兴趣,请帮忙点赞,在看,分享.       

    (如果你真的喜欢这篇文章,请记得回来打个赏,作为支持我继续下去的动力,这是一个正反馈过程. 越多的人打赏,作者越有动力分享,读者就能享受更多的福利.毕竟打赏的金额富不了我,穷不了你,却能支持这个公众号长久发文.)

请微信联系管理员: 

syjf1976 

sharry_xlp  

Yannick_Duan 

申请进入公众号讨论群提问或者参与话题讨论