Jak ułatwić pisanie złożonych zapytań SQL? [Zamknięte]

Bardzo trudno jest mi pisać skomplikowane zapytania SQL obejmujące sprzężenia w wielu (co najmniej 3-4) tabelach i obejmujące kilka warunków zagnieżdżonych. Zapytania, które mam napisać, można łatwo opisać w kilku zdaniach, ale ich wypełnienie może wymagać zwodniczej ilości kodu. Często używam widoków tymczasowych do pisania tych zapytań, które wydają się trochę kulą. Jakich wskazówek możesz udzielić, aby ułatwić te złożone zapytania? Mówiąc dokładniej, jak rozbić te zapytania na etapy, które muszę wykonać, aby napisać kod SQL?

Zauważ, że jestem SQL-em, o którego napisanie poproszono mnie, jest częścią zadań domowych do kursu bazy danych, więc nie chcę oprogramowania, które wykona za mnie pracę. Chcę właściwie zrozumieć kod, który piszę.

Więcej szczegółów technicznych:

• Baza danych jest hostowana na serwerze PostgreSQL działającym na komputerze lokalnym.
• Baza danych jest bardzo mała: nie ma więcej niż siedem tabel, a największa tabela ma mniej niż około 50 wierszy.
• Zapytania SQL są przekazywane niezmienione do serwera za pośrednictwem LibreOffice Base.

Opieram większość tego na próbie uzyskania „właściwej” odpowiedzi, więc możesz odkryć, że są pewne problemy z wydajnością. Nie ma sensu przyspieszać niepoprawnego zapytania.

Zrozum relacje w tabeli - większość z nich będzie jedna do wielu. Poznaj tabelę „wielu”. Zidentyfikuj pola wymagane dla twoich połączeń.

Pomyśl o scenariuszach dołączenia do LEWEGO - wybierz wszystkich pracowników i ich wypłaty z ostatniego miesiąca. Co jeśli nie dostaną wypłaty w zeszłym miesiącu?

Poznaj zestaw wyników: 1) W arkuszu kalkulacyjnym ręcznie wprowadź co najmniej jeden poprawny rekord dla zapytania. 2) Napisz zapytanie w wystarczająco prostej formie, aby określić, ile rekordów należy zwrócić. Użyj obu tych elementów, aby przetestować zapytanie, aby upewnić się, że dołączenie do nowej tabeli nie zmieni wyniku.

Podziel zapytanie na części możliwe do zarządzania - nie musisz pisać wszystkiego od razu. Złożone zapytania mogą czasem być po prostu zbiorem prostych zapytań.

Uwaga na mieszane poziomy agregacji : jeśli musisz umieścić wartości miesięczne, kwartalne i roczne w tym samym zestawie wyników, musisz je obliczyć osobno w zapytaniach pogrupowanych według różnych wartości.

Wiedzieć, kiedy UNION Czasami łatwiej jest rozbić podgrupy na ich własne wybrane instrukcje. Jeśli masz tabelę pomieszaną z kierownikami i innymi pracownikami, a w każdej kolumnie musisz wykonać instrukcje Case oparte na członkostwie w jednej z tych grup, łatwiej jest napisać zapytanie kierownika i związek do zapytania pracownika. Każdy z nich zawierałby własną logikę. Konieczne jest dołączanie elementów z różnych tabel w różnych rzędach.

Formuły złożone / zagnieżdżone - staraj się konsekwentnie wcinać i nie bój się używać wielu linii. „PRZYPADEK PRZY PRZYPADKU PRZYPADEK PRZY PRZYPADKU” doprowadzi Cię do szału. Zastanów się nad tym. Zachowaj złożone cielęta na koniec. Najpierw uzyskaj prawidłowe rekordy. Następnie atakujesz złożone formuły, wiedząc, że pracujesz z odpowiednimi wartościami. Wyświetlanie wartości użytych w formułach pomoże ci dostrzec obszary, w których musisz uwzględnić wartości NULL i gdzie poradzić sobie z błędem dzielenia przez zero.

Często testuj, dodając nowe tabele, aby upewnić się, że nadal otrzymujesz pożądany zestaw wyników i wiedząc, kto jest winowajcą.

1. Wcięcie byłoby pierwszą rzeczą do zrobienia, jeśli jeszcze tego nie robisz. Jest to przydatne nie tylko w przypadku prostych zapytań, ale ma kluczowe znaczenie, jeśli chodzi o sprzężenia i zapytania nieco bardziej złożone niż select top 1 [ColumnName] from [TableName].

2. Po prawidłowym wcięciu nic nie zabrania dodawania komentarzy w samym zapytaniu, jeśli jest to właściwe. Nie nadużywaj ich: jeśli kod jest wystarczająco wyraźny, dodawanie komentarzy tylko pogorszy przejrzystość kodu. Ale nadal są mile widziane w mniej wyraźnych częściach zapytania.

   Pamiętaj, że dłuższe zapytania (w tym zapytania z komentarzami) oznaczałyby większe wykorzystanie przepustowości między serwerem aplikacji a serwerem bazy danych. Pamiętaj też, że jeśli nie pracujesz nad produktem w skali Google z dużą liczbą żądań na sekundę, wymagającym wyjątkowej wydajności i użycia zasobów, rozmiar dodany w komentarzach może nie zmienić niczego pod względem wydajności.

