SPL 比 SQL 更难了还是更容易？

Dear 丶 2023-09-29 18:00 16阅读 0赞

SPL作为专门用于结构化和半结构化数据的处理技术，在实际应用时经常能比SQL快几倍到几百倍，同时代码还会短很多，尤其在处理复杂计算时优势非常明显。用户在看到这些应用效果后对SPL往往很感兴趣，但又担心掌握起来太难，毕竟SPL的理念和语法都跟SQL有较多不同，这要求用户需要重新了解一些概念和学习新的语法，用户可能会心生疑虑。  
![在这里插入图片描述][a185d51f9b67408ab0fea07a7c5625f1.jpeg_pic_center]  
那么SPL的上手难度究竟如何呢？这里我们以SQL为起点讨论一下这个问题。

### 1 ###

SQL一直以来都是使用最广泛的结构化数据查询语言，在实现一般的查询计算时非常简单。像分组汇总一句简单的group by就实现了，相对Java这种要写几十行的高级语言简直不能更简单。而且，SQL的语法设计也符合英语习惯，查询数据时就像说一句英语，这样也大大降低了使用难度。

不过，SQL的简单还主要面向简单查询，情况稍一复杂就不太一样了，三五行的简单查询只存在于教科书中，实际业务要复杂得多。

我们用一个经常举的例子来说明：计算**某只股票的最长连续上涨天数**。

这个计算并不难，按照自然的方法可以先按交易日排好序，设置一列计数器，逐条记录比较，如果上涨计数器就累加1，否则就清零，最后求出计数器的最大值即可。

但是，很不幸，SQL无法直接描述这个有过程的逻辑（除非用存储过程），于是只能更换思路实现：

