Utilizarea limbajului SQL pentru cereri OLAP · Roll-up ierarhic pe tabela de fapte si pe unele...

Utilizarea limbajului SQL

pentru cereri OLAP

Mihaela Muntean

2015

Cuprins

Implementarea operatiilor OLAP de baza in SQL -traditional:

◦ Rollup

◦ Slice

◦ Dice

◦ Pivotare

SQL-2008

◦ Optiunea ROLLUP

◦ Optiunea CUBE, grouping sets

◦ Optiunea PIVOT

◦ RANK(), DENSE_RANK()

Mihaela Muntean

Operaţii de bază

Slice şi dice sau selecţii în cub.

◦ “Afişează vânzările anuale din Bucureşti”.

Drill down / rollup - utilizează ierarhiile din dimensiuni şi măsurile pentru agregări sau de-agregări.

Drill across - combină mai multe cuburi cu una sau mai multe dimensiuni comune (joncţiunea de cuburi).

Ranking sau top/bottom n -analize de tip “primii n” sau

“ultimii n” după anumite criterii.

Pivotare/rotirea unui cub pentru a pune în evidenţă alte

aspecte analitice.

Mihaela Muntean

Mihaela Muntean

SELECT –SQL traditional Cererile OLAP utilizeaza:

◦ functii de agregare (COUNT, SUM, AVERAGE,

MIN, MAX )

◦ clauza GROUP By

roll-up, drill-down

slice, dice

pivot

Mihaela Muntean

Se considera urmatoarea tabela de fapte VVanzari cu masura Cantitate

Tabelele de dimensiuni:

VLocatie (MagId, Oras ……),

VProdus (ProdId, Denumire_prod, Categorie……) ,

VTimp (TimpId, luna…….)

Ierarhiile din dimensiuni sunt:

MagIdOras (ierarhie din Locatie)

ProdIdCategorie (ierarhie din Produs)

TimpIdluna (ierarhia din Timp)

Mihaela Muntean

Roll-up dimensional

pe tabela de fapte prin eliminarea unei dimensiuni /mai multor

dimensiuni, in timp ce dimensiunile ramase sunt reprezentate de

cheile lor in clauza Group By

O dimensiune “eliminata” –dimensiunea Timp:

SELECT ProdId, MagId, SUM(cantitate ) AS Total_dupa_Prod_Loc

FROM vvanzari GROUP BY ProdId, Magid order by prodid; ;

Doua dimensiuni “eliminate”: Locatie si Timp :

SELECT ProdId, SUM(cantitate ) AS Total_dupa_Prod

FROM vvanzari GROUP BY ProdId;

Toate dimensiunile “eliminate “:

SELECT SUM(cantitate ) AS Total_general FROM Vvanzari;

Mihaela Muntean

Mihaela Muntean

Mihaela Muntean

Roll-up ierarhic

pe tabela de fapte si pe unele dimensiuni prin parcurgerea

ierarhiilor din dimensiuni, spre varful lor

Numarul de rollup-uri ierarhice depinde de numarul de

nivele din ierarhii.

O ierarhie simpla (fara ramuri paralele) cu n nivele, da n

rollup-uri.

Roll-up ierarhic dupa ierarhia din dimensiunea Locatie

(MagIdoras ) –vanzarile dupa oras, produs si zi

SELECT oras, ProdId, TimpId, SUM(cantitate ) AS Oras_Total

FROM vvanzari v, vLocatie l WHERE v.magId = l.magId

GROUP BY oras,ProdId, Timpid order by oras;

Mihaela Muntean

Mihaela Muntean

Roll-up ierarhic

Roll-up dupa ierarhia din dimensiunea Timp(TimpIdluna ) –

vanzarile dupa luna, produs, magazin

SELECT luna, magId, Prodid,

SUM(cantitate ) AS luna_Total

From Vvanzari V, Vtimp T Where V.Timpid = T.Timpid

GROUP BY luna, prodid, magId order by luna, magid ;

SELECT luna, SUM(cantitate ) AS luna_Total

From Vvanzari V, Vtimp T Where V.Timpid = T.Timpid

GROUP BY luna order by luna;

Mihaela Muntean

Mihaela Muntean

Roll-up ierarhic

Roll-up ierarhic dupa doua dimensiuni ( doua ierarhii )- vanzarile dupa

oras, categorie si zi

SELECT categorie, oras, TimpId, SUM(cantitate ) AS Tip_oras_Total

