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的差值。

SybaseSQL

SELECT SECURITY_ID, DATE, REP, REP - PREV_REP AS DIFF

FROM (

            SELECT T1.SECURITY_ID, T1.DATE, T1.REP

                        , COALESCE(T2.REP, 0AS 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直接支持有序集合,很容易实现相邻数据间的计算。

问答搜集

https://stackoverflow.com/questions/64143908/difference-between-rows-of-the-same-column-if-particular-conditions-are-met