4.7 GROUP BY, HAVING
Konstrukcja GROUP BY umożliwia pogrupowanie wartości według zbioru właściwości. Konstrukcja HAVING umożliwia określenie dodatkowych ograniczeń, które zawężają wynik zapytania. Ograniczenia działają na grupach będących wynikiem działania GROUP BY.
Składnia klauzul GROUP BY oraz HAVING jest następująca:
klauzula_groupby ::= GROUP BY składnik_groupby {, składnik_groupby}*
składnik_groupby ::= wyrażenie_ścieżkowe_o_pojedyńczej_wartości | zmienna_identyfikacyjna
klazula_having ::= HAVING wyrażenie_warunkowe
Jeśli zapytanie zawiera klauzule WHERE oraz GROUP BY, najpierw wykonywana jest klauzula WHERE, po której następuje utworzenie group i odrzucenie tych, które nie spełniają warunku klauzuli HAVING.
Wymagania dla klauzuli SELECT, kiedy używana jest klauzula GROUP BY, są analogiczne do wymagań SQL: dowolny element występujący w klauzuli SELECT (inny niż parametr wejściowy funkcji agregującej) musi również występować w klauzuli GROUP BY. W tworzeniu group, wartości null są traktowane jako jednakowe, tj. należące do tej samej grupy.
W przypadku grupowania po encji, encja nie może zawierać serializowanych pól stanowych lub pól stanowych opartych o LOB (jednakże, co to znaczy grupowanie po encji nadal jest dla mnie niezrozumiałe - odkładam do wyjaśnienia na kolejne strony specyfikacji).
Klauzula HAVING musi definiować kryteria wyszukiwania względem grup lub funkcji agregujących.
Jeśli nie istnieje klauzula GROUP BY i korzysta się z klauzuli HAVING, wynik zapytania traktowany jest jako pojedyńcza grupa, i klauzula SELECT może jedynie zawierać funkcje agregujące. Dostawca trwałości nie jest zobowiązany do wspierania zapytań z klauzulą HAVING przy braku GROUP BY. Przenośne aplikacje nie powinny polegać na HAVING bez użycia GROUP BY.
Query query = em
.createQuery("SELECT p.rodzajProjektu, COUNT(p) FROM Projekt p GROUP BY p.rodzajProjektu ORDER BY p.rodzajProjektu");
List<Object[]> projekty = query.getResultList();
assert projekty.size() == 2 : projekty.size();
assert projekty.get(0)[0] == RodzajProjektu.OTWARTY && ((Long) projekty.get(0)[1]) == 3 :
"Oczekiwano 3 projektów otwartych, a otrzymano " + projekty.get(0)[1] + " projekty typu " + projekty.get(0)[0];
assert projekty.get(1)[0] == RodzajProjektu.KOMERCYJNY && ((Long) projekty.get(1)[1]) == 1 :
"Oczekiwano 1 projektu komercyjnego, a otrzymano " + projekty.get(1)[1] + " projekt(y) typu " + projekty.get(1)[0];
Specyfikacja przedstawia dwa przykłady zapytań z wykorzystaniem GROUP BY oraz HAVING.
Pierwsze zapytanie służy do pobrania statusu klientów, średniej ilości złożonych zamówień klientów z danym statusem oraz ich ilości pogrupowanych po statusie klientów (klauzula GROUP BY) dla statusu oznaczonego jako 1 lub 2 (klauzula HAVING).
Drugie zapytanie zwraca kraj oraz ilość klientów dla danego kraju, w których ilość klientów przekracza 3.
4.8 Klauzula SELECT
Klauzula SELECT wyznacza wynik zapytania. W wyniku zapytania możemy otrzymać pojedyńczą encję, listę encji, pojedyńczą wartość bądź tablicę wartości, wliczając w to tablicę wartości różnych typów.
Klauzula SELECT może zawierać:
- Wyrażenie ścieżkowe o pojedyńczej wartości (ang. single valued path expression)
- Wyrażenie agregujące (ang. aggregate expression), tj. działające na grupie wartości
- Zmienną identyfikującą (ang. identification variable)
- Wyrażenie OBJECT
- Wyrażenie tworzące/egzemplarzowe (ang. constructor expression)
Moją uwagę zwrócił element - wyrażenie tworzące/egzemplarzowe. Nigdy wcześniej nie spotkałem się z tym określeniem (tak przy okazji, nie widziałem tego również w Hibernate). Więcej za moment wraz z przykładami.
Należy zauważyć, że klauzula SELECT musi składać się wyłącznie z wyrażeń, które w wyniku zwracają pojedyńcze wartości. Niedozwolone jest zbudowanie zapytania, którego klauzula SELECT zawierałaby wyrażenie stworzone w ten sposób:
SELECT o.projekty FROM Osoba o
Wykonanie powyższego zapytania z Apache OpenJPA 0.9.7-SNAPSHOT zakończy się niepowodzeniem z następującym komunikatem:
<0.9.7-incubating-SNAPSHOT nonfatal user error> org.apache.openjpa.persistence.ArgumentException:
Query projections cannot include array, collection, or map fields. Invalid query:
"SELECT o.projekty FROM Osoba o"
Pole projekty jest polem relacji wiele-do-wielu i jest kolekcją encji, co jest niedozwolone.
Powyższe zapytanie zapisane z wykorzystaniem łączenia jest już całkowicie legalne i zwróci listę projektów, do których przypisane są osoby (projekty mogą być powielone).
SELECT p FROM Osoba o, IN(o.projekty) p
Słowo kluczowe DISTINCT wskazuje, aby powielone wartości zostały usunięte z wyniku zapytania.
SELECT DISTINCT p FROM Osoba o, IN(o.projekty) p
Samodzielne zmienne identyfikujące w klauzuli SELECT mogą opcjonalnie być parametrem wejściowym operatora OBJECT (co nie zmienia semantyki zapytania).
SELECT DISTINCT OBJECT(p) FROM Osoba o, IN(o.projekty) p
Zabronione jest korzystanie z OBJECT, aby oznaczać wyrażenia ścieżkowe.
Poniższe zapytanie jest nieprawidłowe ze względu na użycie wyrażenia ścieżkowego z OBJECT.
SELECT DISTINCT OBJECT(p.nazwa) FROM Osoba o, IN(o.projekty) p
4.8.1 Typ wyniku klauzuli SELECT
Typ wyniku zapytania określonego przez klauzulę SELECT jest typem abstrakcyjnego schematu (AST) encji, typem pola-stanu, wynikiem funkcji agregującej, wynikiem operacji tworzącej egzemplarz encji, lub ich kombinacją.
Typ wynikowy klauzuli SELECT jest wyznaczany przez typy elementów składowych klauzuli. Wiele elementów składa się na tablicę Object[], której elementy są ułożone zgodnie z kolejnością odpowiadających im wyrażeń w klauzuli SELECT.
Query query = em.createQuery("SELECT o, COUNT(o.projekty) FROM Osoba o JOIN o.projekty p GROUP BY o");
List<Object[]> resultQuery = query.getResultList();
assert resultQuery.size() == 3 :
"Oczekiwano 3 projektów (unikatowych) z uczestnikami podczas, gdy zwrócono " + resultQuery.size();
final Object[] firstElementInQueryResult = resultQuery.get(0);
assert firstElementInQueryResult.length == 2 :
"Oczekiwano 2 elementów w tablicy, a otrzymano " + firstElementInQueryResult.length;
final Object firstElement = resultQuery.get(0)[0];
assert firstElement instanceof Osoba :
"Oczekiwano, że pierwszy element w tablicy wyników jest typu Osoba, a otrzymano typ " +
firstElement.getClass().getCanonicalName();
final Object secondElement = resultQuery.get(0)[1];
assert secondElement instanceof Long :
"Oczekiwano, że drugi element w tablicy wyników jest typu Long, a otrzymano typ " + secondElement.getClass().getCanonicalName();
Typ wyniku wyrażenia składowego w klauzuli SELECT jest wyliczany następująco:
- Wyrażenie ścieżkowe o pojedyńczej wartości, które jest wyrażeniem ścieżkowym pola stanu skutkuje w egzemplarzu tego samego typu jak odpowiadający pole-stanu encji. Jeśli pole stanu encji jest typem podstawowym, odpowiadający typ obiektowy jest zwracany.
Query query = em.createQuery("SELECT o.numer, o.imie, o.nazwisko FROM Osoba o");
List<Object[]> resultQuery = query.getResultList();
assert resultQuery.size() == 4 : "Oczekiwano 4 osób, a zwrócono " + resultQuery.size();
final Object[] firstElementInQueryResult = resultQuery.get(0);
assert firstElementInQueryResult.length == 3 : "Oczekiwano 3 elementów w tablicy, a otrzymano " + firstElementInQueryResult.length;
final Object firstElement = resultQuery.get(0)[0];
assert firstElement instanceof Long : "Oczekiwano, że pierwszy element w tablicy wyników jest typu Long, a otrzymano typ " + firstElement.getClass().getCanonicalName();
final Object secondElement = resultQuery.get(0)[1];
assert secondElement instanceof String : "Oczekiwano, że drugi element w tablicy wyników jest typu String, a otrzymano typ " + secondElement.getClass().getCanonicalName();
final Object thirdElement = resultQuery.get(0)[2];
assert thirdElement instanceof String : "Oczekiwano, że trzeci element w tablicy wyników jest typu String, a otrzymano typ " + secondElement.getClass().getCanonicalName(); - Wyrażenie ścieżkowe o pojedyńczej wartości, które jest wyrażeniem ścieżkowym asocjacji o pojedyńczej wartości skutkuje w egzemplarzu encji (pod)typu pola-asocjacji.
Query query = em.createQuery("SELECT p.nazwa, p.chair FROM Projekt p");
List<Object[]> resultQuery = query.getResultList();
final Object[] firstElementInQueryResult = resultQuery.get(0);
assert firstElementInQueryResult.length == 2 : "Oczekiwano 2 elementów w tablicy, a otrzymano " + firstElementInQueryResult.length;
final Object firstElement = resultQuery.get(0)[0];
assert firstElement instanceof String : "Oczekiwano, że pierwszy element w tablicy wyników jest typu String, a otrzymano typ " + firstElement.getClass().getCanonicalName();
final Object secondElement = resultQuery.get(0)[1];
assert secondElement instanceof Osoba : "Oczekiwano, że drugi element w tablicy wyników jest typu Osoba, a otrzymano typ " + secondElement.getClass().getCanonicalName(); - Typ wynikowy zmiennej identyfikacyjnej jest (pod)typem encji, na którą wskazuje zmienna.
Query query = em.createQuery("SELECT p, o FROM Projekt p, IN(p.chair) o");
List<Object[]> resultQuery = query.getResultList();
final Object[] firstElementInQueryResult = resultQuery.get(0);
assert firstElementInQueryResult.length == 2 : "Oczekiwano 2 elementów w tablicy, a otrzymano " + firstElementInQueryResult.length;
final Object firstElement = resultQuery.get(0)[0];
assert firstElement instanceof Projekt : "Oczekiwano, że pierwszy element w tablicy wyników jest typu Projekt, a otrzymano typ " + firstElement.getClass().getCanonicalName();
final Object secondElement = resultQuery.get(0)[1];
assert secondElement instanceof Osoba : "Oczekiwano, że drugi element w tablicy wyników jest typu Osoba, a otrzymano typ " + secondElement.getClass().getCanonicalName(); - Typ wyniku wyznaczonego przez wyrażenie agregujące opisany jest poniżej.
- Typ wyniku wyznaczonego przez wyrażenie tworzące jest typu, dla którego wywoływany jest konstruktor. Typy parametrów wejściowych konstruktora są wyznaczane zgodnie z powyższymi regułami.
4.8.2 Wyrażenie tworzące w klauzuli SELECT
Klauzula SELECT może zawierać wyrażenie tworzące będące wywołaniem konstruktora. Klasa, do której należy konstruktor nie musi być klasą encji lub być zmapowana w jakikolwiek sposób do bazy danych. Nazwa konstruktora musi być kwalifikowana, tj. poprzedzona nazwą pakietu, do której należy.
Składnia wyrażenia tworzącego jest następująca:
wyrażenie_tworzące ::= NEW nazwa_konstruktora( parametr_wejściowy {,parametr_wejściowy})
parametr_wejściowy ::= wyrażenie_ścieżkowe_o_pojedyńczej_wartości | wyrażenie_agregujące
Przykład: Zapytanie, w którym tworzone są egzemplarze PewnaKlasa dla każdego rekordu korzystając z 4-argumentowego konstruktora.
Query query = em
.createQuery("SELECT NEW pl.jaceklaskowski.jpa.PewnaKlasa(p.nazwa, o.imie, o.nazwisko, o.numer), o, p.nazwa FROM Projekt p JOIN p.chair o");
List<Object[]> resultQuery = query.getResultList();
final Object[] firstElementInQueryResult = resultQuery.get(0);
assert firstElementInQueryResult.length == 3 : "Oczekiwano 3 elementów w tablicy, a otrzymano " + firstElementInQueryResult.length;
final Object firstElement = resultQuery.get(0)[0];
assert firstElement instanceof PewnaKlasa : "Oczekiwano, że pierwszy element w tablicy wyników jest typu PewnaKlasa, a otrzymano typ " + firstElement.getClass().getCanonicalName();
final Object secondElement = resultQuery.get(0)[1];
assert secondElement instanceof Osoba : "Oczekiwano, że drugi element w tablicy wyników jest typu Osoba, a otrzymano typ " + secondElement.getClass().getCanonicalName();
final Object thirdElement = resultQuery.get(0)[2];
assert thirdElement instanceof String : "Oczekiwano, że trzeci element w tablicy wyników jest typu String, a otrzymano typ " + thirdElement.getClass().getCanonicalName();
UWAGA: TopLink Essentials 2.0 BUILD 40 pozwala na korzystanie z wyrażenia tworzącego, które korzysta ze zmiennych identyfikujących (co jest niezgodne ze specyfikacją, ale nie zabronione).
UWAGA: Apache OpenJPA 0.9.7-SNAPSHOT nie wykona poprawnie powyższego przykładu ze względu na błąd analizy zapytania, które posiadania wiele wyrażeń w klauzuli SELECT z wyrażeniem egzemplarzowym.
Jeśli konstruktor należy do encji, zwrócone encje będą w stanie NOWY (w przeciwieństwie do innych zapytań, w których encje są w stanie ZARZĄDZANY).
{
Query query = em.createQuery("SELECT NEW pl.jaceklaskowski.jpa.entity.Osoba(o.imie, o.nazwisko) FROM Projekt p JOIN p.chair o");
List<Osoba> osoby = query.getResultList();
final Osoba osoba = osoby.get(0);
assert !em.contains(osoba) : "Egzemplarz encji nie może być zarządzany przez zarządcę trwałości";
}
{
Query query = em.createQuery("SELECT o FROM Projekt p JOIN p.chair o");
List<Osoba> osoby = query.getResultList();
final Osoba osoba = osoby.get(0);
assert em.contains(osoba) : "Egzemplarz encji musi być zarządzany przez zarządcę trwałości";
}
4.8.3 Wartości NULL w wyniku zapytania
Jeśli wynik zapytania korzysta z pola-asocjacji lub pola-stanu, którego wartość jest NULL, wartość NULL jest zwrócona w wyniku. Konstrukcja IS NOT NULL służy do usunięcia wartości NULL z wyniku zapytania.
Specyfikacja zwraca uwagę na pola-stanu, których typem jest numeryczny typ prosty w Javie, które nie przyjmują wartości NULL. Niedozwolone jest, aby zapytanie korzystające z takich pól zwróciło wartość NULL.
4.8.4 Funkcje agregujące w klauzuli SELECT
Wynikiem zapytania może być wynik funkcji agregującej przyłożonej do wyrażenia ścieżkowego.
Funkcje agregujące mogące występować w klauzuli SELECT to AVG, COUNT, MAX, MIN, SUM.
Wszystkie funkcje agregujące, poza COUNT, wymagają, aby ich argumentem wejściowym było wyrażenie ścieżkowe, które kończy się polem-stanu. Wyrażenie ścieżkowe będące parametrem wejściowym dla COUNT może kończyć się polem-stanu, polem-asocjacji, lub zmienną identyfikującą.
Parametrem wejściowym dla funkcji SUM oraz AVG muszą być typu liczbowego. Parametry wejściowe dla MAX oraz MIN muszą odpowiadać polom-stanu o typie porządkowym, tj. mogącym się uporządkować - typy liczbowe, łańcuchowe, znakowe oraz kalendarzowe.
Typy zwracane w zapytaniu z funkcjami agregującymi:
- COUNT zwraca Long
- MAX, MIN zwraca typ pola-stanu parametru wejściowego
- AVG zwraca Double
- SUM zwraca Long, jeśli pole-stanu jest typu liczbowego (poza BigInteger); Double, jeśli pole-stanu jest typu zmiennoprzecinkowego; BigInteger, jeśli pole-stanu jest typu BigInteger; BigDecimal, jeśli pole-stanu jest typu BigDecimal.
Query query = em
.createQuery("SELECT MAX(o.dzienImienin) FROM Osoba o WHERE o.imie = :imie AND o.imie <> :imie");
query.setParameter("imie", "Jacek");
Calendar calendar = (Calendar) query.getSingleResult();
assert calendar == null : "Nie istnieje osoba, która spełnia warunek w klauzuli WHERE, MAX musi zwrócić NULL, a otrzymano " + calendar;
W przypadku braku wartości, z których mogłaby skorzystać funkcja COUNT, zwracane jest 0.
Query query = em
.createQuery("SELECT COUNT(o.dzienImienin) FROM Osoba o WHERE o.imie = :imie AND o.imie <> :imie");
query.setParameter("imie", "Jacek");
long liczbaOsob = (Long) query.getSingleResult();
assert liczbaOsob == 0 : "Nie istnieje osoba, która spełnia warunek w klauzuli WHERE, COUNT musi zwrócić 0, a otrzymano " + liczbaOsob;
Argument funkcji agregującej może być poprzedzony słowem kluczowym DISTINCT, aby usunąć powielone wartości przed faktycznym wywołaniem funkcji. Zastosowanie DISTINCT w przypadku MAX oraz MIN jest dozwolone, ale nie wpływa na wynik.
query = em.createQuery("SELECT COUNT(p) FROM Osoba o JOIN o.projekty p");
liczbaProjektow = (Long) query.getSingleResult();
assert liczbaProjektow == 7 : "Oczekiwano 7 powielonych projektów, które są przypisane do osób, a otrzymano " + liczbaProjektow;
query = em.createQuery("SELECT COUNT(DISTINCT p) FROM Osoba o JOIN o.projekty p");
liczbaProjektow = (Long) query.getSingleResult();
assert liczbaProjektow == 3 : "Oczekiwano 3 unikatowych projektów, które są przypisane do osób, a otrzymano " + liczbaProjektow;
Wartości NULL są eliminowane zanim uruchomiona zostanie funkcja agregująca, bez względu na użycie DISTINCT. Jest to szczególnie istotne przy funkcji COUNT, gdzie wywołanie COUNT(o.dzienImienin) zliczy wyłącznie osoby z niepustym (nie NULL) dniem imienin podczas, gdy COUNT(o) zlicza wszystkie encje bez względu na wartości pól trwałych. Rozwiązaniem jest zastosowanie IS NOT NULL w klauzuli WHERE (wspaniale przedstawia to ostatni z przykładów w rozdziale 4.8.4.1 strona 102).
query = em.createQuery("SELECT COUNT(o) FROM Osoba o");
liczbaOsob = (Long) query.getSingleResult();
assert liczbaOsob == 4 : "4 encje powinny być zwrócone, a otrzymano " + liczbaOsob;
query = em.createQuery("SELECT COUNT(o.dzienImienin) FROM Osoba o");
liczbaOsobPoDzienImienin = (Long) query.getSingleResult();
assert liczbaOsobPoDzienImienin == 2 : "2 encje mają dostępny atrybut dzienImienin, a otrzymano " + liczbaOsobPoDzienImienin;
query = em.createQuery("SELECT COUNT(o) FROM Osoba o WHERE o.dzienImienin IS NOT NULL");
liczbaOsobPoDzienImienin = (Long) query.getSingleResult();
assert liczbaOsobPoDzienImienin == 2 : "2 encje mają dostępny atrybut dzienImienin, a otrzymano " + liczbaOsobPoDzienImienin;
Jak już wspomniałem, rozdział 4.8.4.1 jest zbiorem 5 przykładów z opisem ich wyników. Dobra lektura na utrwalenie wiadomości.
Brak komentarzy:
Prześlij komentarz