Použití CROSS JOIN

Dnes jsem měl poprvé za celou svou praxi příležitost použít křížové spojení pomocí CROSS JOIN. Tento druh spojení vytvoří kombinace všech řádků ze spojovaných tabulek. Potřeboval jsem sestavit jakýsi kalendář obsazenosti jednotlivých místností. Vytvořil jsem si tabulku pokojů, tabulku rezervací, ale problém nastal s vlastním kalendářem. Ani MySQL, ani PostgreSQL nemají možnost vypsání všech datumů v určitém intervalu pomocí jednoduchého SELECTu.

Na úvod jsem si vytvořil tabulku pokojů:

  1. CREATE TABLE pokoje (
  2.      pokoj_cislo INTEGER,
  3.      pokoj_oznaceni VARCHAR(32),
  4.      pokoj_pocetluzek INTEGER,
  5.      pokoj_pocetpristylek INTEGER,
  6.      PRIMARY KEY (pokoj_cislo)
  7. );

V dalším kroku si vytvořím tabulku číselníků a jejich položek a uložím do ní několik záznamů:

  1. CREATE TABLE ciselniky (
  2. 	ciselnik_cislo INTEGER,
  3. 	ciselnik_nazev VARCHAR(32),
  4. 	PRIMARY KEY (ciselnik_cislo)
  5. );
  6.  
  7. CREATE TABLE polozky(
  8. 	polozka_cislo INTEGER,
  9. 	polozka_ciselnik INTEGER,
  10. 	polozka_nazev VARCHAR(64),
  11. 	polozka_hodnota TEXT,
  12. 	PRIMARY KEY (polozka_cislo),
  13. 	FOREIGN KEY (polozka_ciselnik) REFERENCES ciselniky(ciselnik_cislo)
  14. 	ON DELETE CASCADE ON UPDATE CASCADE
  15. );
  16. INSERT INTO ciselniky VALUES (1, 'Stavy pobytu');
  17. INSERT INTO polozky VALUES (0, 1, 'Volný', '255,255,255');
  18. INSERT INTO polozky VALUES (1, 1, 'Počátek pobytu', '255,255,0');
  19. INSERT INTO polozky VALUES (2, 1, 'Celodenní pobyt', '255, 0, 255'); 
  20. INSERT INTO polozky VALUES (3, 1, 'Konec pobytu', '0, 255, 255');

Mimochodem číselníky jsou velmi užitečná věc :-)
V dalších krocích vytvořím tabulky objednávek:

  1. CREATE TABLE objednavky (
  2. 	objednavka_cislo INTEGER,
  3. 	objednavka_objednavajici VARCHAR(64),
  4. 	objednavka_datum DATE,
  5. 	objednavka_potvrzeno DATE,
  6. 	objednavka_odsouhlaseno DATE,
  7. 	PRIMARY KEY (objednavka_cislo)
  8. );

A tabulky vlastních rezervací:

  1. CREATE TABLE rezervace (
  2. 	rezervace_pokoj INTEGER,
  3. 	rezervace_druh INTEGER,
  4. 	rezervace_objednavka INTEGER,
  5. 	rezervace_datum DATE,
  6. 	rezervace_cena NUMERIC(12,2), 
  7. 	FOREIGN KEY (rezervace_pokoj) REFERENCES pokoje(pokoj_cislo) ON DELETE CASCADE ON UPDATE CASCADE,
  8. 	FOREIGN KEY (rezervace_druh) REFERENCES polozky(polozka_cislo) ON DELETE CASCADE ON UPDATE CASCADE
  9. );

Základní tabulky bych měl, ale vyvstává problém s kalendářem. Jak na něj? Nabízí se několik řešení, mě napadly dvě:

  1. Vytvořit tabulku datumů
  2. Vytvořit funkci, generující datumy v určitém intervalu

Výhodou prvního řešení je fakt, že je poměrně jednoduché a snadno přenositelné. Nevýhodou je to, že je pro každý den v roce nutné vytvořit záznam, takže pro cca 20 let to je bratru 7300 záznamů. Druhé řešení je poměrně elegantní, bohužel je také platformově závislé. Následující funkce je určena pro PostgreSQL (verze 7.4).

  1. CREATE OR REPLACE FUNCTION dnyvintervalu(DATE, DATE) RETURNS SETOF DATE AS '
  2. DECLARE
  3. startdate ALIAS FOR $1;
  4. enddate ALIAS FOR $2;
  5. st date;
  6. BEGIN
  7. 	st := startdate;
  8. 	WHILE st < enddate + 1 LOOP
  9. 		RETURN NEXT st;
  10. 		st := st + 1;
  11. 	END LOOP;
  12. 	RETURN;
  13. END;
  14. ' LANGUAGE plpgsql;

