Srki, u pravu si da moje resenje nije ispravno. A tema se rasplamsala bez obzira na tvoje elegantno resenje.
Mrzelo me je da odredujem tacan kraj perioda jer sam mislio da nema uticaja na konacan rezultat, ali sam se prevario.
Da bih istrajao u svojoj ideji upotrebe OVERLAPS operacije, ipak moram odrediti skup perioda ciji je presek prazan skup, a unija skup [MIN(start_time), MAX(end_time)).
To dodatno komplikuje upit, koji sada izgleda:
Code:
SELECT p_max_number - COALESCE(MAX(i.maksimalan_broj_musterija), 0)
FROM (SELECT i3.start_time AS start_time,
i3.end_time AS end_time,
COALESCE(SUM(vr.broj_musterija_koje_customer_dovodi), 0) AS maksimalan_broj_musterija
FROM (SELECT MAX(i2.point_in_time) AS start_time, i1.point_in_time AS end_time
FROM (SELECT start_time AS point_in_time
FROM vip_restoran
UNION
SELECT end_time AS point_in_time
FROM vip_restoran
) AS i1
INNER JOIN
(SELECT start_time AS point_in_time
FROM vip_restoran
UNION
SELECT end_time AS point_in_time
FROM vip_restoran
) AS i2
ON i1.point_in_time > i2.point_in_time
GROUP BY i1.point_in_time
) AS i3
LEFT OUTER JOIN
vip_restoran AS vr
ON (i3.start_time, i3.end_time) OVERLAPS (vr.start_time, vr.end_time)
GROUP BY i3.start_time, i3.end_time
) AS i
WHERE (i.start_time, i.end_time) OVERLAPS (TIMESTAMP p_start_time, TIMESTAMP p_end_time)
Ovaj upit je proveren na PostgreSQL 8.1, koji ne podrzava OVER.
Kako izgleda Srkijev upit bez upotrebe OVER-a, to jest sta taj OVER u pozadini radi?
"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