Przykłady złożonych zapytań z tablicami: agregacja, filtrowanie, sortowanie

Dzisiaj ogarniemy, jak wyciągnąć maksimum z tablic w zapytaniach: zbierać wartości w grupy, filtrować po zawartości i nawet sortować bezpośrednio w tablicach. To nie jest teoria dla teorii — takie patenty pojawiają się w raportach, analityce, personalizacji i masie realnych sytuacji. Wszystko jest proste, jak się załapie zasadę — i właśnie tym się teraz zajmiemy.

Agregacja danych z tablicami

Praca z tablicami szczególnie błyszczy, gdy trzeba pogrupować dane. Zamiast dostawać kilka wierszy — zbieramy potrzebne wartości w jedną zgrabną tablicę. To ułatwia analizę, robi wynik bardziej kompaktowy i często pozwala olać zbędne podzapytania. Zobaczmy, jak to działa w praktyce.

Przykład 1: grupowanie danych do tablic z array_agg()

Kiedy chcesz zebrać wartości z kilku wierszy w jednej grupie do tablicy, ratuje cię array_agg(). To chyba najfajniejsza funkcja do ogarniania tablic przy agregacji.

-- Mamy tabelę students z polami id, name i course
CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    course VARCHAR(100)
);

-- Wrzucamy kilka wierszy
INSERT INTO students (name, course) VALUES
('Alicja', 'Matematyka'),
('Bob', 'Matematyka'),
('Czarek', 'Fizyka'),
('Dawid', 'Fizyka'),
('Emma', 'Matematyka');

-- Grupujemy studentów po kursach do tablic
SELECT course, array_agg(name) AS students
FROM students
GROUP BY course;

Wynik:

Grupowanie wartości do tablic jest wygodne, jeśli chcesz przekazać dane w formacie, który łatwo rozbić, np. w JSON.

Przykład 2: tworzenie zagnieżdżonych tablic

A co jeśli mamy jeszcze jedną tabelę i chcemy zebrać dane z dwóch tabel do tablic? Na przykład tabela courses z info o nauczycielach.

-- Tworzymy tabelę nauczycieli
CREATE TABLE courses (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    teacher VARCHAR(100)
);

-- Wrzucamy dane
INSERT INTO courses (name, teacher) VALUES
('Matematyka', 'Prof. Min'),
('Fizyka', 'Prof. Peterson');

-- Zagnieżdżone zapytanie do tworzenia tablic
SELECT
    c.name AS course_name,
    array_agg(s.name) AS students,
    c.teacher
FROM
    courses c
LEFT JOIN
    students s
ON
    c.name = s.course
GROUP BY
    c.name, c.teacher;

Wynik:

Teraz mamy wygodną tabelę pokazującą kursy, ich nauczycieli i studentów w formacie tablic.

Filtrowanie danych z tablicami

Same tablice — już są mocnym narzędziem, ale prawdziwa magia zaczyna się, gdy nauczysz się filtrować dane na ich podstawie. Chcesz wybrać tylko tych userów, którzy mają w liście zainteresowań konkretne słowo? Albo zamówienia, gdzie każda cena przekracza dany próg? Wszystko to ogarniesz bezpośrednio w SQL — bez zbędnej logiki po stronie apki.

Przykład 1: filtrowanie wierszy po elementach tablicy

Załóżmy, że chcemy znaleźć wszystkie wiersze, gdzie tablica zawiera konkretne value, np. szukamy studentów zapisanych na kurs matematyki.

-- Filtrowanie studentów zapisanych na kursy przez `ANY`
SELECT *
FROM students
WHERE course = ANY(ARRAY['Matematyka', 'Fizyka']);

Tutaj ANY pozwala podać tablicę wartości, a zapytanie zwróci wiersze, gdzie course pasuje do choćby jednego value z tablicy.

Przykład 2: Sprawdzanie przecięcia tablic

Załóżmy teraz, że mamy tabelę student_interests, gdzie zainteresowania studentów są w formie tablic. Chcemy znaleźć studentów, których zainteresowania pokrywają się z naszymi kryteriami.

-- Tworzymy tabelę z zainteresowaniami studentów
CREATE TABLE student_interests (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    interests TEXT[]
);

-- Wrzucamy dane
INSERT INTO student_interests (name, interests) VALUES
('Alicja', ARRAY['programowanie', 'muzyka']),
('Bob', ARRAY['sport', 'programowanie']),
('Czarek', ARRAY['czytanie', 'fotografia']),
('Emma', ARRAY['muzyka', 'sport']);

-- Szukamy studentów zainteresowanych programowaniem lub muzyką
SELECT *
FROM student_interests
WHERE interests && ARRAY['programowanie', 'muzyka'];

Operator && sprawdza przecięcie dwóch tablic. Jeśli choć jeden element z tablicy po lewej pokrywa się z tablicą po prawej, wiersz przechodzi filtr.

Wynik:

Sortowanie tablic

Czasem kolejność wartości w tablicy ma znaczenie — szczególnie jeśli zbierasz tablicę z różnych wierszy albo chcesz przygotować dane do wyświetlenia. PostgreSQL pozwala posortować elementy bezpośrednio w zapytaniu, bez dodatkowej obróbki.

Przykład 1: sortowanie wartości w tablicy

Czasem trzeba posortować elementy w tablicy. Na przykład posortujmy tablicę zainteresowań studentów alfabetycznie.

-- Sortujemy elementy tablicy przez funkcję `array_sort()`
SELECT 
    name,
    array_sort(interests) AS sorted_interests
FROM 
    student_interests;

Wynik:

Przykład 2: sortowanie wierszy po długości tablicy

A teraz załóżmy, że chcemy posortować studentów po liczbie ich zainteresowań — od najbardziej zajawkowych do najbardziej "nudnych".

-- Sortujemy wiersze po długości tablicy
SELECT 
    name, 
    interests, 
    array_length(interests, 1) AS interests_count
FROM 
    student_interests
ORDER BY 
    interests_count DESC;

Wynik:

Chociaż wszyscy studenci mają tyle samo zainteresowań w tym przykładzie, podobne zapytanie można przerobić na większe tabele.

2

Zadanie

SQL SELF, poziom 36, lekcja 3

Niedostępne

Grupowanie wartości w tablice

Grupowanie wartości w tablice

Otwórz