您的位置：首页 > 报表 > 利用oracle函数化多数据集为单数据集提升报表速度

利用oracle函数化多数据集为单数据集提升报表速度

问题提出
报表名称：商品采购分析表
报表功能：统计指定日期段内商品采购信息，按照一定规则统计采购时间，采购量，采购次数等信息。

数据结构描述：
SP_ITEM—采购单明细表
STK_C—商品代码
STK_NAME-商品名称
PK_NO-单据号(主键)
NET_PRICE-进价
UOM_QTY-数量

SP_MAS—采购单表
MAS_PK_NO -单据号(外键)
MAS_DATE-单据日期
MAS_CODE-单据类别(MAS-CODE为GR表示是采购单)
ACC_CODE—供应商代码
ACC_NAME—供应商名称

报表需要从数据库商品采购表中查询数据，并以下面的样式进行展现：
商品采购分析表

商品代码

商品名称

采购总量

采购次数

最后采购时间

最后采购量

倒数第二次采购时间

倒数第二次采购量

采购次数（<50）

采购次数（50-100）

采购次数（100件以上）

报表实现
下面将对比单数据集及多数据集两种实现方式，对比两种实现方式各自的优缺点。

多数据集实现方式:
报表参数：
起始日期：startDate 日期型
结束日期：endDate 日期型
数据集：
Ds1: SELECT b.stk_c,b.stk_name,sum(b.uom_qty) as ZS, count(b.stk_c) as CS from sp_mas a, sp_item b where a.pk_no=b.mas_pk_no and a.mas_code=’GR’and a.mas_date>? and a.mas_date<? group by b.stk_c,b.stk_name, 参数为起始日期startDate和endDate
–该数据集获取指定日期段内商品代码，商品名称，采购总量及采购次数。
Ds2:select b.stk_c,b.uom_qty,a.mas_date from sp_mas a, sp_item b where a.pk_no=b.mas_pk_no and a.mas_code=’GR’ and a.mas_date>? and a.mas_date<? order by b.stk_c,a.mas_date desc
参数为起始日期startDate和endDate
–该数据集获取指定日期段商品编码，商品采购量，采购日期，按照商品代码升序，采购日期升序排列。
Ds3：select b.stk_c,count(b.stk_c) as CS1 from sp_mas a, sp_item b where a.pk_no=b.mas_pk_no and a.mas_code=’GR’ and b.uom_qty<50 and a.mas_date>? and a.mas_date<? group by b.stk_c
参数为起始日期startDate和endDate
–该数据集统计指定日期段内商品采购量小于50的商品采购次数。
Ds4：select b.stk_c,count(b.stk_c) as CS2 from sp_mas a, sp_item b where a.pk_no=b.mas_pk_no and a.mas_code=’GR’ and b.uom_qty>50 and b.uom_qty<=100 and a.mas_date>? and a.mas_date<? group by b.stk_c
参数为起始日期startDate和endDate
–该数据集统计指定日期段内商品采购量50-100的商品采购次数。
Ds5: select b.stk_c,count(b.stk_c) as CS3 from sp_mas a, sp_item b where a.pk_no=b.mas_pk_no and a.mas_code=’GR’ and b.uom_qty>100 and a.mas_date>? and a.mas_date<? group by b.stk_c
参数为起始日期startDate和endDate
–该数据集统计指定日期段内商品采购量大于100的商品采购次数。
报表模板设计：

单元格表达式：
A3：=ds1.Select(STK_C)
B3：=ds1.STK_NAME
C3：=ds1.ZS
D3：=ds1.CS
E3：= ds2.top(MAS_DATE,1,true,STK_C==A3,MAS_DATE)
取得ds2中满足条件的第一条记录的MAS_DATE字段值，即取得商品代码为A3的商品的采购日期的最大值。
F3：=ds2.UOM_QTY
G3：= ds2.top(MAS_DATE,1,true,MAS_DATE<date(E3)&&STK_C==A3,MAS_DATE)
取得ds2中满足条件的第一条记录的MAS_DATE字段值，即取得商品代码为A3并且采购日期小于E3的商品的采购日期的最大值。
H3：=ds2.UOM_QTY
I3：=nvl(ds3.Select(CS1,,STK_C==A3),0)
J3：=nvl(ds4.Select(CS2,,STK_C==A3),0)
K3：=nvl(ds5.Select(CS3,,STK_C==A3),0)

