Generowanie serii i sekwencji
Twórz wiersze za pomocą rekurencji
Generowanie serii i sekwencji to bezpłatna lekcja SQL Academy na CoddyKit. To lekcja 3 z 4. Możesz przeczytać całą lekcję poniżej za darmo — a potem ćwiczyć ją interaktywnie w przeglądarce z wbudowanym edytorem kodu i tutorem AI dostępnym 24/7. To część ścieżki edukacyjnej SQL Academy, a Twój postęp synchronizuje się między webem a aplikacją CoddyKit. Kurs SQL Academy zawiera 4 lekcji w sumie.
Czym jest generowana seria?
Czasami potrzebna jest lista liczb, dat lub innych sekwencyjnych wartości, które nie występują w żadnej tabeli. SQL umożliwia tworzenie takich list w locie za pomocą rekurencji lub wbudowanych funkcji.
W tej lekcji nauczą się Państwo generować sekwencje za pomocą WITH RECURSIVE — potężnego narzędzia, które pozwala zapytaniu odwoływać się do własnego wyniku.
Pierwsze rekurencyjne CTE
Rekurencyjne CTE (Common Table Expression) ma dwie części połączone przez UNION ALL: przypadek bazowy generujący pierwszy wiersz oraz krok rekurencyjny odwołujący się do samego CTE w celu wygenerowania kolejnego wiersza.
Rekurencja kończy się, gdy krok rekurencyjny nie zwraca żadnych wierszy.
WITH RECURSIVE counter(n) AS (
SELECT 1
UNION ALL
SELECT n + 1 FROM counter WHERE n < 5
)
SELECT n FROM counter;Jak rozwija się rekurencja
Pomaga wyobrazić sobie każdy krok osobno. Przypadek bazowy zasila tabelę, a następnie każde przejście rekurencyjne dodaje jeszcze jeden wiersz, aż warunek WHERE przestanie być spełniony.
Dla zapytania WHERE n < 5 silnik generuje wiersze 1, 2, 3, 4, 5, a następnie zatrzymuje się, ponieważ wyrażenie 5 < 5 jest fałszywe.
WITH RECURSIVE steps(n, note) AS (
SELECT 1, 'base case'
UNION ALL
SELECT n + 1, 'recursive step'
FROM steps
WHERE n < 4
)
SELECT n, note FROM steps;Generowanie liczb parzystych
Rozmiar kroku można zmienić, dodając w części rekurencyjnej wartość większą niż 1. W ten sposób powstaje każda liczba parzysta od 2 do 10.
Wzorzec jest zawsze taki: next_value = current_value + step.
WITH RECURSIVE evens(n) AS (
SELECT 2
UNION ALL
SELECT n + 2 FROM evens WHERE n < 10
)
SELECT n FROM evens;Odliczanie w dół
Rekurencja nie ogranicza się do zliczania w górę. Zamiast dodawać, należy odejmować, aby uzyskać sekwencję malejącą. Należy upewnić się, że warunek zatrzymania używa > zamiast <, aby uniknąć nieskończonej pętli.
WITH RECURSIVE countdown(n) AS (
SELECT 10
UNION ALL
SELECT n - 1 FROM countdown WHERE n > 1
)
SELECT n FROM countdown;Generowanie zakresu dat
Jednym z najbardziej praktycznych zastosowań rekurencyjnych CTE jest tworzenie serii dat. Rozpoczyna się od określonej daty i dodaje po jednym dniu aż do osiągnięcia daty końcowej.
Jest to szczególnie przydatne do uzupełniania luk w raportach — pojawia się każda data, nawet jeśli dla danego dnia nie ma danych.
WITH RECURSIVE dates(d) AS (
SELECT DATE '2024-01-01'
UNION ALL
SELECT d + INTERVAL '1 day'
FROM dates
WHERE d < DATE '2024-01-07'
)
SELECT d FROM dates;Uzupełnianie braków w raportach za pomocą serii dat
Wyobraźmy sobie tabelę sales, która zawiera wiersze tylko dla dni, w których wystąpiła sprzedaż. Łącząc wygenerowaną serię dat z tabelą sales za pomocą LEFT JOIN, otrzymujemy każdy dzień w zakresie, a dla dni bez sprzedaży wartość NULL.
WITH RECURSIVE cal(d) AS (
SELECT DATE '2024-03-01'
UNION ALL
SELECT d + INTERVAL '1 day' FROM cal WHERE d < DATE '2024-03-05'
),
sales(sale_date, amount) AS (
VALUES
(DATE '2024-03-01', 100),
(DATE '2024-03-03', 250),
(DATE '2024-03-05', 180)
)
SELECT cal.d, COALESCE(sales.amount, 0) AS amount
FROM cal
LEFT JOIN sales ON cal.d = sales.sale_date
ORDER BY cal.d;Generowanie tabliczki mnożenia
Rekurencyjne CTE mogą przenosić wiele kolumn, co pozwala tworzyć bogatsze wyniki. Tutaj jednocześnie śledzone są indeks wiersza i obliczona wartość.
WITH RECURSIVE mult(n, result) AS (
SELECT 1, 1 * 7
UNION ALL
SELECT n + 1, (n + 1) * 7
FROM mult
WHERE n < 10
)
SELECT n, result AS seven_times_n FROM mult;Liczby Fibonacciego
Fibonacci to klasyczna sekwencja, w której każda liczba jest sumą dwóch poprzednich: 0, 1, 1, 2, 3, 5, 8 …
Rekurencyjne CTE śledzi zarówno bieżącą wartość a, jak i następną wartość b, zamieniając je miejscami w każdym kroku.
WITH RECURSIVE fib(a, b) AS (
SELECT 0, 1
UNION ALL
SELECT b, a + b FROM fib WHERE a < 100
)
SELECT a AS fibonacci FROM fib;Używanie generate_series w PostgreSQL
PostgreSQL oferuje wbudowany skrót o nazwie generate_series(), który tworzy sekwencje bez konieczności pisania rekurencyjnego CTE. Przyjmuje wartość początkową, końcową oraz opcjonalny krok.
Jest to najbardziej zwięzły sposób generowania zakresów liczb lub dat w PostgreSQL.
SELECT gs AS num
FROM generate_series(1, 10, 2) AS gs;Generowanie miesięcznych przedziałów
Przekazanie interwału '1 month' do funkcji generate_series() pozwala utworzyć kalendarz miesięczny. Jest to idealne rozwiązanie do tworzenia nagłówków miesięcznych raportów lub sprawdzania, w których miesiącach brakuje danych.
SELECT gs::DATE AS month_start
FROM generate_series(
'2024-01-01'::DATE,
'2024-06-01'::DATE,
INTERVAL '1 month'
) AS gs;Sprawdzenie wiedzy
Sprawdź swoją wiedzę na temat generowania serii za pomocą rekurencyjnych CTE.
Podsumowanie: generowanie serii i sekwencji
W tej lekcji nauczyli się Państwo tworzyć sekwencje liczb i dat bez żadnej tabeli źródłowej:
- WITH RECURSIVE — przypadek bazowy oraz krok rekurencyjny połączone za pomocą UNION ALL; kończy działanie, gdy krok rekurencyjny nie zwraca żadnych wierszy.
- Elastyczny rozmiar kroku — dodawanie lub odejmowanie dowolnej wartości umożliwia zliczanie w górę, zliczanie w dół albo pomijanie wartości.
- Zakresy dat — dodanie interwału pozwala generować dzienne, miesięczne lub niestandardowe serie kalendarzowe.
- CTE z wieloma kolumnami — przenoszenie dodatkowego stanu między iteracjami umożliwia tworzenie bogatszych wyników, takich jak ciąg Fibonacciego.
- generate_series() — wbudowana funkcja PostgreSQL do zwięzłego generowania sekwencji liczb i dat.
Generowane serie są niezbędne do uzupełniania luk w raportach, tworzenia danych testowych oraz wszędzie tam, gdzie potrzebny jest pełny zakres wartości niezależnie od zawartości danych.
Często zadawane pytania
Czy lekcja „Generowanie serii i sekwencji” jest bezpłatna?
Tak — pełny tekst „Generowanie serii i sekwencji” jest dostępny za darmo tutaj w sieci. Aby ćwiczyć ją interaktywnie (wbudowany edytor kodu i tutor AI dostępny 24/7) i odblokować resztę kursu SQL Academy, przejdź na CoddyKit PRO. Kurs SQL Academy zawiera 4 lekcji w sumie.
Co nauczysz się w „Generowanie serii i sekwencji”?
Twórz wiersze za pomocą rekurencji Ćwiczysz SQL Academy z praktycznym kodem, który uruchamiasz bezpośrednio w przeglądarce, a tutor AI dostępny 24/7 odpowiada na Twoje pytania podczas pracy nad lekcją.
Czy potrzebuję doświadczenia, aby zacząć SQL Academy?
Nie wymagamy żadnego doświadczenia. SQL Academy w CoddyKit jest strukturyzowany dla początkujących i zaawansowanych użytkowników, więc możesz zacząć tutaj lub od początku i uczyć się w swoim tempie. To lekcja 3 z 4.
Ile czasu zajmuje lekcja „Generowanie serii i sekwencji”?
Większość lekcji CoddyKit trwa około 5–10 minut. Każda lekcja to mały, interaktywny krok, dzięki czemu robisz systematyczne postępy i zawsze wracasz dokładnie do tego samego miejsca — na webie i w aplikacji.
Czy mogę pisać i uruchamiać kod w tej lekcji SQL Academy?
Tak. Każda lekcja SQL Academy zawiera wbudowany edytor kodu, więc piszesz i uruchamiasz prawdziwy kod bezpośrednio w przeglądarce i od razu otrzymujesz sprzężenie zwrotne od AI — bez konfiguracji na komputerze.
Wszystkie lekcje w tym kursie
- Jak działają rekurencyjne CTE
- Przechodzenie drzewa kategorii
- Generowanie serii i sekwencji
- Unikanie nieskończonych pętli