BG Development


Страници: (2) [1] 2   ( Първото ново мнение ) Reply to this topicStart new topicStart Poll

> Групиране на данни по интервал
thrawn
Публикувано на: 20-01-2021, 15:23
Quote Post



Име:
Група: Потребител
Ранг: Почетен член

Мнения: 3076
Регистриран на: 17.01.17



Нещо зациклих, та малко помощ няма да ми дойде зле.

Имам лог система в която се записа дата и част (timestamp without time zone) на събитие и период на неактивност (interval) + id на потребител.

Целта е да групирам записите по id така, че във всяка група да няма периоди на неактивност по-големи от 5 минути. Разбирасе, всичко подредено хронологично.

Някаква идея как да се реализира с sql?
За postgresql иде реч.

----
Ето примерни данни
CODE
"admin"      "2021-01-20 15:04:38.028776"      "51 years 19 days 15:04:38.028776"
"admin"      "2021-01-20 15:04:58.712545"      "00:00:20.683769"
"admin"      "2021-01-20 15:05:00.125379"      "00:00:01.412834"
"admin"      "2021-01-20 15:24:41.083475"      "00:19:40.958096"
"admin"      "2021-01-20 15:24:42.960778"      "00:00:01.877303"
"admin"      "2021-01-20 15:24:44.410533"      "00:00:01.449755"
"admin"      "2021-01-20 15:24:45.947244"      "00:00:01.536711"


От това трябва да получа две групи
CODE
"admin"      "2021-01-20 15:04:38.028776"      "51 years 19 days 15:04:38.028776"
"admin"      "2021-01-20 15:04:58.712545"      "00:00:20.683769"
"admin"      "2021-01-20 15:05:00.125379"      "00:00:01.412834"

CODE
"admin"      "2021-01-20 15:24:41.083475"      "00:19:40.958096"
"admin"      "2021-01-20 15:24:42.960778"      "00:00:01.877303"
"admin"      "2021-01-20 15:24:44.410533"      "00:00:01.449755"
"admin"      "2021-01-20 15:24:45.947244"      "00:00:01.536711"


като реално ме интересува минималната и максималната стойност на дата във всяка група
CODE
"admin"      "2021-01-20 15:04:38.028776"      "2021-01-20 15:05:00.125379"
"admin"      "2021-01-20 15:24:41.083475"      "2021-01-20 15:24:45.947244"


Това мнение е било редактирано от thrawn на 20-01-2021, 15:30
PMEmail Poster
Top
thrawn
Публикувано на: 20-01-2021, 15:54
Quote Post



Име:
Група: Потребител
Ранг: Почетен член

Мнения: 3076
Регистриран на: 17.01.17



Ето това съм избацал до сега, ама търся по грамотно решение
CODE
with tmp1 as (
      select
            username, event_time,
            case when idle_interval > interval '5 minutes' then 1 else 0 end c,
            0 as sid
      from logs
),
tmp2 as (
      select
            username, event_time,
            sum(sid + c) over(partition by username order by event_time) sid
      from tmp1
)
select username, max(event_time), min(event_time) from tmp2
group by username, sid;
PMEmail Poster
Top
ici
Публикувано на: 20-01-2021, 16:05
Quote Post


Group Icon
Име: Ивайло Илчев
Група: VIP
Ранг: Почетен член

Мнения: 17632
Регистриран на: 06.06.04



CODE
SELECT user, min(event), max(event)
FROM table
GROUP BY user
HAVING event < 5min
ORDER BY user


--------------------
Ние не сме в една лодка, ние сме в една буря. Лодките са различни.

Следващият път когато се почувстваш ненужен, грозен и недооценен, помни че освен това си и тъп.
PMEmail PosterUsers Website
Top
thrawn
Публикувано на: 20-01-2021, 16:36
Quote Post



Име:
Група: Потребител
Ранг: Почетен член

Мнения: 3076
Регистриран на: 17.01.17



Не става така, having връща конкретен ред.
В твоя пример event трябва да част от group клаузата (или да се ползва в агрегатна функция). А това променя коренно условието.

---
едит

В моя вариант това
CODE
sum(sid + c) over(partition by username order by event_time) sid

е достатъчно да бъде
CODE
sum(c) over(partition by username order by event_time) sid


Това мнение е било редактирано от thrawn на 20-01-2021, 17:00
PMEmail Poster
Top
thrawn
Публикувано на: 20-01-2021, 17:26
Quote Post