单数据集实现方式：
报表参数：
起始日期：startDate 日期型
结束日期：endDate 日期型
报表数据集：
Ds1：select b.*,a.DAY1,a.QTY1,a.DAY2,a.QTY2
from
(select t2.stk_c, t2.stk_name,t2.uom_qty as QTY1,t1.mas_date as Day1,row_number() over (partition by t2.stk_c order by t1.mas_date desc) rn,lead (mas_date) over (PARTITION BY t2.stk_c order by t1.mas_date desc) as Day2,lead (t2.uom_qty) over (PARTITION BY t2.stk_c order by t1.mas_date desc) as QTY2
from sp_mas t1,sp_item t2 where t1.mas_code=’GR’ and t2.mas_pk_no=t1.pk_no and t1.mas_date>? and t1.mas_date<?)a,
(select t2.stk_c,t2.stk_name, sum(t2.uom_qty)as ZS,count(t2.stk_c) as CS, sum(case when t2.uom_qty<=50 then 1 else 0 end) as CS1, sum(case when t2.uom_qty>50 and t2.uom_qty<=100 then 1 else 0 end)as CS2,sum(case when t2.uom_qty>100 then 1 else 0 end) as CS3
from sp_mas t1,sp_item t2 where t1.mas_code=’GR’ and t2.mas_pk_no=t1.pk_no and t1.mas_date>? and t1.mas_date<? group by t2.stk_c,t2.stk_name)b
where a.stk_c=b.stk_c and a.rn=1

sql解析，该数据集sql用了两个子查询结果a和b，分别来解析：
子查询结果a
select t2.stk_c, t2.stk_name,t2.uom_qty as QTY1,t1.mas_date as Day1,
row_number() over (partition by t2.stk_c order by t1.mas_date desc) rn,
lead (mas_date) over (PARTITION BY t2.stk_c order by t1.mas_date desc) as Day2,
lead (t2.uom_qty) over (PARTITION BY t2.stk_c order by t1.mas_date desc) as QTY2
from sp_mas t1,sp_item t2
where t1.mas_code=’GR’ and t2.mas_pk_no=t1.pk_no and t1.mas_date>? and t1.mas_date<?
其中row_number()(partition by t2.stk_c order by t1.mas_date desc) 得出按照商品编码t2.stk_c分组组内按照采购日期(t1.mas_date)排序的序号，实现了组间排序。
lead (t2.uom_qty) over (PARTITION BY t2.stk_c order by t1.mas_date desc)得到按照商品编码t2.stk_c分组组内按照采购日期(t1.mas_date)排序后当前记录的下一条记录的采购量(t2.uom_qty)字段值。关于over（）分析函数详见附录。
子查询a查询得出的结果如下：

可以看出查询结果对商品编码进行了分组，并对采购日期进行了排序，rn为组内排序号,另外求出了组内排序后下一条记录的采购日期和采购量。从红色箭头可以看出，实际上DAY2值就是下一条记录的DAY1值。

子查询结果b
select t2.stk_c,t2.stk_name, sum(t2.uom_qty)as ZS,count(t2.stk_c) as CS,
sum(case when t2.uom_qty<=50 then 1 else 0 end) as CS1,
sum(case when t2.uom_qty>50 and t2.uom_qty<=100 then 1 else 0 end)as CS2,
sum(case when t2.uom_qty>100 then 1 else 0 end) as CS3
from sp_mas t1,sp_item t2 where t1.mas_code=’GR’ and t2.mas_pk_no=t1.pk_no and t1.mas_date>? and t1.mas_date<?
group by t2.stk_c,t2.stk_name
按照商品编码，商品名称分组统计了采购次数，采购量，50件以下采购量，50-100件采购量，100件以上采购量。用到了SELECT CASE WHEN判断采购量取值范围，分别统计出三个指标，避免了多次的查询遍历，提升效率。关于SELECT CASE WHEN详见附录。
查询后得出结果如下：

最后对子查询结果进行内连接，报表需要查询到的是商品的最后一次及倒数第二次的采购日期及采购量，所以只需要找到子查询a中分组排序序号为1的记录故检索条件为a.stk_c=b.stk_c and a.rn=1。最终得到如下结果，该结果和最终报表展现的结果一致。

