SQL Interview Prep · Lekcja

Refaktoryzacja zagnieżdżonych zapytań do CTE

Wzorzec przydatny podczas rozmowy na żywo: przekształcanie nieczytelnego zagnieżdżonego zapytania w kolejne etapy CTE

Refaktoryzacja zagnieżdżonych zapytań do CTE to bezpłatna lekcja SQL Interview Prep 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 Interview Prep, a Twój postęp synchronizuje się między webem a aplikacją CoddyKit. Kurs SQL Interview Prep zawiera 4 lekcji w sumie.

Przekształcanie zapytania podczas rozmowy na żywo

To typowe zadanie na stanowisko średniego szczebla: oto zapytanie — proszę je uczynić czytelnym. Osoba rekrutująca pokazuje głęboko zagnieżdżone zapytanie SELECT i obserwuje, jak rozkładają je Państwo na części. Zamiana zagnieżdżenia na sekwencję nazwanych CTE to najczytelniejsze rozwiązanie.

W tej lekcji pokazano dokładne kroki, aby mogli je Państwo spokojnie wykonać przy tablicy.

Należy zacząć od najbardziej wewnętrznego zapytania

Zagnieżdżone podzapytania są wykonywane koncepcyjnie od środka na zewnątrz. Dlatego zapytanie również należy czytać od środka: najpierw trzeba znaleźć najgłębiej zagnieżdżone zapytanie SELECT w nawiasach — to będzie pierwszy etap potoku.

Należy nadać mu opisową nazwę i przenieść je do CTE. Wszystkie miejsca, które odwoływały się do tego wewnętrznego bloku, powinny teraz odwoływać się do nazwy CTE.

SELECT *
FROM (
    SELECT customer_id, SUM(amount) AS total
    FROM orders
    GROUP BY customer_id
) t
WHERE t.total > 1000;

Przeniesienie jednego poziomu do CTE

Należy wziąć tę najgłębiej zagnieżdżoną tabelę pochodną i przekształcić ją w CTE. Zapytanie zewnętrzne pozostaje bez zmian, z wyjątkiem tego, że teraz pobiera dane z nazwanego CTE.

Ten pojedynczy krok usuwa jedną warstwę mentalnego zagnieżdżenia i nadaje etapowi znaczącą nazwę.

WITH spend AS (
    SELECT customer_id, SUM(amount) AS total
    FROM orders
    GROUP BY customer_id
)
SELECT *
FROM spend
WHERE total > 1000;

Rzeczywiście zagnieżdżony przykład

Oto trudniejszy przykład do przekształcenia: dwa poziomy zagnieżdżenia oraz filtr w stylu korelowanym. Celem jest obliczenie średniej wartości zamówienia wśród klientów z najwyższego poziomu wydatków.

Zapytanie jest poprawne, ale trudne do odczytania. Rozłożymy je na części etap po etapie.

SELECT AVG(o.amount) AS avg_order
FROM orders o
WHERE o.customer_id IN (
    SELECT customer_id
    FROM (
        SELECT customer_id, SUM(amount) AS total
        FROM orders
        GROUP BY customer_id
    ) s
    WHERE s.total > 1000
);

Nazwanie pierwszego etapu

Najgłębszy blok oblicza łączne wydatki każdego klienta. Należy przenieść go do CTE o nazwie spend. Teraz środkowa warstwa po prostu filtruje to CTE.

Proszę zauważyć, że każde wydzielenie zmniejsza głębokość zagnieżdżenia o jeden poziom i dodaje nazwę, która sama dokumentuje przeznaczenie etapu.

WITH spend AS (
    SELECT customer_id, SUM(amount) AS total
    FROM orders
    GROUP BY customer_id
)
SELECT AVG(o.amount) AS avg_order
FROM orders o
WHERE o.customer_id IN (
    SELECT customer_id FROM spend WHERE total > 1000
);

Nazwanie drugiego etapu

Należy wydzielić filtr dotyczący spend do osobnego CTE o nazwie big_spenders. Pozostałe główne zapytanie staje się płaskim łączeniem lub testem przynależności do jasno nazwanego zbioru.

Każdy etap ma teraz jedno zadanie — to znak rozpoznawczy przejrzystego kodu SQL.

WITH spend AS (
    SELECT customer_id, SUM(amount) AS total
    FROM orders GROUP BY customer_id
),
big_spenders AS (
    SELECT customer_id FROM spend WHERE total > 1000
)
SELECT AVG(o.amount) AS avg_order
FROM orders o
JOIN big_spenders b ON b.customer_id = o.customer_id;

Zachowanie semantyki podczas refaktoryzacji

Najważniejsza zasada: refaktoryzacja nie może zmienić wyników. Należy uważać na pułapki, które po cichu zmieniają rezultat:

Zamiana IN na JOIN może wprowadzić zduplikowane wiersze, jeśli prawa strona nie jest unikatowa.
NOT IN z wartościami NULL działa inaczej niż NOT EXISTS.
Poziom szczegółowości agregacji musi pozostać taki sam.