Nyní mám vytvořenou funkci, která mi vypíše jednotlivé dny v daném rozmezí. Převést ji do T-SQL nebo do MySQL nebude takový problém.

  1. SELECT dnyvintervalu FROM dnyvintervalu('2005-5-2','2005-5-5');

dnyvintervalu
2005-5-2
2005-5-3
2005-5-4
2005-5-5

Mám funkce, mám tabulky, teď ještě doplním nějaká data o pokojích a o některých rezervacích.

  1. INSERT INTO pokoje (pokoj_cislo, pokoj_oznaceni, pokoj_pocetluzek) VALUES (101, 'p101', 2);
  2. INSERT INTO pokoje (pokoj_cislo, pokoj_oznaceni, pokoj_pocetluzek) VALUES (102, 'p102', 3);
  3. INSERT INTO pokoje (pokoj_cislo, pokoj_oznaceni, pokoj_pocetluzek) VALUES (103, 'p103', 3);
  4. INSERT INTO pokoje (pokoj_cislo, pokoj_oznaceni, pokoj_pocetluzek) VALUES (104, 'p104', 2);
  5. INSERT INTO pokoje (pokoj_cislo, pokoj_oznaceni, pokoj_pocetluzek) VALUES (105, 'p105', 3);
  6.  
  7. INSERT INTO rezervace (rezervace_pokoj, rezervace_druh, rezervace_datum) VALUES (101, 1, '2006.5.5');
  8. INSERT INTO rezervace (rezervace_pokoj, rezervace_druh, rezervace_datum) VALUES (101, 2, '2006.5.6');
  9. INSERT INTO rezervace (rezervace_pokoj, rezervace_druh, rezervace_datum) VALUES (101, 3, '2006.5.7');
  10. INSERT INTO rezervace (rezervace_pokoj, rezervace_druh, rezervace_datum) VALUES (102, 1, '2006.5.4');
  11. INSERT INTO rezervace (rezervace_pokoj, rezervace_druh, rezervace_datum) VALUES (102, 2, '2006.5.5');
  12. INSERT INTO rezervace (rezervace_pokoj, rezervace_druh, rezervace_datum) VALUES (102, 2, '2006.5.6');
  13. INSERT INTO rezervace (rezervace_pokoj, rezervace_druh, rezervace_datum) VALUES (102, 2, '2006.5.7');
  14. INSERT INTO rezervace (rezervace_pokoj, rezervace_druh, rezervace_datum) VALUES (102, 3, '2006.5.8');
  15. INSERT INTO rezervace (rezervace_pokoj, rezervace_druh, rezervace_datum) VALUES (105, 1, '2006.5.5');
  16. INSERT INTO rezervace (rezervace_pokoj, rezervace_druh, rezervace_datum) VALUES (105, 2, '2006.5.6');
  17. INSERT INTO rezervace (rezervace_pokoj, rezervace_druh, rezervace_datum) VALUES (105, 2, '2006.5.7');
  18. INSERT INTO rezervace (rezervace_pokoj, rezervace_druh, rezervace_datum) VALUES (105, 2, '2006.5.8');
  19. INSERT INTO rezervace (rezervace_pokoj, rezervace_druh, rezervace_datum) VALUES (105, 2, '2006.5.9');
  20. INSERT INTO rezervace (rezervace_pokoj, rezervace_druh, rezervace_datum) VALUES (105, 2, '2006.5.10');
  21. INSERT INTO rezervace (rezervace_pokoj, rezervace_druh, rezervace_datum) VALUES (105, 2, '2006.5.11');
  22. INSERT INTO rezervace (rezervace_pokoj, rezervace_druh, rezervace_datum) VALUES (105, 2, '2006.5.12');

Mám data, vzhůru do dotazů. Prvním dotazem spojím pokoje s jednotlivými datumy

  1. SELECT p.pokoj_cislo AS cislo, p.pokoj_oznaceni AS oznaceni, p.pokoj_pocetluzek AS luzka,
  2. p.pokoj_pocetpristylek AS pristylky, den FROM pokoje AS p
  3. CROSS JOIN dnyvintervalu('2006-5-5','2006-5-7') AS den;

-------+----------+-------+-----------+------------
 cislo | oznaceni | luzka | pristylky |    den
