[es] - Upit za nalaženje najveće rupe između dva datuma

jablan
Mladen Jablanović
Beograd

Član broj: 8286
Poruke: 3002
*.dynamic.sbb.rs.

Sajt: blog.radioni.ca

Profil

Upit za nalaženje najveće "rupe" između dva datuma

^{23.04.2008. u 20:16}

Imam tabelu u kojoj je jedno od polja timestamp.

Treba mi najlakši način da za neki zadati period nađem najveću razliku u vremenima između dva hronološki susedna sloga.

U pitanju je Postgres baza, ali bih preferirao opšte rešenje.

_{Probajte Ruby}

^{23.04.2008. u 20:16}

Shinhan
Assistant PHP programmer
Subotica

Član broj: 12327
Poruke: 168
*.ADSL.neobee.net.

Jabber: shinhan@elitesecurity.org
ICQ: 400847988

Profil

Re: Upit za nalaženje najveće "rupe" između dva datuma

^{23.04.2008. u 21:29}

Subquerijem uzmi datum sledećeg hronološkog sloga, time dobijaš i razliku.
E onda, ceo gornji query posmatraš kao subquery koji sortiraš po razlici datuma.
U MySQL-u:

CREATE TABLE `log_events` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`event_time` TIMESTAMP NOT NULL ,
`description` VARCHAR( 255 ) NOT NULL ,
PRIMARY KEY ( `id` )
) ENGINE = MYISAM;

SELECT le1.event_time, (SELECT MIN(le4.event_time) FROM log_events AS le4 WHERE le4.event_time > le1.event_time LIMIT 1) AS next_event_time, difference FROM
(SELECT le2.event_time, DATEDIFF((SELECT MIN(le3.event_time) FROM log_events AS le3 WHERE le3.event_time > le2.event_time LIMIT 1), le2.event_time) AS difference FROM log_events AS le2) AS le1
ORDER BY le1.difference DESC
LIMIT 1

Ne znam Postgres, ali valjda ćeš skontati iz gornjeg primera :)

"Common sense is not so common." - Voltaire

^{23.04.2008. u 21:29}

M E N E
borislav
Temerin

Član broj: 30434
Poruke: 177
80.74.165.*

Profil

Re: Upit za nalaženje najveće "rupe" između dva datuma

^{24.04.2008. u 09:54}

SELECT TOP 1 A.id
from MojaTabela as A
inner join MojaTabela as B on A.id=B.id-1
order by (B.timestamp-A.timestamp) desc

Ovo bi trebalo da odradi posao. Probao sam i kod mene radi (napravio sam tabelu sa jednom IDENTITY kolonom i sa drugom u koju sam ubacivao bezveze brojeve)
Vraca ID onog reda u kojem timestamp sa timestampom sledeceg reda (id+1) grade najvecu razlku.
NAPOMENA: kako je ovde napravljeno, prihvata se samo pozitivna razlka, tu sada moze da se igra sa ABS i bogznakakvim funkcijama u order statementu

(nesto kasnije)
DODATNI EDIT: sad sam skontao da tebi treba razlika, a ja ti dajem slog na kojem se ta razlika nalazi. Samo u SELECTU biraj (B.timestamp-A.timestamp)

_{[Ovu poruku je menjao M E N E dana 24.04.2008. u 11:13 GMT+1]}

Uhvatili ste me nespremnog

^{24.04.2008. u 09:54}

jablan
Mladen Jablanović
Beograd

Član broj: 8286
Poruke: 3002
*.adsl-a-1.sezampro.yu.

Sajt: blog.radioni.ca

Profil

Re: Upit za nalaženje najveće "rupe" između dva datuma

^{24.04.2008. u 10:30}

Hvala momci, probaću i okačiću prilagođeno Postgre rešenje, ako bude radilo. Inače, MENE, ne mogu da se oslonim na automatski generisane ID-jeve (a i inače se to izbegava), pošto hronološki redosled ne prati nužno redosled ID-jeva.

Evo Shinhan-ovo rešenje, radi i u PSQL:

Code:
SELECT le1.time, (SELECT MIN(le4.time) FROM table1 AS le4 WHERE le4.time > le1.time LIMIT 1) AS next_event_time, difference FROM
(SELECT le2.time, (SELECT MIN(le3.time) FROM table1 AS le3 WHERE le3.time > le2.time LIMIT 1) - le2.time AS difference FROM table1 AS le2) AS le1
ORDER BY le1.difference DESC
LIMIT 1 offset 1;

Morao sam da dodam OFFSET zbog toga što prvo uvek izbaci poslednji koji nema prethodni (tj. druga dva polja su mu NULL-ovi).

Još jednom hvala!

_{[Ovu poruku je menjao jablan dana 24.04.2008. u 11:48 GMT+1]}

_{Probajte Ruby}

^{24.04.2008. u 10:30}

chachka
Srđan Mijatov
Subotica

Moderator
Član broj: 53780
Poruke: 495
*.reverse.sattrakt.net.

Sajt: www.baze-podataka.net

Profil

Re: Upit za nalaženje najveće "rupe" između dva datuma

^{24.04.2008. u 11:45}

Probaj ovaj upit, na mojim podacima radi brže, a i logički mi je jednostavniji ;)

Code:
SELECT l.time, MAX(r.time) AS next_event_time, l.time - MAX(r.time) AS difference
  FROM table1 AS l
       LEFT OUTER JOIN
       table1 AS r
         ON l.time > r.time
GROUP BY l.time
ORDER BY 3 DESC
LIMIT 1

"The best code is no code at all." - Zidar

^{24.04.2008. u 11:45}

Fitopatolog
Dušan Marjanov
Novi Sad

Član broj: 90936
Poruke: 320
79.101.223.*

Profil

