SQL关联两表查询会话前后最近周期读数的冗余行过滤方法

你原来的查询用范围关联，会把所有满足b.start >= before.timestamp和b.end <= after.timestamp的A表记录全部匹配，每个会话会生成N*M条冗余行（N是小于等于当前会话start的A表记录数，M是大于等于当前会话end的A表记录数），只需要过滤保留最近的两条即可。

通用SQL写法（兼容所有支持标准SQL的数据库） #

先通过子查询找到每个会话对应的最近前后时间戳，再关联A表取读数：

SELECT
  b.start,
  b.end,
  before_max.max_before_ts AS before_reading_timestamp,
  after_min.min_after_ts AS after_reading_timestamp,
  b.value,
  a_before.reading AS before_reading,
  a_after.reading AS after_reading
FROM B b
-- 匹配每个会话start之前的最大时间戳
INNER JOIN (
  SELECT 
    b_sub.start,
    MAX(a_sub.timestamp) AS max_before_ts
  FROM B b_sub
  INNER JOIN A a_sub ON b_sub.start >= a_sub.timestamp
  GROUP BY b_sub.start
) before_max ON b.start = before_max.start
-- 匹配每个会话end之后的最小时间戳
INNER JOIN (
  SELECT 
    b_sub.end,
    MIN(a_sub.timestamp) AS min_after_ts
  FROM B b_sub
  INNER JOIN A a_sub ON b_sub.end <= a_sub.timestamp
  GROUP BY b_sub.end
) after_min ON b.end = after_min.end
-- 关联A表取前置读数
INNER JOIN A a_before ON before_max.max_before_ts = a_before.timestamp
-- 关联A表取后置读数
INNER JOIN A a_after ON after_min.min_after_ts = a_after.timestamp

如果存在部分会话没有匹配的前后读数，可以把INNER JOIN换成LEFT JOIN避免过滤掉这部分会话。

更高性能写法（支持LATERAL JOIN的数据库适用，如MySQL8.0+、PostgreSQL、Spark SQL、Hive2.0+） #

用侧连接直接为每个会话查询最近的一条读数，不需要二次分组，性能更好：

SELECT
  b.start,
  b.end,
  before.timestamp AS before_reading_timestamp,
  after.timestamp AS after_reading_timestamp,
  b.value,
  before.reading AS before_reading,
  after.reading AS after_reading
FROM B b
LEFT JOIN LATERAL (
  SELECT timestamp, reading
  FROM A
  WHERE timestamp <= b.start
  ORDER BY timestamp DESC
  LIMIT 1
) before ON TRUE
LEFT JOIN LATERAL (
  SELECT timestamp, reading
  FROM A
  WHERE timestamp >= b.end
  ORDER BY timestamp ASC
  LIMIT 1
) after ON TRUE

内容的提问来源于stack exchange，提问作者drum