Použití pomocné tabulky obsahující kalendář
Skočit na navigaci
Skočit na vyhledává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)