-------+----------+-------+-----------+------------
   101 | p101     |     2 |         0 | 2006-05-05
   101 | p101     |     2 |         0 | 2006-05-06
   101 | p101     |     2 |         0 | 2006-05-07
   102 | p102     |     3 |         0 | 2006-05-05
   102 | p102     |     3 |         0 | 2006-05-06
   102 | p102     |     3 |         0 | 2006-05-07
   103 | p103     |     3 |         0 | 2006-05-05
   103 | p103     |     3 |         0 | 2006-05-06
   103 | p103     |     3 |         0 | 2006-05-07
   104 | p104     |     2 |         0 | 2006-05-05
   104 | p104     |     2 |         0 | 2006-05-06
   104 | p104     |     2 |         0 | 2006-05-07
   105 | p105     |     3 |         0 | 2006-05-05
   105 | p105     |     3 |         0 | 2006-05-06
   105 | p105     |     3 |         0 | 2006-05-07

Uvedený výpis sice není špatný, ale nic nám neříká o tom, v jakém stavu se v konkrétní den konkrétní pokoj nachází.
Spojíme tedy tuto tabulku s tabulkou rezervací.

  1. SELECT p.pokoj_cislo AS cislo, p.pokoj_oznaceni AS oznaceni,
  2. p.pokoj_pocetluzek AS luzka, p.pokoj_pocetpristylek AS pristylky,
  3. den, r.rezervace_druh AS stav FROM pokoje AS p
  4. CROSS JOIN dnyvintervalu('2006-5-5','2006-5-7') AS den
  5. LEFT JOIN rezervace AS r ON r.rezervace_datum = den
  6. AND r.rezervace_pokoj=p.pokoj_cislo;

 cislo | oznaceni | luzka | pristylky |     den    | stav
-------+----------+-------+-----------+------------+------
   101 | p101     |     2 |         0 | 2006-05-05 |    1
   101 | p101     |     2 |         0 | 2006-05-06 |    2
   101 | p101     |     2 |         0 | 2006-05-07 |    3
   102 | p102     |     3 |         0 | 2006-05-05 |    2
   102 | p102     |     3 |         0 | 2006-05-06 |    2
   102 | p102     |     3 |         0 | 2006-05-07 |    2
   103 | p103     |     3 |         0 | 2006-05-05 |
   103 | p103     |     3 |         0 | 2006-05-06 |
   103 | p103     |     3 |         0 | 2006-05-07 |
   104 | p104     |     2 |         0 | 2006-05-05 |
   104 | p104     |     2 |         0 | 2006-05-06 |
   104 | p104     |     2 |         0 | 2006-05-07 |
   105 | p105     |     3 |         0 | 2006-05-05 |    1
   105 | p105     |     3 |         0 | 2006-05-06 |    2
   105 | p105     |     3 |         0 | 2006-05-07 |    2

Už sice víme jak to se kterým pokojem vypadá, ale ještě by to chtělo textové popisky.

  1. SELECT p.pokoj_cislo AS cislo, p.pokoj_oznaceni AS oznaceni,
  2. p.pokoj_pocetluzek AS luzka, p.pokoj_pocetpristylek AS pristylky,
  3. den, po.polozka_nazev AS stav FROM pokoje AS p
  4. CROSS JOIN dnyvintervalu('2006-5-5','2006-5-7') AS den
  5. LEFT JOIN rezervace AS r ON r.rezervace_datum = den
  6. AND r.rezervace_pokoj=p.pokoj_cislo
  7. LEFT JOIN polozky AS po ON r.rezervace_druh = po.polozka_cislo;

-------+----------+-------+-----------+------------+-----------------
 cislo | oznaceni | luzka | pristylky |    den     |      stav
-------+----------+-------+-----------+------------+-----------------
   101 | p101     |     2 |         0 | 2006-05-05 | Počátek pobytu
   101 | p101     |     2 |         0 | 2006-05-06 | Celodenní pobyt
   101 | p101     |     2 |         0 | 2006-05-07 | Konec pobytu
   102 | p102     |     3 |         0 | 2006-05-05 | Celodenní pobyt
   102 | p102     |     3 |         0 | 2006-05-06 | Celodenní pobyt
   102 | p102     |     3 |         0 | 2006-05-07 | Celodenní pobyt
   103 | p103     |     3 |         0 | 2006-05-05 |
   103 | p103     |     3 |         0 | 2006-05-06 |
   103 | p103     |     3 |         0 | 2006-05-07 |
   104 | p104     |     2 |         0 | 2006-05-05 |
   104 | p104     |     2 |         0 | 2006-05-06 |
   104 | p104     |     2 |         0 | 2006-05-07 |
   105 | p105     |     3 |         0 | 2006-05-05 | Počátek pobytu
   105 | p105     |     3 |         0 | 2006-05-06 | Celodenní pobyt
   105 | p105     |     3 |         0 | 2006-05-07 | Celodenní pobyt

