如何最好地检索每个记录的最近的先前记录

小编典典

如何最好地检索每个记录的最近的先前记录

sql

假设您有一个简单的表，该表代表由someID标识的另一个实体的时间序列。每行都由someID和时间戳标识，该时间戳不受任何规则的递增，即间隔可以变化：

CREATE TABLE someSeries
(
   someID int not null,
   rowTS datetime not null,
   val int not null
);
ALTER TABLE someSeries 
ADD CONSTRAINT PK_someSeries(someID, rowTS);

是否有一种优雅而有效的方法（因此不使用笛卡尔积）来返回所有行并显示该someID的行的rowTS和最近的先前rowTS？

例如，如果数据是

someID        rowTS            val
------------------------------------
1             9/1/2012         2
1             9/2/2012         3
1             9/5/2012         5
2             9/2/2012         1
2             9/4/2012         6
3             9/5/2012         7
3             9/7/2012         9
3             9/10/2012        2

该查询应返回

someID        rowTS            prevRowTS          val          prevVal
------------------------------------------------------------------------
1             9/1/2012         null               2            null
1             9/2/2012         9/1/2012           3            2 
1             9/5/2012         9/2/2012           5            3
2             9/2/2012         null               1            null
2             9/4/2012         9/2/2012           6            1
3             9/5/2012         null               7            null
3             9/7/2012         9/5/2012           9            7
3             9/10/2012        9/7/2012           2            9

当前，我在应用程序中需要这样的东西，并且在应用程序层中需要这样做，基本上我将最后一个rowTS存储在someID主表中，该表是PK，然后在插入时间序列时得到从主表中获取值并查找最近的前一个记录，并进行一些计算（例如，比较val和prevVal），并将其插入到时间序列表中。

但是我想知道是否只有SQL才能有一种快速的方法。唯一想到的是笛卡尔乘积，不用说，这不是很有效。

阅读 200

2021-05-16

共1个答案

小编典典

由于您说使用什么RDBMS都没有关系，因此在SQL Server中执行以下操作：

;WITH cte
AS
(
    SELECT *, ROW_NUMBER() OVER(Partition BY someID ORDER BY someID, rowTS) row_num
    FROM @Temp
)
SELECT c1.someID, c1.rowTS, 
  (SELECT MAX(c2.rowTS) 
   FROM cte c2 
   WHERE c2.someID = c1.someID AND c2.row_num < c1.row_num) AS prevRowTS,
  c1.val,
  (SELECT MAX(c2.val) 
   FROM cte c2 
   WHERE c2.someID = c1.someID AND c2.row_num < c1.row_num) AS prevVal
FROM cte c1

这是一个现场演示

2021-05-16