Reportovací funkce

Databáze ORACLE i POSTGRESQL nabízí nadstavbu standardního SQL, které se označuje jako window nebo jako reportovací (reporting) funkce. Výraz window znamená, že funkce zpracovávají definovanou podmnožinu výsledku dotazu, tzv. okno (window). V podstatě jde o aplikaci agregačních funkcí na vymezený rozsah řádků, který je nezávislý na výrazu v sekci GROUP BY.

Podívejme se na častý případ, kdy potřebuje znát procentuální zastoupení zvolené kategorie v tabulce.  Mějme tabulku pacientů se sloupcem označující pohlaví. Chceme získat sumární přehled s procentuálním zastoupením žen a mužů. Pro získání počtu jednotlivých kategorií použijeme standardní seskupovací výraz GROUP BY. Abychom ale mohli vyjádřit procentuální zastoupení, potřebujeme zároveň celkový počet záznamů, což ve standardním SQL můžeme provést pomocí vnořeného dotazu na pozici sloupce:

     SELECT pohlavi, COUNT(*) pocet,
                        (SELECT  COUNT(*)  FROM pacient) celkem,
                        COUNT(*) * 100 / (SELECT  COUNT(*)  FROM pacient) procento
             FROM pacient
            GROUP BY pohlavi

Tab. 4: Výsledek seskupení s procentuálním vyjádřením

Pomocí reportovací funkce můžeme stejného výsledku dosáhnout bez vnořeného dotazu:

     SELECT pohlavi, COUNT(*) pocet,
                        SUM (COUNT(*))  OVER ()  celkem,
                        COUNT(*) * 100 / SUM (COUNT(*))  OVER ()  procento
             FROM pacient
            GROUP BY pohlavi

Jak je vidět, jde o aplikaci agregační funkce (SUM) na výsledek jiné agregační funkce (COUNT) s vymezením rozsahu agregace. Rozsah agregace je definován za klíčovým slovem OVER, v našem případě agregujeme přes celou množinu, což je vyjádřeno prázdnými závorkami.  Můžeme však chtít vytvořit sumární report, kde bude procento mužů a žen rozvedeno dle státní příslušnosti:

Tab. 5: Parciální procentuální vyjádření

Tuto sestavu s parciálními součty získáme drobnou úpravou původního dotazu:

            SELECT stat, pohlavi, COUNT(*) pocet,
                        SUM (COUNT(*))  OVER (PARTITION BY stat)  celkem,
                        COUNT(*) * 100 / SUM (COUNT(*))  OVER (PARTITION BY stat)                              procento
             FROM pacient
            GROUP BY stat, pohlavi

Window funkce nejsou vázány jen na agregační konstrukce s GROUP BY. Lze je použít i v jednoduchých výpisech, kde chceme srovnat konkrétní hodnotu například s průměrem. Mějme tabulku s aplikovanou léčbou konkrétního léku jednotlivým pacientům. Tabulka obsahuje identifikaci pacienta, datum podání a množství podaného léku. V tabulárním reportu chceme srovnávat jednotlivé aplikace s celkovým průměrem v celé tabulce. Tuto sestavu získáme z databáze následovně:

   SELECT patient_id, datum_podani, davka, AVG(davka) OVER () prumerna_davka
   FROM lecba

Pokud bychom vynechali klauzuli OVER, hlásila by databáze chybu nesprávného použití agregační funkce. V tomto případě je vše v pořádku a ve čtvrtém sloupci bude ve všech řádcích stejná hodnota, která odpovídá průměrné dávce v celé tabulce lecba.

Window funkce využíváme také při výpočtech kumulativních součtů. Při kumulativním součtu sečítáme všechny hodnoty vybraného sloupce od prvního řádku až po aktuální. Například z tabulky lecba z předchozího příkladu chceme sledovat kumulativní spotřebu léku v čase. Použijeme agregační funkci SUM() doplněnou o klauzuli OVER, ve které specifikujeme pravidlo setřídění:

    SELECT patient_id, datum_podani, davka,
            SUM(davka) OVER (ORDER BY datum_podani) kumulativni_spotreba
    FROM lecba

Tímto zajistíme, že funkce SUM() agreguje data od prvního záznamu až po aktuální řádek. Jde o implicitně definované agregační okno.

Tab. 6: Kumulativní součet

Rozsah agregace (agregační okno) lze specifikovat i explicitně, což umožňuje počítat například klouzavý průměr. Klouzavý průměr je průměrná hodnota vypočítaná v časové řadě v definovaném časovém okně. Obvykle počítáme průměrnou hodnotu z několika předchozích hodnot. Toto explicitní okno definujeme za klauzuli ROWS BETWEEN, za níž můžeme použít některou z následujících možností:

• UNBOUNDED PRECEDING - všechny předchozí řádky
• UNBOUNDED FOLLOWING - všechny následující řádky
• CURRENT ROW - aktuálně zpracovávaný řádek
• n PRECEDING - n předchozích řádků
• n FOLLOWING - n následujících řádků

Když se vrátíme k příkladu s celkovou průměrnou dávkou, můžeme jej rozšířit o výpočet klouzavého průměru z posledních tří předchozích aplikací následovně:

 SELECT patient_id, datum_podani, davka, AVG(davka) OVER () celkova_prumerna_davka,
            AVG(davka) OVER (ORDER BY datum_podani
              ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) klouzavy_prumer
FROM lecba

Tab.7: Klouzavý průměr

Hodnota klouzavého průměru se spočítá jako součet 3 předchozích hodnot plus hodnota v počítaném řádku podělený čtyřmi. U prvních tří řádků se počítá průměr z redukovaného počtu dostupných řádků.