SQL - używając aliasu w Group By


143

Jestem po prostu ciekawy składni SQL. Więc jeśli mam

SELECT 
 itemName as ItemName,
 substring(itemName, 1,1) as FirstLetter,
 Count(itemName)
FROM table1
GROUP BY itemName, FirstLetter

Byłoby to nieprawidłowe, ponieważ

GROUP BY itemName, FirstLetter 

naprawdę powinno być

GROUP BY itemName, substring(itemName, 1,1)

Ale dlaczego nie możemy po prostu użyć tego pierwszego dla wygody?


13
jest to dozwolone w Postgresql
Michael Buen

7
MySQL również na to pozwala
Kip

1
o których rdbms mówisz?
Shiwangini

Odpowiedzi:


292

SQL jest implementowany tak, jakby zapytanie zostało wykonane w następującej kolejności:

  1. Klauzula FROM
  2. Klauzula WHERE
  3. Klauzula GROUP BY
  4. HAVING klauzula
  5. Klauzula SELECT
  6. Klauzula ORDER BY

W przypadku większości systemów relacyjnych baz danych ta kolejność wyjaśnia, które nazwy (kolumny lub aliasy) są prawidłowe, ponieważ musiały zostać wprowadzone w poprzednim kroku.

Dlatego w Oracle i SQL Server nie można użyć terminu w klauzuli GROUP BY zdefiniowanej w klauzuli SELECT, ponieważ GROUP BY jest wykonywana przed klauzulą ​​SELECT.

Są jednak wyjątki: MySQL i Postgres wydają się mieć dodatkową inteligencję, która na to pozwala.


3
Podoba mi się to wyjaśnienie. Chociaż nie mogę spekulować, jak trudno jest dodać go do silnika jako cukier syntaktyczny.
Haoest

11
Czy jest jakiś pomysł, czy baza danych jest wystarczająco inteligentna, aby zrealizować to samo wyrażenie, znajduje się w klauzulach SELECT i GROUP BY bez ponownego oceniania wyrażeń? tj. jeśli tak GROUP BY substring(itemName, 1,1), to czy baza danych jest wystarczająco inteligentna, aby nie przyjąć wydajności wynikającej z ponownego obliczenia podciągu w klauzuli SELECT?
Kip

10
W klauzuli SELECT zapytania z grupowaniem masz dostęp tylko do wyrażeń GROUP BY i wartości zagregowanych. Więc nie chodzi o bycie mądrym; musi być wdrożony w ten sposób, aby zgrupowanie działało. (I jest to wymagane przez standard SQL). Ale nawet w bardziej błahych przypadkach (np. To samo wyrażenie w klauzuli WHERE i SELECT) najnowocześniejsze systemy baz danych z pewnością obliczą je tylko raz. Ta optymalizacja nazywana jest zwykłą eliminacją podwyrażeń .
Codo

6
Co ma wspólnego polecenie wykonania z pytaniem? To nie jest tak, że pytający próbował GROUP BY na COUNT (). W rzeczywistości zapytanie, o które pytano, działa dobrze w MySQL i prawdopodobnie PostgreSQL, jak wskazano w komentarzach.

1
W przypadku mysql, sql_modebez uwzględnienia ONLY_FULL_GROUP_BY w masce bitowej, Optymalizator ma szansę na uzyskanie lepszych wyników przy zróżnicowanym / różnym użyciu aliasu w HAVINGklauzuli.
Drew

28

Zawsze możesz użyć podzapytania, aby móc użyć aliasu; Oczywiście sprawdź wydajność (możliwe, że serwer db będzie działał tak samo, ale weryfikacja nigdy nie zaszkodzi):

SELECT ItemName, FirstLetter, COUNT(ItemName)
FROM (
    SELECT ItemName, SUBSTRING(ItemName, 1, 1) AS FirstLetter
    FROM table1
    ) ItemNames
GROUP BY ItemName, FirstLetter

2
W miarę możliwości należy unikać zapytań podrzędnych z powodu złej wydajności. Korzystanie z kopii funkcji jest znacznie lepsze, ponieważ jest oczywiście wykrywane przez optymalizator bazy danych i wykonywane tylko raz.
Roland

1
@Roland, ale w tym przypadku nie ma innego planu wykonania. Czy są jakieś inne kwestie związane z wydajnością?
Guido Mocha

@Roland, Skorelowane podzapytania lub inna składnia, która prowadzi do pętli lub zachowania wiersz po wierszu, należy unikać, a istnieje ograniczenie co do tego, jak głęboko należy sięgać z podzapytaniami zagnieżdżonymi, ale generalnie nie jest prawdą, że zapytania podrzędne prowadzą do złej wydajności. W tym przypadku, jak powiedział Chris, możesz zweryfikować plan wykonania (plan zapytań AKA, plan wyjaśnienia), porównując zarówno z podzapytaniem, jak i bez, i sprawdzić, czy naprawdę jest jakaś różnica. Prawie każdy silnik bazy danych ponownie zapisze zapytanie, więc nie masz całkowitej kontroli nad tym, co zostanie wykonane. O to chodzi w deklaratywnej składni.
Davos,

