如何用 esProc 补充数据库 SQL 的缺失能力

某些数据库 SQL 缺失必要的能力，通常要编写大段的代码，才能间接实现类似的功能，有些情况甚至要改用存储过程，连结构都变了。常见的比如：生成时间序列、保持分组子集、动态行列转换、自然序号、相对位置、按序列和集合生成多条记录、累积计算、条件分组、跨库计算、集合计算、序列计算、自关联结构、递归计算、对齐式关联等。用下面几个例子快速感受一下。

生成时间序列：某库表的 Time 字段是时间，时间的间隔有时大于 1 分钟。

Time	Value
10:10:05	3
10:11:06	4
10:13:13	5
10:13:19	9
10:13:32	8
10:14:35	2

现在要将数据每分钟分成一个窗口，补上缺失的窗口，对每个窗口统计 4 个值：前一个窗口的最后一条 start_value；本窗口的最后一条；本窗口的最小值；本窗口的最大值。第一分钟的 start_value 用本窗口的第一条记录；如果缺少某窗口的数据，则用前一个窗口的最后一条代替。

start	end	start_value	end_value	min	max
10:10:00	10:11:00	3	3	3	3
10:11:00	10:12:00	3	4	4	4
10:12:00	10:13:00	4	4	4	4
10:13:00	10:14:00	4	8	5	9
10:14:00	10:15:00	8	2	2	2

很多数据库的 SQL 没有方便的方法生成月份序列，很多数据库要用多层嵌套查询 + 多个窗口函数才能间接实现。

保持分组子集：某表存储多个账号在多个日期发生的事件。

Row	Account Number	Date
1	1001	2011-01-10
2	1001	2011-02-01
3	1001	2011-02-20
4	1001	2011-02-22
5	2001	2011-04-11
6	2001	2012-01-01
7	2001	2012-01-30
8	2001	2012-02-09

现在要找出每个账号下符合条件的一对事件，分别是：日期最早的事件 a、距 a 事件 30 天以上的事件中日期最早的事件 b。

Row	Account Number	Date
1	1001	2011-01-10
3	1001	2011-02-20
5	2001	2011-04-11
6	2001	2012-01-01

SQL 分组后必须立刻汇总，很难按条件 b 筛选记录，只能用 join 语句配合多个 CTE 子句间接实现。

动态行列转换：某库表记录了不同产品每个月的销售额，其中产品的值未知。

product	month	amount
AA	1	100
AA	1	150
AA	2	200
AA	2	120
BB	2	180
BB	2	220
CC	3	80

现在要对产品、月份分组，对销售额求和，再将产品由行转列。

month	AA	BB	CC
1	250
2	320	400
3			80

某些数据库的 SQL 缺失动态行列转换能力，行转列时必须写出列名，很多数据库只能改用存储过程。

esProc SPL 内置丰富的计算类库，可以补充这些数据库 SQL 缺失的能力，比如上面 3 个例子：

"https://c.raqsoft.com.cn/article/1742353802112"

“@从 SQL 到 SPL：计算组内符合条件的一对最小值 - 乾学院”

“从 SQL 到 SPL：Create columns from distinct values of a column - 乾学院”

下面，我们就来尝试一下如何将 esProc 集成到应用中。

先下载 esProc，推荐标准版：集算器 产品下载 | 报表加速器下载-半结构化计算软件下载

下载并安装相应的版本。

安完后，试一下 esProc IDE 是否可以正常访问数据库。先把数据库的 JDBC Driver 放到目录 "[安装目录]\common\jdbc"，这是 esProc 的类路径之一。比如 mySQL 的 JDBC：

打开 esProc IDE，找到菜单 "Tool->Connect to Data Source"，新建 JDBC 数据源，填入具体数据库的连接信息。下面是一个 mySQL 的数据源：

返回到数据源界面，试着连接数据源，跨库运算时可以同时连接多个数据源。如果数据源名变成粉色，说明配置成功。

在 IDE 中新建脚本，写 SPL 语句，连接 mysql 数据库，加载第 1 个例子的数据：

=connect("mysql").query@x("select * from main")

按 ctrl-F9 执行，可以在 IDE 右边看到执行结果，以数据表的形式呈现，这对调试 SPL 代码很方便。

