SQLShack (Polski)

W kilku poprzednich artykułach z tej serii, położyliśmy podwaliny pod sposób tworzenia raportu. Pójdziemy o krok dalej i zobaczymy, jak używać operatora tabeli przestawnej SQL Server. Zaczniemy od prostego zapytania i powoli przejdziemy do dynamicznego SQL i PIVOT. Zacznijmy.

Model danych i ogólna idea

Model danych, którego będziemy używać, jest tym samym, którego używaliśmy w tej serii.

Nasze dzisiejsze zadanie polega na utworzeniu raportu (przy użyciu operatora SQL Server PIVOT), w którym każde miasto będzie w osobnym wierszu i gdzie policzymy liczbę wszystkich wyników połączeń związanych z każdym miastem. Dlatego wszystkie wyniki (wszystkie wartości ze słownika) powinny być kolumnami w naszym raporcie.

Ponieważ „obraz jest wart tysiąca słów”, opiszemy również nasz ostateczny cel.

Aby to osiągnąć, będziemy potrzebować danych z 4 tabel wywołań, call_outcome, customer i city. Dlatego dobrze by było, gdybyś przyjrzał się bliżej poniższemu modelowi i przeanalizował, jak te tabele są powiązane.

Zaczniemy od zbadania danych w tabelach i przy każdym zapytaniu będziemy zbliżać się o krok do żądany wynik (zapytanie). Podejście zastosowane w tym miejscu jest takie samo, jak zastosowane w Learn SQL: Utwórz raport ręcznie, używając artykułu o zapytaniach SQL.

Kategorie i dane raportów

Pierwszy zestaw zapytań, od którego zaczniemy, to ten, który analizuje dane obecnie obecne we wszystkich 4 tabelach, których będziemy potrzebować do stworzenia raportu. Zrobimy to, abyśmy mogli potwierdzić, że raport końcowy zwrócił to, co powinien. Następnie utworzymy zapytania wh ich kategorie raportowania zwrotów i dane raportu, a także zapytania do tabel przestawnych SQL Server.

Te zapytania są tak proste, jak mogłyby być, więc nie ma nic specjalnego do komentowania na temat ich składni. Jeśli chodzi o zwrócone dane, powinniśmy pamiętać o następujących kwestiach:

• Tabela miast zawiera 6 miast, dlatego w raporcie końcowym powinniśmy mieć 6 wierszy.
• 3 możliwe wyniki w tabeli call_outcome, więc powinniśmy mieć 3 kolumny na wyniki (łącznie 4 kolumny – > jedna dla nazwy miasta)
• Wywołanie w tabeli ma 10 wierszy (tylko 8 na powyższym obrazku), więc w ostatecznej tabeli suma wszystkich wyników powinna wynosić 10 (każde wezwanie może mieć dokładnie 1 wynik)

Następna rzecz do zrobienia polega na przygotowaniu kategorii raportowania. Chcemy mieć połączenie wszystkich miast i wszystkich możliwych wyników połączeń. Aby to osiągnąć, użyjemy CROSS JOIN (produkt kartezjański).

Posiadanie wszystkich kategorii raportowania gwarantuje, że będziemy mieć wiersz w raporcie, bez względu na to, czy ta para zawiera dane, czy nie. I właśnie tego chcemy – aby zobaczyć 0 w tej kategorii raportowania i całkowicie nie przegapić tej kategorii.

Następną rzeczą do zrobienia jest połączenie wszystkich 4 tabel zawierających potrzebne dane.

Samo zapytanie nie jest złożone. W rezultacie powinniśmy zauważyć, że mamy 10 wierszy, czyli tyle samo, co liczba wywołań, które mamy w bazie. Ponadto użyłem funkcji DATEDIFF, aby zwrócić czas trwania każdego połączenia w sekundach. Mógłbym po prostu wstawić tutaj call.id, ale chciałem przypomnieć sobie funkcję DATEDIFF, o której wspomnieliśmy w tym artykule. Możemy użyć tego czasu trwania, jeśli potrzebujemy SUMA / ŚREDNIA sekund spędzonych na każdą kategorię raportowania (wynik wywołania miasta &).

Raport bez SQL Server TABELA PRZESTAWNA

