精华内容
下载资源
问答
  • SuperMap iClient 6R for JavaScript 本实例展示了如何通过关联外挂表标签矩阵专题图。
  • 如何优化多数据集关联报表

    千次阅读 2018-11-12 11:42:52
    多数据集关联报表是很常见的报表形式,它允许开发者分别从不同的来源(表或数据库)分别准备数据形成不同的数据集,在报表端(模板)通过表达式描述数据集间的关系完成关联。这样可以避免在数据准备时写过于复杂的 ...

    多数据集关联报表是很常见的报表形式,它允许开发者分别从不同的来源(表或数据库)分别准备数据形成不同的数据集,在报表端(模板)通过表达式描述数据集间的关系完成关联。这样可以避免在数据准备时写过于复杂的 SQL/ 存储过程,降低维护难度。尤其当报表数据来源于多个数据库时,多数据集的优势更加明显。

    凡事都有两面性,多数据集为开发带来方便的同时却对性能造成了极大的影响。在报表端进行多数据集关联时要计算关联表达式(举例:ds2.select(name,,id==A1))时,报表引擎一般会采用顺序遍历的方式进行,先拿一个数据集的第一条记录去第二个数据集中遍历查找符合条件的记录,然后是第二条,第三条…。因此两个数据集关联的时间复杂度是 O(n²),数据量不大时感受还不明显,数据量稍大一些就会很慢,随着数据集数量的增多报表性能也会呈指数下降。

    因此在实际报表业务中,当多数据集关联导致报表性能降低时可以考虑将多个数据集 SQL 合并成一句,利用数据库的关联计算能力提升性能。但这种方式又会导致 SQL 过于复杂,很难维护,而太复杂的 SQL 很可能被数据库搞错优化路径,结果性能仍不可控。并且合并 SQL 的方式有适用场景的限制(如无法完成跨异构库关联、文本关联等)。

    下面介绍采用集算器的优化方法,写法简单且性能高,能够普遍适用于各种场景:

    1. 单数据库,多个数据集 SQL 比较复杂,很难写成一句
    2. 单数据库,多数据集中使用了存储过程,无法整合成一句 SQL
    3. 单数据库,多数据集合并成一句 SQL 后性能仍不如人意
    4. 多数据库,多数据集来源多个数据库,无法通过一句 SQL 进行查询
    5. 涉及文件数据,多数据集中部分数据来自文件,无法使用 SQL 进行统一查询

    不同于 SQL(关系代数)采用笛卡尔积再过滤的方式看待 JOIN,基于离散数据集模型的集算器将关联运算做了区分(只考虑等值 JOIN):多对一的主外键表采用外键属性化方式关联、一对一的同维表采用同维表等同化方式关联、一对多的主子表采用主子表一体化关联,针对不同的表间关系采用不同算法进行运算,可以获得更简单的写法和更高的性能以及更广泛的适用范围。

    我们将通过一些示例来说明面向各种情况时,如何使用集算器获得最优的实现和效率。需要说明的是,为了描述方便我们使用抽象后最简单的情况说明各种关联运算,实际业务会复杂得多,每个数据集 SQL 也会复杂得多,但是不管怎样多数据集关联关系也逃不出多对一、一对一和一对多的情况,所以拿原子操作来说明问题,以期大家遇到问题时可以采用最合适的方式处理。

    报表集成

    这里假定读者已经了解集算器与报表的关系,集算器仅为报表提供数据准备,将原来的多数据集通过集算器完成关联计算,将计算以结果以单 / 多数据集的方式提供给报表进行呈现。

    集算器脚本可以直接被润乾报表 5.0 及以上版本直接引用(集算器数据集);如果是其他报表工具,集算器提供了标准 JDBC 和 ODBC 接口,可以采用类似调用存储过程的方式调用集算器脚本,详细可以参考教程《应用集成 - 被 JAVA 调用》章节,以及《集算器与 BIRT 集成》或《集算器与 JasperReport 集成》。

    因此下面大部分例子将省略报表制作部分,主要说明集算器处理多数据集关联计算的过程。

    外键表(多对一)

    表 A 的某些字段与表 B 的主键关联。A 表称为事实表,B 表称为维表。A 表中与 B 表主键关联的字段称为 A 指向 B 的外键,B 也称为 A 的外键表。外键表是多对一的关系,且只有 JOIN 和 LEFT JOIN,一般不会用到 FULL JOIN。如:订单表和客户表

    Orders 表和 Customer 表的主键都是其中的 id 字段,Orders 表的 customerID 字段是指向 Customer 表的外键。

    这里说的主键是指逻辑上的主键(下同),也就是在表中取值唯一的字段(组),一个表上可能有多个字段(组)都取值唯一(并不常见),可以认为都是主键。不是一定是在物理表上建立的那个主键。

    单外键举例

    报表中有两个数据集,数据分别来自订单信息表(Orders)和客户表(Customer)(实际业务中可能是两条复杂 SQL),订单表的客户 ID 指向客户表的主键客户 ID,属于典型的主外键关系。

    【计算目标】 查询某时间段内订单和客户详单

    集算器数据准备

    单库情况

    当两个数据集来源于单个数据库,数据集 SQL 比较复杂不易合并时,通过集算器实现多对一关联计算,脚本如下:

     AB
    1=connect(“db”)/ 建立数据库连接
    2=A1.query(“select * from 订单 where 订购日期 >=? and 订购日期 <=?”,begin,end)/ 查询订单数据
    3=A1.query@x(“select * from 客户”)/ 查询客户数据
    4>A2.switch(客户 ID,A3: 客户 ID)/ 关联,在 A2 订单表客户 ID 字段上建立指向客户表的指针
    5=A2.new(客户 ID. 公司名称: 客户名称, 订单 ID, 订购日期, 运货费, 订单金额)/ 通过外键属性化的方式,将外键表字段作为客户 ID 属性使用

    脚本解析:

    1、前 3 行连接数据库后分别取订单和客户数据作为两个独立数据集(事实上 A2 和 A3 的 SQL 可以任意复杂,取数阶段无需将两条 SQL 合并,分别查询即可);这里为了说明指针与记录,将两个表所有字段都选出,实际业务中应该用哪些字段取哪些。

    2、A2 中使用了脚本参数 begin 和 end 来接收起止时间范围

    3、注意 A3 的 query 函数使用了 @x 选项,代表查询后关闭连接,使用完数据库连接一定要及时关闭(也可以通过 Aclose() 显示关闭数据库连接)

    4、A4 中通过 switch 函数在 A2 订单表的客户 ID 字段上建立指向客户表记录的指针实现关联

    5、A5 利用建立关联关系通过“外键字段. 维表字段”的方式进行引用,如“客户 ID: 客户名称”,将维表记录看做外键的的属性,这便是外键属性化的由来;

    6、A5 为报表返回关联后结果集

    关于 switch 函数

    在 SQL 的概念体系中并不区分外键表和主子表,多对一和一对多从 SQL 的观点看来只是关联方向不同,本质上是一回事。比如,订单也可以理解成订单明细的外键表。但是,集算器把它们区分开,在简化语法和性能优化时使用不同的手段。

    switch 是集算器中实现多对一关联的函数,通过建立事实表和维表之间的外键指针实现连接。其原理是通过 HASH 算法在外键字段上建立指向维表记录的指针,这样在建立关联的时间与数据库中最快的关联方式 HASH JOIN 一样,但接下来使用连接结果时就不需要再查找 HASH TABLE,直接通过指针定位到内存中的维表记录。

    建立外键指针后外键字段的原值不再存储,而被转化为指向维表记录的指针,所有维表字段都可以通过“外键字段. 维表字段”方式引用,因此 switch 函数只适合做单外键的关联(原外键字段值变了),多外键关联时需要使用 A.join 函数(后面会说明多外键情况)。

    指针式连接的意义在于一次建立多次使用,重复使用时由于无需再建立连接性能高效得多。如上述例子中,除了获取订单和客户详单,还想针对客户所在区域汇总订单数量,那么可以写成这样(B5 格):

     AB
    1=connect(“db”) 
    2=A1.query(“select * from 订单 where 订购日期 >=begin and 订购日期 <=end”) 
    3=A1.query@x(“select * from 客户”) 
    4>A2.switch(客户 ID,A3: 客户 ID) 
    5=A2.new(客户 ID. 公司名称: 客户名称, 订单 ID, 订购日期, 运货费, 订单金额)=A2.groups(客户 ID. 所在区域;count(订单 ID):num)

    B5 的计算继续使用了在 A2 客户 ID 字段上建立的指针,而无需重新建立关联。实际应用中,指针式关联建立后,重复使用次数越多性能优势越明显。

    在报表中复用连接,计算不同的结果集多用于分片报表,分片报表在报表业务中并非很常见,但也不算罕见,不过对应业务都比较复杂,不大合适举例,这里就不细说了。当遇到报表分片且有相同关联情况时可以考虑使用集算器进行连接复用。

    多库情况

    前面提到多库尤其是异构多库情况下无法利用 SQL 做关联计算,在报表中计算性能又低,这时非常适合使用集算器来做。下面假设订单和客户表分别来源两个不同数据库 db1 和 db2,计算目标仍然是:查询某时间段内订单和客户详单,来看集算器的具体写法。

     AB
    1=connect(“db1”)=connect(“db2”)
    2=A1.query@x(“select * from 订单 where 订购日期 >=? and 订购日期 <=?”,begin,end) 
    3=B1.query@x(“select * from 客户”) 
    4>A2.switch(客户 ID,A3: 客户 ID) 
    5=A2.new(客户 ID. 公司名称: 客户名称, 订单 ID, 订购日期, 运货费, 订单金额) 

    注意到和单库情况的区别了吗?

    多库情况只需要在脚本中建立多库的连接(A1 和 B1)分别执行 SQL 查询(A2 和 A3),剩下的运算和单库完全一致,轻松实现基于多库的关联计算。

    事实上,集算器(脚本)还非常利于应用移植和数据库扩展,当底层数据库发生变化或者由单库拆分成多库时,只需更改数据库连接,主要的计算逻辑完全不用改。更进一步,如果连接信息也维护在配置中,则可以写出更加通用的脚本做到系统扩展时脚本无缝移植。

    涉及文本

    集算器作为开放计算引擎提供了多数据源支持,除了关系数据库外,本地文件(Excel、TXT、CSV、JSON/XML)、NoSQL、Hadoop 等也可以直接作为数据源参与运算。因此如果报表中有数据来源于文本、Excel 等文件,可以通过集算器直接处理(SQL 就无能为力了)。

    沿用上面的例子,假设客户信息来源于 TXT,计算目标仍然是:查询某时间段内订单和客户详单。来看集算器的写法。

     AB
    1=connect(“db1”) 
    2=A1.query@x(“select * from 订单 where 订购日期 >=? and 订购日期 <=?”,begin,end) 
    3=file(“/usr/ 客户.txt”).import@t()/ 读入文件数据
    4>A2.switch(客户 ID,A3: 客户 ID) 
    5=A2.new(客户 ID. 公司名称, 订单 ID, 订购日期, 运货费, 订单金额) 

    涉及到文本有什么变化吗?只将 A3 改为读取文件数据即可,核心计算逻辑仍然没有变化。

    上面我们通过多对一的两个表对单库、多库和文件三种情况进行说明,报表遇到相应问题可以使用集算器处理。实际业务中还可能涉及多层外键情况,即多表外键关联。

    多层外键关联举例

    报表中有三个数据集,数据分别来自订单信息表(Orders)、客户表(Customer)和地区表(Area),订单表的客户 ID 指向客户表的主键客户 ID,客户表的所在区域指向区域表的主键区域 ID。

    【计算目标】 查询某时间段内订单及其客户与所在区域详细信息

    集算器数据准备

     AB
    1=connect(“db”) 
    2=A1.query(“select * from 订单 where 订购日期 >=? and 订购日期 <=?”,begin,end) 
    3=A1.query(“select 客户 ID, 公司名称 from 客户”)=A1.query@x(“select 区域 ID, 区域名称 from 地区”)
    4>A3.switch(所在区域,B3: 区域 ID)>A2.switch(客户 ID,A3: 客户 ID)
    5=A2.new(客户 ID. 所在区域. 区域名称: 区域, 客户 ID. 公司名称: 客户, 订单 ID, 订购日期, 运货费, 订单金额) 

    脚本解析:

    1、A2-B4 分别查询订单、客户和地区数据

    2、A4 中通过 switch 函数在 A3 所在区域上建立指向地区记录的指针实现关联

    3、同理,B4 在 A2 订单表的客户 ID 字段上建立指向客户表记录的指针实现关联,这里得到了一个三层结果的集合

    4、A5 通过外键属性化的方式引用区域和客户信息,可以看到无论有多少层外键都可以通 过 [点](.)的方式作为外键属性引用

    在实际业务中很常见的星型结构还会涉及到同一个事实表和多个维表进行关联,不同于传统的 HASH 分段 JOIN 方案,集算器无需两两消除、多次遍历,通过遍历一次事实表即可完成与多个维表的关联,非常高效,适合多数据库表关联性能低下需要改善的场景。

    下面以一个事实表与两个维表关联说明多维表情况下集算器处理方式。

    关联多个维表举例

    订单信息表(Orders)与客户表(Customer)、雇员表(Employee),订单表的客户 ID 指向客户表的主键客户 ID;销售 ID 指向雇员表的员工 ID

    【计算目标】 按客户所在区域和销售人员汇总订单金额

    集算器数据准备

    集算器实现脚本:

     AB
    1=connect(“db”) 
    2=A1.query(“select * from 订单”) 
    3=A1.query(“select * from 客户”) 
    4=A1.query@x(“select * from 雇员”) 
    5>A2.switch(客户 ID,A3: 客户 ID; 雇员 ID,A4: 雇员 ID)/ 同时关联两个维表
    6=A2.groups(客户 ID. 地区: 地区, 雇员 ID. 姓名: 姓名;sum( 订单金额):amount)/ 外键属性化方式访问维表字段,汇总指标

    在 A5 中通过 switch 将订单信息同时与客户表和雇员表关联,客户 ID 和雇员 ID 分别指向对应维表的记录

    这里可以看到,通过遍历一次订单表就关联了客户和雇员,当外键关联较多时使用 switch 更加简单高效。相反,在写 SQL 关联多个表时,偶尔会出现漏写 join 条件导致数据库被跑死的情况,而集算器则完全避免了这种情况。

    多外键情况举例

    单外键下无论是数据来源数据库或是文件均可使用 switch 进行处理,实际业务中还可能存在多外键的情况。报表中有两个数据集分别来自学生表(Students)和班级表(Classes),学生表的专业号和班级号为外键字段,分别指向班级表的联合主键(专业号,班级号)。

    【计算目标】 查询所有学生的学号,姓名,专业,班级,班主任

    集算器数据准备

     AB
    1=connect(“db”) 
    2=A1.query(“select * from 学生”) 
    3=A1.query@x(“select * from 班级”).keys(专业号, 班级号) 
    4=A2.join(专业号: 班级号,A3, 班主任)/ 双主键关联

    脚本解析:

    1、A3 查询班级数据,并通过 keys 设置班级的主键为专业号和班级号;

    2、A4 使用 A.join() 函数进行双主键关联,将班主任信息添加到学生信息中,形成目标结果集

    与 switch 处理单外键关联不同,当出现多外键的情况下需要使用 A.join 完成关联。

    同维表(一对一)

    表 A 的主键与表 B 的主键关联,A 和 B 互称为同维表。同维表是一对一的关系,JOIN、LEFT JOIN 和 FULL JOIN 的情况都会有,如:员工表和经理表。

    两个表的主键都是 id,经理也是员工,两表共用同样的员工编号,经理会比普通员多一些属性,另用一个经理表来保存。

    单主键举例

    报表中有三个数据集,分别来自回款表(OrderPayment)、客户表(Customer)和订单表(Orders),回款表的客户 ID 指向客户表主键客户 ID,订单表的客户 ID 指向客户表主键客户 ID。

    【计算目标】 按客户(所有)查看某时间段订单总额和回款总额

    这是很常见的一类报表,按照某个维度(如地区、日期、人员)汇总多个指标(如订单额、回款额),但我们发现报表的三个数据集之间并不是像销售表和员工表(主键都是人员 ID)那样互为同维表,不过结合计算目标分析一下,由于一个客户会有多笔订单和回款记录,因此需要对两个表分别按照客户 ID 分组后(结果集以客户 ID 为主键)向客户表主键客户 ID 对齐,显然三个集合是一组以客户 ID 为主键的同维表。

    集算器数据准备

     AB
    1=connect(“db”) 
    2=A1.query(“select 客户 ID,sum( 订单金额) 订单金额 from 订单 where 订购日期 >=? and 订购日期 <? group by 客户 ID”) 
    3=A1.query(“select 客户 ID,sum( 回款金额) 回款金额 from 回款 where 回款日期 >=begin and 回款日期 <=end group by 客户 ID “) 
    4=A1.query@x(“select 客户 ID, 公司名称 from 客户”) 
    5=join@1(A4: 客户, 客户 ID;A2: 订单, 客户 ID;A3: 回款, 客户 ID) 
    6=A5.new(客户. 公司名称: 客户名称, 订单. 订单金额: 订单金额, 回款. 回款金额: 回款金额) 

    脚本解析:

    1、A2 和 A3 针对订单和回款数据分别按照客户 ID 进行分组汇总;

    2、A5 按照客户表左关联(@1 选项代表左连接)订单和回款数据

    3、A6 获得关联结果返回报表数据集

    这里关注一下 join 函数(上述例子 A5=join@1(A4: 客户, 客户 ID; A2: 订单, 客户 ID;A3: 回款, 客户 ID)),可以看到 join 的各个表之间看起来似乎是无关的,在集算器中关联时无需关注表间关系,只需要同时向某一个维度(如客户维度)对齐即可,这样在关联表增多或减少时修改非常方便。如果是 SQL 的写法必须指定两个表的关联条件,关联的表数量太多时就容易漏写一两个条件导致出现叉乘算错的情况,如果漏写条件的表比较大,还容易把数据库跑死;集算器的 join 则避免了这种情况。

    另外,从上述例子来看当涉及多个事实表同时向维表对齐汇总时,一定要先 group 再 join,如果先 join 再 group 就会算错,写成 SQL 应该是维表和有两个 group by 的子查询 join。

    多主键情况举例

    与多外键情况类似,当同维表采用联合主键时就会存在多主键同维表关联的情况。报表中有两个数据集,分别来自回款表(OrderPayment)和订单表(Orders),两个表没有关联关系。

    【计算目标】 按客户和年份汇总回款金额和订单金额

    这两个表直接并没有关联关系,但经过同样两个维度分组汇总后,就形成了两个以客户和日期为主键的同维表

    按照计算目标,要同时获得回款金额和订单金额,需要将两个表进行关联计算。

    集算器数据准备

     AB
    1=connect(“db”) 
    2=A1.query(“select 客户 ID,year( 订购日期) 年份,sum(订单金额) 订单金额 from 订单 where 订购日期 >=? and 订购日期 <=? group by 客户 ID, 年份”,begin,end) 
    3=A1.query(“select 客户 ID,year( 回款日期) 年份,sum(回款金额) 回款金额 from 回款 where 回款日期 >=? and 回款日期 <=? group by 客户 ID, 年份 “,begin,end) 
    4=join@f(B2: 订单, 客户 ID, 年份;B3: 回款, 客户 ID, 年份) 
    5=A4.new(订单. 客户 ID: 客户, 订单. 年份: 年份, 订单. 订单金额: 订单金额, 回款. 回款金额: 回款金额) 

    脚本解析:

    1、A2 和 A3 分别查询订单和回款数据,并按客户和年份汇总订单额和回款额;

    2、A4 通过全连接对齐带有两个主键(客户 ID, 年份)的结果集

    3、A5 根据关联结果返回报表数据集

    同维表与外键表混合

    在实际业务中还经常能见到同维表和外键表混合使用的情况,集算器处理起来仍然简单高效。

    举例

    沿用上述单主键同维表的例子,现在还有一张地区表(Area),客户表外键字段所在区域指向区域表主键区域 ID。

    【计算目标】 按客户所在区域和客户查看某时间段订单总额和回款总额

    分析后仍然得到下面的同维表,只不过客户表外键字段所在区域又指向了地区表,出现了同维表和外键表混合的情况。

    集算器数据准备

     AB
    1=connect(“db”) 
    2=A1.query(“select 客户 ID,sum( 订单金额) 订单金额 from 订单 where 订购日期 >=? and 订购日期 <=? group by 客户 ID”,begin,end) 
    3=A1.query(“select 客户 ID,sum( 回款金额) 回款金额 from 回款 where 回款日期 >=?  and 回款日期 <=? group by 客户 ID “,begin,end) 
    4=A1.query(“select 客户 ID, 公司名称, 所在区域 from 客户”) 
    5=A1.query@x(“select 区域 ID, 区域名称 from 区域”) 
    6>A4.switch(所在区域,A5: 区域 ID) 
    7=join@1(A4: 客户, 客户 ID;A2: 订单, 客户 ID;A3: 回款, 客户 ID) 
    8=A7.new(客户. 所在区域. 区域名称: 区域, 客户. 公司名称: 客户名称, 订单. 订单金额: 订单金额, 回款. 回款金额: 回款金额) 

    脚本解析:

    1、A6 在 A4 客户信息中建立外键关联

    2、A7 关联后结果可以看到集算器的结果集可以是任意多层结构

    3、A8 通过外键属性化方式引用区域名称,为报表返回结果集

    主子表(一对多)

    表 A 的主键与表 B 的部分主键关联,A 称为主表,B 称为子表。主子表是一对多的关系,只有 JOIN 和 LEFT JOIN,不会有 FULL JOIN,如:订单和订单明细。

    Orders 表的主键是 id,OrderDetail 表中的主键是 (id,no),前者的主键是后者的一部分,订单表是主表,订单明细表子表。从子表去看主表,与前述提到的外键表非常类似,只是外键表不要求外键字段是主键,因此从子表角度观察表间关系可以将主子表看做外键表的特殊情况,所以有时也可以采用外键表处理关联的方法(switch)。

    主子表关联计算在报表中并不常见,即使有,多数情况下可以转换成将主表作为外键表关联,或者子表 group 后变成同维表处理。当主表作为外键表处理时,除了可以用到外键表(多对一)switch 的处理方式,还可以通过 join 实现。除了 join 可以关联多外键情况,当关联的两个结果集按照关联字段有序时还可以使用归并算法,性能比 switch 更高(数据量不大时优势并不明显)。

    子表关联主表将主表作为外键表可参考前述外键表中 < 单外键举例 >,子表 group 后变成同维表关联的例子可参考前述同维表中 < 单主键举例 >。

    下面介绍一种有序归并实施关联计算的方法,读者在对主子表(包括同维表)进行关联计算时也可选用,以获得更高性能。

    主子表有序归并举例

    报表有两个数据集,分别来自订单表(Orders)和订单明细表(OrderDetails)

    订单表主键与订单明细表部分主键关联,订单表是主表,订单明细表是子表。现在两个表都按照订单 ID 有序

    【计算目标】 查询某时间段内客户订单明细

    集算器数据准备

     AB
    1=connect(“db”) 
    2=A1.query(“select 订单 ID, 客户 ID from 订单 order by 订单 ID where 订购日期 >=? and 订购日期 <=?”,begin,end) 
    3=A1.query@x(“select 订单 ID, 订购产品, 价格, 数量 from 订单明细 order by 订单 ID “) 
    4=join@m(A2:o, 订单 ID;A3:od, 订单 ID) 
    5=A4.new(o. 客户 ID: 客户,o. 订单 ID: 订单,od. 订购产品: 产品,od. 价格: 价格,od. 数量: 数量) 

    脚本解析:

    1、A2-A3 分别查询订单和订单明细数据,结果集按订单 ID 有序

    2、A4 通过有序归并算法(@m 选项)对两个集合按照订单 ID 关联

    3、A5 获得关联结果为报表返回结果集

    有序归并可以极大提高关联效率,下面简单解释一下。

    设两个关联表的规模(记录数)分别是 N 和 M,则 HASH 分段技术的计算复杂度(关联字段的比较次数)大概是 SUM(Ni*Mi),其中 Ni 和 Mi 分别是 HASH 值为 i 的两表记录数,满足 N=SUM(Ni) 和 M=SUM(Mi),这大概率会比完全遍历时的复杂度 N*M 要小很多(运气较好的时候会小 K 倍,K 是 HASH 值的取值范围)。

    如果这两个表针对关联键都有序,那么我们就可以使用归并算法来处理关联,这时的复杂度是 N+M;在 N 和 M 都较大的时候(一般都会远大于 K),这个数会远小于 SUM(Ni*Mi),这就是有序归并的好处。

    润乾报表层次数据集

    通过这些例子,集算器为报表准备数据时最终返回的均为标准 ResutSet,这就经常需要将集算器的分层结构(如 switch 和 join 后结果集)转换成标准的二维表,虽然转换工作不复杂,但如果能直接使用分层结果集会更加简单高效。

    润乾报表 5.0 及以上版本就支持直接使用带有层次的数据集进行数据呈现。沿用订单和订单明细的主子表结构。

    【报表展现目标】

    报表每个单元上面是订单信息(单条),下面是明细信息(多条),属于典型的主子报表。

    集算器数据准备

     AB
    1=connect(“demo”) 
    2=A1.query(“select 订单 ID, 客户 ID from 订单 order by 订单 ID where 订购日期 >=? and 订购日期 <=?”,begin,end) 
    3=A1.query@x(“select 订单 ID, 订购产品, 价格, 数量 from 订单明细”)=A3.group(订单 ID)
    4=join@1(A2:o, 订单 ID;B3:od, 订单 ID) 
    5=A4.new(o. 客户 ID: 客户,o. 订单 ID: 订单,od) 

    脚本解析:

    1、B3 将订单明细按照订单 ID 分组,得到分组子集(保留分组成员)

    2、A4 订单关联订单明细,一条订单信息对应多条明细

    3、A5 生成报表可以接收的多层结果并为报表返回数据集

    润乾报表设计

    设置参数

    打开报表设计器,新建报表后设置查询参数

    设置数据集

    设置报表数据集,选择集算器数据集类型,添加上述准备好的集算器脚本文件,并设置报表参数与集算器脚本参数对应

    数据集设置后,在报表设计器右下角的数据集窗口中即输出层次结构如下:

    编写报表表达式

    直接使用集算器提供的层次结果集设置报表表达式,其中设置 B4、B5、B6、B7、B8 左主格为 C4(按照订单扩展)

    通过以上步骤即可完成基于层次数据集的报表设计,目前只有润乾报表提供了层次数据集支持,在制作主子表、分组明细报表时就可以在数据准备(数据集)阶段将数据准备好,然后为报表返回带有层次的数据集,报表直接引用无需再次关联或分组,可以带来更高的报表性能。

    子表有序计算举例

    区分主子表后,如果从主表观察子表常常会涉及分组子集和有序运算,这时用集算器处理就非常方便了。举一个并不十分常见的例子,读者可以感受一下。

    报表有三个数据集,分别来自回款表(OrderPayment)、订单表(Orders)和订单明细表(OrderDetails)。

    订单表的主键是订单 ID,回款表的主键是(编号,订单 ID),订单明细表的主键是(编号,订单 ID),订单的主键是回款和订单明细的一部分,订单表是主表,回款表和订单明细表是子表。

    【计算目标】 统计每个客户的每个订单中,最大和最小两笔回款,最高和最低两个价格

    这里并不是计算汇总值,而是要找出每个客户的每笔订单中回款金额最大和最小的两笔回款,以及每笔订单中订购产品最高和最低的两个价格,用以识别客户类型及其回款能力。

    集算器数据准备

     ABC
    1=connect(“db”)  
    2=A1.query(“select 订单 ID, 客户 ID from 订单 order by 客户 ID, 订单 ID”)  
    3=A1.query(“select 订单 ID, 回款金额 from 回款 order by 订单 ID, 回款金额 desc”)=A3.group(订单 ID)=B3.(.m(1).m(-1))
    4=A1.query@x(“select 订单 ID, 单价 as 价格 from 订单明细 order by 订单 ID, 价格 desc”)=A4.group(订单 ID)=B4.(.m(1).m(-1))
    5=join@1(A2:o, 订单 ID;C3:op, 订单 ID;C4:od, 订单 ID)  
    6=A5.new(o. 客户 ID: 客户,o. 订单 ID: 订单,op.m(1). 回款金额: 最大回款金额,op.m(-1). 回款金额: 最小回款金额,od.m(1). 价格: 最高单价,od.m(-1). 价格: 最低单价 )  

    脚本解析:

    1、A2 查询订单数据,按照客户和订单排序

    2、A3 查询回款数据,按照订单排序,回款金额降序

    3、B3 按照订单 ID 分组,由于要查找分组成员(最大和最小值),所以这里需要使用 group 函数分组并保留分组结果(不聚合)

    4、C3 找出每组中回款金额最大和最小两条记录

    5、同理 A4-C4 按照订单分组查找每组中价格最高和最低两条记录

    6、A5 根据客户和订单信息左关联上述两个结果集(注意:关联一定要在前面两个分组后进行,如果先关联则会出现多对多叉乘,导致结果错误)。

    每一条关联结果,订单只有一条记录,回款和订单明细则包含两条记录,这是主子表关联关联计算的特点,主表的一条记录指向子表的多条记录

    7、根据关联结果,生成最终结果集,并为报表返回结果集

    以上通过集算器关联运算解决了多数据集关联报表的性能问题,实测中报表性能可获得数倍到数百倍的提升(随数据规模和关联表数量线性增长)。同时集算器解决方案实现比较简单,适用范围更广,适用于数据库无法完成的跨异构库关联、文本关联等情况,从而为报表性能优化、降低报表应用耦合性提供了新思路。

    展开全文
  • 目前实现这类跨库关联报表的方式有多种,但都会存在这样那样的问题。  使用报表工具自身多源关联功能  现在大多数主流报表工具都支持多数据源关联,这在某些方面确实为报表用户带来了便利。然而我们也经常会遇到...
  • 数据挖掘怎么做关联性分析呢?

    千次阅读 2020-09-30 15:14:52
    接下来我们以购物篮这个典型的应用来为大家介绍,在购物场景下,是如何做关联分析,并帮助购物者更快速买到自己想要的东西。 全文讲解中所用到的产品是由亿信华辰提供的数据挖掘平台豌豆DM。 整个过程分为以下几个...

    大家打开某宝,会发现,购物网站越来越懂我们了,推荐的商品正好就是我们想买的。其实这里面用到了数据挖掘中的关联规则,是典型的应用。类似应用还有很多,例如:资讯类APP的推荐(今日头条);微博推荐等。

    接下来我们以购物篮这个典型的应用来为大家介绍,在购物场景下,是如何做关联分析,并帮助购物者更快速买到自己想要的东西。

    全文讲解中所用到的产品是由亿信华辰提供的数据挖掘平台豌豆DM。

    整个过程分为以下几个步骤:

    应用目标:从订单数据集中找出关联度较高的商品。

    创建数据集

    下图的数据集为某商城的订单数据集(1000条订单号,20个商品类别)。

    数据探索

    首先通过豌豆DM提供的数据探索功能,查看数据是否存在缺失值,如果缺失应通过数据预处理功能,剔除缺失的数据。通过数据探索发现,该数据集的完整性较好,不需要做数据预处理。

    构建模型

    然后创建关联规则的挖掘过程,选择FP-Growth或Apriori算法来训练模型,得到我们需要的关联规则。下图例子中,我们认为支持度大于10%,可信度大于60%的规则,是客户经常同时购买的商品。如客户经常就会将喜力啤酒、苏打、饼干一起购买。

    这便是商城当发现你购买了啤酒或苏打,会推荐你购买饼干的原因。

    得到关联规则模型结果后,我们可以发布该模型到模型库,以供后续模型应用使用。

    模型应用

    最后在模型应用界面,我们只需简单的拖拽,即可完成模型应用的制作。模型应用以表格和推荐图的形式,展现了推荐商品的规则。当关联规则较多的时候,我们也可以通过筛选输入商品的参数,快速查询该商品的推荐规则。

     

    从上图我们发现:

    当购物者购买了牛油果和洋姜时,系统会自动为他推荐喜力啤酒;购买橄榄和腌牛肉,系统会自动推荐胶鲜鱼,省去了购物者搜索的操作,提高了购物网站的销量。

    这就是豌豆DM关联分析应用的魅力所在,让不知不觉中为用户提供了很大的便利。

    展开全文
  • 这里《如何优化多数据集关联报表》尝试列举各种报表关联情况,并给出优化方案,提升多数据集关联报表性能看这一篇就够了!多数据集关联报表是很常见的报表形式,它允许开发者分别从不同的来源(表或数据库)分别准备...

    【摘要】

    关联计算在集合运算中的复杂度无出其右,在报表业务中经常引发查询性能问题,如何优化是大家经常面临的难题!这里《如何优化多数据集关联报表》尝试列举各种报表关联情况,并给出优化方案,提升多数据集关联报表性能看这一篇就够了!

    多数据集关联报表是很常见的报表形式,它允许开发者分别从不同的来源(表或数据库)分别准备数据形成不同的数据集,在报表端(模板)通过表达式描述数据集间的关系完成关联。这样可以避免在数据准备时写过于复杂的 SQL/ 存储过程,降低维护难度。尤其当报表数据来源于多个数据库时,多数据集的优势更加明显。

    凡事都有两面性,多数据集为开发带来方便的同时却对性能造成了极大的影响。在报表端进行多数据集关联时要计算关联表达式(举例:ds2.select(name,,id==A1))时,报表引擎一般会采用顺序遍历的方式进行,先拿一个数据集的第一条记录去第二个数据集中遍历查找符合条件的记录,然后是第二条,第三条…。因此两个数据集关联的时间复杂度是 O(n²),数据量不大时感受还不明显,数据量稍大一些就会很慢,随着数据集数量的增多报表性能也会呈指数下降。

    因此在实际报表业务中,当多数据集关联导致报表性能降低时可以考虑将多个数据集 SQL 合并成一句,利用数据库的关联计算能力提升性能。但这种方式又会导致 SQL 过于复杂,很难维护,而太复杂的 SQL 很可能被数据库搞错优化路径,结果性能仍不可控。并且合并 SQL 的方式有适用场景的限制(如无法完成跨异构库关联、文本关联等)。

    下面介绍采用集算器的优化方法,写法简单且性能高,能够普遍适用于各种场景:

    1. 单数据库,多个数据集 SQL 比较复杂,很难写成一句
    2. 单数据库,多数据集中使用了存储过程,无法整合成一句 SQL
    3. 单数据库,多数据集合并成一句 SQL 后性能仍不如人意
    4. 多数据库,多数据集来源多个数据库,无法通过一句 SQL 进行查询
    5. 涉及文件数据,多数据集中部分数据来自文件,无法使用 SQL 进行统一查询

    不同于 SQL(关系代数)采用笛卡尔积再过滤的方式看待 JOIN,基于离散数据集模型的集算器将关联运算做了区分(只考虑等值 JOIN):多对一的主外键表采用外键属性化方式关联、一对一的同维表采用同维表等同化方式关联、一对多的主子表采用主子表一体化关联,针对不同的表间关系采用不同算法进行运算,可以获得更简单的写法和更高的性能以及更广泛的适用范围。

    我们将通过一些示例来说明面向各种情况时,如何使用集算器获得最优的实现和效率。需要说明的是,为了描述方便我们使用抽象后最简单的情况说明各种关联运算,实际业务会复杂得多,每个数据集 SQL 也会复杂得多,但是不管怎样多数据集关联关系也逃不出多对一、一对一和一对多的情况,所以拿原子操作来说明问题,以期大家遇到问题时可以采用最合适的方式处理。

    报表集成

    这里假定读者已经了解集算器与报表的关系,集算器仅为报表提供数据准备,将原来的多数据集通过集算器完成关联计算,将计算以结果以单 / 多数据集的方式提供给报表进行呈现。

    集算器脚本可以直接被润乾报表 5.0 及以上版本直接引用(集算器数据集);如果是其他报表工具,集算器提供了标准 JDBC 和 ODBC 接口,可以采用类似调用存储过程的方式调用集算器脚本,详细可以参考教程《应用集成 - 被 JAVA 调用》章节,以及《集算器与 BIRT 集成》或《集算器与 JasperReport 集成》。

    因此下面大部分例子将省略报表制作部分,主要说明集算器处理多数据集关联计算的过程。

    外键表(多对一)

    表 A 的某些字段与表 B 的主键关联。A 表称为事实表,B 表称为维表。A 表中与 B 表主键关联的字段称为 A 指向 B 的外键,B 也称为 A 的外键表。外键表是多对一的关系,且只有 JOIN 和 LEFT JOIN,一般不会用到 FULL JOIN。如:订单表和客户表

    Orders 表和 Customer 表的主键都是其中的 id 字段,Orders 表的 customerID 字段是指向 Customer 表的外键。

    这里说的主键是指逻辑上的主键(下同),也就是在表中取值唯一的字段(组),一个表上可能有多个字段(组)都取值唯一(并不常见),可以认为都是主键。不是一定是在物理表上建立的那个主键。

    单外键举例

    报表中有两个数据集,数据分别来自订单信息表(Orders)和客户表(Customer)(实际业务中可能是两条复杂 SQL),订单表的客户 ID 指向客户表的主键客户 ID,属于典型的主外键关系。

    【计算目标】 查询某时间段内订单和客户详单

    集算器数据准备

    单库情况

    当两个数据集来源于单个数据库,数据集 SQL 比较复杂不易合并时,通过集算器实现多对一关联计算,脚本如下:

    AB
    1=connect(“db”)/ 建立数据库连接
    2=A1.query(“select * from 订单 where 订购日期 >=? and 订购日期 <=?”,begin,end)/ 查询订单数据
    3=A1.query@x(“select * from 客户”)/ 查询客户数据
    4>A2.switch(客户 ID,A3: 客户 ID)/ 关联,在 A2 订单表客户 ID 字段上建立指向客户表的指针
    5=A2.new(客户 ID. 公司名称: 客户名称, 订单 ID, 订购日期, 运货费, 订单金额)/ 通过外键属性化的方式,将外键表字段作为客户 ID 属性使用

    脚本解析:

    1、前 3 行连接数据库后分别取订单和客户数据作为两个独立数据集(事实上 A2 和 A3 的 SQL 可以任意复杂,取数阶段无需将两条 SQL 合并,分别查询即可);这里为了说明指针与记录,将两个表所有字段都选出,实际业务中应该用哪些字段取哪些。

    2、A2 中使用了脚本参数 begin 和 end 来接收起止时间范围

    3、注意 A3 的 query 函数使用了 @x 选项,代表查询后关闭连接,使用完数据库连接一定要及时关闭(也可以通过 Aclose() 显示关闭数据库连接)

    4、A4 中通过 switch 函数在 A2 订单表的客户 ID 字段上建立指向客户表记录的指针实现关联

    5、A5 利用建立关联关系通过“外键字段. 维表字段”的方式进行引用,如“客户 ID: 客户名称”,将维表记录看做外键的的属性,这便是外键属性化的由来;

    6、A5 为报表返回关联后结果集

    关于 switch 函数

    在 SQL 的概念体系中并不区分外键表和主子表,多对一和一对多从 SQL 的观点看来只是关联方向不同,本质上是一回事。比如,订单也可以理解成订单明细的外键表。但是,集算器把它们区分开,在简化语法和性能优化时使用不同的手段。

    switch 是集算器中实现多对一关联的函数,通过建立事实表和维表之间的外键指针实现连接。其原理是通过 HASH 算法在外键字段上建立指向维表记录的指针,这样在建立关联的时间与数据库中最快的关联方式 HASH JOIN 一样,但接下来使用连接结果时就不需要再查找 HASH TABLE,直接通过指针定位到内存中的维表记录。

    建立外键指针后外键字段的原值不再存储,而被转化为指向维表记录的指针,所有维表字段都可以通过“外键字段. 维表字段”方式引用,因此 switch 函数只适合做单外键的关联(原外键字段值变了),多外键关联时需要使用 A.join 函数(后面会说明多外键情况)。

    指针式连接的意义在于一次建立多次使用,重复使用时由于无需再建立连接性能高效得多。如上述例子中,除了获取订单和客户详单,还想针对客户所在区域汇总订单数量,那么可以写成这样(B5 格):

    AB
    1=connect(“db”)
    2=A1.query(“select * from 订单 where 订购日期 >=begin and 订购日期 <=end”)
    3=A1.query@x(“select * from 客户”)
    4>A2.switch(客户 ID,A3: 客户 ID)
    5=A2.new(客户 ID. 公司名称: 客户名称, 订单 ID, 订购日期, 运货费, 订单金额)=A2.groups(客户 ID. 所在区域;count(订单 ID):num)

    B5 的计算继续使用了在 A2 客户 ID 字段上建立的指针,而无需重新建立关联。实际应用中,指针式关联建立后,重复使用次数越多性能优势越明显。

    在报表中复用连接,计算不同的结果集多用于分片报表,分片报表在报表业务中并非很常见,但也不算罕见,不过对应业务都比较复杂,不大合适举例,这里就不细说了。当遇到报表分片且有相同关联情况时可以考虑使用集算器进行连接复用。

    多库情况

    前面提到多库尤其是异构多库情况下无法利用 SQL 做关联计算,在报表中计算性能又低,这时非常适合使用集算器来做。下面假设订单和客户表分别来源两个不同数据库 db1 和 db2,计算目标仍然是:查询某时间段内订单和客户详单,来看集算器的具体写法。

    AB
    1=connect(“db1”)=connect(“db2”)
    2=A1.query@x(“select * from 订单 where 订购日期 >=? and 订购日期 <=?”,begin,end)
    3=B1.query@x(“select * from 客户”)
    4>A2.switch(客户 ID,A3: 客户 ID)
    5=A2.new(客户 ID. 公司名称: 客户名称, 订单 ID, 订购日期, 运货费, 订单金额)

    注意到和单库情况的区别了吗?

    多库情况只需要在脚本中建立多库的连接(A1 和 B1)分别执行 SQL 查询(A2 和 A3),剩下的运算和单库完全一致,轻松实现基于多库的关联计算。

    事实上,集算器(脚本)还非常利于应用移植和数据库扩展,当底层数据库发生变化或者由单库拆分成多库时,只需更改数据库连接,主要的计算逻辑完全不用改。更进一步,如果连接信息也维护在配置中,则可以写出更加通用的脚本做到系统扩展时脚本无缝移植。

    涉及文本

    集算器作为开放计算引擎提供了多数据源支持,除了关系数据库外,本地文件(Excel、TXT、CSV、JSON/XML)、NoSQL、Hadoop 等也可以直接作为数据源参与运算。因此如果报表中有数据来源于文本、Excel 等文件,可以通过集算器直接处理(SQL 就无能为力了)。

    沿用上面的例子,假设客户信息来源于 TXT,计算目标仍然是:查询某时间段内订单和客户详单。来看集算器的写法。

    AB
    1=connect(“db1”)
    2=A1.query@x(“select * from 订单 where 订购日期 >=? and 订购日期 <=?”,begin,end)
    3=file(“/usr/ 客户.txt”).import@t()/ 读入文件数据
    4>A2.switch(客户 ID,A3: 客户 ID)
    5=A2.new(客户 ID. 公司名称, 订单 ID, 订购日期, 运货费, 订单金额)

    涉及到文本有什么变化吗?只将 A3 改为读取文件数据即可,核心计算逻辑仍然没有变化。

    上面我们通过多对一的两个表对单库、多库和文件三种情况进行说明,报表遇到相应问题可以使用集算器处理。实际业务中还可能涉及多层外键情况,即多表外键关联。

    多层外键关联举例

    报表中有三个数据集,数据分别来自订单信息表(Orders)、客户表(Customer)和地区表(Area),订单表的客户 ID 指向客户表的主键客户 ID,客户表的所在区域指向区域表的主键区域 ID。

    【计算目标】 查询某时间段内订单及其客户与所在区域详细信息

    集算器数据准备

    AB
    1=connect(“db”)
    2=A1.query(“select * from 订单 where 订购日期 >=? and 订购日期 <=?”,begin,end)
    3=A1.query(“select 客户 ID, 公司名称 from 客户”)=A1.query@x(“select 区域 ID, 区域名称 from 地区”)
    4>A3.switch(所在区域,B3: 区域 ID)>A2.switch(客户 ID,A3: 客户 ID)
    5=A2.new(客户 ID. 所在区域. 区域名称: 区域, 客户 ID. 公司名称: 客户, 订单 ID, 订购日期, 运货费, 订单金额)

    脚本解析:

    1、A2-B4 分别查询订单、客户和地区数据

    2、A4 中通过 switch 函数在 A3 所在区域上建立指向地区记录的指针实现关联

    3、同理,B4 在 A2 订单表的客户 ID 字段上建立指向客户表记录的指针实现关联,这里得到了一个三层结果的集合

    4、A5 通过外键属性化的方式引用区域和客户信息,可以看到无论有多少层外键都可以通 过 [点](.)的方式作为外键属性引用

    在实际业务中很常见的星型结构还会涉及到同一个事实表和多个维表进行关联,不同于传统的 HASH 分段 JOIN 方案,集算器无需两两消除、多次遍历,通过遍历一次事实表即可完成与多个维表的关联,非常高效,适合多数据库表关联性能低下需要改善的场景。

    下面以一个事实表与两个维表关联说明多维表情况下集算器处理方式。

    关联多个维表举例

    订单信息表(Orders)与客户表(Customer)、雇员表(Employee),订单表的客户 ID 指向客户表的主键客户 ID;销售 ID 指向雇员表的员工 ID

    【计算目标】 按客户所在区域和销售人员汇总订单金额

    集算器数据准备

    集算器实现脚本:

    AB
    1=connect(“db”)
    2=A1.query(“select * from 订单”)
    3=A1.query(“select * from 客户”)
    4=A1.query@x(“select * from 雇员”)
    5>A2.switch(客户 ID,A3: 客户 ID; 雇员 ID,A4: 雇员 ID)/ 同时关联两个维表
    6=A2.groups(客户 ID. 地区: 地区, 雇员 ID. 姓名: 姓名;sum( 订单金额):amount)/ 外键属性化方式访问维表字段,汇总指标

    在 A5 中通过 switch 将订单信息同时与客户表和雇员表关联,客户 ID 和雇员 ID 分别指向对应维表的记录

    这里可以看到,通过遍历一次订单表就关联了客户和雇员,当外键关联较多时使用 switch 更加简单高效。相反,在写 SQL 关联多个表时,偶尔会出现漏写 join 条件导致数据库被跑死的情况,而集算器则完全避免了这种情况。

    多外键情况举例

    单外键下无论是数据来源数据库或是文件均可使用 switch 进行处理,实际业务中还可能存在多外键的情况。报表中有两个数据集分别来自学生表(Students)和班级表(Classes),学生表的专业号和班级号为外键字段,分别指向班级表的联合主键(专业号,班级号)。

    【计算目标】 查询所有学生的学号,姓名,专业,班级,班主任

    集算器数据准备

    AB
    1=connect(“db”)
    2=A1.query(“select * from 学生”)
    3=A1.query@x(“select * from 班级”).keys(专业号, 班级号)
    4=A2.join(专业号: 班级号,A3, 班主任)/ 双主键关联

    脚本解析:

    1、A3 查询班级数据,并通过 keys 设置班级的主键为专业号和班级号;

    2、A4 使用 A.join() 函数进行双主键关联,将班主任信息添加到学生信息中,形成目标结果集

    与 switch 处理单外键关联不同,当出现多外键的情况下需要使用 A.join 完成关联。

    同维表(一对一)

    表 A 的主键与表 B 的主键关联,A 和 B 互称为同维表。同维表是一对一的关系,JOIN、LEFT JOIN 和 FULL JOIN 的情况都会有,如:员工表和经理表。

    两个表的主键都是 id,经理也是员工,两表共用同样的员工编号,经理会比普通员多一些属性,另用一个经理表来保存。

    单主键举例

    报表中有三个数据集,分别来自回款表(OrderPayment)、客户表(Customer)和订单表(Orders),回款表的客户 ID 指向客户表主键客户 ID,订单表的客户 ID 指向客户表主键客户 ID。

    【计算目标】 按客户(所有)查看某时间段订单总额和回款总额

    这是很常见的一类报表,按照某个维度(如地区、日期、人员)汇总多个指标(如订单额、回款额),但我们发现报表的三个数据集之间并不是像销售表和员工表(主键都是人员 ID)那样互为同维表,不过结合计算目标分析一下,由于一个客户会有多笔订单和回款记录,因此需要对两个表分别按照客户 ID 分组后(结果集以客户 ID 为主键)向客户表主键客户 ID 对齐,显然三个集合是一组以客户 ID 为主键的同维表。

    集算器数据准备

    AB
    1=connect(“db”)
    2=A1.query(“select 客户 ID,sum( 订单金额) 订单金额 from 订单 where 订购日期 >=? and 订购日期 <? group by 客户 ID”)
    3=A1.query(“select 客户 ID,sum( 回款金额) 回款金额 from 回款 where 回款日期 >=begin and 回款日期 <=end group by 客户 ID “)
    4=A1.query@x(“select 客户 ID, 公司名称 from 客户”)
    5=join@1(A4: 客户, 客户 ID;A2: 订单, 客户 ID;A3: 回款, 客户 ID)
    6=A5.new(客户. 公司名称: 客户名称, 订单. 订单金额: 订单金额, 回款. 回款金额: 回款金额)

    脚本解析:

    1、A2 和 A3 针对订单和回款数据分别按照客户 ID 进行分组汇总;

    2、A5 按照客户表左关联(@1 选项代表左连接)订单和回款数据

    3、A6 获得关联结果返回报表数据集

    这里关注一下 join 函数(上述例子 A5=join@1(A4: 客户, 客户 ID; A2: 订单, 客户 ID;A3: 回款, 客户 ID)),可以看到 join 的各个表之间看起来似乎是无关的,在集算器中关联时无需关注表间关系,只需要同时向某一个维度(如客户维度)对齐即可,这样在关联表增多或减少时修改非常方便。如果是 SQL 的写法必须指定两个表的关联条件,关联的表数量太多时就容易漏写一两个条件导致出现叉乘算错的情况,如果漏写条件的表比较大,还容易把数据库跑死;集算器的 join 则避免了这种情况。

    另外,从上述例子来看当涉及多个事实表同时向维表对齐汇总时,一定要先 group 再 join,如果先 join 再 group 就会算错,写成 SQL 应该是维表和有两个 group by 的子查询 join。

    多主键情况举例

    与多外键情况类似,当同维表采用联合主键时就会存在多主键同维表关联的情况。报表中有两个数据集,分别来自回款表(OrderPayment)和订单表(Orders),两个表没有关联关系。

    【计算目标】 按客户和年份汇总回款金额和订单金额

    这两个表直接并没有关联关系,但经过同样两个维度分组汇总后,就形成了两个以客户和日期为主键的同维表

    按照计算目标,要同时获得回款金额和订单金额,需要将两个表进行关联计算。

    集算器数据准备

    AB
    1=connect(“db”)
    2=A1.query(“select 客户 ID,year( 订购日期) 年份,sum(订单金额) 订单金额 from 订单 where 订购日期 >=? and 订购日期 <=? group by 客户 ID, 年份”,begin,end)
    3=A1.query(“select 客户 ID,year( 回款日期) 年份,sum(回款金额) 回款金额 from 回款 where 回款日期 >=? and 回款日期 <=? group by 客户 ID, 年份 “,begin,end)
    4=join@f(B2: 订单, 客户 ID, 年份;B3: 回款, 客户 ID, 年份)
    5=A4.new(订单. 客户 ID: 客户, 订单. 年份: 年份, 订单. 订单金额: 订单金额, 回款. 回款金额: 回款金额)

    脚本解析:

    1、A2 和 A3 分别查询订单和回款数据,并按客户和年份汇总订单额和回款额;

    2、A4 通过全连接对齐带有两个主键(客户 ID, 年份)的结果集

    3、A5 根据关联结果返回报表数据集

    同维表与外键表混合

    在实际业务中还经常能见到同维表和外键表混合使用的情况,集算器处理起来仍然简单高效。

    举例

    沿用上述单主键同维表的例子,现在还有一张地区表(Area),客户表外键字段所在区域指向区域表主键区域 ID。

    【计算目标】 按客户所在区域和客户查看某时间段订单总额和回款总额

    分析后仍然得到下面的同维表,只不过客户表外键字段所在区域又指向了地区表,出现了同维表和外键表混合的情况。

    集算器数据准备

    AB
    1=connect(“db”)
    2=A1.query(“select 客户 ID,sum( 订单金额) 订单金额 from 订单 where 订购日期 >=? and 订购日期 <=? group by 客户 ID”,begin,end)
    3=A1.query(“select 客户 ID,sum( 回款金额) 回款金额 from 回款 where 回款日期 >=? and 回款日期 <=? group by 客户 ID “,begin,end)
    4=A1.query(“select 客户 ID, 公司名称, 所在区域 from 客户”)
    5=A1.query@x(“select 区域 ID, 区域名称 from 区域”)
    6>A4.switch(所在区域,A5: 区域 ID)
    7=join@1(A4: 客户, 客户 ID;A2: 订单, 客户 ID;A3: 回款, 客户 ID)
    8=A7.new(客户. 所在区域. 区域名称: 区域, 客户. 公司名称: 客户名称, 订单. 订单金额: 订单金额, 回款. 回款金额: 回款金额)

    脚本解析:

    1、A6 在 A4 客户信息中建立外键关联

    2、A7 关联后结果可以看到集算器的结果集可以是任意多层结构

    3、A8 通过外键属性化方式引用区域名称,为报表返回结果集

    主子表(一对多)

    表 A 的主键与表 B 的部分主键关联,A 称为主表,B 称为子表。主子表是一对多的关系,只有 JOIN 和 LEFT JOIN,不会有 FULL JOIN,如:订单和订单明细。

    Orders 表的主键是 id,OrderDetail 表中的主键是 (id,no),前者的主键是后者的一部分,订单表是主表,订单明细表子表。从子表去看主表,与前述提到的外键表非常类似,只是外键表不要求外键字段是主键,因此从子表角度观察表间关系可以将主子表看做外键表的特殊情况,所以有时也可以采用外键表处理关联的方法(switch)。

    主子表关联计算在报表中并不常见,即使有,多数情况下可以转换成将主表作为外键表关联,或者子表 group 后变成同维表处理。当主表作为外键表处理时,除了可以用到外键表(多对一)switch 的处理方式,还可以通过 join 实现。除了 join 可以关联多外键情况,当关联的两个结果集按照关联字段有序时还可以使用归并算法,性能比 switch 更高(数据量不大时优势并不明显)。

    子表关联主表将主表作为外键表可参考前述外键表中 < 单外键举例 >,子表 group 后变成同维表关联的例子可参考前述同维表中 < 单主键举例 >。

    下面介绍一种有序归并实施关联计算的方法,读者在对主子表(包括同维表)进行关联计算时也可选用,以获得更高性能。

    主子表有序归并举例

    报表有两个数据集,分别来自订单表(Orders)和订单明细表(OrderDetails)

    订单表主键与订单明细表部分主键关联,订单表是主表,订单明细表是子表。现在两个表都按照订单 ID 有序

    【计算目标】 查询某时间段内客户订单明细

    集算器数据准备

    AB
    1=connect(“db”)
    2=A1.query(“select 订单 ID, 客户 ID from 订单 order by 订单 ID where 订购日期 >=? and 订购日期 <=?”,begin,end)
    3=A1.query@x(“select 订单 ID, 订购产品, 价格, 数量 from 订单明细 order by 订单 ID “)
    4=join@m(A2:o, 订单 ID;A3:od, 订单 ID)
    5=A4.new(o. 客户 ID: 客户,o. 订单 ID: 订单,od. 订购产品: 产品,od. 价格: 价格,od. 数量: 数量)

    脚本解析:

    1、A2-A3 分别查询订单和订单明细数据,结果集按订单 ID 有序

    2、A4 通过有序归并算法(@m 选项)对两个集合按照订单 ID 关联

    3、A5 获得关联结果为报表返回结果集

    有序归并可以极大提高关联效率,下面简单解释一下。

    设两个关联表的规模(记录数)分别是 N 和 M,则 HASH 分段技术的计算复杂度(关联字段的比较次数)大概是 SUM(Ni*Mi),其中 Ni 和 Mi 分别是 HASH 值为 i 的两表记录数,满足 N=SUM(Ni) 和 M=SUM(Mi),这大概率会比完全遍历时的复杂度 N*M 要小很多(运气较好的时候会小 K 倍,K 是 HASH 值的取值范围)。

    如果这两个表针对关联键都有序,那么我们就可以使用归并算法来处理关联,这时的复杂度是 N+M;在 N 和 M 都较大的时候(一般都会远大于 K),这个数会远小于 SUM(Ni*Mi),这就是有序归并的好处。

    润乾报表层次数据集

    通过这些例子,集算器为报表准备数据时最终返回的均为标准 ResutSet,这就经常需要将集算器的分层结构(如 switch 和 join 后结果集)转换成标准的二维表,虽然转换工作不复杂,但如果能直接使用分层结果集会更加简单高效。

    润乾报表 5.0 及以上版本就支持直接使用带有层次的数据集进行数据呈现。沿用订单和订单明细的主子表结构。

    【报表展现目标】

    报表每个单元上面是订单信息(单条),下面是明细信息(多条),属于典型的主子报表。

    集算器数据准备

    AB
    1=connect(“demo”)
    2=A1.query(“select 订单 ID, 客户 ID from 订单 order by 订单 ID where 订购日期 >=? and 订购日期 <=?”,begin,end)
    3=A1.query@x(“select 订单 ID, 订购产品, 价格, 数量 from 订单明细”)=A3.group(订单 ID)
    4=join@1(A2:o, 订单 ID;B3:od, 订单 ID)
    5=A4.new(o. 客户 ID: 客户,o. 订单 ID: 订单,od)

    脚本解析:

    1、B3 将订单明细按照订单 ID 分组,得到分组子集(保留分组成员)

    2、A4 订单关联订单明细,一条订单信息对应多条明细

    3、A5 生成报表可以接收的多层结果并为报表返回数据集

    润乾报表设计

    设置参数

    打开报表设计器,新建报表后设置查询参数

    设置数据集

    设置报表数据集,选择集算器数据集类型,添加上述准备好的集算器脚本文件,并设置报表参数与集算器脚本参数对应

    数据集设置后,在报表设计器右下角的数据集窗口中即输出层次结构如下:

    编写报表表达式

    直接使用集算器提供的层次结果集设置报表表达式,其中设置 B4、B5、B6、B7、B8 左主格为 C4(按照订单扩展)

    通过以上步骤即可完成基于层次数据集的报表设计,目前只有润乾报表提供了层次数据集支持,在制作主子表、分组明细报表时就可以在数据准备(数据集)阶段将数据准备好,然后为报表返回带有层次的数据集,报表直接引用无需再次关联或分组,可以带来更高的报表性能。

    子表有序计算举例

    区分主子表后,如果从主表观察子表常常会涉及分组子集和有序运算,这时用集算器处理就非常方便了。举一个并不十分常见的例子,读者可以感受一下。

    报表有三个数据集,分别来自回款表(OrderPayment)、订单表(Orders)和订单明细表(OrderDetails)。

    订单表的主键是订单 ID,回款表的主键是(编号,订单 ID),订单明细表的主键是(编号,订单 ID),订单的主键是回款和订单明细的一部分,订单表是主表,回款表和订单明细表是子表。

    【计算目标】 统计每个客户的每个订单中,最大和最小两笔回款,最高和最低两个价格

    这里并不是计算汇总值,而是要找出每个客户的每笔订单中回款金额最大和最小的两笔回款,以及每笔订单中订购产品最高和最低的两个价格,用以识别客户类型及其回款能力。

    集算器数据准备

    ABC
    1=connect(“db”)
    2=A1.query(“select 订单 ID, 客户 ID from 订单 order by 客户 ID, 订单 ID”)
    3=A1.query(“select 订单 ID, 回款金额 from 回款 order by 订单 ID, 回款金额 desc”)=A3.group(订单 ID)=B3.(.m(1).m(-1))
    4=A1.query@x(“select 订单 ID, 单价 as 价格 from 订单明细 order by 订单 ID, 价格 desc”)=A4.group(订单 ID)=B4.(.m(1).m(-1))
    5=join@1(A2:o, 订单 ID;C3:op, 订单 ID;C4:od, 订单 ID)
    6=A5.new(o. 客户 ID: 客户,o. 订单 ID: 订单,op.m(1). 回款金额: 最大回款金额,op.m(-1). 回款金额: 最小回款金额,od.m(1). 价格: 最高单价,od.m(-1). 价格: 最低单价 )

    脚本解析:

    1、A2 查询订单数据,按照客户和订单排序

    2、A3 查询回款数据,按照订单排序,回款金额降序

    3、B3 按照订单 ID 分组,由于要查找分组成员(最大和最小值),所以这里需要使用 group 函数分组并保留分组结果(不聚合)

    4、C3 找出每组中回款金额最大和最小两条记录

    5、同理 A4-C4 按照订单分组查找每组中价格最高和最低两条记录

    6、A5 根据客户和订单信息左关联上述两个结果集(注意:关联一定要在前面两个分组后进行,如果先关联则会出现多对多叉乘,导致结果错误)。

    每一条关联结果,订单只有一条记录,回款和订单明细则包含两条记录,这是主子表关联关联计算的特点,主表的一条记录指向子表的多条记录

    7、根据关联结果,生成最终结果集,并为报表返回结果集

    以上通过集算器关联运算解决了多数据集关联报表的性能问题,实测中报表性能可获得数倍到数百倍的提升(随数据规模和关联表数量线性增长)。同时集算器解决方案实现比较简单,适用范围更广,适用于数据库无法完成的跨异构库关联、文本关联等情况,从而为报表性能优化、降低报表应用耦合性提供了新思路。

    展开全文
  • 首先问题的背景是一个业务压力测试,排除了很多的前期问题,使用的最有效手段就是索引,在最后一个环节,问题开始陷入焦灼状态,因为这一条SQL的相关表有16张,而且是在业务环节中频繁调用和引用的逻辑。...

    这是学习笔记的第 2163 篇文章


      最近优化了一条MySQL的慢查询SQL,还是蛮有感触,小结一下。 

      首先问题的背景是一个业务做压力测试,排除了很多的前期问题,使用的最有效手段就是索引,在最后一个环节,问题开始陷入焦灼状态,因为这一条SQL的相关表有16张,而且是在业务环节中频繁调用和引用的逻辑。

      一般碰到问题都会有一个疑问,说这是谁写的SQL,应该快速重构,但是大部分优化场景都是:优化可以做,但业务不能停。 所以重构需要,但是不是现在。

      在一种很复杂的心情下开始了优化,当然在查看了执行计划后让我除了绝望还有一种惊喜。那就是里面有一个明显全表扫描的逻辑,也就意味着尽管这么多表关联,但是数据量也可以接受,在优化器解析时大部分逻辑是走了索引,优化好最后一个全表扫描,整个问题就迎刃而解了。

    当然我不用把整个SQL粘贴处理,全文超过5000字符,所以我做了简化,在做了一些对比测试之后,把问题的逻辑简化为下面的SQL形式,也就意味着这个SQL优化成功,则整个优化就意味着成功。

    目标看起来很简单,但是让人开始纠结的是里面的都是left join,怎么破?

    SQL语句如下:

    SELECT     prod_id, prod_name, tag_urlFROM    product sku        LEFT JOIN    (SELECT         jt.tag_url, jts.prod_id    FROM        tag jt    LEFT JOIN prod_tag jts ON jt.tag_id = jts.tag_id    WHERE        jts.sku_id IN (1 , 2, 3, 4)            AND NOW() >= jt.start_time            AND jt.store_id = 0            AND jt.end_time >= NOW()) AS tag_new ON sku.sku_id = tag_new.sku_id

    这条语句的逻辑怎么理解呢,通过执行计划看到的tag这张表是走了全表扫描。我们用下面的图来表示整个解析过程。

    整个SQL的逻辑是输出其中product表的数据(字段prod_id,prod_name)和tag表的数据(tag_url),其中表tag和表tag_product)他们通过字段(tag_id)进行关联,然后和外部的表使用prod_id进行关联,为了体现出是left join(左连接),我把表product的位置及往上放了放。

    整个逻辑其实从上面的图看起来还是有点别扭,tag_product的数据还得反向和外部的表进行关联。

    所以对于上面的逻辑,其实数据表product和表tag要联合输出数据,需要借助一个中间表tag_product,那么tag_product应该是连接数据的纽带,一个相对比较合理的方式就是其实基于表product,tag_product和tag这样的顺序来进行过滤。

    所以我补充了如下的图来说明这个逻辑。

    从通常的设计来说,这样是最合理的方式,可以使得逻辑关系更加清晰。

    看起来这应该是比较合理的方式了。

    SELECT prod_id,prod_name,tag_urlFROM product sku LEFT JOIN tag_product jts on  jts.sku_id =sku.sku_id    left join (select tag_url,tag_id             from tag jt where jt.start_time <= NOW()            AND jt.store_id = 0            AND jt.end_time >= NOW()) tag_new             on tag_new.tag_id=jts.tag_id

    在经过测试之后,感觉已经很接近问题的真相了。

    但是在进一步和业务沟通,了解了业务的实现细节,发现整个逻辑似乎和我们理解的不大一样。 

    比如tag表的数据

    tag_id:1,tag_status:ACTIVE,

    tag_id:2,tag_status:INACTIVE

    tag_product的数据

    tag_id:1,prod_id:100

    tag_id:1,prod_id:200

    tag_id:2,prod_id:100

    按照业务逻辑,如果tag表中的做过滤后的数据为

    tag_id:1,tag_status:ACTIVE,

    则根据SQL的逻辑,left join会和表tag_product再做一次连接,数据以tag表中的tag_id为准,输出就是:

    tag_id:1,prod_id:100

    tag_id:1,prod_id:200

    而如果采用上述的连接方式,其实就会出现意料之外的数据。 

    比如,按照tag_product进行过滤

    tag_id:1,prod_id:100

    tag_id:1,prod_id:200

    tag_id:2,prod_id:100

    然后和tag做关联,tag输出数据为:

    tag_id:1,tag_status:ACTIVE,

    tag_id:2,tag_status:INACTIVE

    这样一来就失去了过滤的意义。

    当然沟通的过程中,也进一步理解了需求,其实我们所谓的逻辑幂等,不是真正意义上的业务逻辑幂等。
    从业务逻辑幂等上,是按照表tag的输出为标准。所以整个tag和tag_product的关联可以降维为普通的表关联,而非left join.

    整个改进的逻辑如下图所示:

    在业务层明确之后,而且输出结果和预期一致的情况下,整个改动的部分就是删除了left join中的left,整个 SQL的执行效率又变得更加流畅。 

    近期热文:

    迁移到MySQL的业务架构演进实战

    数据库修改密码风险高,如何保证业务持续,这几种密码双活方案可以参考

    MySQL业务双活的初步设计方案

    如何优化MySQL千万级大表,我写了6000字的解读

    一道经典的MySQL面试题,答案出现三次反转

    业务双活的数据切换思路设计(下)

    业务双活的数据切换思路设计(一)

    MySQL中的主键和rowid,看似简单,其实有一些使用陷阱需要注意

    小白学MySQL要多久?我整理了10多个问题的答案

    转载热文:

    《吊打面试官》系列-Redis基础

    唯一ID生成算法剖析,看看这篇就够了

    关于大数据运维能力的一些思考

    DBA菜鸟的进化简史:不忘初心,记工作中踩过的三个坑

    美女主持直播,被突发意外打断!湾区网友却高喊: 我懂!超甜

    QQ群号:763628645

    QQ群二维码如下, 添加请注明:姓名+地区+职位,否则不予通过

    在看,让更多人看到
    展开全文
  • spss modeler决策树和关联分析 完整示例 包括数据文件和程序文件
  • 报表做关联往往导致报表效率不高,计算过慢,从而引发性能问题。为此,润乾报表提供了高性能数据关联方式(需要结合集算器实现),可以显著提升报表的计算效率。这里就通过一个常见的多源关联分片报表实例来说明...
  • 该E-R图为Oracle EBS财务系统的总账模块的日记账各个表之间的关联系,对于初学者有一定的帮助,能够很清晰的掌握各表之间内在联系
  • 报表自动化,顾名思义就是人不用表,表格自动生成。就拿日报、季报来说,每天、每月的数据都需要更新,日报、月报就要重复,如果可以通过工具或代码自动生成周报、日报,不需要人反复表,这就是报表自动化。...
  • 使用Jasper或BIRT等报表工具时,常会碰到一些非常规的统计,用报表工具本身或SQL都难以处理,比如与主表相关的子表分布在多个数据库中,报表要展现这些数据源动态关联的结果。集算器具有结构化强计算引擎,集成简单...
  • Layui关联表单

    2019-06-17 21:37:38
    现在我们一个layui表单关联,什么叫表单关联呢,比如有两个layui表格,把第一个表格的数据查询出来后,然后点击表单里的某一条数据,根据这一条数据的主键id,查询第二张表单的数据,并把数据回填。 第一步先准备...
  • 在设计器下一张简单的参数模板报表:连接数据源,设置数据集 设置好数据集之后,设置单元格的编辑风格是下拉数据集 后面的地区和城市的单元格编辑风格需要设置关联过滤表达式: 在设置参数模板...
  • 同样辛苦的还有我们的财务同学(手动滑稽),你的Excel已经在等着你了,准备好加班加点财务报表吧。不过话说回来,这么大的数据量,你真的不怕Excel卡死吗? 再换句话说,你最后拿出下面冷冰冰的销售表格给老板,...
  • DevExpress使用dataset变量关联报表数据

    千次阅读 2016-03-08 22:06:41
    这次的需求是希望使用DevExpress中的相关控件制作报表,而一般来说,不管是用微软自己的rdlc报表还是DevExpress,凡是涉及到数据绑定的环节基本上都是在讲如何直接连数据库然后databinding,而我这次不需要从数据库中...
  • 数据库:如何数据报表

    千次阅读 2019-05-28 09:38:43
    关联:可选择气泡图,用来表示两个、或更多变量之间的联系; 比较:可选择条图,按照强调的方式可以排列任何顺序,适用于高亮Top3或Top5数据; 构成:可以选择饼图,展示每一部分所占全部的百分比; 分布:可以选择...
  • 用plinkGWAS(PCA、关联分析)并用R绘图plink一、观察初始数据质量控制样本缺失率和位点缺失率过滤(产生.imiss和lmiss文件)合理的创建标题,有助于目录的生成如何改变文本的样式插入链接与图片如何插入一段漂亮...
  • SPSS Modeler——超市商品购买关联分析

    万次阅读 多人点赞 2018-10-31 11:21:09
    关联分析,用于发现隐藏在大型数据集中的有意义的联系。这种联系反映一个事物与其他事物之间的相互依存性和关联性。如果两个或者多个事物之间存在一定的关联关系,那么,其中一个事物就能够通过其他事物预测到。 ...
  • BI 是商业智能(Business Intelligence)的缩写,是数据分析领域的一个大类,其内容包含多维分析、报表查询、数据可视化、系统管理等方面,属于典型的 OLAP(在线联机分析)业务。构成 BI 系统的各部分关系大概这样...
  • mysql关联查询语句

    万次阅读 2019-01-04 10:36:55
    这里必须完全指定列名,要不然DBMS无法区分需要哪一个表和哪一个表的什么去做关联。 (4)where子句的重要性:在连接两个表时,实际要的是就是将第一表中的每一行与第二个表中的每一行去配对,where子句作为...
  • 关联供应商

    2019-09-27 14:40:48
    一、在和同学的一个项目中,有一个地方是相互关联的。但我负责的模块是客户的,他负责的却是供应商的,当中就有个功能就是客户关联供应商的,而他那边就刚好相反,也就是供应商关联客户。这种时候就要写出和我们两...
  • fineReport同一张报表多个数据源关联

    万次阅读 2017-07-12 15:19:17
    我们在做报表的时候经常会遇到这样一个问题,为了提高检索性能,我们可能会把比较长的关联复杂的sql拆成多个sql,单独显示,所有我们就定义多个数据,说白了,每个数据源都是sql查询的字段而已。下边以两个数据源为...
  • FineReport_数据集关联

    2021-06-03 14:57:39
    当多个数据集进行关联使用到一张模板时可选择数据列关联,或单元格关联 选择条件ji
  • 两个表格如何建立关联

    千次阅读 2015-09-24 00:01:42
    如果表A删除一条记录时,表B中也随着删除一条相关联的记录,那么外键关系中,表A的主键是表B的外键。这种关系,实际上表B是表A的从属表(即表A是父表),选择对 INSERT 和 UPDATE 强制关系时,如果向表B中插入数据
  • 怎么用clementine做关联规则分析

    万次阅读 多人点赞 2015-11-30 18:00:24
    小伙伴们是不是还在为怎么做关联分析而苦恼呢?想自己写算法觉得太难,用软件分析又不会。不要怕,小迪来教了怎么用clementine做关联规则分析。 1、首先你得在你的电脑上安装clementine,安装方法我在另一篇博文上...
  • 本篇基于多表关联实现一个数据透视表,来看看 Power Pivot 被称为超级数据透视表比 Excel 的数据透视表有哪些革命性进步。数据源在 SQL Server 中,示例数据我放到 Github 上方便大家下载。我们将用到两个表: ...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 106,075
精华内容 42,430
关键字:

关联报表怎么做