如何检查表示多个小时时间序列(DB2)的表中缺少的值

Question

想象一下，我有一个名为FOO的DB2表，它有一个时间序列id、一个每小时的时间戳值和一个整数。这就是定义：

CREATE TABLE FOO(
Id_timeseries      INTEGER NOT NULL,
number              DECIMAL(10, 3) NOT NULL,
timestamp          TIMESTAMP NOT NULL,
);

我想知道，对于每个时间序列(假设有几个时间序列(，在两个给定的日期之间是否有空值，这些空日期是什么(我想给出这些值的范围会困难得多(

示例：

Id_timeseries    number     timestamp
1                 28        2017-01-01 01:00:00
1                 28        2017-01-01 02:00:00
1                 28        2017-01-01 03:00:00
2                 28        2017-01-01 01:00:00
2                 28        2017-01-01 02:00:00
2                 28        2017-01-01 03:00:00
1                 28        2017-01-01 07:00:00
1                 28        2017-01-01 06:00:00

我想知道从2017:01-01 00:00:00到2017:01-02 00:00:00缺少的houly值

输出：

Id_timeseries    from                   to
1                2017:01-01 00:00:00    2017:01-01 00:00:00
1                2017:01-01 04:00:00    2017:01-01 05:00:00
1                2017:01-01 08:00:00    2017:01-01 23:00:00
2                2017:01-01 00:00:00    2017:01-01 00:00:00
2                2017:01-01 04:00:00    2017:01-01 23:00:00

Answer 1

试试这个：

WITH FOO (Id_timeseries, number, timestamp) AS 
(
VALUES
(1, 28, timestamp('2017-01-01 01:00:00'))
, (1, 28, timestamp('2017-01-01 02:00:00'))
, (1, 28, timestamp('2017-01-01 03:00:00'))
, (1, 28, timestamp('2017-01-01 06:00:00'))
, (1, 28, timestamp('2017-01-01 07:00:00'))
--, (1, 28, timestamp('2017-01-01 00:00:00'))
--, (1, 28, timestamp('2017-01-01 23:00:00'))
--, (1, 28, timestamp('2017-01-02 00:00:00'))
, (2, 28, timestamp('2017-01-01 01:00:00'))
, (2, 28, timestamp('2017-01-01 02:00:00'))
, (2, 28, timestamp('2017-01-01 03:00:00'))
)
-- Internal gaps
SELECT Id_timeseries, timestamp_prev + 1 hour as from, timestamp - 1 hour as to
FROM
(
SELECT Id_timeseries, timestamp, lag(timestamp) over (partition by Id_timeseries order by timestamp) timestamp_prev
FROM FOO
)
WHERE timestamp_prev <> timestamp - 1 hour
-- Start gap
UNION ALL
SELECT Id_timeseries, timestamp('2017-01-01 00:00:00') as from, min(timestamp) - 1 hour as to
FROM FOO
GROUP BY Id_timeseries
HAVING timestamp('2017-01-01 00:00:00') <> min(timestamp)
-- End gap
UNION ALL
SELECT Id_timeseries, max(timestamp) + 1 hour as from, timestamp('2017-01-02 00:00:00') as to 
FROM FOO
GROUP BY Id_timeseries
HAVING timestamp('2017-01-02 00:00:00') <> max(timestamp)
ORDER BY Id_timeseries, from;

结果是：

|ID_TIMESERIES|FROM                      |TO                        |
|-------------|--------------------------|--------------------------|
|1            |2017-01-01-00.00.00.000000|2017-01-01-00.00.00.000000|
|1            |2017-01-01-04.00.00.000000|2017-01-01-05.00.00.000000|
|1            |2017-01-01-08.00.00.000000|2017-01-02-00.00.00.000000|
|2            |2017-01-01-00.00.00.000000|2017-01-01-00.00.00.000000|
|2            |2017-01-01-04.00.00.000000|2017-01-02-00.00.00.000000|