Az Oracle SQL 4.

1. Az Oracle SQL 4. Összesítés és csoportosítás lekérdezésekben. Halmazműveletek. Markó Tamás, PTE TTK

2. A rádiótelefonokat kérem KIKAPCSOLNI! Markó Tamás, PTE TTK

3. Összesítés és csoportosítás Markó Tamás, PTE TTK

4. Gyűjtőfüggvények 1. • Összesítő vagy aggregáló függvényeknek is nevezik őket • Rekordok egy csoportjára csak egy értéket adnak vissza • COUNT(mezőnév) • azon rekordok száma, ahol az adott mező nem üres • COUNT(*): a rekordok száma a táblában • SUM(mezőnév) • az adott oszlopban lévő értékek összege Markó Tamás, PTE TTK

5. Gyűjtőfüggvények 2. • MIN(mezőnév) • az adott oszlopban lévő értékek minimuma • MAX(mezőnév) • az adott oszlopban lévő értékek maximuma • AVG(mezőnév) • az adott oszlopban lévő értékek átlaga • STDDEV(mezőnév) • az adott oszlopban lévő értékek szórása • VARIANCE(mezőnév) • az adott oszlopban lévő értékek szórásnégyzete average standard deviation Markó Tamás, PTE TTK

6. COUNT - példák • SELECT COUNT(*) FROM vevok; • a vevok tábla sorainak száma • SELECT COUNT(cikkszam) FROM rtetel WHERE egysegar > 50; • azon rendelési tételek száma, ahol az egységár meghaladja az 50 Ft-ot Markó Tamás, PTE TTK

7. SUM - példák • SELECT SUM(mennyiseg * egysegar) FROM rtetel WHERE rszam = 4; • a 4-es számú rendelésen szereplő áruk összértéke • SELECT SUM(mennyiseg) FROM rtetel; • az összes megrendelt áru mennyisége (fajtától és mennyiségi egységtől függetlenül) Markó Tamás, PTE TTK

8. MIN, MAX, AVG - példa • SELECT MIN(cikknev), MAX(egysegar), AVG(atlegysegar) FROM cikk WHERE m_egyseg = ‘Kg’; • Azokra a cikkekre, amiknek “Kg” a mennyiségi egysége- a névsorban legelső cikknév, - az egységár maximuma,- és a cikkek átlagos egységárának átlaga Markó Tamás, PTE TTK

9. STDDEV, VARIANCE példa • SELECT STDDEV (egysegar), VARIANCE(atlegysegar) FROM cikk; • A cikkek egységárának szórása és átlagos egységárának varianciája Markó Tamás, PTE TTK

10. Az adatok csoportosítása • SELECT … FROM … [WHERE …] GROUP BY oszlop1 [, oszlop2 … ]; • Általában összesítő függvényekkel együtt használatos • A SELECT utáni oszlopneveknek vagy a GROUP BY-nál is elő kell fordulni, vagy már a SELECT-nél egy összesítő függvényben kell szerepelni Markó Tamás, PTE TTK

11. GROUP BY - 1. példa • SELECT cikkszam, AVG(mennyiseg) FROM rtetel GROUP BY cikkszam; • A megrendeléseken szereplő cikkek cikkszámát és az átlagos megrendelt mennyiséget kapjuk • Mellékhatásként a cikkszámok növekvő rendezése is megtörténik Markó Tamás, PTE TTK

12. GROUP BY - 2. példa • SELECT rszam, sum(mennyiseg * egysegar) as ertek, count(*) as tetelszam FROM rtetel GROUP BY rszam; • Megadja rendelésenként az összértéket és a tételszámot Markó Tamás, PTE TTK

13. Csoportosítás több mező szerint • A rendszer először az elsőként megadott mező értékei szerint képez csoportokat … • … aztán minden csoportban alcsoportokat képez a második mező szerint … • … aztán minden alcsoportban al-alcsoportokat képez a harmadik mező szerint … • stb. Markó Tamás, PTE TTK

14. Csoportosítás több mező szerint - 1. példa • SELECT rszam, szall_hi, sum(mennyiseg * egysegar) as ertek FROM rtetel GROUP BY rszam, szall_hi; • Megadja a rendelt áruk összértékét megrendelésenként, azon belül szállítási határidő szerint csoportosítva Markó Tamás, PTE TTK

