smilejingwei的博客

由于业务需要将数据按年存储在两个结构相同的数据库中，要进行数据统计就会涉及多库混合计算。通过数据库或硬编码实现都比较麻烦，借助 esProc 可以简化这类运算。

新建二维表，先计算出当前记录的所有下级记录。再计算当前票据的总工时，也就是下级子票据的工时之和 + 直接工时。A2：用 switch 函数将父票据的字段值修改为父票据的记录引用，建立自关联关系。记录引用可以直观表达父子关系，下图是 3 号票据所有级别的父票据，[8,20,26,30]。现在要递归地计算出每张票据的直接工时和所有下级子票据的工时之和，即总工时。​SQL不支持引用，不方便表达自关联关系，缺乏递归函数，代码很难写。SPL 提供了引用函数，可以建立自关联；提供了递归函数，可以取所有下级节点：Try

循环中用 contain 函数判断集合 ["James","Luke"] 是否包含当前位置的 ename 值，如果包含，则布尔值序列同样位置的成员被赋值成 true，否则就是 false。MYSQL 数据库中，订单表 orders 是事实表，存储了 2024 年全年订单，主键是订单号 oid，字段有客户号 cid，日期 odate，雇员姓名 ename，金额 amount，数据量一千万。总共一千万的数据量，用 esProc 对位序列提速枚举字段的过滤条件，在例二字符串比较的情况下，效果比较显著。

A3：(if(~.icount(DATE)==~.count(), [~], ~.group(CUST)))，处理每组数据，如果本组日期没有重复，则返回当前组，比如第 1 组；[]是集合符号，因为 ~ 本身是集合，所以 [~] 是集合的集合，这样处理可以使数据结构前后一致，方便统一处理。如果组内的日期有重复，则将本组记录再按 CUST 分组，同样保留当前小组日期最近的那条记录，同样将 AMOUNT 替换为当前小组的 AMOUNT 之和。SQL分组后必须立刻汇总，不能保留分组子集，间接实现的代码比较复杂。

MYSQL 数据库中，订单表 orders 存储了 2024 年全年订单，主键是订单号 oid，字段有客户号 cid，日期 odate，数据量一千万。注意 A6 中 create 函数增加了 @p 选项，这是因为子表 details 中的关联字段 oid 值并不是唯一的，@p 表示第一个字段 oid 是分段键，这样可以防止分段读取时，把一个 oid 的明细记录拆分开。A3 把 7 号产品的订单明细，按照 oid 有序分组，每组只保留第一个 oid，这样不用生成分组子集，性能更好。

现在要根据指定的日期，用计划的入库量和入库后的库存倒推出初始日期，也就是零库存或负库存的那一天，并补上这期间每一天入库前的库存UPDATED_CUSTQTY，最后返回完整数据。A3：循环修改 A2 的每条记录：如果当前是第 1 条记录，则入库前的库存等于”入库后的库存 - 入库量”，否则等于”上一条入库前的库存 - 入库量”，直到这个值小于 0 为止，结果保留一位小数。某库表记录了特定日期计划的入库量和入库后的库存，比如 2 月 26 日计划入库 0.6，入库后库存为 3。esProc是免费的，欢迎。

