Typowe błędy przy pracy z agregatami

Teraz czas zagłębić się w typowe błędy, które pojawiają się przy używaniu tych funkcji. Nawet najbardziej ogarnięci SQL-mistrzowie czasem wchodzą na miny, a naszym celem jest nauczyć się je zauważać i skutecznie omijać.

Pisałeś kiedyś zapytanie i nagle wyskoczył dziwny błąd typu "column must appear in the GROUP BY clause or be used in an aggregate function"? Albo wynik twojego zapytania wyglądał dziwnie i nie miałeś pojęcia dlaczego? To tylko wierzchołek góry lodowej typowych błędów przy pracy z funkcjami agregującymi. Ten wykład to twój przewodnik po przetrwaniu w morzu błędów i nieporozumień.

Błąd 1: Użycie nieagregowanej kolumny poza GROUP BY

Problem

Napisałeś zapytanie, które zwraca dane zagregowane, ale po drodze dorzuciłeś kolumnę, która nie jest częścią grupowania i nie jest objęta funkcją agregującą. Na przykład:

SELECT department, salary, SUM(salary)
FROM employees
GROUP BY department;

PostgreSQL od razu ci powie:

ERROR: column "employees.salary" must appear in the GROUP BY clause or be used in an aggregate function

Dlaczego tak się dzieje?

Kiedy używasz GROUP BY, PostgreSQL łączy wiersze według podanych kolumn. Ale jeśli dorzucasz jeszcze jedną kolumnę (tu salary), PostgreSQL nie wie, co z nią zrobić. Nie rozumie, czy chcesz jedną pensję, średnią czy coś innego.

Jak to naprawić? Są dwa sposoby:

1. Upewnij się, że wszystkie nieagregowane kolumny są w GROUP BY:

SELECT department, salary
FROM employees
GROUP BY department, salary;

2. Albo opakuj kolumnę funkcją agregującą, jeśli to ma sens:

SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;

Tip: jeśli PostgreSQL narzeka na GROUP BY, zapytaj siebie: "Czy ta kolumna na pewno jest mi potrzebna w zapytaniu? Jeśli tak, to jaką dokładnie rolę pełni?"

Błąd 2: Złe użycie COUNT() i NULL

Problem: chcesz policzyć ilu pracowników podało swoje bonusy i piszesz:

SELECT COUNT(bonus) AS bonus_count
FROM employees;

Ale nagle widzisz, że wynik jest mniejszy niż się spodziewałeś. Dlaczego? Bo COUNT(column) ignoruje wiersze, gdzie column to NULL.

Rozwiązanie: jeśli chcesz policzyć wszystkie wiersze, użyj COUNT(*):

SELECT COUNT(*) AS total_count
FROM employees;

Albo doprecyzuj, że chcesz tylko te wiersze, gdzie bonus nie jest NULL:

SELECT COUNT(bonus) AS bonus_count
FROM employees
WHERE bonus IS NOT NULL;

Podpowiedź: jeśli chcesz rozróżnić między rekordami z NULL a całkowitym brakiem rekordów w tabeli, uważnie wybieraj między COUNT(*) a COUNT(column).

Błąd 3: Zapomniana filtracja przez HAVING zamiast WHERE

Problem: chcesz znaleźć działy, gdzie średnia pensja przekracza 5000. Nowy dev może napisać coś takiego:

SELECT department, AVG(salary) AS avg_salary
FROM employees
WHERE AVG(salary) > 5000
GROUP BY department;

PostgreSQL wyrzuci błąd:

ERROR: aggregate functions are not allowed in WHERE clause

To się dzieje, bo filtracja na poziomie WHERE dzieje się przed grupowaniem, a funkcje agregujące są liczone już po grupowaniu. Więc średnia pensja AVG(salary) nie jest jeszcze policzona na etapie WHERE.

Żeby to naprawić, użyj HAVING do filtrowania danych zagregowanych:

SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 5000;

Błąd 4: Filtracja z WHERE i zamieszanie w kolejności wykonywania

Problem: chcesz się dowiedzieć ilu pracowników jest w działach, gdzie wiek pracowników jest większy niż 30 lat. Zapytanie może wyglądać tak:

SELECT department, COUNT(*)
FROM employees
GROUP BY department
WHERE age > 30;

PostgreSQL znowu cię zasmuci:

ERROR: syntax error at or near "WHERE"

Dlaczego tak? Operator WHERE zawsze jest przetwarzany przed GROUP BY. W tym przypadku po prostu postawiłeś WHERE w złym miejscu.

Żeby tego uniknąć, zmień kolejność: najpierw odfiltruj wiersze, potem grupuj.

SELECT department, COUNT(*)
FROM employees
WHERE age > 30
GROUP BY department;

Błąd 5: Użycie NULL z SUM(), AVG() i innymi funkcjami

Problem: chcesz znaleźć sumę bonusów wypłaconych pracownikom i piszesz:

SELECT SUM(bonus) AS total_bonus
FROM employees;

Ale wynik wydaje się podejrzanie niski. A to dlatego, że połowa pracowników nie ma wpisanych bonusów i te NULL są po prostu ignorowane.

Rozwiązanie: obsłuż NULL wcześniej. Na przykład możesz zamienić NULL na 0:

SELECT SUM(COALESCE(bonus, 0)) AS total_bonus
FROM employees;

Teraz wszystkie NULL będą zamienione na 0 i suma będzie poprawna.

Dokładniej omówimy działanie funkcji COALESCE już za parę wykładów.

Błąd 6: użycie kilku funkcji agregujących bez zrozumienia ich relacji

Problem: chcesz policzyć ogólną liczbę pracowników i sumę pensji. Ale piszesz coś, co zwraca dziwne wyniki:

SELECT COUNT(salary) AS count_salary, SUM(salary) AS total_salary
FROM employees;

Dlaczego tu coś może pójść nie tak? Jeśli ktoś ma pensję NULL, COUNT(salary) i SUM(salary) dadzą różne wyniki, co może zamieszać.

Zawsze pamiętaj, że funkcje agregujące działają niezależnie od siebie. Jeśli są NULL, to prowadzi do różnych wyników. Używaj COALESCE albo COUNT(*), żeby mieć spójność:

SELECT COUNT(*) AS total_employees, SUM(COALESCE(salary, 0)) AS total_salary
FROM employees;

Błąd 7: Nieoptymalne zapytania z dużą liczbą grupowań

Problem: odpalasz zapytanie z dużą ilością grupowań i działa ono pięć godzin zamiast pięciu minut:

SELECT department, job_title, location, COUNT(*)
FROM employees
GROUP BY department, job_title, location;

Przed wykonaniem grupowania zastanów się, czy na pewno wszystkie kolumny są potrzebne w GROUP BY. Im więcej unikalnych wartości w grupie, tym dłużej trwa zapytanie. Jeśli możesz, ogranicz grupowanie:

SELECT department, COUNT(*)
FROM employees
GROUP BY department;

Te błędy są powszechne i nawet najbardziej doświadczeni SQL-developerzy się na nie łapią. Mam nadzieję, że teraz łatwiej będzie ci omijać te rafy i pisać zapytania, które działają szybko, poprawnie i elegancko.