16

Przynajmniej w PostgreSQL możesz użyć numeru kolumny w zestawie wyników w swojej klauzuli GROUP BY:

SELECT 
 itemName as ItemName,
 substring(itemName, 1,1) as FirstLetter,
 Count(itemName)
FROM table1
GROUP BY 1, 2

Oczywiście zaczyna to być uciążliwe, jeśli robisz to interaktywnie i edytujesz zapytanie, aby zmienić liczbę lub kolejność kolumn w wyniku. Ale nadal.


GROUP BY FirstLetterjest dozwolone w Postgresql. Na przykład, spróbuj uruchomić to w Postgresql: wybierz podciąg (nazwa_tabeli, 1,2) jako tname z grupy information_schema.tables według tname
Michael Buen

1
@MichaelBuen Wydaje mi się potencjalnie problematyczne. Z szybkiego testu wynika, że ​​jeśli istnieje alias i kolumna tabeli bazowej o tej samej nazwie, ta ostatnia ma priorytet? SQL Fiddle . Więc jeśli polegasz na tej grupie przez alias, późniejsza zmiana schematu może po cichu przerwać zapytanie i zmienić semantykę.
Martin Smith

@MartinSmith dopiero teraz wiedział, że to problem, powstrzyma się od używania tego, dzięki. Biorąc pod uwagę, że PostgreSQL zezwala na ten skrót, powinni nadać aliasowi priorytet, w przeciwnym razie nie powinni w ogóle zezwalać na ten skrót.
Michael Buen

To był straszny pomysł projektantów PostgreSQL. Jest to mylące, gdy tylko spróbujesz użyć GROUP BYwyrażenia, które zawiera funkcje agregujące lub funkcje okna, które „oczywiście” nie działają.
Lukas Eder

13

SQL Server nie pozwala na odwoływanie się do aliasu w klauzuli GROUP BY ze względu na logiczną kolejność przetwarzania. Klauzula GROUP BY jest przetwarzana przed klauzulą ​​SELECT, więc alias nie jest znany podczas oceny klauzuli GROUP BY. To wyjaśnia również, dlaczego możesz użyć aliasu w klauzuli ORDER BY.

Oto jedno źródło informacji o fazach przetwarzania logicznego programu SQL Server .


8

Nie odpowiadam, dlaczego tak jest, ale chciałem tylko pokazać sposób obejścia tego ograniczenia w SQL Server, używając CROSS APPLYdo utworzenia aliasu. Następnie używasz go w GROUP BYklauzuli, na przykład:

SELECT 
 itemName as ItemName,
 FirstLetter,
 Count(itemName)
FROM table1
CROSS APPLY (SELECT substring(itemName, 1,1) as FirstLetter) Alias
GROUP BY itemName, FirstLetter

4

Uwaga, używanie aliasu w grupie Group By (w przypadku usług, które ją obsługują, takich jak postgres) może mieć niezamierzone rezultaty. Na przykład, jeśli utworzysz alias, który już istnieje w instrukcji wewnętrznej, funkcja Group By wybierze nazwę pola wewnętrznego.

-- Working example in postgres
select col1 as col1_1, avg(col3) as col2_1
from
    (select gender as col1, maritalstatus as col2, 
    yearlyincome as col3 from customer) as layer_1
group by col1_1;

-- Failing example in postgres
select col2 as col1, avg(col3)
from
    (select gender as col1, maritalstatus as col2,
    yearlyincome as col3 from customer) as layer_1
group by col1;

3

Niektóre DBMS pozwalają na użycie aliasu zamiast powtarzania całego wyrażenia.
Teradata jest jednym z takich przykładów.

Unikam notacji pozycji porządkowej zgodnie z zaleceniami Billa z powodów udokumentowanych w tym pytaniu SO .

Łatwą i niezawodną alternatywą jest zawsze powtarzanie wyrażenia w klauzuli GROUP BY.
DRY NIE ma zastosowania do SQL.


1

Uważaj na aliasy podczas grupowania wyników z widoku w SQLite. Otrzymasz nieoczekiwane wyniki, jeśli nazwa aliasu jest taka sama, jak nazwa kolumny dowolnej podstawowej tabeli (do widoków).


0

Kiedyś odkryłem, że Rdb, poprzedni produkt DEC obsługiwany teraz przez Oracle, zezwalał na użycie aliasu kolumny w GROUP BY. Od wersji Mainstream Oracle do wersji 11 nie można używać aliasu kolumny w funkcji GROUP BY. Nie jestem pewien, co Postgresql, SQL Server, MySQL itp. Pozwoli lub nie pozwoli. YMMV.

Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.