MySQL - Pomoć sa upitom

Ovako, da pojednostavnim...

Imam 2 tabele (Apartman one to many Zauzetost)

Apartman

ApartmanID (int) (primary)

Naziv (text)

Zauzetost

ZauzetostID (int) (primary)

ApartmanID (int)

Od (DATETIME)

Do (DATETIME)

Apartman je... jel, apartman, dok se u tabeli Zauzetost nalaze vec postojece rezervacije.

U aplikaciji imam pretragu apartmana na nacin da korisnik unese period od kad do kad on zeli rezervirati apartman u tom hotelu i trebalo bi mu izbaciti sve apartmane koji su slobodni, odnosno, period koji je korisnik unio ne poklapa se sa nitijednim periodom koji se vec nalazi u tabeli.

Ovo sam rijesio na jedan glup, kompliciran i nadasve ruzan nacin i nisam zadovoljan. Trebao bih pomoc da ovo napisem po P.S.u.

Dakle, parametre koje SQL upit prima su pocetak i kraj nove rezervacije tipa DATETIME i treba vratiti sve apartmane koji su slobodni.

Unaprijed hvala!

Kako napraviti da provjerava da se NE preklapaju. Lako mi je pogledati da li "podskup" klijentov upit spada u skup "postojece rezervacije". Kako rijesiti da vrati one koji NE spadaju.

Znaci to bi bilo jednako ovome

SELECT <params> FROM Apartman INNER JOIN Zauzetost ON Apartman.ApartmanID = Zauzetost.ZauzetostID WHERE NOT (Zauzetost.Od <= KorisnikOD AND Zauzetost.Do >= KorisnikDO) GroupBy Apartman

???

EDIT: Mrkač... nisam ovo pokusao, ali budem...

Ja uopce nisam stovatelj SQL-a kao takvog, razmazio me LINQ i EF :))))))

