Použití pomocné tabulky obsahující kalendář

Z PostgreSQL
Přejít na: navigace, hledání

Pomocné tabulky se v SQL používají poměrně často. Umožňují doplnit určitá data, která potřebujeme pro výpočet. Například, pro určení průměrných denních výdajů potřebujeme doplnit základní data i o dny, kdy nedošlo k výdaji (jenom proto aby se zvýšil počet dní). Typicky pomocná tabulka bude obsahovat číselnou řadu nebo kalendář. V PostgreSQL máme k dispozici funkci generate_series, která vygeneruje řádky, takže pomocná tabulka může být dynamická. Relativně obtížně se určuje, zda-li se jedná o pracovní den, či nikoliv. K tomu použiji vlastní funkce:

CREATE OR REPLACE FUNCTION velnedele(_rok INTEGER) RETURNS DATE AS $$
DECLARE B INTEGER; D INTEGER; E INTEGER; Q INTEGER;
  DD INTEGER; MM INTEGER;
BEGIN
    IF _rok < 1900 OR _rok > 2099 THEN
      RAISE EXCEPTION 'Out of range';
  END IF;
  B := 255 - 11 * ($1 % 19); D := ((B - 21) % 30) + 21;
  IF D > 38 THEN D := D - 1; END IF;
  E := ($1 + $1/4 + D + 1) % 7; Q := D + 7 - E;
  IF Q < 32 THEN DD:=Q; MM := 3; ELSE DD := Q - 31; MM := 4; END IF;
  RETURN to_date(to_char(DD, '00') || 
    to_char(MM, '00') || to_char(_rok,'0000'), 'DD MM YYYY');
END;
$$ LANGUAGE plpgsql IMMUTABLE STRICT; 

-- vrati nazev nebo NULL, pokud den neni statnim svatkem
CREATE OR REPLACE FUNCTION StatniSvatek(d date)
RETURNS varchar AS $$
DECLARE 
  vnedele date;
  result varchar;
BEGIN
  result := CASE to_char(d, 'MMDD')
                WHEN '0101' THEN 'Nový rok'
                WHEN '0501' THEN 'Svátek práce'
                WHEN '0508' THEN 'Den osvobození'
                WHEN '0705' THEN 'Den slovanských věrozvěstů Cyrila a Metoděje'
                WHEN '0706' THEN 'Den upálení mistra Jana Husa'
                WHEN '0928' THEN 'Den české státnosti'
                WHEN '1028' THEN 'Den vzniku samostatného československého státu'
                WHEN '1117' THEN 'Den boje za svobodu a demokracii'
                WHEN '1224' THEN 'Štědrý den'
                WHEN '1225' THEN '1. svátek vánoční'
                WHEN '1226' THEN '2. svátek vánoční' END;
  IF result IS NULL THEN
    vnedele := velnedele(EXTRACT('year' FROM d)::integer);
    result := CASE
                  WHEN d = vnedele     THEN 'Neděle velikonoční'
                  WHEN d = vnedele + 1 THEN 'Pondělí velikonoční' END;
  END IF;
  RETURN result;
END;
$$ LANGUAGE plpgsql IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION DenDlouze(d date)
RETURNS varchar AS $$
SELECT CASE EXTRACT('dow' FROM $1) 
            WHEN 0 THEN 'Neděle'
            WHEN 1 THEN 'Pondělí'
            WHEN 2 THEN 'Úterý'
            WHEN 3 THEN 'Středa'
            WHEN 4 THEN 'Čtvrtek'
            WHEN 5 THEN 'Pátek'
            WHEN 6 THEN 'Sobota' END;
$$ LANGUAGE sql IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION DenKratce(d date)
RETURNS varchar AS $$
SELECT CASE EXTRACT('dow' FROM $1) 
            WHEN 0 THEN 'Ne'
            WHEN 1 THEN 'Po'
            WHEN 2 THEN 'Út'
            WHEN 3 THEN 'St'
            WHEN 4 THEN 'Čt'
            WHEN 5 THEN 'Pá'
            WHEN 6 THEN 'So' END;
$$ LANGUAGE sql IMMUTABLE STRICT;

Pomocna tabulka pro rok 2007 může mít následující obsah:

postgres=# SELECT d, DenDlouze(d), DenKratce(d), 
                  NOT(EXTRACT('dow' FROM d) IN (0,6) OR StatniSvatek(d) IS NOT NULL) AS prac_den, 
                  StatniSvatek(d) 
              FROM (SELECT date '2007-01-01' + i AS d 
                       FROM generate_series(355,364) g(i))s;
     d      | dendlouze | denkratce | prac_den |   statnisvatek    
------------+-----------+-----------+----------+-------------------
 2007-12-22 | Sobota    | So        | f        | 
 2007-12-23 | Neděle    | Ne        | f        | 
 2007-12-24 | Pondělí   | Po        | f        | Štědrý den
 2007-12-25 | Úterý     | Út        | f        | 1. svátek vánoční
 2007-12-26 | Středa    | St        | f        | 2. svátek vánoční
 2007-12-27 | Čtvrtek   | Čt        | t        | 
 2007-12-28 | Pátek     | Pa        | t        | 
 2007-12-29 | Sobota    | So        | f        | 
 2007-12-30 | Neděle    | Ne        | f        | 
 2007-12-31 | Pondělí   | Po        | t        | 
(10 rows)

Tuto tabulku si uložím do skutečné tabulky kalendar.