Име:
Група: Потребител
Ранг: Почетен член

Мнения: 3076
Регистриран на: 17.01.17



ето примерни данни за тестове http://sqlfiddle.com/#!17/29eeb/1
PMEmail Poster
Top
wqw
Публикувано на: 22-01-2021, 11:33
Quote Post


Group Icon
Име: Владимир Висулчев
Група: VIP
Ранг: Почетен член

Мнения: 6249
Регистриран на: 10.06.04



CODE
WITH tmp1 AS (
 SELECT *
   , LEAD(event_time) OVER (PARTITION BY username ORDER BY event_time) next_event_time
   , LAG(event_time) OVER (PARTITION BY username ORDER BY event_time) prev_event_time
 FROM logs
)
, tmp2 AS (
 SELECT username, event_time
   , (ROW_NUMBER() OVER (PARTITION BY username ORDER BY event_time) + 1) / 2 AS group_id
 FROM tmp1
 WHERE EXTRACT(epoch FROM next_event_time - event_time) / 60 > 5 OR next_event_time IS NULL
   OR EXTRACT(epoch FROM event_time - prev_event_time) / 60 > 5 OR prev_event_time IS NULL
)
SELECT username
 , MIN(event_time) AS first_event_time
 , MAX(event_time) AS last_event_time
 , MAX(event_time) - MIN(event_time) AS duration
FROM tmp2  
GROUP BY username, group_id
ORDER BY 1, 2

Идеята е да синтезираш group_id за да може да групираш по него в последния SELECT.

cheers,
</wqw>


--------------------
PMEmail PosterUsers Website
Top
thrawn
Публикувано на: 22-01-2021, 16:34
Quote Post



Име:
Група: Потребител
Ранг: Почетен член

Мнения: 3076
Регистриран на: 17.01.17



То и при мен логиката е да създам поле по което в последствие да групирам - sid то.
Иначе, няма нужда да смяташ сам интервалите. Те са налични директно в idle_interval.
PMEmail Poster
Top
wqw
Публикувано на: 22-01-2021, 17:50
Quote Post


Group Icon
Име: Владимир Висулчев
Група: VIP
Ранг: Почетен член

Мнения: 6249
Регистриран на: 10.06.04



QUOTE (thrawn @ 22-01-2021, 16:34)
Иначе, няма нужда да смяташ сам интервалите. Те са налични директно в idle_interval.

Това ми се струва странно при положение, че имаш интервал от 51 години в примерните данни.

Аз помислих че интервалите са ти на база event_time на редовете т.е. интервал като понятие е между event_time на текущия event и event_time на следващия event. Поне по този начин изглеждаше резултата, не на база на idle_interval.

cheers,
</wqw>


--------------------
PMEmail PosterUsers Website
Top
thrawn
Публикувано на: 22-01-2021, 18:28
Quote Post



Име:
Група: Потребител
Ранг: Почетен член

Мнения: 3076
Регистриран на: 17.01.17



51 -години е интервал от "нулата" 01-01-1970, демек от null.
Реално погледнато мен не ме бърка числото а само това дали е по-голямо или по-малко от 5 минути.

Просто предпочетох да го направя да смята данните еднократно при записване отколкото да ги смятам всеки път при поискване.

Иначе, да интервалите са между редовете, но вече са сметнати.

Това мнение е било редактирано от thrawn на 22-01-2021, 18:29
PMEmail Poster
Top
wqw
Публикувано на: 22-01-2021, 18:53
Quote Post


Group Icon
Име: Владимир Висулчев
Група: VIP
Ранг: Почетен член

Мнения: 6249
Регистриран на: 10.06.04



Ахааа, интервала е *назад* спрямо event_time. Не знам защо не ми хрумна, може би защото никога не проектирам по този начин времеви данни. Обикновено имам start_time и elapsed, но не end_time + back_interval, което е по-лесно за логване сега се досещам.

cheers,
</wqw>

Това мнение е било редактирано от wqw на 22-01-2021, 18:54


--------------------
PMEmail PosterUsers Website
Top
1 потребители преглеждат тази тема в момента (1 гости, 0 анонимни потребители)
Потребители, преглеждащи темата в момента:

Topic Options Страници: (2) [1] 2  Reply to this topicStart new topicStart Poll

 


Copyright © 2003-2019 | BG Development | All Rights Reserved
RSS 2.0