EDIT: Dobio sam nekakav projekt za modifikaciju nekakve web stranice - ASP + mySQL... Covjeku dodje za plakat kad se vrati sa ASP.Net-a na ovo :(((((( 

Daj plz napisi upit...

Zapravo nisu bas isti query-ji. Mrkacev query ce vratit sve apartmane koji nisu zauzeti u tom razdoblju, a tnakirov ce vratit sve apartmane koji nisu zauzeti u tom razdoblju, ali su zauzeti u nekom razdoblju (imaju barem jedan redak) u tablici zauzetost.
Da bi tnakirov query ispravno radio trebalo bi zamjenit inner join sa left joinom i dodat u uvjet da polja zauzetost.od i zauzetost.do mogu bit null ovako:
SELECT Apartman.*
 FROM Apartman
 LEFT JOIN Zauzetost ON Zauzetost.ApartmanID = Zauzetost.ApartmanID
WHERE NOT (Zauzetost.OD <= KorisnikOD AND Zauzetost.DO >= KorisnikDO)
 OR ( Zauzetost.Od is NULL AND Zauzetost.DO IS NULL)
GROUPBY Apartman.ApartmanID

Ovaj query ce vratit iste rezultate kao mrkacev, ali je potencijalno brzi jer ne koristi subselect. Ako optimizator ne primjeti da je subselect u WHERE dijelu mrkacevog querija isti za svaki redak tablice apartman izvrsit ce ga ponovno
za svaki apartman.

Kako bi to izveo?

Taj tvoj query vratit će mu kupus tj. sve apartmane. Po kojoj logici si ti slagao i čemu LEFT JOIN? Znaš li ti što znači LEFT, a što INNER join?

Nema tog optimizatora koji će okrenuti subselect za svaki apartman u upitu, a subselecti su redovito u praksi brži od joina.

Okey, nisam vidio da modificiraš.

A ništa, join i zdravo.

U ovoj tabeli di su zauzetosti se automatski dodaju rezervacije cim se plate, a takodjer, vlasnik moze sam dodavati nekakve custom retzervacije koje je on ostvario van ove aplikacije. Mislim da je ovo najefikasniji i najbolji nacin na koji se to moglo rijesiti.

Opet ti o nekom nepoznavanju. Nije fer da tako razgovaraš i da si bezobrazan, posebno nakon ovog upita. Mislim, nije mi baš jasna tvoja logika ni odakle crpiš to?

Bazu sam kreirao tako da približava ono što je tnakir opisao.

Baza izgleda ovako (s mojim "malim" podacima).

USE [apartmanitest
GO
/****** Object:  Table [dbo].[apartman]    Script Date: 05/09/2011 13:16:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[apartman](
    [apartmanid] [int] IDENTITY(1,1) NOT NULL,
    [apartmanname] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_apartmani] PRIMARY KEY CLUSTERED
(
    [apartmanid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[apartman] ON
INSERT [dbo].[apartman] ([apartmanid], [apartmanname]) VALUES (1, N'apartman001')
INSERT [dbo].[apartman] ([apartmanid], [apartmanname]) VALUES (2, N'apartman002')
INSERT [dbo].[apartman] ([apartmanid], [apartmanname]) VALUES (3, N'apartman003')
INSERT [dbo].[apartman] ([apartmanid], [apartmanname]) VALUES (4, N'apartman004')
INSERT [dbo].[apartman] ([apartmanid], [apartmanname]) VALUES (5, N'apartman005')
INSERT [dbo].[apartman] ([apartmanid], [apartmanname]) VALUES (6, N'apartman006')
INSERT [dbo].[apartman] ([apartmanid], [apartmanname]) VALUES (7, N'apartman007')
INSERT [dbo].[apartman] ([apartmanid], [apartmanname]) VALUES (8, N'apartman008')
INSERT [dbo].[apartman] ([apartmanid], [apartmanname]) VALUES (9, N'apartman009')
INSERT [dbo].[apartman] ([apartmanid], [apartmanname]) VALUES (10, N'apartman010')
SET IDENTITY_INSERT [dbo].[apartman] OFF
/****** Object:  Table [dbo].[zauzetost]    Script Date: 05/09/2011 13:16:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[zauzetost](
    [zauzetostid] [int] IDENTITY(1,1) NOT NULL,
    [apartmanid] [int] NOT NULL,
    [od] [date] NOT NULL,
    [do] [date] NOT NULL,
 CONSTRAINT [PK_rezervacije] PRIMARY KEY CLUSTERED
(
    [zauzetostid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[zauzetost] ON
INSERT [dbo].[zauzetost] ([zauzetostid], [apartmanid], [od], [do]) VALUES (3, 1, CAST(0xCC330B00 AS Date), CAST(0xD1330B00 AS Date))
INSERT [dbo].[zauzetost] ([zauzetostid], [apartmanid], [od], [do]) VALUES (4, 1, CAST(0xD1330B00 AS Date), CAST(0xD6330B00 AS Date))
INSERT [dbo].[zauzetost] ([zauzetostid], [apartmanid], [od], [do]) VALUES (5, 2, CAST(0xBD330B00 AS Date), CAST(0xC0330B00 AS Date))
INSERT [dbo].[zauzetost] ([zauzetostid], [apartmanid], [od], [do]) VALUES (6, 3, CAST(0xB9330B00 AS Date), CAST(0xC7330B00 AS Date))
INSERT [dbo].[zauzetost] ([zauzetostid], [apartmanid], [od], [do]) VALUES (7, 5, CAST(0xD3330B00 AS Date), CAST(0xD6330B00 AS Date))
INSERT [dbo].[zauzetost] ([zauzetostid], [apartmanid], [od], [do]) VALUES (8, 6, CAST(0xBF330B00 AS Date), CAST(0xC4330B00 AS Date))
INSERT [dbo].[zauzetost] ([zauzetostid], [apartmanid], [od], [do]) VALUES (9, 9, CAST(0xBA330B00 AS Date), CAST(0xCE330B00 AS Date))
SET IDENTITY_INSERT [dbo].[zauzetost] OFF
/****** Object:  ForeignKey [FK_rezervacije_apartmani]    Script Date: 05/09/2011 13:16:08 ******/
ALTER TABLE [dbo].[zauzetost]  WITH CHECK ADD  CONSTRAINT [FK_rezervacije_apartmani] FOREIGN KEY([apartmanid])
REFERENCES [dbo].[apartman] ([apartmanid])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[zauzetost] CHECK CONSTRAINT [FK_rezervacije_apartmani]
GO

Skripte koje su nam na raspolaganju, tvoja skripta, tnakirova skripta, pa zatim moja i mrkačeva izgledaju ovako:

--blakcov
-- SELECT Apartman.apartmanid FROM Apartman LEFT JOIN Zauzetost ON Zauzetost.ApartmanID = Zauzetost.ApartmanID WHERE NOT (Zauzetost.OD <= '1/12/2011' AND Zauzetost.DO >= '1/22/2011') OR ( Zauzetost.Od is NULL AND Zauzetost.DO IS NULL) group by apartman.apartmanid

--tnakirov
-- SELECT apartman.apartmanid FROM Apartman INNER JOIN Zauzetost ON Apartman.ApartmanID = Zauzetost.ZauzetostID WHERE NOT (Zauzetost.Od <= '1/12/2011' AND Zauzetost.Do >= '1/22/2011') group by apartman.apartmanid

--moj
-- select apartman.apartmanid from apartman where apartmanid not in (select apartmanid from zauzetost where '1/12/2011' <= do AND od <= '1/22/2011')

--mrkačev
-- select ApartmanId from apartman where ApartmanId not in (select apartmanId from Zauzetost where od<='1/22/2011' and do>='1/12/2011')

E sad, ja sam unio ručno par termina i izvrtio skripte.

Ovako je to izgledalo u praksi za male podatke:

Sadržaj:

apartmanid  apartmanname
----------- --------------------------------------------------
1           apartman001
2           apartman002
3           apartman003
4           apartman004
5           apartman005
6           apartman006
7           apartman007
8           apartman008
9           apartman009
10          apartman010

(10 row(s) affected)
(1 row(s) affected)
zauzetostid apartmanid  od         do
----------- ----------- ---------- ----------
3           1           2011-01-20 2011-01-25
4           1           2011-01-25 2011-01-30
5           2           2011-01-05 2011-01-08
6           3           2011-01-01 2011-01-15
7           5           2011-01-27 2011-01-30
8           6           2011-01-07 2011-01-12
9           9           2011-01-02 2011-01-22

(7 row(s) affected)

A ovako to izgleda kad izvršimo upite za skripte:

--blakcov
 SELECT Apartman.apartmanid FROM Apartman LEFT JOIN Zauzetost ON Zauzetost.ApartmanID = Zauzetost.ApartmanID WHERE NOT (Zauzetost.OD <= '1/12/2011' AND Zauzetost.DO >= '1/22/2011') OR ( Zauzetost.Od is NULL AND Zauzetost.DO IS NULL) group by apartman.apartmanid

--tnakirov
 SELECT apartman.apartmanid FROM Apartman INNER JOIN Zauzetost ON Apartman.ApartmanID = Zauzetost.ZauzetostID WHERE NOT (Zauzetost.Od <= '1/12/2011' AND Zauzetost.Do >= '1/22/2011') group by apartman.apartmanid

--moj
 select apartman.apartmanid from apartman where apartmanid not in (select apartmanid from zauzetost where '1/12/2011' <= do AND od <= '1/22/2011')

--mrkačev
 select ApartmanId from apartman where ApartmanId not in (select apartmanId from Zauzetost where od<='1/22/2011' and do>='1/12/2011')

apartmanid
-----------
1
2
3
4
5
6
7
8
9
10

(10 row(s) affected)
(1 row(s) affected)

apartmanid
-----------
3
4
5
6
7
8

(6 row(s) affected)
(1 row(s) affected)

apartmanid
-----------
2
4
5
7
8
10

(6 row(s) affected)
(1 row(s) affected)

ApartmanId
-----------
2
4
5
7
8
10

(6 row(s) affected)
(1 row(s) affected)

Onda sam krenuo probati kako to izgleda na većem setu podataka jer mi je tvoj upit smrdio. Dakle, pomoću posebnog inhouse alata u bazu sam unio 10.000 apartmana i 120.000 rezervacija po posve slučajnom sistemu.

Izvršavanje tvoje skripte diglo je temperaturu moje radne stanice na 65°C (s alarmom o temperaturi) iako u normalnom radu ne prelazi 40°C. Izvršavanje je trajalo 88 sekundi, a vratilo je 10.000 apartmana, točno onoliko apartmana koliko se i nalazi u bazi podataka.

Tnakirov upit trajao je 48-7 milisekundi i vratio je 9691 apartman.

Moj upit je trajao 20-16 milisekundi i vratio je 4883 apartmana.

Mrkačev je trajao 21-19 milisekundu i vratio je 4883 apartmana.

4883 apartmana je otprilike i logičan statistički rezultat u saturiranoj bazi nakon generatora.

Nisam mogao vjerovati kakvo si to smeće napisao pa sam OPET pokrenuo tvoj upit i trajao je jednakih 88 sekundi. Na stranu to što ni tvoj ni tnakirov upit ne daju točne rezultate, ti si uspio što sam mislio da je nemoguće i što nije uspjela konverzija 4 FHD videa paralelno: uspio si i7-975 @4.6GHz s 4 jezgre i 4 virtualne jezgre doslovno priklati na jednu i pol minutu i dignuti mi alarm termosenzora na 65°C što nije uspio ni Crysis 2 na 2560 i max detaljima.

Svaka ti čast, ako nešto znaš to je db programiranje i kako rade baze. Svakako preporučam tnakiru da upotrijebi tvoju, optimiziranu verziju, jer MS SQL Server i njegov optimizator očito jako vole tvoj stil jer je tvoj execution plan na SQL Serveru uvjerljivo najkompleksniji i optimizator je sasvim prozviždao izvodeći svašta ludo nad podacima da bi izveo tvoj upit koji, da bude gore, uvijek vrati sve apartmane. Možda prođeš kao marketing Intelu i AMD-u: "trebamo snažnija računala za apartmanske trivijalnosti!".

Mislim, nemoj se uvrijediti, ja sam samo zdravo-seljački testirao sve navedeno s nikakvim lošim namjerama.

OK, imaš bug (baš ti je simpa bio), ali hoćeš reći da je ovo bolje:

--blakcov
 SELECT Apartman.apartmanid FROM Apartman LEFT JOIN Zauzetost ON apartman.apartmanId = zauzetost.apartmanId WHERE NOT (Zauzetost.OD <= '1/12/2011' AND Zauzetost.DO >= '1/22/2011') OR ( Zauzetost.Od is NULL AND Zauzetost.DO IS NULL) group by apartman.apartmanid

I dalje vraćaš:

apartmanid
-----------
1
2
3
4
5
6
7
8
10

(9 row(s) affected)

Kako to odgovara upit između 12. i 22. siječnja 2011 za ove podatke:

zauzetostid apartmanid  od         do
----------- ----------- ---------- ----------
3           1           2011-01-20 2011-01-25
4           1           2011-01-25 2011-01-30
5           2           2011-01-05 2011-01-08
6           3           2011-01-01 2011-01-15
7           5           2011-01-27 2011-01-30
8           6           2011-01-07 2011-01-12
9           9           2011-01-02 2011-01-22

(7 row(s) affected)

Vratio si apartmane koji su zauzeti unutar traženog raspona.

I blakc je zaslužio pivo :) Samo da popravi ovaj rezultat. Ono u mojoj analizi je bug u njegovom uvjetu, pa ne traje toliko. Sada je bitno vidjeti kako blakc optimizira upit da daje apartmane koji nemaju rezervaciju unutar traženog raspona. Mrkačev i moj su praktički isti, a Blakcov je baziran na JOIN-u i navodno je brži.

Kad dobijemo iste rezultate, onda ću rado pokrenuti test da vidimo brzinu :)

Nema razlog da oblokavanje pivom ne zakači i blakca, barem će manje bugova pisati pijan. :P

---

Da dodam: Blakcova skripta se u prosjeku (nad velikim setom podataka) vrti 65ms, dok se moja vrti 16ms. Inače, Blakc je u pravu kad kaže da je u teoriji subquery može ići za svaki red outer joina, ali meni se u praksi u ovom slučaju (ne vraćamo iste rezultate, oprez) pokazuje bržim subquery, nego join. Ja subquery koristim s virtualnim tablicama, ne direktno ovako. Postoji i velika mogućnost da server u praksi interno pretvara ove subqueryje u join. U nekim slučajevima mi se pokazalo (malo sam raširio test) da je subquery brži, a u nekima obrnuto, da je brži join. Stvar ovisi o dosta toga (i kompleksnosti također). Zapravo, možda će u MySQL-u rezultat biti bitno drugačiji (ja koristim SQL Server 2008R2).

Svakako, mislim da je malo nezahvalno nadmetati se i svađati oko ovoga. Tema je vrlo promjenjiva.

Skužio si. Vraća i one koji barem jednom spadaju pod validne, jer nije svjestan iteracije kroz druge rezervacije. Takav upit će vratit i apartman #1 iz mog seta podataka. Zato i jest tu :)

E sad, probaj s joinom riješit taj problem. :)