时间戳上的SQL连接，其中只接受第一个/最旧的结果

Question

假设我有两个表：

Table #1 t1

ID | Col1 | MeasureTime         | Parameter_ID
----------------------------------------------
1  | abc  | 2020-11-11 07:00:00 | 1
2  | abc  | 2020-11-11 08:00:00 | 1

Table #2 t2

ID | Parameter_ID | Col2 | ValidFrom
----------------------------------------------
1  | 1            | xyz  | 2020-11-11 06:30:00
2  | 1            | def  | 2020-11-11 07:30:00

我希望使用Parameter_ID连接这两个表，并且希望具有1:1的关系。来自t2的ValidFrom是此参数生效的时间。但不幸的是，没有ValidTo，相反，我必须检查我是否加入了来自t2的第一个ValidFrom有效的Parameter_ID。

所以我想要这样的结果：

Table #3 t3

ID | Col1 | MeasureTime         | Parameter_ID | ValidFrom | Col2
-----------------------------------------------------------------
1  | abc  | 2020-11-11 07:00:00 | 1            | 06:30:00  | xyz
2  | abc  | 2020-11-11 08:00:00 | 1            | 07:30:00  | def

但是如果我要做一个简单的：

SELECT * 
FROM t1 
JOIN t2 ON t1.PARAMETER_ID = t2.PARAMETER_ID AND t1.Measuretime >= t2.ValidFrom

我会得到：

Table #3 t3

ID | Col1 | MeasureTime         | Parameter_ID | ValidFrom | Col2
-----------------------------------------------------------------
1  | abc  | 2020-11-11 07:00:00 | 1            | 06:30:00  | xyz
2  | abc  | 2020-11-11 08:00:00 | 1            | 06:30:00  | xyz
3  | abc  | 2020-11-11 08:00:00 | 1            | 07:30:00  | def

我其实并不想让ID 2。

希望我的问题变得清晰。我可以这样想：“如果t2有多个结果，按ValidFrom ASC排序，只取第一个结果”。但不幸的是，我真的不确定如何使用SQL来做到这一点。也许这个问题还有一个更优雅的解决方案？

Answer 1

我建议使用lead()生成一个valid_to。然后使用join

select t1.*, t2.valid_from, t2.col2
from table1 t1 join
     (select t2.*,
             lead(valid_from) over (partition by parameter_id order by valid_from) as valid_to
      from t2
     ) t2
     on t1.parameter_id = t2.parameter_id and
        t1.MeasureTime >= t2.valid_from and
        (t1.MeasureTime < t2.valid_to or t2.valid_to is null);

虽然您希望只对齐两个表中的值，但听起来正确的解决方案是从日期范围包括table1日期的table2中获取行。

Answer 2

您可以使用row_number()

select a.* from 

(SELECT *, row_number()over(partition by Col1,MeasureTime order by MeasureTime) rn
FROM t1 
JOIN t2 ON t1.PARAMETER_ID = t2.PARAMETER_ID AND t1.Measuretime >= t2.ValidFrom
) a were a.rn=1

Answer 3

您可以使用ROW_NUMBER分析函数扩展原始查询，如下所示：

SELECT * FROM
(SELECT T1.*, T2.VALID_FROM, T2.COL2,
        ROW_NUMBER() 
          OVER (PARTITION BY T1.ID ORDER BY T2.VALID_FROM DESC NULLS LAST) AS RN
  FROM T1 JOIN T2 ON T1.PARAMETER_ID = T2.PARAMETER_ID
                 AND T1.MEASURETIME >= T2.VALIDFROM)
WHERE RN = 1