Pisanie zapytań analitycznych
Przekrój, analizuj szczegółowo i agreguj metryki
Pisanie zapytań analitycznych to bezpłatna lekcja SQL Academy na CoddyKit. To lekcja 4 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 są zapytania analityczne?
Zapytania analityczne wykraczają poza proste wyszukiwanie wierszy. Zamiast pytać które zamówienie złożył klient 42?, pytają jaki jest łączny przychód według regionu i kwartału? albo jak ten miesiąc wypada w porównaniu z poprzednim?
W hurtowni danych opartej na schemacie gwiazdy zapytania analityczne wykonują operacje slice (filtrowanie jednego wymiaru), dice (filtrowanie wielu wymiarów) i roll-up (agregowanie do mniej szczegółowego poziomu), aby wydobywać informacje biznesowe z tabel faktów.
Powtórzenie: schemat gwiazdy
Schemat gwiazdy składa się z jednej centralnej tabeli faktów (np. fact_sales) otoczonej przez tabele wymiarów (np. dim_date, dim_product, dim_store). Zapytania analityczne łączą tabelę faktów z tymi wymiarami, które są potrzebne w bieżącej analizie.
SELECT
s.store_name,
d.year,
d.quarter,
SUM(f.revenue) AS total_revenue,
SUM(f.units_sold) AS total_units
FROM fact_sales f
JOIN dim_store s ON s.store_id = f.store_id
JOIN dim_date d ON d.date_id = f.date_id
GROUP BY
s.store_name,
d.year,
d.quarter
ORDER BY
d.year,
d.quarter,
s.store_name;Slice: filtrowanie jednego wymiaru
Slice oznacza ograniczenie zbioru wyników do jednej wartości jednego wymiaru — na przykład analizę wyłącznie danych dla roku 2024. Klauzula WHERE jest narzędziem do wykonywania operacji slice.
Wczesne wykonanie operacji slice zmniejsza liczbę wierszy, które baza danych musi agregować, dzięki czemu zapytania na dużych tabelach faktów działają szybciej.
-- Slice: only year 2024
SELECT
p.category,
SUM(f.revenue) AS total_revenue
FROM fact_sales f
JOIN dim_product p ON p.product_id = f.product_id
JOIN dim_date d ON d.date_id = f.date_id
WHERE d.year = 2024
GROUP BY p.category
ORDER BY total_revenue DESC;Dice: filtrowanie wielu wymiarów
Dice oznacza jednoczesne zastosowanie filtrów do co najmniej dwóch wymiarów — na przykład analizę sprzedaży elektroniki w regionie północnym w pierwszym kwartale. Każdy dodatkowy warunek WHERE wyodrębnia mniejszy fragment danych.
-- Dice: category = 'Electronics', region = 'North', Q1
SELECT
d.month,
SUM(f.revenue) AS revenue,
SUM(f.units_sold) AS units
FROM fact_sales f
JOIN dim_product p ON p.product_id = f.product_id
JOIN dim_store s ON s.store_id = f.store_id
JOIN dim_date d ON d.date_id = f.date_id
WHERE
p.category = 'Electronics'
AND s.region = 'North'
AND d.year = 2024
AND d.quarter = 1
GROUP BY d.month
ORDER BY d.month;Roll-up: agregowanie do wyższego poziomu szczegółowości
Roll-up oznacza przejście od szczegółowego poziomu danych (dzienna sprzedaż dla każdego sklepu) do mniej szczegółowego poziomu (miesięczna sprzedaż według regionu). Osiąga się to przez usunięcie kolumn niższego poziomu z GROUP BY i ponowne wykonanie agregacji.
Modyfikator ROLLUP pozwala wygenerować sumy częściowe i sumy końcowe w jednym zapytaniu, zamiast pisać wiele bloków UNION ALL.
-- Roll up from store/month to region/quarter with subtotals
SELECT
s.region,
d.quarter,
SUM(f.revenue) AS revenue
FROM fact_sales f
JOIN dim_store s ON s.store_id = f.store_id
JOIN dim_date d ON d.date_id = f.date_id
WHERE d.year = 2024
GROUP BY ROLLUP(s.region, d.quarter)
ORDER BY s.region NULLS LAST, d.quarter NULLS LAST;Porównania okres do okresu za pomocą LAG
Jednym z najczęściej stosowanych wzorców analitycznych jest porównywanie miary z tą samą miarą w poprzednim okresie. Funkcja okna LAG() pozwala bezpośrednio pobrać wartość z poprzedniego wiersza do bieżącego wiersza, bez używania samozłączenia.
W tym przykładzie obliczamy procentowy wzrost przychodów miesiąc do miesiąca.
WITH monthly AS (
SELECT
d.year,
d.month,
SUM(f.revenue) AS revenue
FROM fact_sales f
JOIN dim_date d ON d.date_id = f.date_id
GROUP BY d.year, d.month
)
SELECT
year,
month,
revenue,
LAG(revenue) OVER (ORDER BY year, month) AS prev_month_revenue,
ROUND(
100.0 * (revenue - LAG(revenue) OVER (ORDER BY year, month))
/ NULLIF(LAG(revenue) OVER (ORDER BY year, month), 0),
2) AS mom_growth_pct
FROM monthly
ORDER BY year, month;Sumy narastające za pomocą SUM OVER
Suma narastająca (suma skumulowana) dodaje wartość każdego wiersza do sumy wszystkich wcześniejszych wierszy w określonej kolejności. Doskonale nadaje się to do śledzenia skumulowanych przychodów w ciągu roku lub monitorowania stopniowego wykorzystywania budżetu.
Klauzula ramki ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW sprawia, że zakres okna jest jawny i jednoznaczny.
SELECT
d.year,
d.month,
SUM(f.revenue) AS monthly_revenue,
SUM(SUM(f.revenue)) OVER (
PARTITION BY d.year
ORDER BY d.month
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS ytd_revenue
FROM fact_sales f
JOIN dim_date d ON d.date_id = f.date_id
GROUP BY d.year, d.month
ORDER BY d.year, d.month;Ranking wymiarów za pomocą DENSE_RANK
Ranking pozwala znaleźć najlepsze lub najsłabsze wyniki w obrębie grupy. DENSE_RANK() przypisuje kolejne rangi bez luk w przypadku remisów, dlatego jest preferowanym wyborem w rankingach prezentowanych w raportach BI.
Umieszczenie wyniku rankingu w CTE i filtrowanie według rangi pozwala uzyskać przejrzysty i czytelny wzorzec top-N.
WITH ranked_products AS (
SELECT
p.product_name,
p.category,
SUM(f.revenue) AS revenue,
DENSE_RANK() OVER (
PARTITION BY p.category
ORDER BY SUM(f.revenue) DESC
) AS rnk
FROM fact_sales f
JOIN dim_product p ON p.product_id = f.product_id
JOIN dim_date d ON d.date_id = f.date_id
WHERE d.year = 2024
GROUP BY p.product_name, p.category
)
SELECT *
FROM ranked_products
WHERE rnk <= 3
ORDER BY category, rnk;Procentowy udział za pomocą SUM w oknie
Znajomość bezwzględnej wartości przychodów produktu jest przydatna, ale informacja, że produkt odpowiada za 38 % przychodów kategorii, jest bardziej praktyczna. Funkcja SUM() działająca w oknie dla całej partycji dostarcza mianownika bez złączenia z podzapytaniem.
SELECT
p.category,
p.product_name,
SUM(f.revenue) AS product_revenue,
SUM(SUM(f.revenue)) OVER (PARTITION BY p.category) AS category_revenue,
ROUND(
100.0 * SUM(f.revenue)
/ SUM(SUM(f.revenue)) OVER (PARTITION BY p.category),
1) AS pct_of_category
FROM fact_sales f
JOIN dim_product p ON p.product_id = f.product_id
JOIN dim_date d ON d.date_id = f.date_id
WHERE d.year = 2024
GROUP BY p.category, p.product_name
ORDER BY p.category, pct_of_category DESC;Średnie kroczące do wygładzania trendów
Dzienne lub tygodniowe wartości sprzedaży są zmienne. Średnia krocząca wygładza krótkoterminowe wahania, dzięki czemu można dostrzec podstawowy trend. W tym przykładzie średnia krocząca z 3 miesięcy jest obliczana za pomocą przesuwanej ramki okna.
WITH monthly_rev AS (
SELECT
d.year,
d.month,
SUM(f.revenue) AS revenue
FROM fact_sales f
JOIN dim_date d ON d.date_id = f.date_id
GROUP BY d.year, d.month
)
SELECT
year,
month,
revenue,
ROUND(
AVG(revenue) OVER (
ORDER BY year, month
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
),
2) AS moving_avg_3m
FROM monthly_rev
ORDER BY year, month;CUBE dla wszystkich kombinacji wymiarów
CUBE rozszerza działanie ROLLUP, obliczając sumy częściowe dla każdej możliwej kombinacji wymienionych wymiarów, a nie tylko dla hierarchicznej ścieżki agregacji w górę. W jednym przebiegu tworzy pełne podsumowanie międzywymiarowe — jest to przydatne w wielowymiarowych dashboardach, w których użytkownicy mogą swobodnie zmieniać układ danych.
Wartość NULL w kolumnie grupowania oznacza wszystkie wartości danego wymiaru — użyj GROUPING(), aby odróżnić zamierzone wartości NULL w danych od wartości NULL wynikających z agregacji w górę.
SELECT
CASE WHEN GROUPING(s.region) = 1 THEN 'ALL REGIONS' ELSE s.region END AS region,
CASE WHEN GROUPING(p.category) = 1 THEN 'ALL CATEGORIES' ELSE p.category END AS category,
CASE WHEN GROUPING(d.quarter) = 1 THEN 'ALL QUARTERS' ELSE d.quarter::TEXT END AS quarter,
SUM(f.revenue) AS revenue
FROM fact_sales f
JOIN dim_store s ON s.store_id = f.store_id
JOIN dim_product p ON p.product_id = f.product_id
JOIN dim_date d ON d.date_id = f.date_id
WHERE d.year = 2024
GROUP BY CUBE(s.region, p.category, d.quarter)
ORDER BY s.region NULLS LAST, p.category NULLS LAST, d.quarter NULLS LAST;Która operacja ogranicza wyniki do jednej wartości wymiaru?
Sprawdź swoją wiedzę na temat terminologii zapytań analitycznych stosowanej w hurtowniach danych.
Podsumowanie: pisanie zapytań analitycznych
W tej lekcji poznali Państwo podstawowe wzorce tworzenia zapytań analitycznych dla schematu gwiazdy:
- Slice — filtrowanie jednego wymiaru za pomocą WHERE w celu skupienia się na określonym segmencie.
- Dice — jednoczesne filtrowanie wielu wymiarów w celu wyodrębnienia precyzyjnego fragmentu danych.
- Roll-up — agregowanie do mniej szczegółowego poziomu; użycie
ROLLUPlubCUBEdo tworzenia wielopoziomowych sum częściowych. - LAG / LEAD — porównania okres do okresu bez samozłączeń.
- Sumy narastające & średnie kroczące — skumulowane i wygładzone miary za pomocą ramek okna.
- DENSE_RANK — przejrzysty ranking top-N w obrębie partycji.
- Procentowy udział — funkcja SUM w oknie jako mianownik przy obliczaniu udziałów.
Połączenie tych wzorców pokrywa zdecydowaną większość wymagań dotyczących BI i raportowania, z którymi spotkają się Państwo w produkcyjnych hurtowniach danych.
Ucz się SQL dzięki korepetycjom AI — za darmo
Pisz i uruchamiaj kod w przeglądarce, otrzymuj natychmiastową pomoc od korepetytora AI dostępnego 24/7 i kontynuuj naukę w sieci lub w aplikacji.
- Kursy
- 46
- Lekcje
- 183
Często zadawane pytania
Czy lekcja „Pisanie zapytań analitycznych” jest bezpłatna?
Tak — pełny tekst „Pisanie zapytań analitycznych” 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 „Pisanie zapytań analitycznych”?
Przekrój, analizuj szczegółowo i agreguj metryki Ć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 4 z 4.
Ile czasu zajmuje lekcja „Pisanie zapytań analitycznych”?
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
- OLTP a OLAP
- Tabele faktów i wymiarów
- Schematy gwiazdy i płatka śniegu
- Pisanie zapytań analitycznych