SPL 支持的数据库类型和函数定义在发布包 esproc-bin.jar 中的字典文件 /com/scudata/dm/sql/function.xml 中。1+?1+INTERVAL?2,?1+CAST(?1,?FUNCTIONS 节点代表一个函数组，type 是函数组类型，FixParam 表示参数个数固定的函数组。FUNCTION 节点代表一个简单 SQL 函数，name 是函数名，paramcount 是参数个数，value 是翻译本函数时的默认值，空串时表示无需翻译。

SPL用switch()方法实现两表连接，A2计算过程中，涉及四个序表，class.txt加载为序表class，teacher.txt加载为序表teacher，然后用switch()函数把teacher的相关记录替换为class的ClassTeacher字段值，算出CT序表，SPL序表的字段值允许是记录、甚至子序表这样复杂的数据类型，CT就是这种多级嵌套序表。比如日期函数date()，传入整数的年月日、字符串日期都可以，还可以指定字符串日期的解析格式，最终都能得到一个日期。

A6循环每个分层，若当前层为第一层且该层有数据文件，则把当前层文件中的数据合并到第二层并更新全程量qfl与dfl（C7-C21），若当前层为第二层且该层有至少12个数据文件，则把当前层文件中的数据合并到第三层并操作全程量qfl与dfl（C23-C37），若当前层为第三层且该层有至少12个数据文件，则把当前层文件中的数据合并到第四层并操作全程量qfl与dfl（C39-C53）分层后的冷热数据属于不同的数据源，计算也变得复杂些，需要独立计算同源数据的结果后，再将结果合并起来，算出最终的统计结果。

这样，前端提交的 SQL 语句有了统一的入口，可以很方便的将计算路由规则，写在这个 SPL 脚本中。或者写在其他脚本中，由这个脚本调用。希望改成最近 1 天的数据常驻内存，最近 2 到 4 天的存储在本地磁盘，最近一个月的存储在独立的 SPL 服务器上，其他数据还存放在后台数据库中。上线后，用调度工具每天晚间从数据库中导出当天的订单数据到文本文件中，再调用 SPL 的 ETL 代码，转换为 SPL 组表。实际应用中，计算路由的规则可能会很复杂和多变，通过配置来实现会非常困难，用编程的方式实现是最佳方案。

所有字段通常能分成为两部分，一部分是所有记录的共同字段，另一部分是各自不同的字段，其总数量可能多达数百种，但每条记录只占少数几种。本质上，这两个例子描述的数据结构，在逻辑上可以看作是一张大宽表，但是当属性和分类过多时，这张表的字段就会有几千列（共同字段 + 所有可能的属性 * 分类）。A5是个条件表达式，j将多序列字段合成一个序表，然后count出每条记录满足条件的个数，当数量与提的条件相等，说明这条记录需要被取出。（这里是篮球、足球）；所以，在用附表时，先只用条件相关的字段取出主键再找，很可能是更快的。

但是，当运算任务足够复杂时，碰到几百上千行的嵌套 N 层 SQL（慢的 SQL 通常也不会太简单），几乎总能找到足够多可优化的环节，所以我们经历过的案子还没有失手过。这些被提速的场景都有一个共同点：原先都是用各种数据库（也有 HADOOP/Spark）上的 SQL 实现的，包括查询用的几百行 SQL 也有跑批用的几千行存储过程，然后我们改用集算器的 SPL 重新实现之后就有了这样的效果。而 SQL 却不可以。要读懂原来的逻辑重新实现，这个工作量还是很大的，不过能换来数倍数十倍的性能提升，常常还是值得的。

电商业务中漏斗分析是常见的统计需求。用户使用智能设备购物时，系统会建立连接形成会话 session。每个会话又包含很多个操作事件 event，比如：访问（visit）类事件，浏览（view）类事件，详情页（detail）类事件等等。每个用户的操作事件，按照发生时间（简称事件时间）有一定的先后顺序。事件顺序越靠后，完成该事件的用户数量越少，就像是一个漏斗。漏斗转化分析先要统计各步骤操作事件的去重用户数量，在此基础上再进一步计算转化率等等。以三步漏斗为例，三个事件类型是访问，浏览，详情页。

IN 计算的性能较差，主要由于其中有太多的比较运算。对替换后的新数据做 IN 判断时，先要生成一个与列表等长的布尔值集合，其第 i 个值由列表的第 i 个成员是否在 IN 字段的值集合中决定，在其中就是 true，不在就是 false。这里可以看到客群号集合、枚举值集合中成员的个数都是可扩展的，比如三个或更多客群交集，只要增加字符串中逗号分隔的成员就可以了。SPL 提供了虚表对象，可以将组合二值标签的运算透明化，程序员可以继续操作单个的标签字段，实际上会被 SPL 转换成 16 位整数的某些位。

标准 SQL 中提供了五种最常用的聚合运算：SUM/COUNT/AVG/MIN/MAX，都是对集合计算出单值。语法上只是多了分组字段。看起来这两句代码风格是一致的，但分组聚合的计算结果却不是二维的 DataFrame。但这和全集聚合的语法风格又有点不一致了。和 SQL 一样，对象和语法风格都是一致的。不过，聚合运算并不总以这么基础的形式出现，情况更复杂时，SPL 就会显出优势了。

这些页面还要满足几千人的查询，即使采用了缓存方案以避免相同内容的页面被重复计算，早高峰的集中访问期间仍会有数十个不同的页面任务要并发，相当于同时要计算出几千个指标。但实测证明，即使是优化引擎做的较好的 Oracle 数据库，对于比较简单的多种分组，也还是会对数据表遍历多次，显然没有实现遍历复用机制。取数结束后，计算也都完成，结果写入 A5、A7、A9 格。多指标计算主要是对外存大明细表进行遍历，假如每组指标的计算都要遍历一次大明细表，那么硬盘 IO 就会成为瓶颈，众多指标的计算就不可能做到秒级响应了。

这样做，同一个帐户 id 的数据会连续存储，从硬盘上读取出的数据块几乎全部都是需要的目标值，性能提升会相当明显。而列存的一条记录中，每个列都有各自的位置，不可能都记录下来，只能记录序号，查找时就会多一步动作，性能会下降。由于事实表中的 tcorp 已经预先完成序号化，两表关联时直接用事实表中的位置去维表中取记录，用不到维表的主键和索引，所以这里不需要建立维表主键、索引。不过，带值索引会比普通索引占用更大的存储空间，不太适合查找时涉及字段非常多的情况，且重建索引也很麻烦，需要根据项目的实际情况权衡选择。

数据集A中有n个源对象A1,…,An的时空信息，每条信息有三个属性，分别是ID（iA）、位置（lA）、时间（tA），可以认为同一个Ai在A中不会同时出现两次，即没有两条信息的iA和tA都相同。与A同结构的数据集B中有m个待确认是否与A发生碰撞的目标对象B1,…,Bm（对应属性分别记为iB,lB,tB），也同样认为Bi不会在同一时刻出现两次。本文涉及很多集合运算，我们不使用术语“记录”表示数据集中的信息，而使用“成员”这个集合相关的术语。将A按iA分组得到n个子集，仍用A1,…,An命名。

理论上来讲，SPL 是 SQL 的超集，任何 SQL 实现的计算的确都可以使用 SPL 来完成，解释或移植 SQL 是可能的，虽然这也有不少工作量，但难度并不是非常大。高效的代码要针对运算模型的特征去编写，而 SQL 语句中通常并没这样的信息。SPL 则具备简洁高效的特点，提供了更加敏捷的语法可以简化复杂计算，同时支持过程计算天然支持分步编码，计算体系更加开放可以同时针对多种数据源混合计算，利用内置的高性能存储和高性能算法容易获得更高的计算性能，在使用时更加灵活既可以独立使用也可以嵌入应用集成使用。

某时间区间（例如7天）被分成多个固定时长（如15分钟）的时间切片，对象a和对象b在同一时间切片内的相同位置出现过，称为一次碰撞。规则1：相同时间切片内，多次碰撞只记一次。规则2：相同时间切片内，最后出现位置不同的称为不匹配，不匹配的时间切片数量不超过20 时，（包括其它时间切片的）碰撞才被认为有效。要求：已知对象a，查找出指定时间区间内，满足两条规则下，与a发生有效碰撞次数最多的前20个对象b。

来看下大数据分组汇总运算，数据库通常会使用 Hash 分组的方案，算法的性能没问题，但Python没有提供这种算法，硬编码实现很麻烦，这个任务的难度超出一般应用程序员的能力，结果常常是退而求其次，选择一个容易实现的算法。Python无法在进程内使用简单的多线程并行机制，很多程序员只能采用复杂的多进程并行，进程本身的开销和管理复杂得多，并行程度无法和多线程相提并论，加上进程间的通信也很复杂，有时只好不直接通信，用文件系统来传递汇总结果，这又导致性能大幅下降。可以独立存储，与前端应用服务耦合，维护简单；

举例来讲，我们想查询所有北京号码打给上海号码的通话记录，这就需要把通话记录表和电话帐户表做关联，而电话帐户表要被关联两次，分别使用通话记录表的主叫号码和被叫号码关联，才能分别取出主叫号码注册地和被叫号码注册地。就用这个地区表，随手可以再举出让人崩溃的查询例子：北京号码漫游到广东后打给上海号码的电话，这个查询在数据库中完全可以做出来的，通话记录表中可以有基站信息，基站表再和地区表关联可以获得打电话所在地点，但是这么复杂的关联关系，已经彻底无法在 BI 界面上让业务用户理解了。这就发生互相关联的情况，转圈了。

而且，SPL 复组表的数据组织机制相当于把大排序拆成多次可实时进行的小排序，将排列时间分散到日常的数据维护中，除了第一次迁移系统时会有个较长时间的排序外，日后数据不断追加过程的排序时间基本无感，而获得的计算时间提升却是数量级的。有些聚合计算比较简单，不涉及事件发生的次序，只是统计满足某些条件的事件的发生次数或事件信息的合计值（次数本质上是在合计 1），比如银行账号的交易额超过 1 万元的交易次数、节假日发生的交易额，手机通话时间不超过 3 秒的次数，游戏用户购买某类装备的金额，…我们可以把这类任务称为。

其实，追加数据再加入的过程也是个有序归并，把新增数据单独排序后和已有序的历史数据归并，复杂度是线性的，相当于把所有数据重写一次，而不象常规的大数据排序需要缓存式写出再读入。这些在乾学院上都有介绍。而且，执行归并算法需要的内存很少，只要在内存中为每个表保持数条缓存记录就可以了，几乎不会影响其它并发任务对内存的需求。使用有序归并实现并行计算时需要把数据分成多段，单个表分段比较简单，但两个关联表分段时必须同步对齐，否则归并时两个表数据错位了，就无法得出正确的计算结果，而数据有序就可以保证高性能的同步对齐分段。

A5：当前账号与上一条记录相比不变时，当月余额=当月金额变化+上个月的余额；账号变化时，当月余额重置为当月金额变化。现在要统计从期初 2021 年 1 月到期末 2024 年 4 月每个账户每个月的余额，缺失的月份要补齐。A2：生成每月第一天组成的连续序列。SQL没有方便的方法生成月份序列，要用嵌套查询+窗口函数，代码非常复杂。A1：查询数据库，按账户、每月第1天的日期分组，统计每月金额变化。MSSQL 数据库有个资产账户的流水表，日期不连续。SPL提供了生成日期序列的函数，包括连续月份。

SQL 使用了无序集合的概念，即使我们事先把外键序号化了，数据库也无法利用这个特点，不能在无序集合上使用序号快速定位的机制，只能使用索引查找，而且数据库并不知道外键被序号化了，仍然会去计算 HASH 值和比对。SQL 不区分维表和事实表，面对一大一小两个表时，优化过的 HASH JOIN 不会再做分堆缓存，通常会把小表读入内存而去遍历大表，这样仍然会有遍历大维表的动作，性能会比刚才说的外存查找算法差得多。我们仔细分析上面的算法会发现，过程中对于事实表的访问是连续的，但对于维表的访问则是随机的。

将 HASH JOIN 算法参照外键地址化方案进行改造后，也能一定程度地改善多外键一次解析和并行能力，有些数据库能在工程层面上实施这种优化，不过通常只在只有两个表 JOIN 的情况时有效，在有很多表及各种 JOIN 混在一起时，数据库并不容易识别出应当把哪个表当作事实表去并行遍历、而把其它表当作维表建立 HASH 索引，优化并不总是有效的。A3 读出订单表，A4 的动作是将 A3 的外键字段 cid 转换成对应的 A1 的记录，执行完后，订单表字段 cid 将变成客户表的某条记录。

流数据源通常是动态、无界的，看起来与静态、有限的批数据源区别较大，传统的数据库技术在架构上难以直接处理流数据源，只能让位于后来者。heron\samza\storm\spark\flink等计算框架最先完成突破，在流计算技术中占得先发优势。这些框架非常成功，以至于一说到流计算，应用程序员通常都会去转头寻找某种框架，而不宣称是某种框架的计算技术，则通常被认为不适合实现流计算。

现在要先过滤出 start_row 和 end_row 都不为 null 的记录，再针对找到的每条记录进行计算，找到表中 id 在 本记录的 start_row 和 end_row 之间，且 from 或 to 等于本记录的 from 的那些记录，再对这些记录的 value 求和。A2：过滤出 start_row 和 end_row 都不为 null 的记录，即第 5 第 6 条。SPL已开源免费，欢迎前往乾学院了解更多！A1：通过 JDBC 查询数据库。

现在要初步过滤出 ID 等于指定参数的记录，并根据结果集的 ABBREV 字段是否含有”A”做进一步的处理，含有”A”时，进一步过滤出只含有”A”的记录并返回；不含”A”时，直接返回初步过滤结果。A2：进一步过滤 ABBREV 等于 "A" 的记录，如果结果为空集，则返回 A1；如果结果非空集，则返回进一步过滤的结果。A1：通过 JDBC 查询数据库，初步过滤出 ID 等于指定参数的记录.SPL已开源免费，欢迎前往乾学院了解更多！

而漏写了 JOIN 条件意味着将发生多对多的完全叉乘，而这个 SQL 却可以正常执行，一方面计算结果会出错（回忆一下以前说过的，发生多对多 JOIN 时，大概率是语句写错了），另一方面，如果漏写条件的表很大，笛卡尔积的规模将是平方级的，这极有可能把数据库直接“跑死”！在这种关联机制下，技术人员只要一次性把数据结构（元数据）定义好，在合适的界面下（把表的字段列成有层次的树状而不是常规的线状），就可以由业务人员自己实现 JOIN 运算，不再需要技术人员的参与。，在这种机制支持下的 BI 才能拥有足够的敏捷性。

现在要输入以逗号为分隔符、含有多个字符串的参数，要求从该表中找出字段包含所有这些字符串的记录，或者说字段的集合是参数的超集的那些记录。A3：过滤出参数与字段集合的差集为空集的记录，相当于求字段的集合是参数的超集的那些记录。SPL已开源免费，欢迎前往乾学院社区了解更多！Oracle 数据库的某表有多个字符串字段。A2：按空格将参数拆分为字符串集合。A1：通过JDBC查询数据库。

A2：新建二维表，增加新计算列Seq。当前记录的账号与上一条记录相同，且时间间隔一小时内时，Seq+1；否则将Seq重置为1。[-1]表示相对位置的上一条记录。现在要新增一个分组的序号列 Seq，当某个账号在一个小时内发生新事件时，Seq+1；如果一个小时后才发生新事件，则重置 Seq 为 1。A1：通过JDBC查询数据库，拼出日期时间类型的计算列DT，并按账号和DT排序。MSSQL 数据库某表有三个字段：账号、字符串类型的日期和时间。SPL已开源免费，欢迎前往乾学院社区了解更多！

而漏写了 JOIN 条件意味着将发生多对多的完全叉乘，而这个 SQL 却可以正常执行，一方面计算结果会出错（回忆一下以前说过的，发生多对多 JOIN 时，大概率是语句写错了），另一方面，如果漏写条件的表很大，笛卡尔积的规模将是平方级的，这极有可能把数据库直接“跑死”！在这种关联机制下，技术人员只要一次性把数据结构（元数据）定义好，在合适的界面下（把表的字段列成有层次的树状而不是常规的线状），就可以由业务人员自己实现 JOIN 运算，不再需要技术人员的参与。，在这种机制支持下的 BI 才能拥有足够的敏捷性。

这样，JOIN 涉及的三个表（子查询也算作是个临时表）的主键是相同的，它们是一对一的同维表，仍然在前述的范围内。我们从主子表的例子抽象出维度对齐，不过这种 JOIN 并不要求 JOIN 的表是主子表（事实上从前面的语法简化方案可知，主子表运算还不用写这么麻烦）。这种写法，不必关心这三个表之间的关联关系，各自写各自有关的部分就行，似乎这几个表就没有关联关系，把它们连到一起的就是那个要共同对齐的维度（这里是 date）。事实上，维度对齐还有主子表对齐的情况，不过相对罕见，我们这里就不深入讨论了。

我们把外键字段理解成维表的记录后，维表的字段就相当于外键的属性了，department.manager 即是“所属部门的经理”，而这个字段在 department 表中仍然是个外键，那么它对应的维表记录字段可以继续理解为它的属性，也就会有 department.manager.nationality，即“所属部门的经理的国籍”。，显然比笛卡尔积过滤的理解方式要自然直观得多。还要注意的是，我们说过外键关联是不对称的，即事实表和维表是不对等的，只能基于事实表去找维表字段，而不会有倒过来的情况。

我们把外键字段理解成维表的记录后，维表的字段就相当于外键的属性了，department.manager 即是“所属部门的经理”，而这个字段在 department 表中仍然是个外键，那么它对应的维表记录字段可以继续理解为它的属性，也就会有 department.manager.nationality，即“所属部门的经理的国籍”。，显然比笛卡尔积过滤的理解方式要自然直观得多。还要注意的是，我们说过外键关联是不对称的，即事实表和维表是不对等的，只能基于事实表去找维表字段，而不会有倒过来的情况。

如果两个表 JOIN 时的关联字段没有涉及到任何主键，那就会发生多对多的情况，而这种情况几乎一定还会有一个规模更大的表把这两个表作为维表关联起来。我们说，这三种 JOIN 已经涵盖了绝大多数等值 JOIN 的情况，甚至可以说几乎全部有业务意义的等值 JOIN 都属于这三类，把等值 JOIN 限定在这三种情况之中，几乎不会减少其适应范围。同维表之间是对称的，两个表的地位相同。表 A 的某个字段和表 B 的主键字段关联（所谓字段关联，就是前一节说过的在等值 JOIN 的过滤条件中要对应相等的字段）。

如果 Ai 的记录数是 ni，Bi 的记录数是 mi，则过滤条件的计算次数为 sum(ni*mi)，最平均的情况时，ni=n/k，mi=m/k，则总的复杂度只有原始硬遍历手段的 1/k，能有效地提高运算性能！理论上讲，笛卡尔积的结果集应该是以两个集合成员构成的二元组作为成员，不过由于 SQL 中的集合也就是表，其成员总是有字段的记录，而且也不支持泛型数据类型来描述成员为记录的二元组，所以就简单地把结果集处理成两表记录的字段合并后构成的新记录的集合。，其中 ai 和 bi 分别是 A 和 B 的字段。

A3：新建二维表，取A2当前成员的第1至第4部分，命名为新字段Srllno1 至 Srllno4；再取第 5 至最有一部分，合并后命名为新字段 Srllno5。m 函数可以灵活地按位置取成员，并自动处理数组越界。现在要把这个字段拆成 5 个新字段，名字分别是 Srllno1 至 Srllno5，值分别是将原字段拆开后的第 1 至第 4 个字符串，以及第 5 至最后一个字符串。MSSQL 数据库某表的一个字段是逗号分隔的字符串，字符串数量不定。A1：通过JDBC查询数据库，取items字段。