SQL:将时间间隔拆分为一小时的交集

Question

我是 SQL 的新手，我试图将时间间隔分成一小时的多行，并指示间隔每小时重叠多少分钟。

我的数据:

    validitybegin    |     validityend     |     ticketid     |
---------------------+---------------------+------------------+
 2017-08-31 12:22:04 | 2017-08-31 13:08:56 |       ID1        |
 2017-08-31 13:09:02 | 2017-08-31 16:46:51 |       ID2        |
 2017-08-31 13:09:10 | 2017-08-31 14:09:10 |       ID3        |

我想要什么:

       start         |        end          |   overlap   |     ticketid     |
---------------------+---------------------+-------------+------------------+
 2017-08-31 12:00:00 | 2017-08-31 13:00:00 |     38      |       ID1        |
 2017-08-31 13:00:00 | 2017-08-31 14:00:00 |      9      |       ID1        |
 2017-08-31 13:00:00 | 2017-08-31 14:00:00 |     51      |       ID2        |
 2017-08-31 14:00:00 | 2017-08-31 15:00:00 |     60      |       ID2        |
 2017-08-31 15:00:00 | 2017-08-31 16:00:00 |     60      |       ID2        |
 2017-08-31 16:00:00 | 2017-08-31 17:00:00 |     47      |       ID2        |
 2017-08-31 13:00:00 | 2017-08-31 14:00:00 |     51      |       ID3        |
 2017-08-31 14:00:00 | 2017-08-31 15:00:00 |      9      |       ID3        |

执行此操作最简单/最快的方法是什么？

Answer 1

尝试这样的事情:

select t.start, t.end, t.ticketid, 
       CASE WHEN EXTRACT(HOUR from t.validityend::timestamp - t.validitybegin::timestamp + interval '30 second') = 0 
       THEN EXTRACT(MINUTE from date_trunc('minute', t.validityend::timestamp - t.validitybegin::timestamp + interval '30 second')) ELSE
              CASE WHEN EXTRACT(HOUR from t.end - t.validitybegin::timestamp + interval '30 second')  = 0 
              THEN EXTRACT(MINUTE from date_trunc('minute', t.end - t.validitybegin::timestamp + interval '30 second')) ELSE
                 CASE WHEN  EXTRACT(HOUR from t.validityend::timestamp - t.start + interval '30 second') = 0 
                 THEN EXTRACT(MINUTE from date_trunc('minute', t.validityend::timestamp - t.start + interval '30 second')) ELSE 60 END
         END 
       END as overlap
from
(
  select i.*, generate_series as start, generate_series + interval '1 hour' as end
  from intervals i
  cross join generate_series
        ( date_trunc('hour', i.validitybegin::timestamp) 
        , date_trunc('hour',i.validityend::timestamp)
        , '1 hour'::interval)
) t

demo

结果样本数据

start                   end                     ticketid    overlap
-------------------------------------------------------------------
2017-08-31 12:00:00     2017-08-31 13:00:00     ID1         38
2017-08-31 13:00:00     2017-08-31 14:00:00     ID1         9
2017-08-31 13:00:00     2017-08-31 14:00:00     ID2         51
2017-08-31 14:00:00     2017-08-31 15:00:00     ID2         60
2017-08-31 15:00:00     2017-08-31 16:00:00     ID2         60
2017-08-31 16:00:00     2017-08-31 17:00:00     ID2         47
2017-08-31 13:00:00     2017-08-31 14:00:00     ID3         51
2017-08-31 14:00:00     2017-08-31 15:00:00     ID3         9