BG Development Форуми -> Групиране на данни по интервал

DEV.BG/Jobs: Най-големият IT job board в България

BG Development · За реклама · За контакти

Помощ

Търсене

Потребители

Календар

Правила

Здравей! ( Включване | Регистриране )

BG Development Форуми -> Програмиране -> СУБД, SQL програмиране

Страници: (2) [1] 2 ( Първото ново мнение )

Групиране на данни по интервал

Абонирай се за темата | Изпрати адреса на темата на приятел | Принтирай темата

thrawn

Публикувано на: 20-01-2021, 15:23

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

Мнения: 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: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

thrawn

Публикувано на: 20-01-2021, 15:54

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

Мнения: 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;

ici

Публикувано на: 20-01-2021, 16:05

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

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

CODE

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

--------------------

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

Следващият път когато се почувстваш ненужен, грозен и недооценен, помни че освен това си и тъп.

thrawn

Публикувано на: 20-01-2021, 16:36

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

Мнения: 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

thrawn	Публикувано на: 20-01-2021, 17:26
Име: Група: Потребител Ранг: Почетен член Мнения: 3076 Регистриран на: 17.01.17	ето примерни данни за тестове http://sqlfiddle.com/#!17/29eeb/1

wqw

Публикувано на: 22-01-2021, 11:33

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

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

http://sqlfiddle.com/#!17/0d118c/26

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>

--------------------

Latest in Articles: How to use fail2ban with Terminal Servers (RDSH) farm
ERP, лесно е! Блог за Dreem Personal
Unattended Make My Manifest: Registration-Free COM manifests for VB6 applications

thrawn	Публикувано на: 22-01-2021, 16:34
Име: Група: Потребител Ранг: Почетен член Мнения: 3076 Регистриран на: 17.01.17	То и при мен логиката е да създам поле по което в последствие да групирам - sid то. Иначе, няма нужда да смяташ сам интервалите. Те са налични директно в idle_interval.

wqw

Публикувано на: 22-01-2021, 17:50

Име: Владимир Висулчев
Група: 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>

--------------------

thrawn	Публикувано на: 22-01-2021, 18:28
Име: Група: Потребител Ранг: Почетен член Мнения: 3076 Регистриран на: 17.01.17	51 -години е интервал от "нулата" 01-01-1970, демек от null. Реално погледнато мен не ме бърка числото а само това дали е по-голямо или по-малко от 5 минути. Просто предпочетох да го направя да смята данните еднократно при записване отколкото да ги смятам всеки път при поискване. Иначе, да интервалите са между редовете, но вече са сметнати. Това мнение е било редактирано от thrawn на 22-01-2021, 18:29

wqw	Публикувано на: 22-01-2021, 18:53
Име: Владимир Висулчев Група: VIP Ранг: Почетен член Мнения: 6249 Регистриран на: 10.06.04	Ахааа, интервала е назад спрямо event_time. Не знам защо не ми хрумна, може би защото никога не проектирам по този начин времеви данни. Обикновено имам start_time и elapsed, но не end_time + back_interval, което е по-лесно за логване сега се досещам. cheers, </wqw> Това мнение е било редактирано от wqw на 22-01-2021, 18:54 -------------------- Latest in Articles: How to use fail2ban with Terminal Servers (RDSH) farm ERP, лесно е! Блог за Dreem Personal Unattended Make My Manifest: Registration-Free COM manifests for VB6 applications

1 потребители преглеждат тази тема в момента (1 гости, 0 анонимни потребители)

Потребители, преглеждащи темата в момента:

« Предишна тема | СУБД, SQL програмиране | Следваща тема »

Страници: (2) [1] 2

BG Development Форуми -> Програмиране -> СУБД, SQL програмиране