|
|
假设您有一个简单的表，该表代表由someID标识的另一个实体的时间序列。每行都由someID和时间戳标识，该时间戳不受任何规则的递增，即间隔可以变化：5 A0 W7 ~/ v/ Q& v) y3 J' N9 b9 q4 @
CREATE TABLE someSeries
6 K' L- f, u; P! L(
+ d1 S3 Z' D, y someID int not null,5 [. h7 @/ ` U5 o" w6 }
rowTS datetime not null,) I, w% _: O- V
val int not null6 a5 |" S5 ?, F# o" {. P3 x) V
);% ^3 B2 k6 S7 \
ALTER TABLE someSeries
. e+ P ]8 Y9 R& U% z' EADD CONSTRAINT PK_someSeries(someID, rowTS);
* k4 ~; e" ? Z( i9 X$ ~2 ~# H0 ?& [是否有一种优雅而有效的方法（因此不使用笛卡尔积）来返回所有行并显示该someID的行的rowTS和最近的先前rowTS？+ E# V& @8 W, G( D3 z* K. r
例如，如果数据是
9 b; I! H' S/ P3 J1 e% `9 ssomeID rowTS val
; U7 t7 P5 L: U; M- z3 v5 h------------------------------------* Y" @1 g5 g v0 S8 L, a
1 9/1/2012 2" F! k3 X v: Z! G2 S1 k
1 9/2/2012 3
9 q2 `* o( m$ W( f" C1 9/5/2012 50 s: I* i: }8 c5 w$ s- Q1 y1 M
2 9/2/2012 1/ B, G% V; P9 e1 ~" E4 ^. s$ o. i# {
2 9/4/2012 62 ]% T+ z: E H# }
3 9/5/2012 7; v) W0 t$ ^' Q1 H( H
3 9/7/2012 9
0 O9 D! @6 o! ] X* ]9 g5 N3 9/10/2012 2
/ }/ _4 ~) B% V! L- A& I' }该查询应返回
7 t- C1 l& y: O7 J2 X$ D6 ~' bsomeID rowTS prevRowTS val prevVal
8 W7 c+ P2 U4 a2 _' r0 u3 D$ h! B------------------------------------------------------------------------7 c' l4 I% Y" F/ ]# d
1 9/1/2012 null 2 null) e) D* ^0 @) N e
1 9/2/2012 9/1/2012 3 2
3 D' o) k5 y/ I1 9/5/2012 9/2/2012 5 3
" Y8 D# D1 L) A- i2 9/2/2012 null 1 null
, ?5 }% K5 d* X* `2 9/4/2012 9/2/2012 6 19 @+ l" z9 U$ c2 x
3 9/5/2012 null 7 null
* g) l, k- {/ ~4 A% \6 B# u3 9/7/2012 9/5/2012 9 7
- W1 z( o5 z( c$ l3 9/10/2012 9/7/2012 2 95 ]0 Y4 k' A/ `, i4 ]
当前，我在应用程序中需要这样的东西，并且在应用程序层中需要这样做，基本上我将最后一个rowTS存储在someID主表中，该表是PK，然后在插入时间序列时得到从主表中获取值并查找最近的前一个记录，并进行一些计算（例如，比较val和prevVal），并将其插入到时间序列表中。
5 l, Y S7 n# Q, D0 b$ ~0 q( O但是我想知道是否只有SQL才能有一种快速的方法。唯一想到的是笛卡尔乘积，不用说，这不是很有效。 c& W; p" T- A1 a0 Z
5 P/ V+ W) z7 S, ^& ^
解决方案:
{1 B1 W* H$ Q& D 8 i- a P7 {/ n8 e% g
* O2 _. W( R/ |/ s& O3 A- P. d( b/ X- t# t
由于您说使用什么RDBMS都没有关系，因此在SQL Server中执行以下操作：
+ a/ R" N& D' b; |. i" O;WITH cte- J+ f. j" f$ c
AS* c) g6 J" E% K' \2 ] @: l
(
5 H& `/ e3 d2 \, Z% _. N SELECT *, ROW_NUMBER() OVER(Partition BY someID ORDER BY someID, rowTS) row_num
1 O/ R% B( X& H FROM @Temp
) H/ U# E4 u, E: i)
4 j" i9 l: {4 m6 f6 B* f* L9 x DSELECT c1.someID, c1.rowTS,
7 z# v0 j1 \: m( D (SELECT MAX(c2.rowTS)
% X( y3 ]; n) G0 W& _* M FROM cte c2 ( j9 w: H- P, ?" |. N% }
WHERE c2.someID = c1.someID AND c2.row_num 这是一个现场演示 |
|
技术问答
128 人阅读
|
0 人回复
|
2023-09-12
