连接表以获取会话前后的读数

Question

我有两个表A, b

A -包含周期性读数:

tbody> <<tr>57

时间戳	读取
1	1
3	2
	3
	4

使用lead()获取读数的时间范围。然后join:

with r as (
select a.*,
lead(timestamp) over (order by timestamp) as next_timestamp
from a
)
select b.*, rs.value as before_value, re.value as end_value
from b left join
r rs
on b.start >= rs.timestamp and
(b.start < rs.next_timestamp or rs.next_timestamp is null) left join
r re
on b.end >= re.timestamp and
(b.start < re.next_timestamp or re.next_timestamp is null);

连接表并使用MAX(),MIN()和FIRST_VALUE()窗口函数:

SELECT DISTINCT b.start, b.end, 
MAX(CASE WHEN a.timestamp <= b.start THEN a.timestamp END) OVER (PARTITION BY b.start, b.end) before_reading_timestamp,
MIN(CASE WHEN a.timestamp >= b.end THEN a.timestamp END) OVER (PARTITION BY b.start, b.end) after_reading_timestamp,
b.value,
FIRST_VALUE(a.reading) OVER (PARTITION BY b.start, b.end ORDER BY a.timestamp <= b.start DESC, a.timestamp DESC) before_reading,
FIRST_VALUE(a.reading) OVER (PARTITION BY b.start, b.end ORDER BY a.timestamp >= b.end DESC, a.timestamp) after_reading
FROM b LEFT JOIN a
ON a.timestamp <= b.start OR a.timestamp >= b.end;

或者，对于这个读数升序的示例数据:

SELECT DISTINCT b.start, b.end, 
MAX(CASE WHEN a.timestamp <= b.start THEN a.timestamp END) OVER (PARTITION BY b.start, b.end) before_reading_timestamp,
MIN(CASE WHEN a.timestamp >= b.end THEN a.timestamp END) OVER (PARTITION BY b.start, b.end) after_reading_timestamp,
b.value,
MAX(CASE WHEN a.timestamp <= b.start THEN a.reading END) OVER (PARTITION BY b.start, b.end) before_reading,
MIN(CASE WHEN a.timestamp >= b.end THEN a.reading END) OVER (PARTITION BY b.start, b.end) after_reading
FROM b LEFT JOIN a
ON a.timestamp <= b.start OR a.timestamp >= b.end;