Получить итоги из разницы между строками

У меня есть таблица со следующей структурой:

(
    id SERIAL PRIMARY KEY,
    user_id integer NOT NULL REFERENCES user(id) ON UPDATE CASCADE,
    status text NOT NULL,
    created_at timestamp with time zone NOT NULL,
    updated_at timestamp with time zone NOT NULL
)

Пример данных:

"id","user_id","status","created_at","updated_at"
416,38,"ONLINE","2018-08-07 14:40:51.813+00","2018-08-07 14:40:51.813+00"
417,39,"ONLINE","2018-08-07 14:45:00.717+00","2018-08-07 14:45:00.717+00"
418,38,"OFFLINE","2018-08-07 15:43:22.678+00","2018-08-07 15:43:22.678+00"
419,38,"ONLINE","2018-08-07 16:21:30.725+00","2018-08-07 16:21:30.725+00"
420,38,"OFFLINE","2018-08-07 16:49:10.3+00","2018-08-07 16:49:10.3+00"
421,38,"ONLINE","2018-08-08 11:37:53.639+00","2018-08-08 11:37:53.639+00"
422,38,"OFFLINE","2018-08-08 12:29:08.234+00","2018-08-08 12:29:08.234+00"
423,39,"ONLINE","2018-08-14 15:22:00.539+00","2018-08-14 15:22:00.539+00"
424,39,"OFFLINE","2018-08-14 15:22:02.092+00","2018-08-14 15:22:02.092+00"

Когда пользователь моего приложения подключается к сети, вставляется новая строка со статусом ONLINE. Когда они отключаются, вставляется строка со статусом OFFLINE. Существуют и другие записи, созданные для записи различных событий, но для этого запроса важны только OFFLINE и ONLINE.

Я хочу создать диаграмму, показывающую общее количество пользователей в сети за период времени (например, 5 минут) в диапазоне дат. Если пользователь находится в сети в течение какой-либо части этого периода, он должен быть засчитан.

Пример:

datetime, count
2019-05-22T12:00:00+0000, 53
2019-05-22T12:05:00+0000, 47
2019-05-22T12:10:00+0000, 49
2019-05-22T12:15:00+0000, 55
2019-05-22T12:20:00+0000, 59
2019-05-22T12:25:00+0000, 56

Я могу создать аналогичную диаграмму для отдельного пользователя, извлекая все строки состояния в диапазоне дат, а затем обрабатывая их вручную, однако этот подход не будет масштабироваться для всех пользователей.

Я считаю, что что-то подобное можно было бы выполнить с помощью оконных функций, но я не совсем уверен, с чего начать.

Хорошо, что вы опубликовали ожидаемый результат. В то же время вы также должны показать нам образцы строк из вашей таблицы и запрос, который вы пробовали.

Kaushik Nayak 22.05.2019 16:11
3 метода стилизации элементов HTML
3 метода стилизации элементов HTML
Когда дело доходит до применения какого-либо стиля к нашему HTML, существует три подхода: встроенный, внутренний и внешний. Предпочтительным обычно...
Формы c голосовым вводом в React с помощью Speechly
Формы c голосовым вводом в React с помощью Speechly
Пытались ли вы когда-нибудь заполнить веб-форму в области электронной коммерции, которая требует много кликов и выбора? Вас попросят заполнить дату,...
Стилизация и валидация html-формы без использования JavaScript (только HTML/CSS)
Стилизация и валидация html-формы без использования JavaScript (только HTML/CSS)
Будучи разработчиком веб-приложений, легко впасть в заблуждение, считая, что приложение без JavaScript не имеет права на жизнь. Нам становится удобно...
Flatpickr: простой модуль календаря для вашего приложения на React
Flatpickr: простой модуль календаря для вашего приложения на React
Если вы ищете пакет для быстрой интеграции календаря с выбором даты в ваше приложения, то библиотека Flatpickr отлично справится с этой задачей....
В чем разница между Promise и Observable?
В чем разница между Promise и Observable?
Разберитесь в этом вопросе, и вы значительно повысите уровень своей компетенции.
Что такое cURL в PHP? Встроенные функции и пример GET запроса
Что такое cURL в PHP? Встроенные функции и пример GET запроса
Клиент для URL-адресов, cURL, позволяет взаимодействовать с множеством различных серверов по множеству различных протоколов с синтаксисом URL.
1
1
41
1
Перейти к ответу Данный вопрос помечен как решенный

Ответы 1

Ответ принят как подходящий

Поскольку ваш вопрос очень расплывчатый, никто не может вам помочь на 100%. Что ж, вы можете добиться того, чего хотите, возможно, с помощью комбинации предложений with и оконных функций. С предложением with вы можете легко разбить большие проблемы на мелкие части. Возможно, следующий запрос (без учета производительности) может помочь, вы замените public.tbl_test своей таблицей:

with temp_online as (
    select
    *
    from public.tbl_test
    where public.tbl_test.status ilike 'online'
    order by created_at
),

temp_offline as (
    select
    *
    from public.tbl_test
    where public.tbl_test.status ilike 'offline'
    order by created_at
),

temp_change as (
    select
    * ,
    (
        select temp_offline.created_at from temp_offline where temp_offline.created_at > temp_online.created_at  and temp_offline.user_id = temp_online.user_id order by created_at asc limit 1
    ) as go_offline
    from temp_online
),

temp_result as 
(
select *,
go_offline - created_at as online_duration
from temp_change
),

temp_series as 
(
SELECT (generate_series || ' minute')::interval + '2019-05-22 00:00:00'::timestamp  as temp_date
    FROM generate_series(0, 1440,5)
)

select
 temp_series.temp_date,
(select count(*) from temp_result where temp_result.created_at <=  temp_series.temp_date and temp_result.go_offline >= temp_series.temp_date) as count_users
from 
temp_series 

Отлично работает, за исключением того, что ваш последний запрос также должен быть temp_result.go_offline >= temp_series.temp_date OR temp_result.go_offline IS NULL для учета пользователей, которые все еще находятся в сети.

Benedict Lewis 22.05.2019 16:50

Другие вопросы по теме