Warto głośno wymienić te zagrożenia, aby pokazać dbałość o szczegóły.

Weryfikacja refaktoryzacji

Jak dowieść, że refaktoryzacja zachowuje pierwotną logikę? Warto wspomnieć, że należy uruchomić obie wersje i porównać liczbę wierszy oraz sumę kontrolną albo porównać zestawy wyników na próbce danych.

Podczas rozmowy kwalifikacyjnej nawet samo powiedzenie zweryfikowałbym to, porównując liczbę wierszy i kilka przykładowych rekordów pokazuje dyscyplinę inżynierską wykraczającą poza zwykłe przepisanie składni.

SELECT COUNT(*), SUM(amount)
FROM orders
WHERE customer_id IN (SELECT customer_id FROM big_spenders);

Kiedy NIE refaktoryzować

Refaktoryzacja nie zawsze oznacza poprawę. Pojedyncze, płytkie podzapytanie może być czytelniejsze, jeśli pozostanie bez zmian, a nadmierne dzielenie zapytania na wiele małych CTE również może pogorszyć jego czytelność.

Należy kierować się własną oceną: refaktoryzować wtedy, gdy zagnieżdżenie utrudnia zrozumienie celu lub gdy logika jest używana ponownie. Warto powiedzieć osobie prowadzącej rozmowę, że należy zakończyć refaktoryzację, gdy zapytanie można czytać od góry do dołu jako sekwencję odrębnych, nazwanych kroków.

Lista kontrolna refaktoryzacji

Powtarzalny schemat, który warto przedstawić:

Czytać od środka na zewnątrz, aby znaleźć najgłębiej zagnieżdżone podzapytanie.
Przenieść je do nazwanego CTE.
Powtarzać ten proces na kolejnych poziomach, po jednym naraz.
Nazywać każdy etap zgodnie z tym, co on tworzy.
Potwierdzić, że wyniki się nie zmieniły (zwracając uwagę na pułapki związane z IN/JOIN i NULL).

Dzięki temu trudne, zagnieżdżone zapytanie zamienia się w spokojne, etapowe przepisanie.

Jak komunikować refaktoryzację

Proszę mówić podczas pracy: Najbardziej wewnętrzny blok oblicza wydatki poszczególnych klientów, więc nazwę go spend. Następna warstwa wybiera klientów o dużych wydatkach. Następnie zewnętrzne zapytanie oblicza średnią kwotę ich zamówień.

Osoby prowadzące rozmowę oceniają komunikację w takim samym stopniu jak poprawność. Refaktoryzacja opisywana krok po kroku pokazuje dokładnie tę dojrzałość programistyczną na poziomie mid-level, której oczekują.

Szybkie sprawdzenie

Proszę wskazać właściwy pierwszy krok podczas refaktoryzacji głęboko zagnieżdżonego zapytania do postaci z CTE.

Podsumowanie: refaktoryzacja do postaci z CTE

Opanowali Państwo spokojną, powtarzalną metodę refaktoryzacji: czytać od środka na zewnątrz, przenieść najgłębiej zagnieżdżone podzapytanie do nazwanego CTE i przechodzić na zewnątrz po jednym poziomie naraz.

Nazywać każdy etap zgodnie z tym, co on tworzy.
Zachowywać semantykę; zwracać uwagę na duplikaty wynikające z różnic między IN a JOIN oraz na pułapki związane z NULL.
Weryfikować wyniki, porównując liczbę wierszy i przykładowe rekordy.
Nie dzielić zapytania nadmiernie; zakończyć, gdy można je odczytać jako jasną sekwencję nazwanych kroków.

To kończy kurs CTE; mogą teraz Państwo pewnie refaktoryzować zapytania podczas rzeczywistej rozmowy kwalifikacyjnej.

Często zadawane pytania

Czy lekcja „Refaktoryzacja zagnieżdżonych zapytań do CTE” jest bezpłatna?

Tak — pełny tekst „Refaktoryzacja zagnieżdżonych zapytań do CTE” 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 Interview Prep, przejdź na CoddyKit PRO. Kurs SQL Interview Prep zawiera 4 lekcji w sumie.

Co nauczysz się w „Refaktoryzacja zagnieżdżonych zapytań do CTE”?

Wzorzec przydatny podczas rozmowy na żywo: przekształcanie nieczytelnego zagnieżdżonego zapytania w kolejne etapy CTE Ćwiczysz SQL Interview Prep 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 Interview Prep?

Nie wymagamy żadnego doświadczenia. SQL Interview Prep 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 „Refaktoryzacja zagnieżdżonych zapytań do CTE”?

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 Interview Prep?

Tak. Każda lekcja SQL Interview Prep 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

Pisanie pierwszego CTE
Łączenie wielu CTE
CTE a podzapytanie i tabela tymczasowa
Refaktoryzacja zagnieżdżonych zapytań do CTE

← Powrót do SQL Interview Prep