Teraz jesteśmy gotowi do łączenia kategorii i danych. Użyjemy obu wcześniej wspomnianych zapytań jako podzapytań i połączymy je za pomocą LEFT JOIN (aby wszystkie kategorie były obecne w końcowym wyniku).

Mamy wszystkie 18 kombinacji (6 różnych miast * 3 różne wyniki połączeń), a także mamy tutaj wszystkie 10 połączeń (wiersze z czasem trwania połączenia < > NULL).

Utwórzmy teraz zapytanie SQL Server PIVOT.

SQL Server PIVOT TABLE (statyczna)

Jak dotąd udało nam się pobrać potrzebne nam dane i mamy je na liście. Mogliśmy eksportować dane do Excela i tam dokonywać transformacji. Zrobimy to w nadchodzącym artykule.Jednak dzisiaj chcemy rozwiązać ten problem za pomocą operatora SQL Server PIVOT. W tej sekcji omówimy „statyczny” SQL Server PIVOT. Przyjrzyjmy się najpierw użytemu zapytaniu i wynikowi.

Możemy zauważyć, że wynik jest dokładnie taki, jakiego chcieliśmy. Każde miasto mamy w jednym wierszu, a wszystkie 3 kategorie wyników połączeń w oddzielnych kolumnach. Jeśli nie ma danych dla określonej pary miasto-wynik, ta komórka powinien zawierać wartość 0.

Skomentujmy teraz zapytanie SQL Server PIVOT. Oto kilka rzeczy, o których warto tutaj wspomnieć:

• W pierwszej części zapytanie – report_data, skopiowaliśmy i wkleiliśmy zapytanie z poprzedniej sekcji. Jedyną zmianą było to, że nie mieliśmy ORDER BY i GROUP BY w tej części zapytania, ponieważ nie można ich tutaj zastosować (powinny iść po generowany jest końcowy wynik zapytania)
• Część PIVOT zapytania składa się z 2 części. W pierwszej określimy, którą funkcję agregującą chcemy zastosować. W naszym przypadku jest to – COUNT ( call_duration) .W FOR część qu ery, zdefiniujemy kolumny. Literacko podajemy wszystkie wartości, które chcemy mieć jako kolumny. To jest trudne do zakodowania. Jeśli dodamy nową wartość do tabeli, nie wpłynie to na zapytanie (a powinno)

To zapytanie wykonuje swoje zadanie i będzie działało idealnie, jeśli nigdy się nie zmienimy (dodamy nowe , usuń, zaktualizuj ich nazwy) wyniki połączeń w powiązanej tabeli. Może to stanowić problem w przypadku konieczności wprowadzenia zmian w słowniku. Nie chcemy myśleć, czy istnieje zapytanie, które nie działa zgodnie z oczekiwaniami. Aby rozwiązać ten problem, będziemy musieli użyć nowej koncepcji – dynamicznego SQL.

Dynamiczna tabela przestawna serwera SQL

Zanim przejdziemy do kodu, wyjaśnijmy krótko, czym tak naprawdę jest dynamiczny SQL jest (dokładniej omówimy to w kolejnych artykułach). Najprostszym wyjaśnieniem jest to, że w SQL można „budować” zapytania jako ciągi znaków, a następnie przekazywać ten ciąg jako parametr procedury składowanej SQL Server. Procedura ta wykonuje instrukcje SQL przechowywane w tym ciągu (oczywiście , jeśli składnia jest w porządku).

Chociaż może to brzmieć jak coś niezbyt często używanego, istnieje wiele miejsc, w których znacznie ułatwia to życie. Tworzenie zapytania tabeli PIVOT SQL Server z nieznanym liczba kolumn to dokładnie jeden taki przypadek.

Idea tego podejścia jest następująca:

• Zadeklarujemy zmienną, w której będą przechowywane wszystkie nazwy kolumn (@columns ) oraz zmienną, w której ma być przechowywane całe zapytanie (@query)
• Używając zapytania SELECT, znajdziemy wszystkie wartości zapisane w kolumnie result_text. Poniższy kod – @columns + = QUOTENAME (TRIM (co.outcome_text)) + ,, doda nazwę kolumny do listy wszystkich poprzednich nazw kolumn zwróconych przez zapytanie.W rezultacie mamy wszystkie nazwy kolumn zapisane w th Zmienna @columns
• W zmiennej @query zapiszemy całe zapytanie z poprzedniej sekcji, z wyjątkiem części, w której zdefiniowano kolumny. Otrzymamy tę część ze zmiennej @columns
• Ostatnią rzeczą, jaką musimy zrobić, jest przekazanie całego zapytania jako parametru do procedury systemowej SQL Server sp_executesql

