SQL 有序计算

什么是有序计算

       使用过 SQL 的朋友对计算字段都不会陌生，比如 firstname+lastname，year(birthday)，这些计算字段属于行内计算，不管表达式里用到的是单个字段，还是多个字段，使用的数据都在当前记录行内。有行内计算，对应的也就有跨行计算，如：第一名和第二名的差距；从 1 月到当前月份累计的销售额。按照成绩有序，才会有第一名、第二名的说法，累计操作同样基于有序数据，从第几个累加到第几个，这些基于有序集合的计算，就属于有序计算。行内计算关心的是每条数据自身的情况，而跨行的有序计算则关心有序数据的变化情况。

 

相邻记录引用

简单常见的有序计算是相邻记录引用，也就是在计算中要引用某种次序下的相邻记录。比如下面这些问题：

1、 股价每天的涨幅是多少（比上期）

按日期排序时，引用上一天的股价。

2、 前一天 + 当天 + 后一天的平均股价是多少（移动平均）

按日期排序时，引用前后两天的股价。

3、 多支股票数据，计算每支股票内的每日涨幅（分组内的比上期）

按股票分组，组内按日期排序，引用上一天股价。

接下来通过这几个例子研究下 SQL 如何实现这类有序计算。

早期 SQL 的解决方案

       早期的 SQL 没有窗口函数，引用相邻记录的方法是用 JOIN 把相邻记录拼到同一行。

       问题 1 写出来是这样的：

       SELECT day, curr.price/pre.price rate

       FROM (

              SELECT day, price, rownum row1

              FROM tbl ORDER BY day ASC) curr

       LEFT JOIN (

              SELECT day, price, rownum row2

              FROM tbl ORDER BY day ASC) pre

       ON curr.row1=pre.row2+1

       即将本表和本表做 JOIN，把前一天和当天作为连接条件，这样即可将前一天的股价和当天股价连接到同一行中，再用行内计算得到涨幅。一个很简单的问题必须使用子查询才能解决。

 

       再看问题 2，计算股价的移动平均，(前一天 + 当天 + 后一天)/3，同样是使用 JOIN 实现：

       SELECT day, (curr.price+pre.price+after.price)/3 movingAvg

       FROM (

              SELECT day, price, rownum row1

              FROM tbl ORDER BY day ASC) curr

       LEFT JOIN (

              SELECT day, price, rownum row2

              FROM tbl ORDER BY day ASC) pre

       ON curr.row1=pre.row2+1

       LEFT JOIN (

              SELECT day, price, rownum row3

              FROM tbl ORDER BY day ASC) after

       ON curr.row1=after.row3-1

       多取一天，就多 JOIN 一个子查询，试想，如果要计算前 10 天 ~ 后 10 天的移动平均ÿ