获取大于值的下一个Datetime

Question

从同一类型的表中获取下一个可用时间，该时间大于该类型的pickupertime。我试着使用FIRST_VALUE函数，但似乎效果不佳。

DROP TABLE IF EXISTS Test1;
CREATE TEMPORARY TABLE Test1
(
Type        VARCHAR(50),
Pickup_Time TIMESTAMP,
INSERT_TIME      TIMESTAMP
);
DROP TABLE IF EXISTS Test2;
CREATE TEMPORARY TABLE Test2
(
Type VARCHAR(50),
TIMEFRAME  TIMESTAMP
);

INSERT INTO Test1
VALUES ('MAN', '2020-11-01 09:00:00.000000', '2020-11-01 06:00:00.000000');
INSERT INTO Test1
VALUES ('CAT', '2020-11-02 11:00:00.000000', '2020-11-02 05:00:00.000000');

INSERT INTO Test2
VALUES ('MAN', '2020-11-01 01:00:00.000000');
INSERT INTO Test2
VALUES ('MAN', '2020-11-01 02:00:00.000000');
INSERT INTO Test2
VALUES ('MAN', '2020-11-01 03:00:00.000000');
INSERT INTO Test2
VALUES ('MAN', '2020-11-01 04:00:00.000000');
INSERT INTO Test2
VALUES ('MAN', '2020-11-01 05:00:00.000000');
INSERT INTO Test2
VALUES ('MAN', '2020-11-01 06:00:00.000000');
INSERT INTO Test2
VALUES ('MAN', '2020-11-01 07:00:00.000000');
INSERT INTO Test2
VALUES ('MAN', '2020-11-01 08:00:00.000000');
INSERT INTO Test2
VALUES ('MAN', '2020-11-01 09:00:00.000000');
INSERT INTO Test2
VALUES ('MAN', '2020-11-01 10:00:00.000000');
INSERT INTO Test2
VALUES ('MAN', '2020-11-01 11:00:00.000000');
INSERT INTO Test2
VALUES ('MAN', '2020-11-01 12:00:00.000000');
INSERT INTO Test2
VALUES ('MAN', '2020-11-01 13:00:00.000000');

INSERT INTO Test2
VALUES ('CAT', '2020-11-01 01:00:00.000000');
INSERT INTO Test2
VALUES ('CAT', '2020-11-01 02:00:00.000000');
INSERT INTO Test2
VALUES ('CAT', '2020-11-01 03:00:00.000000');
INSERT INTO Test2
VALUES ('CAT', '2020-11-01 04:00:00.000000');
INSERT INTO Test2
VALUES ('CAT', '2020-11-01 05:00:00.000000');
INSERT INTO Test2
VALUES ('CAT', '2020-11-02 06:00:00.000000');
INSERT INTO Test2
VALUES ('CAT', '2020-11-02 07:00:00.000000');
INSERT INTO Test2
VALUES ('CAT', '2020-11-02 08:00:00.000000');
INSERT INTO Test2
VALUES ('CAT', '2020-11-02 09:00:00.000000');
INSERT INTO Test2
VALUES ('CAT', '2020-11-02 10:00:00.000000');
INSERT INTO Test2
VALUES ('CAT', '2020-11-02 11:00:00.000000');
INSERT INTO Test2
VALUES ('CAT', '2020-11-02 12:00:00.000000');
INSERT INTO Test2
VALUES ('CAT', '2020-11-02 13:00:00.000000');

SELECT DISTINCT TT.*,
FIRST_VALUE(CT.TIMEFRAME)
OVER (PARTITION BY TT.Type ORDER BY TIMEFRAME rows between unbounded preceding and unbounded following)
FROM Test1 TT
INNER JOIN Test2 CT on TT.Type = CT.Type
AND TT.Pickup_Time < CT.TIMEFRAME;

以下查询为我提供了所需的输出

+----+--------------------------+--------------------------+--------------------------+
|type|pickup_time               |insert_time               |first_value               |
+----+--------------------------+--------------------------+--------------------------+
|MAN |2020-11-01 09:00:00.000000|2020-11-01 06:00:00.000000|2020-11-01 10:00:00.000000|
|CAT |2020-11-02 11:00:00.000000|2020-11-02 05:00:00.000000|2020-11-02 12:00:00.000000|
+----+--------------------------+--------------------------+--------------------------+

但是，当我用不同的时间为类型MAN进行另一次插入并尝试运行相同的SQL语句时，它不符合我的标准

INSERT INTO Test1
VALUES ('MAN', '2020-11-01 03:00:00.000000', '2020-11-01 01:00:00.000000');

+----+--------------------------+--------------------------+--------------------------+
|type|pickup_time               |insert_time               |first_value               |
+----+--------------------------+--------------------------+--------------------------+
|MAN |2020-11-01 03:00:00.000000|2020-11-01 01:00:00.000000|2020-11-01 04:00:00.000000|
|MAN |2020-11-01 09:00:00.000000|2020-11-01 06:00:00.000000|2020-11-01 04:00:00.000000|
|CAT |2020-11-02 11:00:00.000000|2020-11-02 05:00:00.000000|2020-11-02 12:00:00.000000|
+----+--------------------------+--------------------------+--------------------------+

对于上面的第二个MAN条目，值应该是2020-11-01 10:00:00000000，而不是2020-11-01 04:00:00.00000000。

First_Value是这里要使用的正确选项吗？如果是，如何推导逻辑，如果或哪一个是可以用来实现这一点的最佳函数。

Answer 1

发生此错误是因为MAN有两个条目，但您只按type进行分区，因此两个条目处理相同，您需要按Pickup_Time或ÌNSERT_TIME:进行分区

FIRST_VALUE(CT.TIMEFRAME)
OVER (PARTITION BY TT.Type,TT.Pickup_Time ORDER BY TIMEFRAME rows between unbounded preceding and unbounded following)

这将生成：

type    pickup_time insert_time first_value
MAN 2020-11-01 03:00:00 2020-11-01 01:00:00 2020-11-01 04:00:00
CAT 2020-11-02 11:00:00 2020-11-02 05:00:00 2020-11-02 12:00:00
MAN 2020-11-01 09:00:00 2020-11-01 06:00:00 2020-11-01 10:00:00