FROM Vvanzari v, VProdus p, VLocatie l

WHERE v.magId = l.magId AND v.ProdId = p.ProdId

GROUP BY categorie, oras, TimpId order by categorie, oras, timpid;

Roll-up dupa trei dimensiuni -vanzarile dupa categorie, oras si luna.

SELECT categorie, oras, luna,

SUM(cantitate ) AS Tip_oras_luna_Tot

FROM Vvanzari v, VProdus p, VLocatie l, VTimp t

WHERE v.magId = l.magId AND v.ProdId = p.ProdId

AND v.TimpId = t.TimpId

GROUP BY categorie, oras, luna order by categorie, oras, luna;

Mihaela Muntean

Mihaela Muntean

Slice= Selectia unei sg valori dintr-o dimensiune

Se considera tabela de fapte Vanzari cu

dimensiunile Produs, Oras si Timp :

Vanzari (ProdId, orasId, lunaId, cantitate )

SELECT ProdId, orasId, cantitate FROM

vanzari WHERE lunaId = ‘January’ ;

ProdId, orasId, cantitate (lunaId = January(Vanzari))

Wellington

Nelson

Auckland

Eraser

Linear

Pencil

March February January

123 183 99

23

1230

1 13

1111 2303 Wellington

Nelson

Auckland

Eraser

Linear

Pencil

January

123

23

1230

Mihaela Muntean

Dice=Selectii in doua sau mm dimensiuni- subcub

SELECT orasId, cantitate FROM vanzari

WHERE (lunaId =‘January’ or lunaid=‘February’ )AND (ProdId =

‘Pencil’ or prodid=“Eraser”)

Nu implica reducerea nr de dimensiuni ale cubului

Wellington

Nelson

Auckland

Eraser

Linear

Pencil

March February January

123 183 99

23

1230

1 13

1111 2303

Mihaela Muntean

Operatorul ROLLUP

(SQL -2003)- (SQL 2008) SELECT denumire, zi, SUM(cantitate) AS vanzari

FROM vanzari v, Locatie l, Timp t

WHERE v.orasId = l.orasId AND v.TimpId = t.TimpId

GROUP BY ROLLUP (denumire, zi);

va calcula sum(cantitate) dupa {denumire, zi}, {denumire}, {}

Dacă în ROLLUP sunt specificate N coloane, atunci se produc N+1

tipuri de subtotal

Rollup(x,y) =((), (x), (x,y))

MySQL, SQLServer, -clauza WITH ROLLUP

SELECT… GROUP BY denumire, zi WITH ROLLUP

Oracle: GROUP BY ROLLUP(denumire,zi)

Mihaela Muntean

GROUP BY ROLLUP (denumire, zi);

SELECT denumire, zi, SUM(cantitate) AS vanzari


Where V.Orasid = L.Orasid And V.Timpid = T.Timpid

Group By Denumire, Zi

Union

SELECT denumire,null, SUM(cantitate) AS vanzari



Group By Denumire, null

Union

SELECT null, null, SUM(cantitate) AS vanzari


Where V.Orasid = L.Orasid And V.Timpid = T.Timpid ;

Mihaela Muntean

Rollup parţial



WHERE v.orasId = l.orasId AND v.TimpId = t.TimpId

GROUP BY denumire, ROLLUP (zi) order by l.denumire;

Group by rollup(X) : {( ), (x)}

Group by denumire, rollup(zi)

Group by zi, rollup(denumire))

SELECT zi, denumire, SUM(cantitate) AS vanzari



GROUP BY zi, ROLLUP (denumire) order by zi;

Mihaela Muntean

Mihaela Muntean

GROUP BY denumire, ROLLUP (zi)





Union

SELECT denumire, null, SUM(cantitate) AS vanzari




Order By 1;

Mihaela Muntean

Mihaela Muntean

Operatorul CUBE

Operatorul CUBE realizează toate tipurilor posibile de agregare.

Cubul poate fi un cub complet sau un cub parţial.

Cube(x,y) ={( ), (x), (x,y), (y)}

Cube(x1,x2,….xn)= 2n grouping sets

((), (x1), (x1, x2), (x1,x2,x3),…..(x1,x2,..xn), (x2), (x2,x3), (x2,x3,x4), …(xn))

Mihaela Muntean

Operatorul CUBE



WHERE v.orasId = l.orasid AND v.TimpId = t.TimpId

GROUP BY CUBE (denumire, zi);