加载数据正常后，就可以写正式的 SPL 代码了，第 1 个例子：

	A
1	=connect("mysql").query@x("select * from main where time>? and time<=?",arg1,arg2)
2	=A1.run(Time=time@m(Time))
3	=list=periods@s(A2.min(Time),A2.max(Time),60)
4	=A2.align@a(list,Time)
5	=A4.new(list(#):start, elapse@s(start,60):end, sv=ifn(end_value[-1],~.Value):start_value, ifn(~.m(-1).Value, sv):end_value, ifn(~.min(Value),sv):min, ifn(~.max(Value),sv):max)

先用参数过滤；再将时间改为整分钟数；生成连续分钟的时间序列；将数据按时间序列对齐，每组数据对应一分钟的窗口；按要求用每组数据生成一条新记录。

把上面脚本保存在某个目录中，比如 D:\data\procMain.splx，运行后可以看到结果：

第 2 个例子：

	A
1	=connect("mysql").query@x("select * from ventas")
2	=A1.group(#2)
3	=A2.conj(~1 | ~.select@1((#3 - A2.~1.#3)>30))

加载数据；按 2 个字段分组，但不汇总；取每组第 1 条，再筛选出距第 1 条 30 天以上的记录，也取第 1 条；合并这 2 条记录，最后合并各组的处理结果。

保存成 D:\data\proc2.splx，执行后看结果：

第 3 个例子：

=connect("mysql").query@x("select * from ventas").pivot@s(month;product,sum(amount))

加载数据后动态转置，执行结果：

在 IDE 中调试无误后，就可以把 esProc 集成到 Java 环境中了。
从目录 "[安装目录]\esProc\lib" 下找到 esProc JDBC 相关的 jar 包：esproc-bin-xxxx.jar、icu4j_60.3.jar。

将这两个 jar 包部署到 Java 开发环境的类路径下。
再从目录 "[安装目录]\esProc\config" 下找到 esProc 配置文件 raqsoftConfig.xml，同样部署到 Java 开发环境的类路径下。

配置文件中要改的配置项是 mainPath，这表示脚本等文件的默认路径。注意数据源的信息也在配置文件中。
接下来，就可以编写 Java 代码，通过 esProc JDBC 调用 SPL 脚本了，例子 1：

Class.forName("com.esproc.jdbc.InternalDriver");
Connection con= DriverManager.getConnection("jdbc:esproc:local://");
PreparedStatement st = con.prepareCall("call procMain(?,?)");
st.setTime(1,Time.valueOf("10:00:00"));
st.setTime(2,Time.valueOf("11:00:00"));
ResultSet rs = st.executeQuery();

可以看到，调用 SPL 脚本的过程和调用存储过程是一样的。计算后结果如下：

脚本文件不是必须的，可以把 SPL 脚本转为 SPL 代码，像 SQL 那样嵌入 Java。先在 IDE 中打开例子 2 的脚本文件，选中有代码的单元格 A1-A3，再点击菜单 "Edit->Copy->Code copy"，这样就把多行多列的网格代码转成了单行的 SPL 代码，暂存在粘贴板里。

将转换后的 SPL 代码复制到 Java 代码中

Connection con= DriverManager.getConnection("jdbc:esproc:local://");
PreparedStatement st = con.prepareStatement("==connect(\"mysql\").query@x(\"select * from ventas\")\n=A1.group(#2) \n=A2.conj(~1 | ~.select@1((#3 - A2.~1.#3)>30))");
ResultSet rs = st.executeQuery();

可以看到，Java调用SPL代码的过程和调用SQL代码一样。运行后可以看到结果：

可以看到，Java调用SPL代码的过程和调用SQL代码一样。运行后可以看到结果：

有些 SPL 代码比较简单，没必要用 esProc IDE 编写调试，那就可以直接写在 Java 里。比如例子 3，直接在 Java 中嵌入 SPL 代码：

Connection con= DriverManager.getConnection("jdbc:esproc:local://");
PreparedStatement st = con.prepareStatement("=connect(\"mysql\").query@x(\"select * from ventas\").pivot@s(month;product,sum(amount))");
ResultSet rs = st.executeQuery();

执行结果像下面这样：

乾学院上还有很多补充 SQL 缺失能力的例子，开发遇到问题时可以去找找解决办法。