Jak s touto tabulkou pracovat?

  • Počet pracovních dní (od, do)
postgres=# SELECT count(*) 
              FROM kalendar
             WHERE d BETWEEN '2007-10-01' AND '2007-11-01' AND prac_den;
 count 
-------
    24
(1 row)
  • Odečet n pracovních dnů od určitého dne. Např. 10 pracovních dnů od 20.12.2007 (pozn. ukázkový příklad síly neprocedurálního SQL, s použitím procedur a cyklu lze tuto úlohu řešit řádově rychleji, po delší době úloha, kde by bylo možné úspěšně použít kurzory):
postgres=# SELECT * 
              FROM kalendar k 
             WHERE k.prac_den AND 10 = (SELECT count(*) 
                                           FROM kalendar k2 
                                          WHERE k2.d BETWEEN k.d AND date '2007-12-20' - 1 
                                                AND k2.prac_den);
     d      | dendlouze | denkratce | prac_den | statnisvatek 
------------+-----------+-----------+----------+--------------
 2007-12-06 | Čtvrtek   | Čt        | t        | 
(1 row)
  • všechny svátky během pracovního týdne:

postgres=# SELECT * 
              FROM kalendar 
             WHERE statnisvatek IS NOT NULL AND denkratce NOT IN ('So','Ne');
     d      | dendlouze | denkratce | prac_den |                 statnisvatek                 
------------+-----------+-----------+----------+----------------------------------------------
 2007-01-01 | Pondělí   | Po        | f        | Nový rok
 2007-04-09 | Pondělí   | Po        | f        | Pondělí velikonoční
 2007-05-01 | Úterý     | Út        | f        | Svátek práce
 2007-05-08 | Úterý     | Út        | f        | Den osvobození
 2007-07-05 | Čtvrtek   | Čt        | f        | Den slovanských věrozvěstů Cyrila a Metoděje
 2007-07-06 | Pátek     | Pa        | f        | Den upálení mistra Jana Husa
 2007-09-28 | Pátek     | Pa        | f        | Den české státnosti
 2007-12-24 | Pondělí   | Po        | f        | Štědrý den
 2007-12-25 | Úterý     | Út        | f        | 1. svátek vánoční
 2007-12-26 | Středa    | St        | f        | 2. svátek vánoční
(10 rows)

Inspirováno článkem http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html.

Joe Celko v článku http://www.simple-talk.com/content/article.aspx?article=632 ještě doporučuje přidat sloupec julian_business_nbr tj číslo pracovního dne. V případě svátků se toto číslo nemění. Pro výpočet tohoto čísla lze použít uloženou proceduru nebo window funkci sum - kumulativní:

CREATE OR REPLACE FUNCTION prac_den(date)
RETURNS boolean AS $$
  SELECT NOT(EXTRACT('dow' FROM $1) IN (0,6) OR StatniSvatek($1) IS NOT NULL);
$$ LANGUAGE sql IMMUTABLE STRICT;

postgres=# SELECT d, DenDlouze(d), DenKratce(d), 
                  prac_den(d), 
                  SUM(CASE WHEN prac_den(d) THEN 1 ELSE 0 END) OVER (ORDER BY d) AS cislo_prac_dne,  
                  StatniSvatek(d) 
              FROM (SELECT date '2007-01-01' + i AS d 
                       FROM generate_series(0,366) g(i))s  LIMIT 10;
     d      | dendlouze | denkratce | prac_den | cislo_prac_dne |   statnisvatek    
------------+-----------+-----------+----------+----------------+-------------------
 2007-12-22 | Sobota    | So        | f        |            248 | ----
 2007-12-23 | Neděle    | Ne        | f        |            248 | ----
 2007-12-24 | Pondělí   | Po        | f        |            248 | Štědrý den
 2007-12-25 | Úterý     | Út        | f        |            248 | 1. svátek vánoční
 2007-12-26 | Středa    | St        | f        |            248 | 2. svátek vánoční
 2007-12-27 | Čtvrtek   | Čt        | t        |            249 | ----
 2007-12-28 | Pátek     | Pá        | t        |            250 | ----
 2007-12-29 | Sobota    | So        | f        |            250 | ----
 2007-12-30 | Neděle    | Ne        | f        |            250 | ----
 2007-12-31 | Pondělí   | Po        | t        |            251 | ----
(10 rows)

Díky sloupci cislo_prac_dne snadno můžeme provádět operace nad pracovním kalendářem:

postgres=# SELECT * 
              FROM kalendar 
             WHERE cislo_prac_dne = (SELECT cislo_prac_dne 
                                        FROM kalendar
                                       WHERE d = '2009-07-30') + 0 
               AND prac_den;
     d      | dendlouze | denkratce | prac_den | cislo_prac_dne | statnisvatek 
------------+-----------+-----------+----------+----------------+--------------
 2009-07-30 | Čtvrtek   | Čt        | t        |            650 | ----
(1 row)

postgres=# SELECT * 
              FROM kalendar
             WHERE cislo_prac_dne = (SELECT cislo_prac_dne 
                                        FROM kalendar
                                       WHERE d = '2009-07-30') + 3
               AND prac_den;
     d      | dendlouze | denkratce | prac_den | cislo_prac_dne | statnisvatek 
------------+-----------+-----------+----------+----------------+--------------
 2009-08-04 | Úterý     | Út        | t        |            653 | ----
(1 row)