共计 7464 个字符，预计需要花费 19 分钟才能阅读完成。
什么是有序计算
应用过 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 天的挪动均匀，那须要写 20 个 JOIN，这种语句能写死人。
再看更简单一些的问题 3，股价表里有多支股票时，减少 code 字段辨别不同的股票，那它的涨幅就要限定在某支股票的分组内：
SELECT code, day ,currPrice/prePrice rate
FROM(
SELECT code, day, curr.price currPrice, pre.price prePrice
FROM (
SELECT code, day, price, rownum row1
FROM tbl ORDER BY code, day ASC) curr
LEFT JOIN (
SELECT code, day, price, rownum row2
FROM tbl ORDER BY code, day ASC) pre
ON 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
) rate
FROM tbl
LAG 函数实现援用后面的记录。函数里的参数示意找后面第 1 条的 price，OVER 是窗口函数 LAG 的子句（每个窗口函数都有 OVER 子句），它的作用是定义待剖析的有序汇合，这个例子很简略，待剖析汇合依照日期有序。
问题 2，挪动均匀。能够用取前边函数 LAG+ 取前面函数 LEAD 实现，但这里用 AVG 函数更可取，它能反对一个范畴内（比方前后十条）的均匀，LAG/LEAD 每次只能取到一个值:
SELECT price,
AVG(price) OVER (
ORDER BY day ASC
RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING
) movingAvg
FROM tbl;
这样取前后 n 条也容易了，只有扭转 RANGE BETWEEN 里的范畴。
问题 3，分组 内的有序计算。每支股票的所有股价是一个分组，窗口函数对它也做了反对：
SELECT code, day, price /
LAG(price,1)
OVER (
PARTITION BY code
ORDER BY day ASC
) rate
FROM tbl
OVER 下的 PARTITION BY 子句形容了怎么划分分组，LAG 操作会限定在每个组内。这比之前的 JOIN 做法好了很多，形容分组很直观。；而 JOIN 做法是对数据做组合排序，尽管实际上也是分组的成果，但不容易了解到位。
序号定位
有序汇合里找相邻记录，属于绝对地位定位，有时咱们还会找相对地位的记录，比方计算每天股价与第一天上市价的涨跌差距：
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 *
FROM
SELECT day, price, ROW_NUMBER()OVER (ORDER BY day ASC) seq FROM tbl
WHERE 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)seq FROM tbl) t1
WHERE seq=(
SELECT TRUNC((COUNT(*)+1)/2) middleSeq
FROM tbl t2
WHERE t1.code=t2.code)
除了减少窗口函数里的PARTITION BY，还要留神计算两头地位时，查问条件也要限定在一支股票内。
5、 每支股票最高价格日与前一天相比涨幅是多少
这个问题须要两种排序形式组合起来定位，还是先看单支股票：
SELECT day, price, seq, rate
FROM (
SELECT day, price, seq,
price/LAG(price,1) OVER (ORDER BY day ASC) rate
FROM (
SELECT day, price,
ROW_NUMBER ()OVER (ORDER BY price DESC) seq
FROM tbl)
)
WHERE seq=1
间断两层子查问都通过窗口函数给原始数据减少有用信息，ROW_NUMBER 把价格从高到低标上序号，通过 LAG 计算出每天的涨幅，最初过滤出价格最高的一天就能够了（seq=1）。
留神过滤出最高价格不能先于涨幅的计算，最高价格的前一天还不晓得在哪里，先过滤掉，前面就算不进去涨幅了。
后面曾经有几个针对分组做有序计算的例子了，这个题就不给出最终答案了，读者有趣味能够本人尝试写写怎么得出多支股票最高价时的涨幅。
有序分组
有序信息还能够用于分组。看这个例子：
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(*) ContinuousDays
FROM (
SELECT SUM(RisingFlag) OVER (ORDER BY day) NoRisingDays
FROM (
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(*) ContinuousDays
FROM (
SELECT code,
SUM(RisingFlag) OVER (PARTITION BY code ORDER BY day) NoRisingDays
FROM (
SELECT code, day,
CASE WHEN price>
LAG(price) OVER (PARTITION BY code ORDER BY day)
THEN 0 ELSE 1 END RisingFlag
FROM tbl
)
) GROUP BY NoRisingDays
)
GROUP BY code
HAVING MAX(ContinuousDays)>=3
这个 SQL 曾经很难看懂了。
总结
从下面的探讨能够看出。没有窗口函数 SQL 对有序运算极其不适应（目前还有些数据库不反对窗口函数），实践上能够写，但理论的麻烦水平基本上等同于不能用。在引入窗口函数后，有序计算失去了很好的改善，不过对于稍简单状况还是相当麻烦。
这个起因在于 SQL 的实践根底，也就是关系代数，是以无序汇合作为根底的，仅靠窗口函数这种打补丁的方法并不能从根本上解决问题。
其实，计算机语言中的数组（即汇合）是人造有序的（有序号），在 Java/C++ 这些高级语言的思路下很容易了解和实现有序计算，然而这类语言的汇合计算能力又比拟弱，实现下面这些问题的代码也不短（尽管有序计算的解题思路难度并不大）。
esProc 的 SPL 能够很好地解决这一问题。esProc 是业余的数据计算引擎，基于有序汇合设计，同时提供了欠缺的汇合运算，相当于 Java 和 SQL 劣势的联合。在 SPL 的反对下，有序汇合计算会非常容易，比方下面的问题用 SPL 写进去是这样：
1、T.sort(day).derive(price/price\[-1\]:rate)
2、T.sort(day).derive(avg(price\[-1:1\]):movingAvg)
3、T.sort(day).group(code).(~.derive(price/price\[-1\]:rate)).conj()
4、T.sort(price).group(code).(~((~.len()+1)\2))
5、T.sort(day).group(code).((p=~.pmax(price),~.calc(p,price/price\[-1\])))
6、T.sort(day).group@o(price >price\[-1\]).max(~.len()))
7、T.sort(day).group(code).select(~.group@o(price>price\[-1\]).max(~.len())>3).(code)
SPL 中提供了跨行援用的语法，也反对有序分组等运算，有了这些后，下面那些问题只有按天然思维去组织计算逻辑，一行代码就能优雅地写进去。
