【Demo案例】每个月的销售额均在前N名的客户

目标

根据数据库中的contract表,在Java开发的程序中计算:指定年份中每个月的销售额均在前N名的优质客户.

esProc database development-1

步骤

在集算器中实现计算层

A1:执行SQL,从contract表取出必要的字段。

esProc database development-2

说明:参数定义,当前单元格计算结果,数据库信息

A2:在A1的基础上,过滤出指定年份的数据

esProc database development-3

A3:按照月份分组。右侧每行代表一组,蓝色表示可查看组内成员。

esProc database development-4

比如点击第2行(2月份)。

esProc database development-5

A4:对组内数据,按照客户再次分组,并对销售额求和。右边的每条记录代表一个月。

esProc database development-6

A5:对组内数据,按销售额逆序排序。查看第2行

esProc database development-7

A6:对组内数据,过滤出前N名(这里用topN=10进行测试)。查看第2行

esProc database development-8

A7:对组内数据,取出“Client”字段。查看第2行

esProc database development-9

A8:求各组的交集,即每个月都是前N名的客户。

esProc database development-10

至此任务完成了!下面增加一步介绍如何向java程序输出结果集:

A10:计算结果可通过result语句向JDBC客户端输出。可以有多个result语句并返回多个结果集

esProc database development-11

Java程序可以通过JDBC直接调用:

Class.forName(“com.esproc.jdbc.InternlDriver”);
Connection con=DriverManager.getConnection(“jdbc:esproc:loca://”);
PreparedStatement st=con. prepareStatement(“call p2(?,?)”);

下面介绍上述代码中的语法特点,包括:

  • 网格风格
  • 针对数据库的脚本
  • 彻底集合化
  • 真正的分组
  • 循环函数

特点:网格风格

和传统的文本式的脚本风格不同,集算器是写在网格中的。
格名就是天然的变量名,无须定义
通过格名直观的引用之前的计算结果

esProc database development-12

传统脚本需要一份繁琐的“变量命名规范”,查找和调用也很不直观。

网格风格特别适合分步计算
直观的查看当前的计算结果
根据当前结果决定下一步的算法,引用之前的结果写出脚本
这样,复杂的计算目标可以被分解为多个简单的步骤 ,如下图所示:

esProc database development-13

SQL不直接支持分步,必须一次写出所有的计算过程,代码冗长,难免出错。

特点:数据库计算脚本

集算器可以直接运行SQL (如下图中的A1);可以对来自SQL,Excel,Txt等数据源的数据执行查找(A2)操作;或是分组(A4)和排序(A5)

esProc database development-14
在Java中嵌入脚本时集算器比SQL更易用,可以更方便的解决复杂的计算目标。

特点:彻底集合化

集算器是比SQL更方便的结构化数据计算脚本,除了专用于复杂问题分解的分步运算,集算器还彻底实现了集合化,这也是SQL所不具备的。
下图中,A7是个集合,有12个成员,每个成员本身也是集合,观察前三个月:

esProc database development-15

A7也是集合的集合,可以很方便的表达“每个月前N名的客户”
SQL没有彻底集合化,难以直接表达这种概念。

要计算”每个月的销售额均在前N名的客户”,只需要直观地求这些集合的交集

esProc database development-16

SQL缺乏显式的集合数据类型,无法像这样直接求交集。

和A7略有不同,A3的成员是结构化的记录,下面我们观察第一个成员:

esProc database development-17

集合的成员可以是简单数据类型、另一个集合或者是结构化的记录。
彻底集合化可以方便地解决数据库中与集合有关的复杂计算

特点:真正的分组

分组并不意味着必须汇总,比如A3只需按月分组而无须汇总。

esProc database development-18

分组的本意是将数据划分为多个集合,但SQL由于没有集合数据类型,在分组的同时必须进行汇总,不是真正的分组。

集算器实现了真正的分组

真正的分组可以表达”分组的分组”,比如A4就是对每个成员继续按Client分组。

esProc database development-19

注意:”~”代表当前成员,即每个月,~常用在循环函数中。

真正的分组还体现在分组后的运算,比如A5中对各组数据的排序,A6中对各组数据的查询

esProc database development-20

SQL未提供更基础的分组运算,对分组数据的加工要困难得多。

特点:循环函数

可以对一组数据的成员依次执行的函数称为循环函数。
作为数据库计算脚本,集算器中的函数大多是循环函数。

esProc database development-21

请看下图中的A2,这是基本的循环函数,对A1的每个成员(每条记录)执行过滤
有时需要对成员的成员执行操作,比如A5:需要对A4中每个月的记录排序。

esProc database development-22

这时可以用”~”来代表每个月。 ~可以嵌套使用,比如A4的算法 ;对A3的每个成员,按Client分组;再对分组后的每个成员汇总

esProc database development-23

为了表述更清楚点, 我们来看这个公式“A3.(~.group(Client;~.sum(Quantity*Amount)))”可以由下图表达其含义:

esProc database development-24
~可以方便地访问集合的成员,很多复杂的问题得以轻松解决。

数据库中的计算常和顺序有关,比如A6中:过滤出序号大于等于N的记录

esProc database development-25

和~类似,#表示集合内每个成员的序号
有序的集合可以轻松解决前N名,比同期,比上期等典型的问题
~和#配合循环函数,可以避免大部分复杂的Loop/cursor语句,更适合数据库计算

集算器实现了彻底的集合化和有序化,可以轻松解决数据库计算中的大量难题。

详情请参考其他demo案例