SQL 如何在保持次序时计算某列与上一日期的差值 *
Sybase 数据库中有表SAMPLE,数据如下所示:
SECURITY_ID
DATE
REP
2256
2020-01-01
0
2257
2020-01-02
1
2258
2020-01-03
2
2256
2020-01-02
3
2256
2020-01-03
5
需要在保持原表次序不变的情况下,计算REP列与上一天的差值(DIFF),结果如下:
SECURITY_ID
DATE
REP
DIFF
2256
2020-01-01
0
0
2257
2020-01-02
1
1
2258
2020-01-03
2
2
2256
2020-01-02
3
3
2256
2020-01-03
5
2
按原顺序,SECURITY_ID相同时,求当前日期的REP与上一日REP的差值。
Sybase的SQL:
SELECT SECURITY_ID, DATE, REP, REP - PREV_REP AS DIFF
FROM (
SELECT T1.SECURITY_ID, T1.DATE, T1.REP
, COALESCE(T2.REP, 0) AS PREV_REP
FROM SAMPLE T1
LEFT JOIN SAMPLE T2
ON T1.SECURITY_ID = T2.SECURITY_ID
AND T2.DATE = T1.DATE - 1
)
ORDER BY REP;
这道题用自然思维去解其实很简单,对SECURITY_ID相同的数据,用当前的REP减去上一条(上一个日期)记录的REP即可。但是在SQL中的集合是无序的,需要借助窗口函数完成计算,写起来很麻烦。而且本题中的Sybase不支持相应的窗口函数,只能先自关联后再去计算差值,写出来的SQL更复杂了。
用开源集算器的SPL就很容易写:
A |
|
1 |
=connect("demo") |
2 |
=A1.query@x("SELECT * FROM SAMPLE").derive(null:DIFF) |
3 |
>A2.group(SECURITY_ID).(~.run(DIFF=REP-REP[-1])) |
4 |
return A2 |
SPL直接支持有序集合,很容易实现相邻数据间的计算。
英文版