group by
Grupowanie polega na podzieleniu zbioru wierszy na grupy, które mają pewną wspólną cechę. Grupowania dokonuje się w celu zastosowania funkcji agregujących nie w stosunku do całego zbioru wierszy, ale do poszczególnych grup wierszy. W celu zgrupowania rekordów należy dodać nową klauzulę GROUP BY wraz z wyspecyfikowaniem kolumny lub wyrażenia, według którego mają być pogrupowane wiersze. Na liście klauzuli SELECT mogą się znaleźć tylko kolumny i wyrażenia, według których zapytanie jest grupowane, oraz wywołania funkcji agregujących.
Przed dokonaniem grupowania można zastosować klauzulę WHERE, która wybierze tylko część wierszy z tabeli. Klauzula WHERE wykonuje się przed grupowaniem, a zatem nie można w tej klauzuli sprecyzować warunku zawierającego funkcje grupowe. Aby taki warunek zawrzeć w zapytaniu należy zastosować dodatkową klauzulę HAVING wraz z odpowiednim warunkiem. Jest ona odpowiednikiem klauzuli WHERE, tylko, że wykonuje się ona po procesie grupowania. W poniższym przykładzie najpierw wybrałem numery lokalizacji w których id managera nie jest puste. W drugim pogrupowane numery lokalizacji oraz ilość departamentów w danej lokalizacji ale tylko te w których jest przydzielony manager. W trzecim tylko te lokalizacje w których poza tym że jest przydzielony manager, ilość departamentów jest większa niż 1.
Rollup
Dodanie rollup do group by spowoduje wyświetlenie podsumowania dla każdej grupy. Poniżej przykład grupowania z bez użycia rollup. Możemy wyświetlić sumę wypłat w danym departamencie dla ludzi podległych pod danego managera. Patrz przykład poniżej:
Bez funkcji ROLLUP moglibyśmy wyznaczyć sumę wypłat dla każdego z działów tworząc oddzielne zapytanie, bez grupowania po id managera:
Natomiast jeśli zechcemy wyświetlić podsumowanie wypłat dla ludzi podległych jednemu managerowi (wraz z nim) ale również podsumowanie dla każdego działu jednocześnie, musimy zastosować rollup:
Cube
Dodanie do instrukcji słowa kluczowego powoduje wygenerowanie podsumowań dla wszystkich możliwych kombinacji kolumn wymienionych w zapytaniu oraz dołączenie ogólnej wartości sumy. Poniżej widzimy pierwszą część wyniku jaki dało nam zastosowanie CUBE. Nie różni się on od wyniku zwracanego nam przez zastosowanie ROLLUP.
W końcowej części wyniku widzimy czym różni się ROLLUP odCUBE: Poza podsumowaniem wypłat dla każdego departamentu i wypłat ludzi konkretnego managera w tym departamencie widzimy również podsumowanie wypłat dla ludzi danego managera we wszystkich departamentach razem wziętych, oraz podsumowanie całości.
Ćwiczenia
1. Wyświetl najwyższą pensję dla każdego działu
2. Wyświetl najwyższą pensję dla każdego managera
3. Wyświetl najwyższą pensję dla każdego działu i posortuj rosnąco
4. Wyświetl średnią pensję dla każdego działu bez powtarzających się pensji
5. Wyświetl najwyższą pensję dla każdego działu ale tylko w działach o numerach 50,80 i 60
6. Wyświetl najniższą pensję dla każdego działu ale uwzględniając w obliczeniach tylko osoby które nie mają litery e w nazwisku.
7. Wyświetl średnie zarobki podsumowując je wg. managerów dla każdego działu oddzielnie.
8. Wyświetl średnie zarobki podsumowując je wg. managerów dla każdego działu oddzielnie z jednoczesnym podsumowaniem średnich zarobków osób podległych pod jednego managera.
9. Wyświetl średnie zarobki podsumowując je wg. managerów dla każdego działu oddzielnie ale uwzględniając tylko działy o numerach 50,80,60 10. Wyświetl średnie zarobki podsumowując je wg. managerów dla każdego działu oddzielnie ale uwzględniając tylko działy o numerach 50,80,60 z jednoczesnym podsumowaniem dla każdego managera średnich zarobków osób podległych pod jednego managera tylko w działach 50, 80, 60.
Ten temat omawiam na poniższych szkoleniach:
• Podstawy Oracle SQL
• Podstawy SQL i PL/SQL
Możesz w nich uczestniczyć, a jako czytelnik tego bloga otrzymasz 10% zniżki - poinformuj o tym fakcie konsultanta.
Można prosić o jakiś klucz?
OdpowiedzUsuńMoje rozwiązania:
OdpowiedzUsuńad1: SQL> select department_id, max(salary) from employees group by department_id order by 1;
ad2: SQL> select manager_id, max(salary) from employees group by manager_id order by 1;
ad3: SQL> select department_id, max(salary) from employees where department_id is not null group by department_id order by 2;
ad4: SQL> select distinct round(avg(salary)), department_id from employees where department_id is not null group by department_id order by 1;
ad5: SQL> select department_id, max(salary) from employees where department_id in (50,80,60) group by department_id;
ad6: SQL> select department_id, min(salary) from employees where last_name not like '%E%' and last_name not like '%e%' and department_id is not null group by department_id order by 2;
ad7: SQL> select avg(salary), manager_id, department_id from employees where department_id is not null group by rollup(department_id, manager_id);
ad8: SQL> select avg(salary), manager_id, department_id from employees where department_id is not null and manager_id is not null group by cube(department_id, manager_id);
ad9: SQL> select avg(salary), manager_id, department_id from employees where department_id in (50,80,60) group by rollup(department_id, manager_id);
ad10: SQL> select avg(salary), manager_id, department_id from employees where department_id in (50,80,60) group by cube(department_id, manager_id);
Ten komentarz został usunięty przez autora.
OdpowiedzUsuńFajnie się to czyta. Mam jednak pytania co do rollup:
OdpowiedzUsuń1. Pierwszy przykład: w tekście"... grupowania z bez użycia" napisałeś "z i "bez" - co chyba się wyklucza. W zapytaniu nie odnajduję rollup.
2. W drugim przykładzie jest rollup, a nie ma podsum - czy jest ono potrzebne?.
3. Proponuję bardziej szczegółowo skomentować przykłady oraz opisać na czym polega różnica objęciem rollup jednej lub obu kolumn.
4. W aktualnym wydaniu OracleXE struktura i zawartość przykładowej bazy danych jest inna - może warto by odświeżyć przykłady.
Ogólnie się z Tobą zgadzam, jednakże mam nieco inne propozycje:
OdpowiedzUsuń1. Select department_id, max(salary) najwyzsz_pensja from employees group by department_id order by 1
2. Select manager_id, max(salary) najwyzsza_pensja from employees group by manager_id order by 1
3. Select department_id, max(salary) najwyzsza_pensja from employees group by department_id order by najwyzsza_pensja
4. Select department_id, avg(distinct salary) sred_placa_bez_powt from employees group by department_id order by 1
5. Select department_id, max(salary) najwyzsza_pensja from employees group by department_id having department_id in(50, 60, 80) order by 1
6. Select department_id, min(salary) pensja_minimalna from employees where last_name not like '%e%' group by department_id order by 1
7. Select department_id, manager_id, avg(salary) from employees group by rollup (department_id, manager_id) order by 1, 2
8. Select department_id, manager_id, avg(salary) from employees group by cube (department_id, manager_id) order by 1, 2
9. Select department_id, manager_id, avg(salary) from employees group by rollup (department_id, manager_id) having department_id in (50, 80, 60) order by 1, 2
10. Select department_id, manager_id, avg(salary) from employees group by cube (department_id, manager_id) having department_id in (50, 80, 60) order by 1, 2
W zadaniu 7 wprowadziłbym małą poprawkę, która spowoduje ze nie bedzie podsumowania zbiorowego bo nie bylo w zadaniu o nim mowy. A zatem zwiniemy tylko kolumne manager_id.
OdpowiedzUsuńSelect department_id, manager_id, avg(salary) from employees group by department_id, rollup(manager_id) order by 1, 2;
Moim zdaniem w zadaniu 6 do klauzuli where powinno zostać dodane
OdpowiedzUsuńand last_name not like 'E%'
bez niego pozostają nazwiska rozpoczynające się na E