SQL 和 SPL 的有序运算对比 / 开普饭

使用过 SQL 或 SPL 的朋友对计算字段都不会陌生，比如 firstname+lastname，year(birthday)，这些计算字段属于行内计算。不管表达式里用到的是单个字段，还是多个字段，使用的数据都在当前记录行内。有行内计算，对应的也就有跨行计算，如：第一名和第二名的差距；从 1 月到当前月份累计的销售额。按照成绩有序，才会有第一名、第二名的说法，累计操作同样基于有序数据，从第几个累加到第几个，这些基于有序集合的计算，就属于有序计算。行内计算关心的是每条数据自身的情况，而跨行的有序计算则关心有序数据的变化情况。一．相邻记录引用简单常见的有序计算是相邻记录引用，也就是在计算中要引用某种次序下的相邻记录。比如下面这些问题：1、股价每天的涨幅是多少（比上期）按日期排序时，引用上一天的股价。2、前一天 + 当天 + 后一天的平均股价是多少（移动平均）按日期排序时，引用前后两天的股价。3、多支股票数据，计算每支股票内的每日涨幅（分组内的比上期）按股票分组，组内按日期排序，引用上一天股价。接下来通过这几个例子研究下 SQL 如何实现这类有序计算。1. SQL的解决方案早期 SQL 的解决方案早期的 SQL 没有窗口函数，引用相邻记录的方法是用 JOIN 把相邻记录拼到同一行。问题 1 写出来是这样的：SELECT day, curr.price/pre.price rateFROM (SELECT day, price, rownum row1FROM tbl ORDER BY day ASC) currLEFT JOIN (SELECT day, price, rownum row2FROM tbl ORDER BY day ASC) preON curr.row1=pre.row2+1即将本表和本表做 JOIN，把前一天和当天作为连接条件，这样即可将前一天的股价和当天股价连接到同一行中，再用行内计算得到涨幅。一个很简单的问题必须使用子查询才能解决。再看问题 2，计算股价的移动平均，(前一天 + 当天 + 后一天)/3，同样是使用 JOIN 实现：SELECT day, (curr.price+pre.price+after.price)/3 movingAvgFROM (SELECT day, price, rownum row1FROM tbl ORDER BY day ASC) currLEFT JOIN (SELECT day, price, rownum row2FROM tbl ORDER BY day ASC) preON curr.row1=pre.row2+1LEFT JOIN (SELECT day, price, rownum row3FROM tbl ORDER BY day ASC) afterON curr.row1=after.row3-1多取一天，就多 JOIN 一个子查询，试想，如果要计算前 10 天 ~ 后 10 天的移动平均，那需要写 20 个 JOIN，这种语句能写死人。再看更复杂一些的问题 3，股价表里有多支股票时，增加 code 字段区分不同的股票，那它的涨幅就要限定在某支股票的分组内：SELECT code, day ,currPrice/prePrice rateFROM(SELECT code, day, curr.price currPrice, pre.price prePriceFROM (SELECT code, day, price, rownum row1FROM tbl ORDER BY code, day ASC) currLEFT JOIN (SELECT code, day, price, rownum row2FROM tbl ORDER BY code, day ASC) preON curr.row1=pre.row2+1 AND curr.code=pre.code)这里着重看两个地方：单表排序时，一定要增加股票代码，形成组合排序 code,day，code 还必须要在前面，这不难理解，先把一支股票的数据放在一起，然后这支股票组内数据再按照日期排序；数据排序好了还不算完，连接条件里也要加上股票代码相等，否则两个相邻的不同股票数据挨着，也会计算涨幅，但这是没意义的脏数据。引入窗口函数从 2003 年起，SQL 标准中引入了窗口函数，带来了序的概念。有序计算变得容易了许多。上面的三个例子写起来就简单多了。问题 1，比上期。为了看清楚，把窗口函数拆成多行缩进，方便理解：SELECT day, price /LAG(price,1)OVER (ORDER BY day ASC) rateFROM tblLAG 函数实现引用前面的记录。函数里的参数表示找前面第 1 条的 price，OVER 是窗口函数 LAG 的子句（每个窗口函数都有 OVER 子句），它的作用是定义待分析的有序集合，这个例子很简单，待分析集合按照日期有序。问题 2，移动平均。可以用取前边函数 LAG+ 取后面函数 LEAD 实现，但这里用 AVG 函数更可取，它能支持一个范围内（比如前后十条）的平均，LAG/LEAD 每次只能取到一个值:SELECT price,AVG(price) OVER (ORDER BY day ASCRANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) movingAvgFROM tbl;这样取前后 n 条也容易了，只要改变 RANGE BETWEEN 里的范围。问题 3，分组内的有序计算。每支股票的所有股价是一个分组，窗口函数对它也做了支持：SELECT code, day, price /LAG(price,1)OVER (PARTITION BY codeORDER BY day ASC) rateFROM tblOVER 下的 PARTITION BY 子句描述了怎么划分分组，LAG 操作会限定在每个组内。这比之前的 JOIN 做法好了很多，描述分组很直观。；而 JOIN 做法是对数据做组合排序，虽然实际上也是分组的效果，但不容易理解到位。2. SPL的解决方案问题前面解读过了，先看下问题 1 的 SPL 脚本：A1=T("tbl.txt")2=A1.sort(day).derive(price/price[-1]:rate)A1: 从数据文件导入股市数据表A2: 使用函数 sort 按日期排序后，计算每天股价与前日的涨幅。其中 price[-1] 表示前日的价格。函数 derive 用于为序表增加计算列。SPL 同样也支持从数据库中读取数据表，比如 A1 可以改为：A1=connect("db").query("select * from tbl")A1 的计算结果是一个序表，SPL中的序表是有序集合，集合的成员是有次序的，这一点与Java等高级语言中的数组类似。可以通过序号来访问成员，是有序集合的基本功能。所以在处理有序运算问题时，有序集合具有天然的优势。访问前一个交易日的数据，对于 SQL 来说比较复杂，需要子查询或者窗口函数的帮助。但是对于有序集合来说，只要访问当前序号 -1 的成员就可以了。不但运算效率更高，理解起来也更加简单。我们继续看一下问题 2 和问题 3 的解决方案。问题 2：A1=T("tbl.txt").sort(day).derive(avg(price[-1:1]):movingAvg)导入股市数据表，并按日期排序。计算当日、前一日和后一日的股价平均值。其中 price[-1:1] 表示从前一日到后一日的股价。问题 3：A1=T("tbl.txt").sort(day).group(code).(~.derive(price/price[-1]:rate)).conj()导入股市数据表，并按日期排序。使用函数 group 根据股票代码分组。每支股票分别计算当日与前一日股价的涨幅。其中符号 ~ 用来表示当前成员。使用 SPL 来解决问题 2 和问题 3 时，仍然是通过相对的位置序号来访问相邻成员。解题思路与问题 1 类似，问题 2 只是从访问前 1 天变成访问连续 3 天，问题 3 引入了多支股票所以先按股票代码进行了分组。二．序号定位1. SQL的解决方案有序集合里找相邻记录，属于相对位置定位，有时我们还会找绝对位置的记录，比如计算每天股价与第一天上市价的涨跌差距：SELECT day, price-FIRST_VALUE(price) OVER (ORDER BY day ASC) FROM tbl或者，已经知道第 10 天是最高股价，计算出每天和它的差距：SELECT day, price-NTH_VALUE(price,10)OVER (ORDER BY day ASC) FROM tbl再看复杂点的情况，要定位的序号事先未知，需要根据数据计算出来：4、股票按照股价排序，取出中间位置的股价（中位数）先看简单的单支股票的解法，按照股价排序后，中间位置还不知道在哪，这时得根据实际股票数据的数量算出中间位置：SELECT *FROMSELECT day, price, ROW_NUMBER()OVER (ORDER BY day ASC) seq FROM tblWHERE seq=(SELECT TRUNC((COUNT(*)+1)/2) middleSeq FROM tbl)FROM 里的子查询用 ROW_NUMBER() 给每行生成序号，WHERE 里的子查询计算出中间序号。这个 SQL 里有两个注意事项，一是不能直接针对第一个子查询内部过滤，因为 WHERE 里不能使用同级 SELECT 中的计算字段，这是 SQL 执行顺序导致的；二是 WHERE 里的子查询结果一定是一行一列的单个值，这时能直接把它看成单个值和 seq 做等值比较。计算多支股票中位数的 SQL 如下：SELECT *FROM(SELECT code, day, price,ROW_NUMBER() OVER (PARTITION BY code ORDER BY day ASC)FROM tbl) t1WHERE seq=(SELECT TRUNC((COUNT(*)+1)/2) middleSeqFROM tbl t2WHERE t1.code=t2.code)除了增加窗口函数里的PARTITION BY，还要注意计算中间位置时，查询条件也要限定在一支股票内。5、每支股票最高价格日与前一天相比涨幅是多少这个问题需要两种排序方式组合起来定位，还是先看单支股票：SELECT day, price, seq, rateFROM (SELECT day, price, seq,price/LAG(price,1) OVER (ORDER BY day ASC) rateFROM (SELECT day, price,ROW_NUMBER ()OVER (ORDER BY price DESC) seqFROM tbl))WHERE seq=1连续两层子查询都通过窗口函数给原始数据增加有用信息，ROW_NUMBER 把价格从高到低标上序号，通过 LAG 计算出每天的涨幅，最后过滤出价格最高的一天就可以了（seq=1）。注意过滤出最高价格不能先于涨幅的计算，最高价格的前一天还不知道在哪里，先过滤掉，后面就算不出来涨幅了。前面已经有几个针对分组做有序计算的例子了，这个题就不给出最终答案了，读者有兴趣可以自己尝试写写怎么得出多支股票最高价时的涨幅。2. SPL的解决方案问题 4 要取出股价的中位数，SPL 脚本如下：A1=T("tbl.txt").sort(price).group(code).(~((~.len()+1)\2))导入股市数据表，并按股价排序。根据股票代码分组后，每支股票分别取出股价中位数。解题思路是，股票代码相同的股票分为一组并按股价排序，接下来每支股票的分组子集按照中间位置的序号访问成员就可以了。我们再来看一下问题 5，每支股票最高价格日与前一天相比涨幅是多少。SPL 脚本如下：A1=T("tbl.txt").sort(day).group(code).((p=~.pmax(price),~.calc(p,price/price[-1])))导入股市数据表，并按日期排序。根据股票代码分组。使用函数 pmax 定位股价最大值所在位置，再使用函数 calc 在指定位置上进行计算。SPL 处理定位问题通常来说分为两步：首先获取成员或者满足条件的位置（序号），然后我们就可以根据序号进行访问或计算了。我们并不需要自己来实现定位，SPL 提供了很多定位函数，用于查找成员或表达式在序表中的位置。三．有序分组1. SQL的解决方案有序信息还可以用于分组。看这个例子：6、一支股票最多连续上涨过几天。这个问题有点难想了。基本的思路是把按日期有序的股票记录分成若干组，连续上涨的记录分成同一组，也就是说，某天的股价比上一天是上涨的，则和上一天记录分到同一组，如果下跌了，则开始一个新组。最后看所有分组中最大的成员数量，也就是最多连续上涨的天数。这种分组比较特殊，和记录的次序有关，而 SQL 里只支持等值分组，就需要把这种有序分组转换成常规的等值分组来实现。过程是这样：1) 按日期排序，用窗口函数取出每天的前一天股价；2）对比，如果上涨了的标记为 0，下跌的标记为 1；3）累加当前行以前的标记，累加的结果类似 0,0,1,1,1,1,2,2,3,3,3…，这些就是我们需要的组号了；4）现在可以用 SQL 常规的等值分组了。完整的 SQL 写出来是这样：SELECT MAX(ContinuousDays)FROM (SELECT COUNT(*) ContinuousDaysFROM (SELECT SUM(RisingFlag) OVER (ORDER BY day) NoRisingDaysFROM (SELECT day, CASE WHEN price>LAG(price) OVER (ORDER BY day) THEN 0 ELSE 1 END RisingFlag FROM tbl)) GROUP BY NoRisingDays)这个题已经不简单了，嵌套了四层的子查询。细追究下解题思路，就得说 SQL 语言与 JAVA/C 语言的不同特点，SQL 是集合化语言，提供的计算直接针对集合，没有显式可精细控制的循环操作，更没有过程中的临时变量可利用，这导致解决问题的思路和人的自然思路差异比较大，得变换思路，通过几个规整的集合计算实现出等价效果；用非集合化的语言 JAVA 或 C，比较贴合自然思路，循环处理每个数据，过程中产生新组或加入旧组很直观。当然 JAVA 等语言基本上没有提供集合运算，也是各有特长。合理的查询需求在复杂程度上不会止步：7、连续上涨超过三天的股票有哪些？这个问题题是有序分组 + 分组子集，最后再加个常规的分组、聚合值过滤（HAVING）。通过上个查询的思路得到每支股票的所有上涨组，最外面套上分组运算得出每支股票的最大上涨天数，并用聚合后的条件运算 HAVING 过滤出上涨大于三天的：SELECT code, MAX(ContinuousDays)FROM (SELECT code, NoRisingDays, COUNT(*) ContinuousDaysFROM (SELECT code,SUM(RisingFlag) OVER (PARTITION BY code ORDER BY day) NoRisingDaysFROM (SELECT code, day,CASE WHEN price>LAG(price) OVER (PARTITION BY code ORDER BY day)THEN 0 ELSE 1 END RisingFlagFROM tbl)) GROUP BY NoRisingDays)GROUP BY codeHAVING MAX(ContinuousDays)>=3这个 SQL 已经很难看懂了。2. SPL的解决方案我们先看一下问题 6，一支股票最多连续上涨过几天。SPL 脚本如下：A1=T("tbl.txt").sort(day).group@o(price>price[-1]).max(~.len())导入股市数据表，并按日期排序。使用函数 group 的选项 @o，根据股价是否上涨进行分组。分组时只和相邻的对比，当股价是否上涨发生变化时产生新组。最后统计连续上涨的天数。解题思路是，统计出股票每一次连续上涨的天数，再从中选出最长的一组。SPL 不仅语法简单，更重要的是逻辑清晰，只要按思路顺序编写就可以了。SQL 和 SPL 处理有序分组问题的差别如此巨大，本质上因为 SQL 以无序集合为基础，而 SPL 的序表是有序集合，更擅长于有序计算。除此以外，SPL 还提供了大量的函数，从而更加降低了使用难度。问题 7，连续上涨超过三天的股票有哪些。SPL脚本如下：A1=T("tbl.txt").sort(day).group(code).select(~.group@o(price>price[-1]).max(~.len())>3).(code)导入股市数据表，并按日期排序。先按照股票代码分组，再按照问题 6 的方法，计算出每支股票连续上涨的最大天数，最后选出连续上涨超过 3 天的。这个问题的 SQL 解决方案已经很难看懂了，但是 SPL 脚本还是很简单的。与问题 6 相比，仅仅是多了一个按股票代码分组的过程。SPL 的分组与 SQL 的分组有着本质上的区别。SQL 的分组除了只能得到分组汇总的结果，查询时也只能选出分组时使用的字段和聚合结果。而 SPL 使用直观的记录分组，比如本例中，将相同股票代码值的记录分在一组，分组子集中保留了数据的全部信息。正因为如此，我们才可以对这些分组子集进行下一步的计算。例如在本例中，我们可以对着每个分组子集再次进行有序分组。总结从上面的讨论可以看出。没有窗口函数 SQL 对有序运算极端不适应（目前还有些数据库不支持窗口函数），理论上可以写，但实际的麻烦程度基本上等同于不能用。在引入窗口函数后，有序计算得到了很好的改善，不过对于稍复杂情况还是相当麻烦。这个原因在于 SQL 的理论基础，也就是关系代数，是以无序集合作为基础的，仅靠窗口函数这种打补丁的办法并不能从根本上解决问题。其实，计算机语言中的数组（即集合）是天然有序的（有序号），在 Java/C++ 这些高级语言的思路下很容易理解和实现有序计算，但是这类语言的集合计算能力又比较弱，实现上面这些问题的代码也不短（虽然有序计算的解题思路难度并不大）。esProc 的 SPL 可以很好地解决这一问题。esProc 是专业的数据计算引擎，基于有序集合设计，同时提供了完善的集合运算，相当于 Java 和 SQL 优势的结合。在 SPL 的支持下，有序集合计算会非常容易。SPL 中提供了跨行引用的语法，也支持有序分组等运算，有了这些后，上面那些问题只要按自然思维去组织计算逻辑，一行代码就能优雅地写出来。

SQL 和 SPL 的有序运算对比

相关推荐