Jak najprościej wybrać top <n> wyniku zapytania SQL? – lekcja 14

Wstęp

Nie zawsze potrzebujesz cały posortowany wynik. Jak z posortowanego wyniku wybrać określoną liczbę początkowych wierszy? Innymi słowy jak wybrać top <n> wierszy lub wartości? I jaka jest różnica pomiędzy tymi podejściami? O tym jak to uzyskać przy pomocy SQL będzie niniejsza lekcja.

Top <n> wierszy

Zgodnym ze standardem ANSI sposobem na ograniczenie liczby wierszy wyniku jest użycie klauzuli FETCH. Wspominałem o niej we wcześniejszej lekcji. Pamiętasz?

• dostępna we wszystkich wiodących motorach baz danych
• wykonywana po klauzuli ORDER BY

Jeśli więc nakażesz motorowi bazy danych posortowanie wyniku i zwrócenie jedynie trzech pierwszych wierszy będą to wiersze z posortowanego wyniku.

SELECT
  cluster_name
FROM
  all_tables
ORDER BY
  cluster_name
FETCH NEXT 3 ROWS ONLY;

Top <n> wartości

A co zrobić w przypadku gdy nie wiesz ile będzie wierszy dla ostatniej wartości? Może się zdarzyć, że sortujesz według kolumny zawierającej powtarzające się dane. Wtedy ostatni wiersz może nie być ostatnim, który zawiera interesującą Cię wartość. Jeśli, więc chcesz zwrócić pierwsze kilka wierszy razem z następnymi zawierającymi tę samą wartość co ostatni z wyznaczonego limitu to możesz użyć składni WITH TIES zamiast ONLY. Działa od wersji Oracle 12c i tylko dla posortowanych wyników.

Zwróci wszystkie wiersze mające taką samą wartość cluster_name co ostatni z trzech rekordów. W takim przypadku nigdy nie wiesz ile wierszy dostaniesz.

Wydajność

Jawne specyfikowanie motorowi bazy danych, że interesuje Cię jedynie określona liczba wierszy lub wartości wyniku wpływa pozytywnie na wydajność. Optymalizatory baz danych, a na pewno optymalizator Oracle, przygotowane są do optymalizowania takich zapytań. Dlatego jeśli Twój posortowany wynik zawiera tysiące wierszy, ale interesuje Cię jedynie niewielka liczba początkowych to nie wahaj się powiedzieć o tym instancji bazy danych. Odwdzięczy się mniejszą konsumpcją zasobów komputera. Dzięki czemu wynik dostaniesz szybciej.

Komunikacja sieciowa jest najbardziej kosztowną operacją IO systemu komputerowego, więc przedkładaj filtrowanie danych w bazie danych nad filtrowanie w aplikacji. Nie musisz wierzyć mi na słowo. Przetestuj samodzielnie i pochwal się wynikiem na discord. Możesz też użyć komentarzy pod tym materiałem aby namówić mnie do przeprowadzenia takich testów.

Podsumowanie

Gratuluję Ci ukończenia czternastej lekcji darmowej edycji mojego kursu 'Podstawy SQL dla użytkowników Oracle’. Wiesz z niej, że używając składni FETCH NEXT n ROWS ONLY otrzymasz top n wierszy, a przy pomocy składni FETCH NEXT n ROWS WITH TIES otrzymasz top n wierszy razem z wierszami przekraczającymi limit, ale zawierającymi wartość identyczną co ostatni z limitu. W przypadku WITH TIES nigdy nie wiesz ile wierszy dostaniesz.

Używając sortowania w bazie danych trzeba mieć w pamięci, że jest kosztowne. Zużywa tym więcej zasobów im więcej danych jest do posortowania i im częściej Twoje zapytanie będzie używane. Jedną z możliwych optymalizacji sortowania jest jawne wskazanie liczby interesujących Cię wierszy wyniku.

Zadanie dla Ciebie: napisz i wykonaj zapytanie wybierające z widoku all_tables kolumnę avg_row_len dla wartości avg_row_len większych niż jeden. Wynik posortuj po kolumnie avg_row_len i wybierz pięć pierwszych wartości.

Jeśli potrzebujesz podpowiedzi wbijaj na discord. Tam też chwal się swoim rozwiązaniem prezentując zrzut ekranu swojego klienta. Dedykowany serwer: Marcin Badtke – bazy danych i SQL.

Wiesz już jak używać klauzuli FETCH do ograniczania wyniku zapytania do top <n> wierszy czy wartości. A co zrobić gdy chcesz zobaczyć ile jest wystąpień każdej wartości? Na przykład ile tabel ma każdy schemat? Jak uzyskać to przy pomocy klauzuli GROUP BY będzie następna lekcja. Tymczasem dziękuję za ukończenie niniejszej. Wiesz co trzeba zrobić aby nie przegapić kolejnych.

Epilog

Ten sposób zostawiłem na koniec jako bonus dla wytrwałych i ciekawych. Odradzam jego stosowanie. Prezentuję bo możesz natknąć się na niego w istniejącym już kodzie.

W materiale o pseudokolumnie rownum wspominałem, że jest to natywne rozwiązanie do ograniczania liczby wierszy w wyniku zapytania. Jak sądzisz czy przy jej pomocy możesz ograniczać ilość posortowanego wyniku? Sprawdź:

SELECT
  cluster_name
FROM
  all_tables
WHERE
  rownum<4
ORDER BY
  cluster_name;

Daj znać na discord jaki masz wynik. U mnie wyszło co innego niż w przykładzie gdzie użyłem klauzuli FETCH. Jak myślisz, dlaczego wyniki są różne?

Niemniej pseudokolumny rownum możesz także użyć do zwrócenia top <n> wierszy posortowanego wyniku. W tym celu należy specyficznie skonstruować zapytanie. Pamiętasz jak w lekcji o aliasach mówiłem, że w klauzuli FROM wskazujesz zbiory danych? Takim zbiorem jest tabela i widok. Zbiorem jest także wynik zapytania.

SELECT
  *
FROM
  (SELECT
    cluster_name
  FROM
    all_tables
  ORDER BY
    cluster_name)
WHERE
  rownum<4;

Czyli w zapytaniu umieszczonym w nawiasach w klauzuli FROM sortujesz dane według wybranych kryteriów. Następnie w zapytaniu zewnętrznym wybierasz z posortowanego wyniku top <n> wierszy. Jak zapewne pamiętasz pseudokolumna rownum nie jest przenaszalna. Jest natywna dla Oracle.

Jak myślisz dlaczego użycie rownum do wyboru top <n> wierszy wymaga specyficznej konstrukcji zapytania, a FETCH nie wymaga?

Taka konstrukcja zapytania w terminologii Oracle nazywa się inline view. Nie należy się jej bać. Jest przez motor bazy danych silnie optymalizowana. Zachęcam do przeprowadzenia testów wydajnościowych i pochwalenia się nimi na discordzie. W komentarzach możesz namówić mnie do ich przeprowadzenia.

Pseudokolumna rownum może także służyć do stronicowania wyniku. Domyślasz się jak?

Wróć do poprzedniej lekcji