Ostateczny wynik jest taki sam jak w poprzedniej sekcji, ale tym razem jesteśmy pewni, że nasze zapytanie zadziała, nawet jeśli wprowadzimy zmiany wartości result.outcome_text. Możesz także łatwo zmodyfikować to zapytanie i obliczyć dowolne inne wartości, np. Czas trwania połączenia SUMA / AVG na parę miasto-wynik.

Wniosek

Operator SQL Server PIVOT daje zupełnie nowy widok tego, co możesz osiągnąć bezpośrednio w warstwie bazy danych. W połączeniu z dynamicznym SQL idzie to jeszcze dalej. Gorąco zachęcam do zabawy – nie ma lepszego sposobu na naukę niż samodzielne wypróbowanie. W następnym artykule pokażemy, w jaki sposób możemy wykorzystać dane wyjściowe zapytania SQL i tworzyć tabele i wykresy w programie Excel (powinno to działać nie tylko w SQL Server, ale ogólnie).

Spis treści

Nauka języka SQL: UTWÓRZ BAZĘ DANYCH & UTWÓRZ operacje na tabelach

Nauka języka SQL: WSTAWIANIE DO TABELI

Nauka SQL: klucz podstawowy

Nauka języka SQL: klucz obcy

Nauka języka SQL: instrukcja SELECT

Nauka języka SQL: INNER JOIN vs LEFT JOIN

Learn SQL: skrypty SQL

Learn SQL: Typy relacji

Learn SQL: Join wiele tabel

Nauka języka SQL: funkcje agregujące

Nauka języka SQL: jak napisać złożone zapytanie SELECT?

Naucz się SQL: Baza danych INFORMATION_SCHEMA

Naucz się języka SQL: Typy danych SQL

Naucz się SQL: Teoria zbiorów

Nauka języka SQL: funkcje zdefiniowane przez użytkownika

Nauka języka SQL: procedury składowane zdefiniowane przez użytkownika

Nauka języka SQL: widoki SQL

Nauka języka SQL: wyzwalacze SQL

Nauka języka SQL: ćwiczenie zapytań SQL

Nauka języka SQL: przykłady zapytań SQL

Nauka języka SQL: ręczne tworzenie raportów za pomocą zapytań SQL

Nauka języka SQL: funkcje daty i godziny w programie SQL Server

Nauka języka SQL: tworzenie raportów w programie SQL Server używanie funkcji daty i czasu

Naucz się SQL: tabele przestawne programu SQL Server

Naucz się języka SQL: eksport programu SQL Server do programu Excel

Learn SQL: Wprowadzenie do pętli SQL Server

Learn SQL: SQL Server Cursors

Learn SQL: SQL Best Practices for Delete and Updating data

Naucz się języka SQL: Konwencje nazewnictwa

Naucz się języka SQL: Zadania związane z SQL

Naucz się języka SQL: Łączenia nierównomierne w SQL Server

Nauka języka SQL: wstrzykiwanie SQL

• Autor
• Najnowsze posty

Emil jest specjalistą od baz danych z ponad 10-letnim doświadczeniem we wszystkim związanym z bazami danych. Przez lata pracował w branży informatycznej i finansowej, a teraz pracuje jako wolny strzelec.
Jego przeszłe i obecne zadania obejmują różne dziedziny, od projektowania i kodowania baz danych po nauczanie, doradztwo i pisanie o bazach danych. Nie zapominajmy też, BI, tworzenie algorytmów, szachy, filatelistyka, 2 psy, 2 koty, 1 żona, 1 dziecko …
Możesz go znaleźć na LinkedIn
Wyświetl wszystkie posty Emila Drkusica

Najnowsze posty Emila Drkusica (zobacz wszystkie)

• Learn SQL: SQL Injection – 2 listopada 2020 r.
• Learn SQL: Non-Equi Joins in SQL Server – 29 września 2020 r.
• Learn SQL: Praca związana z SQL – 1 września 2020 r.