SQLShack (Română)

În câteva articole anterioare din această serie, am stabilit bazele modului de creare a unui raport. Vom face un pas mai departe și vom vedea cum să folosim operatorul tabelului SQL Server PIVOT. Vom începe de la interogarea simplă și vom progresa încet către SQL dinamic și PIVOT. Să începem.

Modelul de date și ideea generală

Modelul de date pe care îl vom folosi este același pe care l-am folosit de-a lungul acestei serii.

Sarcina noastră de astăzi este de a crea un raport (folosind operatorul SQL Server PIVOT) în care fiecare oraș va fi într-un rând separat și unde vom număra numărul tuturor rezultatelor apelurilor aferente fiecărui oraș. Prin urmare, toate rezultatele (toate valorile din dicționar) vor fi coloane în raportul nostru.

Deoarece „o imagine valorează o mie de cuvinte”, vom descrie și obiectivul nostru final cu imaginea.

Pentru a realiza acest lucru, va trebui să folosim date din 4 tabele apel, call_outcome, client și oraș. Prin urmare, ar fi bine să aruncați o privire mai atentă asupra modelului de mai jos și să analizați legătura dintre aceste tabele.

Vom începe prin a examina datele din tabele și, cu fiecare interogare, ne vom apropia de un pas mai aproape de rezultatul dorit (interogare). Abordarea utilizată aici este aceeași cu cea utilizată în Aflați SQL: Creați manual un raport folosind articolul de interogări SQL.

Categorii și date de raport

primul set de interogări cu care vom începe este cel care examinează datele prezent în prezent în toate cele 4 tabele pe care va trebui să le folosim pentru a crea raportul. O vom face astfel încât să putem confirma că raportul final a returnat ceea ce ar trebui. După aceea, vom crea interogări wh ich returnează categoriile de raportare și datele raportului, precum și interogările SQL Server PIVOT.

Aceste interogări sunt cât se poate de simple, deci nu este nimic special de comentat în ceea ce privește sintaxa lor. În ceea ce privește datele returnate, ar trebui să fim conștienți de următoarele:

• Tabelul orașului conține 6 orașe și, prin urmare, ar trebui să avem 6 rânduri în raportul final
• Există 3 rezultate posibile în tabelul call_outcome, deci ar trebui să avem 3 coloane pentru rezultate (4 coloane în total – > una este pentru numele orașului)
• Apelul la tabel are 10 rânduri (doar 8 în imaginea de mai sus), deci în tabelul final, suma tuturor rezultatelor ar trebui să fie de 10 (fiecare apel poate avea exact 1 rezultat)

Următorul lucru de făcut este de a pregăti categorii de raportare. Vrem să avem o combinație între toate orașele și toate rezultatele posibile ale apelurilor. Pentru a realiza acest lucru, vom folosi CROSS JOIN (produs cartezian).

Având toate categoriile de raportare trebuie să garantați că vom avea un rând în raport, indiferent dacă perechea respectivă are sau nu date. Și asta este ceea ce dorim – să vedem 0 în acea categorie de raportare și să nu pierdem în totalitate acea categorie.

Următorul lucru de făcut este să ne alăturăm celor 4 tabele care conțin datele de care avem nevoie.

Interogarea în sine nu este complexă. În rezultat, ar trebui să observăm că avem 10 rânduri, același număr cu numărul de apeluri pe care le avem în baza de date. De asemenea, am folosit funcția DATEDIFF pentru a returna durata fiecărui apel în câteva secunde. Aș putea pur și simplu pune call.id aici, dar am vrut să ne reamintim funcția DATEDIFF pe care am menționat-o în acest articol. Am putea folosi această durată dacă avem nevoie de o sumă / medie de secunde petrecute pentru fiecare categorie de raportare (rezultatul apelului de oraș &).

Raport fără TABEL PIVOT SQL Server

