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

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.

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 :)

Potpis

"Common sense is not so common." - Voltaire

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)

Potpis

Uhvatili ste me nespremnog

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:

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!

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

Potpis

"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

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)
);

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

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.

Potpis

"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

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)

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

Potpis

"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

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

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

Potpis

"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