SQL: разделить временной интервал на пересечения по одному часу

Я новичок в 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        |

Каким будет самый простой / самый быстрый способ сделать это?


postgresql sql time intervals
person Esben Eickhardt    schedule 14.11.2017    source источник
comment
К сожалению, код был общим. Я использую Postgres.   -  person Esben Eickhardt    schedule 14.11.2017

Ответы (3)


arrow_upward
2
arrow_downward

Попробуйте что-то вроде этого:

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

демонстрация

Результат для выборки данных

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
person Radim Bača    schedule 14.11.2017
comment
Я не думаю, что полностью понимаю, что вы делаете? Если я запустил раздел, в котором вы выбрали i. * ..... один, разве я не смогу создать таблицу с интервалами? - person Esben Eickhardt; 14.11.2017
comment
@EsbenEickhardt, правда, но тогда вы хотели вычислить перекрытие, нет? - person Radim Bača; 14.11.2017
comment
@EsbenEickhardt, вы можете переписать запрос таким образом, чтобы подзапрос не использовался, однако он был бы гораздо менее читабельным. - person Radim Bača; 14.11.2017
comment
Теперь я понял ваш сценарий и поймал ошибку. Для date_trunc ('hour', i.validityend :: timestamp) вам нужно округлить, иначе вы потеряете информацию. Если вы это исправите, я приму ваш ответ. Спасибо за вашу помощь :) - person Esben Eickhardt; 14.11.2017
comment
@EsbenEickhardt, не могли бы вы подготовить данные в dbfiddle, где они не работают должным образом? Благодарность - person Radim Bača; 14.11.2017
comment
Я просто добавил ID4, чтобы проиллюстрировать, что не работает. dbfiddle.uk/ - person Esben Eickhardt; 14.11.2017
comment
@EsbenEickhardt, значит проблема в 0 в конце, верно? - person Radim Bača; 14.11.2017
comment
Проблема в том, что ID4 перекрывает 31 мин с 13-14 и 60 с 14-15. Ваша функция генерирует только 31 мин. - person Esben Eickhardt; 14.11.2017
comment
@EsbenEickhardt, хорошо, я исправил это и покрыл также случай, когда интервал полностью умещается в один час. - person Radim Bača; 14.11.2017
comment
Это по-прежнему не работает, попробуйте: start 2020-05-27 19: 15: 13.738000 end 2020-05-27 20: 07: 14.969000 ticketid 414 - person superdee73; 29.05.2020

arrow_upward
1
arrow_downward

Оба приведенных выше ответа неверны, первый не удастся

start 2020-05-27 19:15:13.738000 end 2020-05-27 20:07:14.969000 ticketid 414

Второй по многим вещам не сработает. Вот мое рабочее решение:

WITH interal_periods AS (
    WITH intervals AS (
        ...
    )
    SELECT t.start_time,
           t.end_time,
           t.ticketid,
           t.validitybegin,
           t.validityend,
           LEAST(extract(EPOCH FROM t.end_time - validitybegin) / 60, 60) AS minutes_after_start_of_hour,
           CASE
               WHEN extract(EPOCH FROM t.end_time - validityend) / 60 > 0
                   THEN extract(EPOCH FROM t.end_time - validityend) / 60
               ELSE 0
               END                                                        AS minutes_before_end_of_hour
    FROM (
             SELECT i.*, generate_series AS start_time, generate_series + INTERVAL '1 hour' AS end_time
             FROM intervals i
                      CROSS JOIN generate_series
                 (date_trunc('hour', i.validitybegin::timestamp)
                 , date_trunc('hour', i.validityend::timestamp)
                 , '1 hour'::interval)
         ) t
)
SELECT start_time,
       end_time,
       ticketid,
       validitybegin,
       validityend,
       minutes_after_start_of_hour - minutes_before_end_of_hour as overlap
FROM interal_periods
person superdee73    schedule 29.05.2020

arrow_upward
0
arrow_downward

Или вот так:

WITH CTE AS 
(
SELECT *, generate_series ( date_trunc('hour', validitybegin::timestamp) 
        , date_trunc('hour', validityend::timestamp)
        , INTERVAL '1 hour') AS STRT, 
          generate_series ( date_trunc('hour', validitybegin::timestamp) 
        , date_trunc('hour', validityend::timestamp)
        , INTERVAL '1 hour') +  INTERVAL '1 hour' AS END
FROM my_data
),

CTE2 AS 
(
SELECT *, ROW_NUMBER() OVER(PARTITION BY CTE.ticketid ORDER BY CTE.STRT DESC) AS RN, 
    COUNT(CTE.ticketid) OVER(PARTITION BY CTE.ticketid) AS CNT
FROM CTE
)

SELECT CTE2.STRT, CTE2.END, 
CASE WHEN CTE2.CNT>2 THEN CASE 
  WHEN CTE2.RN<>1 AND CTE2.RN<>CTE2.CNT THEN 60 
  WHEN CTE2.RN<>1 AND CTE2.RN=CTE2.CNT THEN ROUND((EXTRACT('EPOCH' FROM CTE2.END-CTE2.VALIDITYBEGIN)/60)::decimal, 0)
  ELSE ROUND((EXTRACT('EPOCH' FROM CTE2.VALIDITYEND-CTE2.STRT)/60)::decimal, 0) 
  END
ELSE CASE 
  WHEN CTE2.RN<>1 THEN ROUND((EXTRACT('EPOCH' FROM CTE2.END-CTE2.VALIDITYBEGIN)/60)::decimal, 0)
  ELSE ROUND((EXTRACT('EPOCH' FROM CTE2.VALIDITYEND-CTE2.STRT)/60)::decimal, 0)
  END
END AS OVERLAP, CTE2.ticketid
FROM CTE2
ORDER BY CTE2.ticketid, CTE2.STRT;
person Newbie92    schedule 16.11.2017