Re: Upit za nalaženje najveće "rupe" između dva datuma

^{25.04.2008. u 11:25}

Evo još jedan upit, sa vrlo osnovnim SQL naredbama:

SELECT a1.ID, a1.dat, b1.ID, b1.dat, a1.dat-b1.dat
FROM abc AS a1, abc AS b1
GROUP BY a1.ID, a1.dat, b1.ID, b1.dat, a1.dat-b1.dat
HAVING a1.dat-b1.dat =(
SELECT max( a.dat-b.dat)
FROM abc AS a, abc AS b
WHERE b.dat=(select max(c.dat) from abc c where c.dat < a.dat)
);

:-)

U gornjem upitu nedostaje jedan red (čudno da niko nije primetio grešku!) tako da upit vraća, osim ispravnih podataka, bilo koji par datuma ako je broj dana između njih jednak maksimalnoj praznini između 2 datuma. Korektan upit je:

SELECT a1.ID, a1.dat, b1.ID, b1.dat, a1.dat-b1.dat
FROM abc AS a1, abc AS b1
WHERE b1.dat=(select max(c.dat) from abc c where c.dat < a1.dat)
GROUP BY a1.ID, a1.dat, b1.ID, b1.dat, a1.dat-b1.dat
HAVING a1.dat-b1.dat =(
SELECT max( a.dat-b.dat)
FROM abc AS a, abc AS b
WHERE b.dat=(select max(c.dat) from abc c where c.dat < a.dat)
);

_{[Ovu poruku je menjao Fitopatolog dana 27.04.2008. u 10:12 GMT+1]}

Bolje prirodna glupost nego veštačka inteligencija.

^{25.04.2008. u 11:25}

Fitopatolog
Dušan Marjanov
Novi Sad

Član broj: 90936
Poruke: 320
79.101.223.*

Profil

Re: Upit za nalaženje najveće "rupe" između dva datuma

^{25.04.2008. u 11:29}

Citat:

chachka: Probaj ovaj upit, na mojim podacima radi brže, a i logički mi je jednostavniji ;)

Umesto levog spajanja može se slobodno staviti INNER JOIN, brže će da radi.

Bolje prirodna glupost nego veštačka inteligencija.

^{25.04.2008. u 11:29}

chachka
Srđan Mijatov
Subotica

Moderator
Član broj: 53780
Poruke: 495
*.ADSL.neobee.net.

Sajt: www.baze-podataka.net

Profil

Re: Upit za nalaženje najveće "rupe" između dva datuma

^{29.04.2008. u 12:43}

Hm... čini mi se da izbor između OUTER ili INNER JOIN-a zavisi od verzije servera. Čini mi se da ima veze sa NULL-om zbog kojeg je i Jablan morao da stavi OFFSET. Nisam baš siguran u ovo što pišem (mrzi me da sada podižem različite servere), ali ću sutra imati priliku da testiram pa ću se javiti.

"The best code is no code at all." - Zidar

^{29.04.2008. u 12:43}

Fitopatolog
Dušan Marjanov
Novi Sad

Član broj: 90936
Poruke: 320
79.101.193.*

Profil

Re: Upit za nalaženje najveće "rupe" između dva datuma

^{29.04.2008. u 15:23}

Outer join vraća više slogova nego inner join i to utiče na usporenje.

Usput, vidim da se traži samo max. broj dana, bez ID slogova koji sadrže početni i krajnji datum. U tom slučaju upit je krajnje trivijalan:

SELECT max( a.dat-b.dat)
FROM abc AS a, abc AS b
WHERE b.dat=(select max(c.dat) from abc c where c.dat < a.dat)

Bolje prirodna glupost nego veštačka inteligencija.

^{29.04.2008. u 15:23}

chachka
Srđan Mijatov
Subotica

Moderator
Član broj: 53780
Poruke: 495
*.ADSL.neobee.net.

Sajt: www.baze-podataka.net

Profil

Re: Upit za nalaženje najveće "rupe" između dva datuma

^{29.04.2008. u 23:06}

A može i upit koji kroz datu tabelu "prolazi" samo 2 puta

Code:
SELECT MAX(t.time - (SELECT MAX(time) FROM table1 WHERE time < t.time))
FROM table1 AS t

"The best code is no code at all." - Zidar

^{29.04.2008. u 23:06}

Fitopatolog
Dušan Marjanov
Novi Sad

Član broj: 90936
Poruke: 320
79.101.193.*

Profil

Re: Upit za nalaženje najveće "rupe" između dva datuma

^{30.04.2008. u 06:57}

Misliš n puta ili (n**2 + 1) slogova, n je broj slogova u tabeli? Ako je datumsko polje indeksirano, ovo nije problem.

_{[Ovu poruku je menjao Fitopatolog dana 30.04.2008. u 09:42 GMT+1]}

Bolje prirodna glupost nego veštačka inteligencija.

^{30.04.2008. u 06:57}

chachka
Srđan Mijatov
Subotica

Moderator
Član broj: 53780
Poruke: 495
*.ADSL.neobee.net.

Sajt: www.baze-podataka.net

Profil

Re: Upit za nalaženje najveće "rupe" između dva datuma

^{30.04.2008. u 08:43}

Ne, nisam mislio na složenost algoritma - zato sam i stavio reč prolazi pod navodnike.

"The best code is no code at all." - Zidar

^{30.04.2008. u 08:43}

Fitopatolog
Dušan Marjanov
Novi Sad

Član broj: 90936
Poruke: 320
79.101.193.*

Profil

Re: Upit za nalaženje najveće "rupe" između dva datuma

^{30.04.2008. u 21:25}

Kakogod, ali tvoj upit ima n prolaza kroz tabelu.

Bolje prirodna glupost nego veštačka inteligencija.

^{30.04.2008. u 21:25}