15. Csoportosítás több mező szerint - 2. példa • SELECT min(nev1 || ‘ ‘ || nev2) as vevo, rtetel.rszam, sum(mennyiseg * egysegar) as ertek FROM vevok, rendel, rtetel WHERE vevok.vevokod = rendel.vevokod AND rendel.rszam = rtetel.rszam GROUP BY rendel.vevokod, rtetel.rszam; • Megadja a rendelt áruk összértékét vevőnként, azon belül megrendelésenként csoportosítva • Ha a GROUP BY után felcseréljük a mezőket, ugyanezeket az adatokat kapjuk, csak más sorrendben Markó Tamás, PTE TTK

16. Csoportosítás több mező szerint - 3. példa • SELECT min(nev1 || ‘ ‘ || nev2) as vevo, min(cikk.cikknev), sum(mennyiseg) as mennyi, avg(egysegar) as atlagos_egysegar, sum(mennyiseg * egysegar) as ertek FROM vevok, rendel, rtetel, cikk WHERE vevok.vevokod = rendel.vevokod AND rendel.rszam = rtetel.rszam AND rtetel.cikkszam = cikk.cikkszam GROUP BY rtetel.cikkszam, rendel.vevokod; • Megadja a rendelt áruk mennyiségét, átlagos egységárát és összértékét vevőnként, azon belül cikkenként csoportosítva

17. Feltétel megadása a csoportosított adatokra • Alakja: HAVING feltétel • A GROUP BY után áll • A feltétel olyan, mint a WHERE után Markó Tamás, PTE TTK

18. HAVING - példa • SELECT rszam, SUM(mennyiseg * egysegar) as ertek, COUNT(*) as tetelszam FROM rtetel GROUP BY rszam HAVING COUNT(*) <= 5; • Megadja rendelésenként az összértéket és a tételszámot, de csak azok a megrendelések jelennek meg, amelyeken legfeljebb 5 tétel szerepel Markó Tamás, PTE TTK

19. … és még rendezni is lehet! • SELECT rszam, SUM(mennyiseg * egysegar) as ertek, COUNT(*) as tetelszam FROM rtetel GROUP BY rszam HAVING COUNT(*) <= 5ORDER BY 2; • Az előző példa adatai összérték szerint rendezve Markó Tamás, PTE TTK

20. A lekérdezések végrehajtási sorrendje • A rendszer kiválasztja a WHERE feltételnek eleget tevő sorokat • Csoportosítja őket a GROUP BY szakasz előírásai szerint • Kiszámítja minden csoportra az összesítő függvények értékét • Kiválogatja a HAVING feltételnek eleget tevő sorokat • Rendezi a megmaradt sorokat az ORDER BY előírásai szerint Markó Tamás, PTE TTK

21. Gyűjtőfüggvények egymásba ágyazása • Példa:SELECT AVG(MAX(egysegar)) FROM rtetel GROUP BY rszam; • Rendelésenként kiszámítja az egységár maximumát - és ennek az átlagát veszi a rendelésekre Markó Tamás, PTE TTK

22. Halmazműveletek Markó Tamás, PTE TTK

23. Rendeltetésük és használatuk • Két lekérdezés (SELECT utasítás) eredményhalmazán hajtódnak végre • A két lekérdezés által szolgáltatott sorok szerkezetének (az oszlopok számának és adattípusának) azonosnak kell lenni Markó Tamás, PTE TTK

24. A támogatott halmazműveletek • UNION: mindkét lekérdezésben szereplő egyedi sorok (únió) • UNION ALL: mindkét lekérdezésben szereplő sorok (ismétlések is előfordulhatnak) • INTERSECT: mindkét lekérdezésben szereplő egyedi sorok (metszet) • MINUS: az első lekérdezésben szereplő, de a másodikban nem szereplő egyedi sorok Markó Tamás, PTE TTK

25. UNION - példa • SELECT vevokod FROM rendelUNIONSELECT vevokod FROM vevok WHERE varos = ‘Budapest’; • Azon vevők kódja, akiknek van megrendelésük, vagy pedig a címük Budapesten van Markó Tamás, PTE TTK

26. MINUS - példa • SELECT cikkszam FROM rtetel WHERE rszam = 1MINUSSELECT cikkszam FROM rtetel WHERE rszam = 4; • Az 1-es megrendelésen szereplő, de a 4-es megrendelésen nem szereplő cikkek cikkszámai Markó Tamás, PTE TTK