Konečně už víme, jak je to s kterým pokojem.
Ještě bychom mohli výpis zlepšit a zaplnit prázdná místa.
Celý dotaz se tím bohužel poněkud více zamotá.
Vrátíme se k předchozímu dotazu a pomocí CASE jej vylepšíme tím, že pokud bude stav NULL, místo toho se vypíše hodnota  :

  1. SELECT p.pokoj_cislo AS cislo, p.pokoj_oznaceni AS oznaceni,
  2. p.pokoj_pocetluzek AS luzka, p.pokoj_pocetpristylek AS pristylky, den,
  3. CASE WHEN r.rezervace_druh IS NULL THEN 0 ELSE r.rezervace_druh END AS stav_cislo
  4. FROM pokoje AS p CROSS JOIN dnyvintervalu('2006-5-5','2006-5-7') AS den
  5. LEFT JOIN rezervace AS r ON r.rezervace_datum = den
  6. AND r.rezervace_pokoj=p.pokoj_cislo;

-------+----------+-------+-----------+------------+------------
 cislo | oznaceni | luzka | pristylky |    den     | stav_cislo
-------+----------+-------+-----------+------------+------------
   101 | p101     |     2 |         0 | 2006-05-05 |          1
   101 | p101     |     2 |         0 | 2006-05-06 |          2
   101 | p101     |     2 |         0 | 2006-05-07 |          3
   102 | p102     |     3 |         0 | 2006-05-05 |          2
   102 | p102     |     3 |         0 | 2006-05-06 |          2
   102 | p102     |     3 |         0 | 2006-05-07 |          2
   103 | p103     |     3 |         0 | 2006-05-05 |          0
   103 | p103     |     3 |         0 | 2006-05-06 |          0
   103 | p103     |     3 |         0 | 2006-05-07 |          0
   104 | p104     |     2 |         0 | 2006-05-05 |          0
   104 | p104     |     2 |         0 | 2006-05-06 |          0
   104 | p104     |     2 |         0 | 2006-05-07 |          0
   105 | p105     |     3 |         0 | 2006-05-05 |          1
   105 | p105     |     3 |         0 | 2006-05-06 |          2
   105 | p105     |     3 |         0 | 2006-05-07 |          2

No a teď konečně můžeme připojit výpis stavů a nahradit tak jeho číselnou reprezentaci reprezentací textovou. Bohužel nemůžeme tabulky spojit přímo, musíme na to jít oklikou přes závorky.

  1. SELECT v.cislo, v.oznaceni, v.luzka, v.pristylky, v.den, po.polozka_nazev AS stav FROM
  2.      (SELECT p.pokoj_cislo AS cislo, p.pokoj_oznaceni AS oznaceni,
  3.      p.pokoj_pocetluzek AS luzka, p.pokoj_pocetpristylek AS pristylky,
  4.      den, CASE WHEN r.rezervace_druh IS NULL THEN 0 ELSE r.rezervace_druh END AS stav_cislo
  5.       FROM pokoje AS p CROSS JOIN dnyvintervalu('2006-5-5','2006-5-7') AS den
  6.       LEFT JOIN rezervace AS r ON r.rezervace_datum = den
  7.       AND r.rezervace_pokoj=p.pokoj_cislo) AS v
  8. INNER JOIN polozky AS po ON v.stav_cislo=po.polozka_cislo;

-------+----------+-------+-----------+------------+-----------------
 cislo | oznaceni | luzka | pristylky |     den    |      stav
-------+----------+-------+-----------+------------+-----------------
   101 | p101     |     2 |         0 | 2006-05-05 | Počátek pobytu
   101 | p101     |     2 |         0 | 2006-05-06 | Celodenní pobyt
   101 | p101     |     2 |         0 | 2006-05-07 | Konec pobytu
   102 | p102     |     3 |         0 | 2006-05-05 | Celodenní pobyt
   102 | p102     |     3 |         0 | 2006-05-06 | Celodenní pobyt
   102 | p102     |     3 |         0 | 2006-05-07 | Celodenní pobyt
   103 | p103     |     3 |         0 | 2006-05-05 | Volný
   103 | p103     |     3 |         0 | 2006-05-06 | Volný
   103 | p103     |     3 |         0 | 2006-05-07 | Volný
   104 | p104     |     2 |         0 | 2006-05-05 | Volný
   104 | p104     |     2 |         0 | 2006-05-06 | Volný
   104 | p104     |     2 |         0 | 2006-05-07 | Volný
   105 | p105     |     3 |         0 | 2006-05-05 | Počátek pobytu
   105 | p105     |     3 |         0 | 2006-05-06 | Celodenní pobyt
   105 | p105     |     3 |         0 | 2006-05-07 | Celodenní pobyt

Nabízí se tu ještě možnost doplnění o aktuální ceník v závislosti na datumu pobytu, ale to si nechám na příště. Tento dotaz je už sám o sobě dost složitý.

Vložil karel, Čt, 2006-07-13 16:28
Tags: