Warning: Declaration of Suffusion_MM_Walker::start_el(&$output, $item, $depth, $args) should be compatible with Walker_Nav_Menu::start_el(&$output, $item, $depth = 0, $args = Array, $id = 0) in /DISK2/WWW/plsql.cz/www/wp-content/themes/suffusion/library/suffusion-walkers.php on line 0
Feb 102013
 

Další šikovná featura Oracle Database a můj již 18 tip na snad využitelnout funkci. Ač tahle je hodně specifická. Dnes je můj tip na pipeline funkci z package DBMS_FREQUENT_ITEMSELF jménem FI_HORIZONTAL. Upřímně – je to funkce, která mi v Oracle nikdy nechyběla a nejspíše mi ani nikdy chybět nebude, nicméně o to více mi těší jí poznat ;)

Jak to celé funguje: Funkci se podstrčí select/kurzor s libovolným počtem sloupců a funkce vyhledává zkrz všechny sloupce všechny možné množiny dat splňující dané podmínky (mohutnost, % výskyt) a výskyt těchto množin pak agreguje zkrz všechny řádky podstrčeného selectu/kurzoru a jako návrat vrátí tabulku jejíž jeden řádek je definován tímto typem:

<návratová typ dbms_frequent_itemset> (
     itemset [Nested Table of Item Type DERIVED FROM tranx_cursor],
     support        NUMBER,
     length         NUMBER,
     total_tranx    NUMBER);

itemset – monžina, která se opakovala. Množina - tedy je to nested tabulka, maximální velikost je 20, neb Oracle podporuje maximálně 20 prvků, jejihž kombinaci počítá.
support – kolikrát se daná množina vyskytuje (v každém řádku může jen jednou)
length – mohutnost vrácené množiny
total_tranx – počet řádků

Mnohem lepší bude si to vyzkoušet, ne?  Vytvořme následující tabulku:

create table tbl_drks(
 name_1 varchar2(20),
 name_2 varchar2(20),
 name_3 varchar2(20),
 name_4 varchar2(20),
 name_5 varchar2(20)
);

A insert několika řádků pro test – poněkuď více než má Oracle v dokumentaci jako příklad neb tam bohužel nejsou postihnuty všechny případy, které mohou nastat:

insert into tbl_drks values('RedBull','BigShock','RockStar',null,'Monster');
insert into tbl_drks values('BigShock','RedBull',null, null,'Monster');
insert into tbl_drks values('BigShock','RedBull','BigShock',null,'RedBull');
insert into tbl_drks values('BigShock','RedBull',null, null,'Monster');
insert into tbl_drks values('Monster','RedBull',null, null,'Monster');
insert into tbl_drks values('Monster','RedBull','RockStar','BigShock','Monster');
insert into tbl_drks values('RockStar','RedBull','BigShock','BigShock','Monster');
insert into tbl_drks values('RockStar','RedBull','RockStar','RockStar','Monster');
commit;

A jak to vypadá v tabulce:

Tabulka pro test DBMS_FREQUENT_ITEMSET

Tabulka pro test DBMS_FREQUENT_ITEMSET

Vytvořme typ pro návrat (pro nested návratovou tabulku):

CREATE TYPE typ_name AS TABLE OF VARCHAR2(20);

A nyní konečně provolání funkce:

SELECT CAST(itemset as typ_name)itemset, support, length, total_tranx
FROM table(DBMS_FREQUENT_ITEMSET.FI_HORIZONTAL(
CURSOR(SELECT name_1, name_2, name_3, name_4, name_5 FROM tbl_drks),
0,
1,
5));

Funkci DBMS_FREQUENT_ITEMSET.FI_HORIZONTAL je podstrčen select vracející celou tabulku tbl_drks a parametry: support_threshold=0 – není omezeno na % počet výskytů, itemset_length_min=1 - minimální mohutnost množiny, itemset_length_max=5 – maximální mohutnost množiny (víc ani být nemůže, neb naše tabulka má 5 sloupců)

A výsledek:

Výsledek DBMS_FREQUENT_ITEMSET.FI_HORIZONTAL

Výsledek DBMS_FREQUENT_ITEMSET.FI_HORIZONTAL



Interpretace výsledku:
Množina (BigShock) se vyskytuje na 6ti řádcích naopak (RockStar) je pouze na 4 řádcích. Jedná se o množinu a tedy pokud se kombinace či hodnota na jednom řádku vyskytuje několikrát je to počitáno pouze jednou jako výskyt – tolik ke sloupci support.
(BighShock, Monster)  je množina mohutnosti 2 – tolik ke sloupci leng(th)
Sloupec total(_tranx) pak jen ukazuje počet řádků z kurzoru – v tabulce bylo 8 řádků.

Ve sloupci total je hodnota 8 jako celkový počet řádků – množiny (Monster),(RedBull) a (Monster,RedBull) se vyskytují 7 či 8. Což znamená jejich výskyt v procentech vůči total_tranx je 7/8*100=87,5%+, so zkusme takto omezit volanou funkci.

Výsledek funkce DBMS_FREQUENT_ITEMSET.FI_HORIZONTAL 2

Výsledek funkce DBMS_FREQUENT_ITEMSET.FI_HORIZONTAL 2

Fajn a ted k tomu přidáme ještě omezení mohutnosti množiny:

Výsledek funkce DBMS_FREQUENT_ITEMSET.FI_HORIZONTAL 3

Výsledek funkce DBMS_FREQUENT_ITEMSET.FI_HORIZONTAL 3

Omezeno na 0.62 proto, že 5/8 je 0.625 tzn. ve výsledku se objeví jen množiny se sloupcem support 5 a větším, čímž vypadává kombinace s nejlepšími energetickými drinky na trhu (Monster,Rockstar) s počtem výskytů 4. Dalším omezením je mohutnost množiny 2 až 3, což eliminuje všechny jednoprvkové množiny (BigShock),(Monster),(Redbull) a (RockStar). Jediná množina s mohutností větší než 3 je (BigShock,Monster,RedBull,RockStar) a ta padá nejen na omezením počtu prvků na 2-3 ale i na počet výskytů 5+ (0.62) neb se se vyskytuje 3x.

Nevyzkoušeli jsme ještě dva nepovinné parametry including_items  (SYS_REFCURSOR)  kde jsou hodnoty, které nezbytně nutně musí být v množinách, které chceme. A  excluding_items  (SYS_REFCURSOR) kde jsou hodnoty, které nesmí být v množinách, které chceme. Ukázka pouze pro including_items:

Výsledek funkce DBMS_FREQUENT_ITEMSET.FI_HORIZONTAL 4

Výsledek funkce DBMS_FREQUENT_ITEMSET.FI_HORIZONTAL 4

Tedy všechny množiny, jejihž mohutnost je větší než 2 a menší než 3, počet výskytů je více než 0.5 (tzn 50% což při total_tranx=8 znamená počet výskytů (support)>=4) A každá z těchto množin musí obsahovat položku (RockStar).

Spíše než o šikovnou a užitečnou funkci se jedná o funkci zajímavou ;) Nevim, kdo z nás podobnou funkcionalitu kdy potřeboval. A však pokud je třeba nějaké podobné funkcionality určitě šáhněme po DBMS_FREQUENT_ITEMSET, protože napsat si podobnou funkci sám by bylo opravdu náročné, respektive napsat jí dobře  po výkonové stránce. Funkce z pohledu výkonu asi nebude napsána jako nějaké ořezávátko, protože možná nejzajímavější na tom je exekuční plán:

Exekuční plán - dbms_frequent_itemset

Exekuční plán - dbms_frequent_itemset

Napadá mi jedno vcelku pěkné použití pro tuto funkci – množiny nic moc – ale taková jednoprvková množina je pořád množina. Tedy funkce umí spočítat počet řádků kde se vyskytuje nějaká hodnota i když je rozhozená skrz několik sloupců. Což znamená jako parametr select z tabulky, itemset_length_min=itemset_length_max=1 a případně including_items na hodnotu, kterou hledáme. Což jsem třebas já několikrát potřeboval a dosud mi na to stačila kombinace CASE a DECODE, ale tohle je mocinky moc mocnější ;)

 Posted by at 01:19

  One Response to “Rychlotip #18 – jaký výskyt má množina i zkrz sloupce”

  1. [...] rychlotip byl o funkci DBMS_FREQUENT_ITEMSET.FI_HORIZONTAL, která vytvoří z hodnot sloupců všechny možné množiny a spočítá jejich výskyt zkrt [...]

 Leave a Reply

(required)

(required)

(required)

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>