Acum suntem gata să ne alăturăm categoriilor și datelor. Vom folosi ambele interogări menționate anterior ca subinterogări și le vom alătura folosind LEFT JOIN (pentru a avea toate categoriile prezente în rezultatul final).

Avem toate cele 18 combinații (6 orașe diferite * 3 rezultate diferite ale apelurilor) și avem, de asemenea, toate cele 10 apeluri prezente aici (rânduri cu durata apelului < > NULL).

Să creăm acum interogarea SQL Server PIVOT.

TABEL PIVOT SQL Server (static)

Până acum, am reușit să extragem datele de care avem nevoie și le avem ca listă. Am putea exporta date în Excel și putem face transformări acolo. De fapt, vom face acest lucru în următorul articol.Totuși, astăzi, dorim să rezolvăm acest lucru folosind operatorul SQL Server PIVOT. În această secțiune, vom acoperi „static” SQL Server PIVOT. Să aruncăm o privire mai întâi asupra interogării utilizate și a rezultatului.

Putem observa că rezultatul este exact ceea ce ne-am dorit. Avem fiecare oraș într-un rând și toate cele 3 categorii pentru rezultatele apelurilor în coloane separate. Dacă nu există date pentru o anumită pereche oraș-rezultat, acea celulă va conține valoarea 0.

Să comentăm acum interogarea SQL Server PIVOT. Acestea sunt câteva lucruri pe care consider important să le menționez aici:

• În prima parte a interogarea – report_data, am copiat-lipit interogarea din secțiunea anterioară. Singura modificare a fost că nu am avut ORDER BY și GROUP BY în această parte a interogării, deoarece nu pot fi aplicate aici (ar trebui să meargă după rezultatul final al interogării este generat)
• Partea PIVOT a interogării este formată din 2 părți. În prima, vom defini ce funcție agregată dorim să aplicăm. În cazul nostru, acesta este – COUNT ( call_duration). În FOR o parte din qu ery, vom defini coloane. Listăm literar toate valorile pe care dorim să le avem ca coloane. Aceasta este o codificare dură. Dacă adăugăm o valoare nouă în tabel, aceasta nu va avea impact asupra interogării (și ar trebui)

Această interogare își face treaba și va funcționa perfect dacă nu ne schimbăm niciodată , ștergeți, actualizați-le numele) rezultatele apelului în tabelul aferent. Aceasta s-ar putea dovedi a fi problema în cazurile în care trebuie să facem modificări în dicționar. Nu vrem să ne gândim dacă există o interogare care nu funcționează conform așteptărilor. Pentru a rezolva acest lucru, va trebui să folosim un concept nou – SQL dinamic.

TABEL PIVOT Dynamic SQL Server

Înainte de a trece la cod, să explicăm pe scurt ce este cu adevărat SQL-ul dinamic. este (vom oferi o recenzie mult mai detaliată a acestuia în articolele viitoare). Cea mai simplă explicație este că, în SQL, puteți „construi” interogările dvs. ca șiruri și apoi treceți acel șir ca parametru pentru procedura stocată SQL Server. Această procedură va executa instrucțiunile SQL stocate în acel șir (desigur , dacă sintaxa este OK).

Deși acest lucru s-ar putea să sune ca ceva care nu este atât de frecvent folosit, are destul de multe locuri în care vă face viața mult mai ușoară. Crearea unei interogări de tabel SQL Server PIVOT cu un numărul de coloane este exact un astfel de caz.

Ideea acestei abordări este următoarea:

• Vom declara o variabilă în care să stochăm toate numele coloanelor (@columns ), și variabila unde se stochează interogarea completă (@query)
• Folosind interogarea SELECT, vom găsi toate valorile stocate în coloana result_text. Următorul cod – @columns + = QUOTENAME (TRIM (co.outcome_text)) + ,, va adăuga numele coloanei la lista tuturor numelor de coloane anterioare returnate de interogare. Rezultatul este că avem toate numele coloanelor stocate în Variabila @columns
• În variabila @query, vom stoca interogarea completă din secțiunea anterioară, cu excepția părții în care sunt definite coloanele. Vom obține această parte din variabila @columns
• Ultimul lucru pe care trebuie să-l facem este să trecem interogarea completă ca parametru la procedura de sistem SQL Server sp_executesql