select max (consecutive_day)
    from (select count(*) (consecutive_day
          from (select sum(rise_mark) over(order by trade_date) days_no_gain
                from (select trade_date,
                             case when closing_price>lag(closing_price) over(order by trade_date)
                                  then 0 else 1 END rise_mark
                      from stock_price ) )
          group by days_no_gain)

使用另一个思路，把交易记录分组，连续在上涨的记录都分到一组，这样只要计算出最大的那一组的成员数就可以了。分组和统计都是SQL支持的运算，但是SQL只有等值分组，没有按照数据的次序来做的有序分组，结果只能用子查询和窗口函数硬造分组标记，将连续上涨的记录的分组标记设置成相同值，这样才能再进行等值分组求出期望的最大值，这种很绕的写法要理解一下才能看懂。而且这还是利用了SQL在2003标准中提供的窗口函数，可以直接计算比昨天的涨幅，从而比较方便地计算出这个标记，但仍然需要几层嵌套。如果是更早期的SQL92标准，连涨计算都很难，整个句子还会复杂很多倍。

读懂这句SQL就能感受SQL在实现这类计算时并不轻松，不支持过程以及有序计算（窗口函数支持程度仍然较低）的SQL使得原本很简单的求解变得十分困难。

除了缺乏有序计算能力外，SQL还有不支持游离记录，集合化不彻底、缺少对象引用机制等不足，这些都会导致代码编写的困难。一个问题从想到解法（自然思路）到实现（写出代码）变得非常绕，要费很大劲才能实现，这就大幅增加了开发难度。事实上，我们在实际业务中经常看到成百上千行的巨长SQL，经常是因为这种“绕”造成的。这些代码的开发周期经常以周甚至月为单位计，**开发成本极高**。而且即使写出来，还会出现过一两个月连作者都看不懂的尴尬情况，维护和交接成本也很高。

代码写的复杂，除了开发效率低成本高以外，往往性能也不佳，即使写得出来也跑不快。

还是用一个经常举的简单例子：**1 亿条数据中取前 10 名**。用SQL写出来并不复杂：

SELECT TOP 10 x FROM T ORDER BY x DESC

这个查询用了ORDER BY，严格按此逻辑执行，意味要将全量数据做排序，而大数据排序是一个很慢的动作。如果内存不够还要向外存写缓存，多次磁盘读写更会使性能急剧下降。

我们知道，这个计算根本不需要大排序，只要始终保持一个10个最大数的集合，遍历（一次）数据时去小留大最后剩下的就是最大的10个了，只需要很少内存就可以完成，不涉及反复外存读写。不幸的是，SQL却写不出来这样的算法。

不过还好，虽然语法有限制但可以在工程实现上想办法，很多数据库引擎碰到这个查询会自动进行优化，从而避免过于低效的算法。但是这种自动优化仍然只对简单的情况有效。

现在我们把TopN计算变得复杂一些，计算每个分组内的前10名。SQL实现（已经有点麻烦了）：

SELECT * FROM (
     SELECT *, ROW_NUMBER() OVER (PARTITION BY Area ORDER BY Amount DESC) rn
     FROM Orders )
    WHERE rn<=10

这里要先借助窗口函数造一个组内序号出来（组内排序），再用子查询过滤出符合条件的记录。由于集合化不够彻底，需要用分区、排序、子查询才能变相实现，导致这个SQL变得有些绕。而且这时候，大部分数据库的优化器就会犯晕了，猜不出这句 SQL 的目的，只能老老实实地执行按语句书写的逻辑去执行排序（这个语句中还是有ORDER BY的字样），结果性能陡降。

完全靠数据库自动优化靠不住，就得去了解执行计划来改造语句，有时候缺少必要的运算根本无法改造成功，只能写UDF自己算，很难也很繁。甚至UDF也不管用，因为无法改变存储，为了保证性能常常还得自己用Java/C++在外围写，这时的复杂度就非常高了，开发成本也会急剧上升。

本来很多按照正常思维编写就能完成的任务，使用SQL却要经常迂回才能实现，导致代码过长且性能很差，经常自己都很难读懂就更别提数据库的自动优化引擎了。跑的慢就需要使用更多硬件资源来弥补，这又会增加硬件成本，导致开发成本和硬件成本双高！

其实，现在业界已经意识到SQL在处理复杂问题时的局限了，成熟好用的数据仓库并不能只提供SQL。有一些数据仓库已经开始引入了Python、Scala，以及应用MapReduce等技术来解决这个问题，但目前为止效果并不理想。MapReduce性能太差，硬件资源消耗极高，而且代码编写非常繁琐，且仍然有很多难以实现的计算；Python 的Pandas在逻辑功能上还比较强，但细节上比较零乱，明显没有精心设计，有不少重复内容且风格不一致的地方，复杂逻辑描述仍然不容易；而且缺乏大数据计算能力以及相应的存储机制，也很难获得高性能；Scala的DataFrame对象使用沉重，对有序运算支持的也不够好，计算时产生的大量记录复制动作导致性能较差，一定程度甚至可以说是倒退。

这也很容易理解，地基不稳的高楼再在楼上怎么修补也无济于事，只有推到重盖才能从根本解决问题。

而这些正是SPL要解决的问题。

### 2 ###

SPL没有再基于SQL的关系代数体系，而是发明了新的**离散数据集**理论以及在此基础上实现的SPL语言（相当于把SQL的高楼推倒重盖）。SPL支持过程计算，并提供了有序计算等多种计算机制，在算法实现上与SQL有很大不同。

拿上面的例子来看。SPL计算股票最长连续上涨天数：

<table> 
 <tbody> 
  <tr> 
   <td></td> 
   <td>A</td> 
  </tr> 
  <tr> 
   <td>1</td> 
   <td>=stock_price.sort(trade_date)</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>=0</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>=A1.max(A2=if(closing_price&gt; closing_price[-1],A2+1,0))</td> 
  </tr> 
 </tbody> 
</table>

基本是按照自然思维解题步骤完成的，排序、比较（用\[-1\]取上日数据）、求最大值，一二三步完成，十分简洁。

即使使用SQL的实现逻辑，SPL也写起来也很简单：

stock_price.sort(trade_date).group@i(closing_price<closing_price[-1]).max(~.len())

计算思路和前面的 SQL完全相同，但SPL直接支持有序分组，表达起来容易多了，不用再绕来绕去。

语法简洁会大幅提升开发效率，开发成本随之降低。同时，也会带来计算性能上的好处。

<table> 
 <tbody> 
  <tr> 
   <td></td> 
   <td>A</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>1</td> 
   <td>=file(“data.ctx”).create().cursor()</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>=A1.groups(;top(10,amount))</td> 
   <td>金额在前10名的订单</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>=A1.groups(area;top(10,amount))</td> 
   <td>每个地区金额在前10名的订单</td> 
  </tr> 
 </tbody> 
</table>

像前面的TopN 运算在SPL中被认为是和 SUM 和 COUNT 一样的聚合运算，只不过返回值是个集合而已。这样可以将高复杂度的排序转换成低复杂度的聚合运算，而且很还能扩展应用范围。

这里的语句中没有排序字样，不会产生大排序的动作，数据量大也不会涉及硬盘交互，在全集还是分组中计算TopN的语法基本一致，都会有较高的性能。类似的高性能算法SPL还有很多，有序分组、位置索引、并行计算、有序归并等等，都可以大幅提升计算性能。

关于SPL的简洁和高效的原因，我们可以再看这个类比：

计算 1+2+3+…+100，普通人就是一步步地硬加，高斯很聪明地用50 \*101一下搞定了。有了**乘法**这种新的运算类型，无论是描述解法（代码简洁）还是实施计算（高效执行）都有了巨大的改观，完成任务变得简单得多了。

所以我们说，50年前诞生的SQL（关系代数）就像只有加法的算数体系，代码繁琐且性能低下也是必然的。而SPL（离散数据集）则是发明了乘法的算数体系，代码简洁且高效也就是自然而然的事情了。

有人可能会问，使用乘法后确实更简单，但需要聪明的高斯才能想得到，而毕竟不是人人都有高斯这么聪明，那是不是说SPL必须要聪明的程序员才能用起来，会不会难度更大？

这要从两方面来说。

一方面，有些计算原来可能想得出但写不出，像前面提到过的有序分组、不必大排序的TopN用SQL就完不成，最后只能忍受“加法”的绕；而SPL提供了很多“乘法”，你想得出解法的同时也能写出来，甚至还很容易。

另一方面，有些解法由于我们没有高斯聪明确实想不到，但高斯已经想到了，我们只要学会就可以了。1+2+…+100会，2+4+…+500也能会，常用的招术并不多， 做一些练习就都能掌握。但确实也不是天生就能会的，需要一些训练，训练多了，这些手段就变成“自然”思维了，难度也并不大。

### 3 ###

其实在实际业务中，SQL很难应付的场景还有很多。这里我们试举几个玩爆SQL的例子。

*  **复杂有序计算：用户行为转换漏斗分析**

用户登录电商网站/APP后会发生页面浏览、搜索、加购物车、下单、付款等多个操作事件。这些事件按照时间有序，每个事件之后都会有用户流失。漏斗转化分析通常先要统计各个操作事件的用户数量，在此基础上再做转换率等复杂的计算。这里多个事件要在指定时间窗口内完成、按指定次序发生才有效，属于典型的复杂多步有序计算，SQL实现起来就十分不易。

*  **多步骤大数据量跑批**

离线跑批涉及的数据量巨大（有时要涉及全量业务数据），且计算逻辑十分复杂，会伴随多步骤计算，彼此有先后顺序。同时跑批通常需要在指定时间窗口内完成，否则会影响业务产生事故。

SQL很难直接实施这些计算，通常要借助存储过程完成。涉及复杂计算时，要用游标读数进行计算，效率很低且无法实施并行计算，效率低下资源占用高。此外，存储过程实现代码往往多达几十步成千上万行，期间会伴随中间结果反复落地，IO成本极高，任务在跑批时间窗口内完不成的现象时有发生。

*  **大数据上多指标计算，反复用关联多**

指标计算是金融电信等行业的常用业务，随着数据量和指标数量（组合）增多完，由于计算过程会多次使用明细数据，反复遍历大表，期间还涉及大表关联、条件过滤、分组汇总、去重计数混合运算，同时还伴随高并发。使用SQL已经无法进行实时计算，经常只能采用事先预加工的方式，无法满足多变的实时查询需要。

因为篇幅原因，这里不可能写太长的代码，就用电商漏斗的例子再感受一下。用SQL实现是这样的：

with e1 as (
     select uid,1 as step1,min(etime) as t1
     from event
     where etime>= to\_date('2021-01-10') and etime<to\_date('2021-01-25')
     and eventtype='eventtype1' and …
     group by 1),
    e2 as (
     select uid,1 as step2,min(e1.t1) as t1,min(e2.etime) as t2
     from event as e2
     inner join e1 on e2.uid = e1.uid
     where e2.etime>= to\_date('2021-01-10') and e2.etime<to\_date('2021-01-25')
     and e2.etime > t1 and e2.etime < t1 + 7
     and eventtype='eventtype2' and …
     group by 1),
    e3 as (
     select uid,1 as step3,min(e2.t1) as t1,min(e3.etime) as t3
     from event as e3
     inner join e2 on e3.uid = e2.uid
     where e3.etime>= to\_date('2021-01-10') and e3.etime<to\_date('2021-01-25')
     and e3.etime > t2 and e3.etime < t1 + 7
     and eventtype='eventtype3' and …
     group by 1)
    select
     sum(step1) as step1,
     sum(step2) as step2,
     sum(step3) as step3
    from
     e1
     left join e2 on e1.uid = e2.uid
     left join e3 on e2.uid = e3.uid

SQL由于缺乏有序计算且集合化不够彻底，需要迂回成多个子查询反复JOIN的写法，编写理解都很困难而且运算性能非常低下。这段代码和漏斗的步骤数量相关，每增加一步数就要再增加一段子查询，实现很繁琐，即使这样，这个计算也并不是所有数据库都能算出来。

同样的计算用SPL来做：

<table> 
 <tbody> 
  <tr> 
   <td></td> 
   <td>A</td> 
  </tr> 
  <tr> 
   <td>1</td> 
   <td>=["etype1","etype2","etype3"]</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>=file("event.ctx").open()</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>=A2.cursor(id,etime,etype;etime&gt;=date("2021-01-10") &amp;&amp; etime&lt;date("2021-01-25") &amp;&amp; A1.contain(etype) &amp;&amp; …)</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td>=A3.group(uid).(~.sort(etime))</td> 
  </tr> 
  <tr> 
   <td>5</td> 
   <td>=A4.new(~.select@1(etype==A1(1)):first,~:all).select(first)</td> 
  </tr> 
  <tr> 
   <td>6</td> 
   <td>=A5.(A1.(t=if(#==1,t1=first.etime,if(t,all.select@1(etype==A1.~ &amp;&amp; etime&gt;t &amp;&amp; etime&lt;t1+7).etime, null))))</td> 
  </tr> 
  <tr> 
   <td>7</td> 
   <td>=A6.groups(;count(~(1)):STEP1,count(~(2)):STEP2,count(~(3)):STEP3)</td> 
  </tr> 
 </tbody> 
</table>

这个计算按照自然想法，其实只要按uid分组后，循环每个分组按照事件类型列表分别查看是否有对应记录（时间），只是第一个事件比较特殊（需要单独处理），查找到后将其作为第二个事件的输入参数即可，此后第2到第N个事件的处理方式相同（可以用通用代码表达），最后按照用户分组计数即可。

上述SPL的解法与自然思维基本一致，利用有序、集合化分组等特性简单7步就可以完成，很简洁。同时，这段代码能够处理任意步骤数的漏斗。由于只遍历一次数据就可以完成计算，不涉及外存交互，性能也更高。

### 4 ###

不过，SPL作为一门程序语言，想要使用SPL达到理想效果，还是要求使用者对SPL提供的函数和算法有一定了解，才能从诸多函数中选择适合的，这也是SPL初学者感到困惑的地方。SPL提供的是一套工具箱，使用者根据实际问题开箱选择工具，是先拧螺丝，还是先裁木板完全由需要决定，但一旦掌握了工具箱内各个工具的使用方法，以后无论遇到什么工程问题都能很好解决，即使要对某些现有的东西进行改造（性能优化）也会游刃有余。而SQL提供的工具很少，这就会导致有时即使想到好方法也无从下手，经常需要通过很绕的方式才能实现，不仅难，还很慢。

当然，使用SPL要掌握内容更多，某种意义上讲是“难”了一点。这就好像做应用题，小学生只用四则运算，看起来很简单；而中学生要学会方程的概念，知识要求变高了。但是小学生要根据具体问题来凑出解法，经常挺难的，每次还不一样；中学生则只要用固定套路列方程就完了，你说哪个更容易呢？

掌握方程自然是要有学习的过程，没有掌握这些知识时，会有些无从下手的感觉，因为陌生，所以会觉得难，SPL也一样。如果拿Java比较的话，SPL的学习难度要远低于Java，毕竟Java中那些面向对象、反射等概念也非常复杂。一个程序员连Java都学得会，SPL完全不在话下，只是要习惯一下，不要先入为主。

此外，对于某些十分复杂对性能有极致要求的场景会涉及一些比较高深的算法知识，难度会大一些，这时可以找SPL专家来咨询共同制定解决方案。其实，要解决这些难题重要的是算法而不是语言本身，不管用什么技术这些工作都要做。只不过SQL由于集合化、离散性、有序性等方面的不足要完成这个工作会异常困难，甚至有些时候无能为力，而SPL要表达这类计算就相对简单。

说了这么多，我们可以得出这样的结论。**SQL只对简单场景容易**，当面对复杂业务逻辑时会因为“绕”导致既难写，跑得又慢，而这些复杂业务才是我们实际应用中的大头（28原则）。要让这些复杂的场景实现变得简单就可以使用SPL来完成，SPL提供了更加简单高效的实现手段。还是那句话，复杂数据计算重点是算法，但算法不仅想出来还要能实现，而且实现起来不能太难（SQL就不行），SPL提供了这种可能。

### SPL资料 ###

*  [SPL下载][SPL]
 *  [SPL源代码][SPL 1]

[a185d51f9b67408ab0fea07a7c5625f1.jpeg_pic_center]: https://img-blog.csdnimg.cn/a185d51f9b67408ab0fea07a7c5625f1.jpeg#pic_center
[SPL]: http://c.raqsoft.com.cn/article/1595816810031
[SPL 1]: https://github.com/SPLWare/esProc