3. Egzekwowanie tego samego stylu w tabelach, kolumnach itp . Również bardzo pomaga w czytelności. Gdy baza danych zawiera tabele dziedzictwo PRODUCT, users, USERS_ObsoleteDONT_USE, PR_SHIPMENTSa HRhbYd_UUktoś robi coś bardzo złego.

4. Ważne jest również egzekwowanie tego samego stylu w przypadku zapytań . Na przykład, jeśli piszesz zapytania do Microsoft SQL Server i postanowiłeś użyć [TableName]zamiast tego TableName, trzymaj się tego. Jeśli przejdziesz do nowej linii po select, nie rób tego tylko w połowie zapytań, ale we wszystkich.

5. Nie używaj* , chyba że istnieją ku temu poważne powody (jak if exists(select * from [TableName] where ...)w Microsoft SQL Server). *Ma nie tylko negatywny wpływ na wydajność w niektórych (jeśli nie w większości) bazach danych, ale nie jest również pomocny dla programisty, który korzysta z zapytania. W ten sam sposób programista musi uzyskać dostęp do wartości według nazwy, nigdy według indeksu.

6. Wreszcie, dla wybranych, nie ma nic złego w udostępnianiu widoku . W każdym innym przypadku procedury składowane mogą być również stosowane w zależności od projektu i ludzi¹, z którymi współpracujesz².

^{¹ Niektóre osoby nie znoszą procedur przechowywanych. Inni nie lubią ich z kilku (doskonale ważnych, przynajmniej z nich) powodów.}

^{² Twoi koledzy, inni uczniowie, nauczyciel itp.}

Trochę ujęcia w ciemności, ale jeśli piszesz wiele widoków tymczasowych, być może jeszcze nie zdałeś sobie sprawy, że w większości miejsc, w których można umieścić tabelę w instrukcji SQL, tabelę można zastąpić zapytaniem.

Zatem zamiast łączyć tabelę A z widokiem tymczasowym B, możesz dołączyć tabelę A do zapytania, którego używałeś jako widoku tymczasowego B. Na przykład:

    SELECT A.Col1, A.Col2, B.Col1,B.Col2
      FROM (SELECT RealTableZ.Col1, RealTableY.Col2, RealTableY.ID as ID
              FROM RealTableZ 
   LEFT OUTER JOIN RealTableY
                ON RealTableZ.ForeignKeyY=RealTableY.ID
             WHERE RealTableY.Col11>14
            ) As B
        INNER JOIN A
                ON A.ForeignKeyY=B.ID

Ten przykład jest raczej bezcelowy, ale powinien wyjaśnić składnię.

W przypadku widoków, które nie są „specjalne” (indeksowane, dzielone na partycje), powinno to skutkować takim samym planem zapytań, jak w przypadku korzystania z widoku.

Jeśli chodzi o ułatwienie pisania, możesz zweryfikować każdy kawałek, aby upewnić się, że otrzymujesz to, czego oczekujesz, przed napisaniem całego zapytania.

Przepraszam, jeśli to już stary kapelusz.

Zamiast widoków tymczasowych użyj klauzuli WITH . To znacznie ułatwia dzielenie dużych zapytań na bardziej czytelne mniejsze części.

1. Zapoznaj się z teorią zbiorów, jeśli jeszcze tego nie wiesz. SQL opiera się na teorii zbiorów, a lepsze poznanie zestawów pomoże ci lepiej poznać działanie SQL.
2. Ćwicz więcej SQl, jeśli dopiero uczysz się języka SQL, zrozumienie, jak zrobić wszystko, zajmie trochę czasu, coś po prostu zajmie trochę czasu, zanim naprawdę je zrozumiesz. Połączenia są doskonałym przykładem, im więcej ich używasz, tym lepiej się na tym dostaniesz.
3. Upewnij się, że tabele, o które pytasz, są odpowiednio zaprojektowane
4. Nie bój się korzystać z widoków wybranych zapytań, zwłaszcza jeśli masz wspólny zestaw, który wymaga udoskonalenia na wiele różnych sposobów

Jak wszystko inne, chcesz podzielić problem na części łatwe do zarządzania.

Nawiasem mówiąc, tak naprawdę rozwiązujesz złożone problemy.

Więc: Chcesz sprawdzić podkwerendę, aby zobaczyć, że naprawdę zwraca to, co chcesz przed uruchomieniem zewnętrznego zapytania. Chcesz wypróbować minimalne łączenie każdego stołu, do którego dołączasz, aby przekonać się, że naprawdę dobrze to przemyślasz. Rzeczy takie jak te. Nadzieja, aby wpisać to wszystko i wydobyć dokładnie to, czego chcesz za jednym razem, jest po prostu nierealna.

Instrukcja SQL, gdy osiągnie pewien poziom złożoności, jest w zasadzie małym programem sama w sobie. Duże znaczenie ma faktyczne zrozumienie, w jaki sposób dane są łączone, wybierane, filtrowane i generowane.