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
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
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
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
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
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
FROM vanzari v, Locatie l, Timp t
Where V.Orasid = L.Orasid And V.Timpid = T.Timpid
Group By Denumire, Zi
Union
SELECT denumire,null, SUM(cantitate) AS vanzari
FROM vanzari v, Locatie l, Timp t
Where V.Orasid = L.Orasid And V.Timpid = T.Timpid
Group By Denumire, null
Union
SELECT null, null, SUM(cantitate) AS vanzari
FROM vanzari v, Locatie l, Timp t
Where V.Orasid = L.Orasid And V.Timpid = T.Timpid ;
Mihaela Muntean
Rollup parţial
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 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
FROM vanzari v, Locatie l, Timp t
Where V.Orasid = L.Orasid And V.Timpid = T.Timpid
GROUP BY zi, ROLLUP (denumire) order by zi;
Mihaela Muntean
GROUP BY denumire, ROLLUP (zi)
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 Denumire, Zi
Union
SELECT denumire, null, SUM(cantitate) AS vanzari
FROM vanzari v, Locatie l, Timp t
Where V.Orasid = L.Orasid And V.Timpid = T.Timpid
Group By Denumire, null
Order By 1;
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
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 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);
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 Denumire, Zi
Union
SELECT denumire, null, SUM(cantitate) AS vanzari
FROM vanzari v, Locatie l, Timp t
Where V.Orasid = L.Orasid And V.Timpid = T.Timpid
Group By Denumire, null
Union
SELECT null, zi, SUM(cantitate) AS vanzari
FROM vanzari v, Locatie l, Timp t
Where V.Orasid = L.Orasid And V.Timpid = T.Timpid
Group By null, Zi
Union
SELECT null, null, SUM(cantitate) AS vanzari
FROM vanzari v, Locatie l, Timp t
Where V.Orasid = L.Orasid And V.Timpid = T.Timpid
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
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 denumire, zi;
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 grouping sets (denumire, zi);
identic cu group by denumire union all group by zi
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 grouping sets ((denumire, zi), (denumire));
Identic cu:
Group by denumire, zi
Union all
Group by denumire
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 grouping sets ((denumire, zi), (denumire), ());
ROLLUP(denumire, zi)!!!!!!
Select Null, Null, sum(cantitate) as vanzari
FROM vanzari v, Locatie l, Timp t
WHERE v.orasId = l.orasid AND v.TimpId = t.TimpId
Union All
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 denumire, zi
Union all
SELECT denumire, null, SUM(cantitate) AS vanzari
FROM vanzari v, Locatie l, Timp t
WHERE v.orasId = l.orasid AND v.TimpId = t.TimpId
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()
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
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
Top Related