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

jablan

Član broj: 8286
Poruke: 4541

+711 Profil

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

^{23.04.2008. u 20:16 - pre 194 meseci}

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.

Odgovor na temu

Shinhan
PHP programmer
Subotica

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

Jabber: shinhan@elitesecurity.org
ICQ: 400847988

+4 Profil

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

^{23.04.2008. u 21:29 - pre 194 meseci}

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

Odgovor na temu

M E N E
borislav
Temerin

Član broj: 30434
Poruke: 231
80.74.165.*

+1 Profil

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

^{24.04.2008. u 09:54 - pre 194 meseci}

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

Odgovor na temu

jablan

Član broj: 8286
Poruke: 4541

+711 Profil

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

^{24.04.2008. u 10:30 - pre 194 meseci}

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]}

Odgovor na temu

chachka
Srđan Mijatov
Programer
BUS Computers
Kikinda

Član broj: 53780
Poruke: 576
*.reverse.sattrakt.net.

Sajt: www.baze-podataka.net

+4 Profil

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

^{24.04.2008. u 11:45 - pre 194 meseci}

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 (ES član)
"Biggest obstacle to learning
SQL is unlearning procedural
programming." - Joe
Celko
"Minimize code, maximize data."
- A. Neil Pappalardo

Odgovor na temu

Fitopatolog
Dušan Marjanov
Novi Sad

Član broj: 90936
Poruke: 683
79.101.223.*

+3 Profil

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

^{25.04.2008. u 11:25 - pre 194 meseci}

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]}

Odgovor na temu

Fitopatolog
Dušan Marjanov
Novi Sad

Član broj: 90936
Poruke: 683
79.101.223.*

+3 Profil

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

^{25.04.2008. u 11:29 - pre 194 meseci}

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.

Odgovor na temu

chachka
Srđan Mijatov
Programer
BUS Computers
Kikinda

Član broj: 53780
Poruke: 576
*.ADSL.neobee.net.

Sajt: www.baze-podataka.net

+4 Profil

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

^{29.04.2008. u 12:43 - pre 194 meseci}

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 (ES član)
"Biggest obstacle to learning
SQL is unlearning procedural
programming." - Joe
Celko
"Minimize code, maximize data."
- A. Neil Pappalardo

Odgovor na temu

Fitopatolog
Dušan Marjanov
Novi Sad

Član broj: 90936
Poruke: 683
79.101.193.*

+3 Profil

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

^{29.04.2008. u 15:23 - pre 194 meseci}

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)

Odgovor na temu

chachka
Srđan Mijatov
Programer
BUS Computers
Kikinda

Član broj: 53780
Poruke: 576
*.ADSL.neobee.net.

Sajt: www.baze-podataka.net

+4 Profil

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

^{29.04.2008. u 23:06 - pre 194 meseci}

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 (ES član)
"Biggest obstacle to learning
SQL is unlearning procedural
programming." - Joe
Celko
"Minimize code, maximize data."
- A. Neil Pappalardo

Odgovor na temu

Fitopatolog
Dušan Marjanov
Novi Sad

Član broj: 90936
Poruke: 683
79.101.193.*

+3 Profil

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

^{30.04.2008. u 06:57 - pre 194 meseci}

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]}

Odgovor na temu

chachka
Srđan Mijatov
Programer
BUS Computers
Kikinda

Član broj: 53780
Poruke: 576
*.ADSL.neobee.net.

Sajt: www.baze-podataka.net

+4 Profil

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

^{30.04.2008. u 08:43 - pre 194 meseci}

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

"The best code is no code at all."
- Zidar (ES član)
"Biggest obstacle to learning
SQL is unlearning procedural
programming." - Joe
Celko
"Minimize code, maximize data."
- A. Neil Pappalardo

Odgovor na temu

Fitopatolog
Dušan Marjanov
Novi Sad

Član broj: 90936
Poruke: 683
79.101.193.*

+3 Profil

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

^{30.04.2008. u 21:25 - pre 194 meseci}

Kakogod, ali tvoj upit ima n prolaza kroz tabelu.

Odgovor na temu