Va produce acelasi rezultat ca si 4 comenzi SELECT – SUM –

GROUP BY (dupa toate subseturile lui {denumire, zi} ca atribute

de grupare ): (denumire, zi), (denumire), (zi), {}

GROUP BY

CUBE

(denumire, zi);





Union





Union

SELECT null, zi, SUM(cantitate) AS vanzari



Group By null, Zi

Union

SELECT null, null, SUM(cantitate) AS vanzari



order by 1;

SELECT nume, denumire, zi, SUM(cantitate)

FROM vanzari v, Timp t, Locatie l, agenti a

WHERE v.TimpId = t.TimpId

AND v.orasId = l.orasId AND v.agentId = a.agentId

GROUP BY CUBE (nume, denumire, zi);

nume

Nume, denumire, zi

Nume, denumire Nume, zi Denumire, zi

denumire

{ }

zi

Grouping SETS Criterii de grupare mai selective decat la CUBE




GROUP BY denumire, zi;




GROUP BY grouping sets (denumire, zi);

identic cu group by denumire union all group by zi




GROUP BY grouping sets ((denumire, zi), (denumire));

Identic cu:

Group by denumire, zi

Union all

Group by denumire




GROUP BY grouping sets ((denumire, zi), (denumire), ());

ROLLUP(denumire, zi)!!!!!!

Select Null, Null, sum(cantitate) as vanzari



Union All




GROUP BY denumire, zi

Union all




GROUP BY denumire;

Operatia de pivotare

SELECT *

FROM <table-expr>

PIVOT

(aggregate-function(<column>)

FOR <pivot-column> IN (<value1>, <value2>,...,

<valuen>) ) AS <alias>

WHERE .....

!!!Operatia de pivotare executa un GROUP BY implicit

utilizand orice coloana din tabela

!!!!! Cererile pivot se vor executa pe un subset de coloane

utilizand subcereri, viziuni/view

Operatia de pivotare

With Pivot_Data As (Select Timpid, Magid, Cantitate

From Vvanzari)

select * from Pivot_Data

Pivot (SUM(cantitate) FOR magid IN (1,2)) ;

Returneaza pozitia unui tuplu dintr-o partitie

Tuplurile sunt ordonate in partitie dupa un criteriu de ordonare

Toate tuplurile cu aceeasi valoare pentru criteriu de de ordonare sunt

considerate avand acelasi rang

Daca n (> 1 ) tupluri au rangul r, atunci urmatorul tuplu are rangul r + n + 1

RANK ( ) OVER ( [query_partition_clause] order_by_clause )

DENSE_RANK ( ) OVER ( [query_partition_clause] order_by_clause )

Ranking este realizat in conjunctie cu clauza ORDER BY

Select agentid, sum(cantitate) cantitate,

rank() over (order by sum(cantitate) desc ) as a_rank

From vanzari group by agentid order by a_rank;

Rank()

Rank 2??

Dense_rank() elimina aceasta problema!

Select oras, Denumire_Prod,

Sum(Cantitate) Cantitate, Sum(Volumvanz),

Dense_Rank()

Over (Order By Sum(Cantitate ) Desc, Sum(Volumvanz) Desc) As A_Rank

From Vvanzari, Vprodus, Vlocatie Where Vvanzari.Prodid=Vprodus.Prodid

And Vvanzari.Magid=Vlocatie.Magid

And Oras in ('Iasi', 'Hunedoara')

Group By Oras, Denumire_Prod;

Ranking pe multiple expresii

Pt valori identice ale cantitatii se face rank() dupa volumul vanzarilor

PARTITION BY imparte setul de date in partitii pe care opereaza RANK ()

Select Oras, Denumire_Prod, Sum(Cantitate) Cantitate,

Dense_Rank()

Over (Partition By oras Order By Sum(Cantitate ) Desc) As A_Rank

From Vvanzari, Vlocatie, Vprodus

Where Vvanzari.Magid=Vlocatie.Magid and

Vvanzari.Prodid=Vprodus.Prodid

Group By oras, Denumire_Prod;

Rank() cu clauza PARTITION BY

Pt fiecare oras se face o ierarhizare a produselor dupa

cantitate

Date post:	23-Jan-2020
Category:	Documents
Upload:	others
View:	7 times
Download:	0 times

Utilizarea limbajului SQL pentru cereri OLAP · Roll-up ierarhic pe tabela de fapte si pe unele...

Documents