Блог

У меня есть dataframe, который состоит из столбцов datetime, class. Столбец class имеет значения ‘open’, ‘close’. Это данные Интернета вещей. Я должен рассчитать общее время открытия, а также общее время закрытия. Dataframe — это что-то вроде-

 index   datetime                    class
------------------------------------------
0      2020-10-05 08:55:00.161326   open
1      2020-10-05 09:00:00.137587   close
2      2020-10-05 09:05:00.089382   close
3      2020-10-05 09:10:00.219278   close
4      2020-10-05 09:15:00.160964   close
5      2020-10-06 09:20:00.315548   close
6      2020-10-06 09:25:00.080932   open
7      2020-10-06 09:30:00.335536   open
8      2020-10-06 09:35:00.202047   close
9      2020-10-06 09:45:00.242022   open
 

Ответ, который мне нужно получить, выглядит следующим образом-

 index   day_count   hour_count          class
-----------------------------------------------
0              0    0:15:00.097376      open
1              1    23:59:59.918860     close
 

По сути, я пытаюсь зафиксировать время выполнения датчика с использованием данных. Если возможно, я также ищу решение в postgresql. Я предпочитаю postgresql вместо dataframe.

Решение Postgres может выглядеть следующим образом:

пошаговая демонстрация: db<>скрипка

 SELECT
    class,
    SUM(duration)                                                         -- 5
FROM (
    SELECT
        *,
        lead(datetime) OVER (ORDER BY datetime) - datetime as duration    -- 4
    FROM (
        SELECT
            *,
            COALESCE(                                                     -- 2
                (class != lag(class) OVER (ORDER BY datetime))::int,      -- 1
                 1
            ) as state
        FROM 
            t
    ) s
    WHERE state != 0                                                      -- 3
) s
GROUP BY class
 

1. lag() переносит текущее class значение в следующую строку. Эти два значения можно сравнить. Если они равны, то результат будет 0 , иначе 1
2. COALESCE это только для самой первой записи, которая, конечно, не содержит предыдущего значения
3. Все значения с value = 0 (промежуточные шаги) удаляются
4. После lead() этого значение копирует следующее значение datetime в текущую строку. Таким образом, можно вычислить разницу между следующим и текущим datetime значением. Теперь мы знаем разницу между class изменением одного значения на другое.
5. Финал: сгруппируйте class и суммируйте длительности.

Вы можете просто использовать оконные функции и агрегацию:

 select class, sum(lead_datetime - datetime) as duration
from (
    select t.*, lead(datetime) over(order by datetime) lead_datetime
    from mytable t
) t
group by class
 

Для каждой строки вычисляется дельта-длительность до следующей строки, которая затем связывается с class текущей строкой. Нет необходимости пропускать промежуточные шаги, вы можете просто суммировать длительности вместе.