Rezultatul final este același ca în secțiunea anterioară, dar de data aceasta suntem siguri că interogarea noastră va funcționa chiar dacă vom face modificări la valorile rezultatului.outcome_text. De asemenea, puteți modifica cu ușurință această interogare și puteți calcula orice alte valori, de exemplu, durata apelului SUM / AVG per pereche oraș-rezultat.

Concluzie

Operatorul SQL Server PIVOT vă oferă un vizualizare complet nouă a ceea ce puteți realiza direct pe stratul bazei de date. Când este combinat cu SQL-ul dinamic, acest lucru merge chiar mai departe. Vă încurajez cu tărie să vă jucați cu el – nu există un mod mai bun de a învăța decât să încercați singur. În articolul următor, vom arăta cum am putea folosi rezultatul interogării SQL și cum să creăm tabele și grafice în Excel (acest lucru va funcționa nu numai în SQL Server, ci în general).

Cuprins

Learn SQL: CREATE DATABASE & CREATE TABLE Operations

Learn SQL: INSERT INTO TABLE

Learn SQL: Primary Key

Learn SQL: Foreign Key

Learn SQL: SELECT statement

Learn SQL: INNER JOIN vs LEFT JOIN

Learn SQL: SQL Scripts

Learn SQL: Tipuri de relații

Learn SQL: Join tabele multiple

Learn SQL: Aggregate Functions

Learn SQL: How to Write a Complex SELECT Query?

Learn SQL: Baza de date INFORMATION_SCHEMA

Learn SQL: Tipuri de date SQL

Learn SQL: Set Theory

Learn SQL: Funcții definite de utilizator

Learn SQL: Proceduri stocate definite de utilizator

Learn SQL: SQL Views

Learn SQL: SQL Triggers

Learn SQL: Practice SQL Queries

Learn SQL: SQL Query examples

Aflați SQL: creați manual un raport folosind interogări SQL

Aflați SQL: funcții de dată și oră SQL Server

Aflați SQL: creați rapoarte SQL Server utilizarea funcțiilor de dată și oră

Aflați SQL: Tabelele pivot SQL Server

Aflați SQL: Exportați SQL Server în Excel

Aflați SQL: Introducere în buclele SQL Server

Aflați SQL: Cursori SQL Server

Aflați SQL: Cele mai bune practici SQL pentru ștergerea și actualizarea datelor

Aflați SQL: Convenții de numire

Aflați SQL: joburi legate de SQL

Aflați SQL: Îmbinări non-Equi în SQL Server

Aflați SQL: SQL Injection

• Autor
• Postări recente

Emil este un profesionist în baza de date cu peste 10 ani de experiență în tot ceea ce privește bazele de date. De-a lungul anilor, a lucrat în industria IT și financiară și acum lucrează ca freelancer.
Angajamentele sale din trecut și prezent variază de la proiectarea și codificarea bazelor de date până la predare, consultanță și scriere despre baze de date. De asemenea, să nu uităm, BI, creând algoritmi, șah, filatelie, 2 câini, 2 pisici, 1 soție, 1 bebeluș …
Îl poți găsi pe LinkedIn
Vezi toate mesajele lui Emil Drkusic

Ultimele mesaje ale lui Emil Drkusic (vezi toate)

• Learn SQL: SQL Injection – 2 noiembrie 2020
• Learn SQL: Non-Equi Joins in SQL Server – 29 septembrie 2020
• Learn SQL: Locuri de muncă legate de SQL – 1 septembrie 2020