报表模板设计：

单元格表达式，因为数据加工已经在数据集sql中完成，使得报填报表单元格表达式简单。
A3：=ds1.Select(STK_C)
B3：=ds1.STK_NAME
C3：=ds1.ZS
D3：=ds1.CS
E3：=ds1.DAY1
F3：=ds1.QTY1
G3：=ds1.DAY2
H3：=ds1.QTY2
I3：=ds1.CS1
J3：=ds1.CS2
K3：=ds1.CS3

报表展现效果：
两种方式得到相同的报表展现效果

统计两张报表的运算时间如下：
多数据集方式报表展现时间：2分11秒
单数据集方式报表展现时间:13秒

小结：

多数据集方式：
优点：多个数据集逻辑独立易于编写。
缺点：报表模板逻辑复杂，多个数据集间需要通过单元格表达式建立关联。需要对数据集进行遍历检索，统计符合条件的记录。
单数据集方式：
优点：报表单元格表达式简单，取数扩展值对一个数据集进行，不需要对其他数据集遍历扩展，运算效率高。
缺点：数据集的sql比较复杂，需要借助oracle的分析函数及select case when语句来完成。

通过该报表的展现效率可以得出单数据集方式的报表运算速度要明显高于多数据集方式。本例灵活运用了oracle的内置函数，化多数据集为单数据集，简化报表模板设计，提升了报表的运算效率。

附录：
函数介绍：
1.Over分析函数：
分析函数的一般格式是函数名(参数列表) over ([partition by 字段名或表达式] [order by 字段名或表达式])，分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是对于每个组返回多行，而聚合函数对于每个组只返回一行。
举例：
利用oracle内置函数优化sql：
函数介绍：
Over函数：
分析函数的一般格式是函数名(参数列表) over ([partition by 字段名或表达式] [order by 字段名或表达式])，分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是对于每个组返回多行，而聚合函数对于每个组只返回一行。
常用的分析函数如下所列:
row_number() over(partition by … order by …)
rank() over(partition by … order by …)
dense_rank() over(partition by … order by …)
count() over(partition by … order by …)
max() over(partition by … order by …)
min() over(partition by … order by …)
sum() over(partition by … order by …)
avg() over(partition by … order by …)
first_value() over(partition by … order by …)
last_value() over(partition by … order by …)
lag() over(partition by … order by …)
lead() over(partition by … order by …)
举例：
统计各班成绩第一名的同学信息
NAME   CLASS GRADE
    —– —– ———————-
    aaa    1      80
    bbb    1      78
    ccc    1      95
    ddd    2      74
    eee    2      92
    fff    3      99
    ggg    3      99
    hhh    3      45
    iii    3      55
    jjj    3      78
通过：
    –
    select * from
    (
    select name,class,s,rank()over(partition by class order by grade desc) rn from t2
    )
    where mm=1
    –
    得到结果：
    NAME   CLASS GRADE                       MM
    —– —– ———————- ———————-
    ccc    1      95                      1
    eee    2      92                      1
    fff    3      99                      1
    ggg    3      99                      1

2.SELECT CASE WHEN
sql语句中的case语句与高级语言中的switch语句，是标准sql的语法，适用于一个条件判断有多种值的情况下分别执行不同的操作。select 与 case结合使用最大的好处有两点，一是在显示查询结果时可以灵活的组织格式，二是有效避免了多次对同一个表或几个表的访问。
举例：
例如表 students(id, name ,birthday, sex, grade)，要求按每个年级统计男生和女生的数量各是多少，统计结果的表头为，年级，男生数量，女生数量。如果不用select case when，为了将男女数量并列显示，统计起来非常麻烦，先确定年级信息，再根据年级取男生数和女生数，而且很容易出错。用select case when写法如下：
SELECT   grade, COUNT (CASE WHEN sex = 1 THEN 1
                                            ELSE NULL
                                            END) 男生数,
                            COUNT (CASE WHEN sex = 2 THEN 1
                                            ELSE NULL
                                            END) 女生数
    FROM students GROUP BY grade;

本文标签：数据平台数据集

文章信息

类别：报表
时间：2012年08月14日

申请试用

利用oracle函数化多数据集为单数据集提升报表速度

文章信息

更多文章

知识库

推荐内容