I de foregående par artikler i denne serie, Vi har sat grundlaget for, hvordan man opretter en rapport. Vi tager et skridt videre og ser, hvordan du bruger SQL Server PIVOT-tabeloperatøren. Vi starter fra den enkle forespørgsel og går langsomt mod dynamisk SQL og PIVOT. Lad os starte.
Datamodel og den generelle idé
Den datamodel, vi bruger, er den samme, som vi har brugt i hele denne serie.
Vores opgave i dag er at oprette en rapport (ved hjælp af SQL Server PIVOT-operatør) hvor hver by vil være i en separat række , og hvor vi tæller antallet af alle opkaldsresultater relateret til hver by. Derfor skal alle resultater (alle værdier fra ordbogen) være kolonner i vores rapport.
Da “et billede er tusind ord værd”, vil vi også beskrive vores endelige mål med billedet.
For at opnå dette skal vi bruge data fra 4 tabeller call, call_outcome, kunde og by. Derfor er det ville være godt, at du kigger nærmere på nedenstående model og analyserer, hvordan disse tabeller er relateret.
Vi begynder med at undersøge dataene i tabellerne og med hver forespørgsel flytter vi et trin tættere på det ønskede resultat (forespørgsel). Den fremgangsmåde, der anvendes her, er den samme som den, der anvendes i Lær SQL: Opret en rapport manuelt ved hjælp af artikel om SQL-forespørgsler.
Rapportkategorier og data
det første sæt forespørgsler, vi starter med, er den, der undersøger data, der i øjeblikket findes i alle de 4 tabeller, vi skal bruge til at oprette rapporten. Vi gør det, så vi kan bekræfte, at den endelige rapport returnerede, hvad den skulle. Derefter opretter vi forespørgsler wh som returnerer rapporteringskategorier og rapportdata samt SQL Server PIVOT-tabelforespørgsler.
|
1
2
3
4
5
|
– – 1 – vælg de data, vi har brug for
VÆLG * FRA opkald;
VÆLG * FRA call_outcome;
VÆLG * FRA kunde;
VÆLG * FRA by;
|
Disse forespørgsler er så enkle som de kunne være, så der er ikke noget særligt at kommentere vedrørende deres syntaks. Med hensyn til de returnerede data skal vi være opmærksom på følgende:
- Bytabellen indeholder 6 byer, og derfor skal vi have 6 rækker i den endelige rapport
- Der er 3 mulige resultater i call_outcome-tabellen, så vi skal have 3 kolonner til resultater (4 kolonner i alt – > en er for bynavn)
- Tabellen kald har 10 rækker (kun 8 på billedet ovenfor), så i finaletabellen skal summen af alle resultater være 10 (hvert opkald kan have nøjagtigt 1 resultat)
Den næste ting at gøre er at forberede rapporteringskategorier. Vi ønsker at have en kombination af alle byer og alle mulige opkaldsresultater. For at opnå dette bruger vi CROSS JOIN (kartesisk produkt).
At have alle rapporteringskategorier skal garantere, at vi får en række i rapporten, uanset om parret har data eller ej. Og det er det, vi ønsker – at se 0 i den rapporteringskategori og ikke gå glip af den kategori helt.
Den næste ting at gøre er at slutte sig til alle de 4 tabeller, der indeholder de data, vi har brug for.
Selve forespørgslen er ikke kompleks. I resultatet skal vi bemærke, at vi har 10 rækker, det samme nummer som antallet af opkald, vi har i databasen. Jeg har også brugt DATEDIFF-funktionen til at returnere varigheden af hvert opkald i sekunder. Jeg kunne bare lægge call.id her, men jeg ville minde os selv om DATEDIFF-funktionen, vi har nævnt i denne artikel. Vi kunne bruge denne varighed, hvis vi har brug for et SUM / AVG på brugt sekunder pr. Rapporteringskategori (by & opkaldsresultat).
Rapport uden SQL Server PIVOT-TABEL
Nu er vi klar til at deltage i kategorier og data. Vi bruger begge tidligere nævnte forespørgsler som underforespørgsler og slutter os til dem ved hjælp af LEFT JOIN (for at have alle kategorierne til stede i den endelige output).
Vi har alle 18 kombinationer (6 forskellige byer * 3 forskellige opkaldsresultater), og vi har også alle 10 opkald til stede her (rækker med opkaldets varighed < > NULL).
Lad os nu oprette SQL Server PIVOT-forespørgsel.
SQL Server PIVOT-TABEL (statisk)
Indtil videre er det lykkedes os at trække de data, vi har brug for, og vi har dem som listen. Vi kunne eksportere data til Excel og foretage transformationer der. Vi gør det faktisk i den kommende artikel.I dag vil vi stadig løse dette ved hjælp af SQL Server PIVOT-operatør. I dette afsnit dækker vi den “statiske” SQL Server PIVOT. Lad os se på den anvendte forespørgsel og resultatet først.
Vi kan bemærke, at resultatet er nøjagtigt det, vi ønskede. Vi har hver by i en række og alle 3 kategorier for opkaldsresultater i separate kolonner. Hvis der ikke er data for et bestemt by-udfaldspar, er den celle skal indeholde værdien 0.
Lad os kommentere SQL Server PIVOT-forespørgslen nu. Dette er et par ting, som jeg finder vigtigt at nævne her:
- I den første del af forespørgslen – report_data, vi har kopieret forespørgslen fra det foregående afsnit. Den eneste ændring var, at vi ikke havde ORDER BY og GROUP BY i denne del af forespørgslen, fordi de ikke kan anvendes her (de skal gå efter det endelige forespørgselsresultat genereres)
- PIVOT-delen af forespørgslen består af 2 dele. I den første definerer vi, hvilken samlet funktion vi vil anvende. I vores tilfælde er dette – COUNT ( call_duration). I FOR en del af spørgsmålet ery, vi definerer kolonner. Vi viser en litterær liste over alle de værdier, vi ønsker at have som kolonner. Dette er hårdkodende. Hvis vi tilføjer ny værdi til tabellen, påvirker det ikke forespørgslen (og det burde det være)
Denne forespørgsel udfører sit job, og det fungerer perfekt, hvis vi aldrig ændrer (tilføj nyt , slet, opdater deres navne) opkaldsresultater i den relaterede tabel. Det kan vise sig at være problemet i tilfælde, hvor vi har brug for at foretage ændringer i ordbogen. Vi ønsker ikke at tænke, om der er en forespørgsel, der ikke fungerer som forventet. For at løse dette skal vi bruge et nyt koncept – dynamisk SQL.
Dynamisk SQL Server PIVOT-TABEL
Før vi går til koden, lad os kort forklare, hvad den dynamiske SQL virkelig er er (vi giver en meget mere detaljeret gennemgang af det i kommende artikler). Den enkleste forklaring er, at i SQL kan du “bygge” dine forespørgsler som strenge og derefter videregive den streng som en parameter for den lagrede SQL Server-procedure. Denne procedure skal udføre de SQL-sætninger, der er gemt i den streng (selvfølgelig , hvis syntaksen er OK).
Selvom dette måske lyder som noget, der ikke er så almindeligt brugt, har det en hel del steder, hvor det gør dit liv meget lettere. Oprettelse af en SQL Server PIVOT-tabelforespørgsel med en ukendt antal kolonner er nøjagtigt et sådant tilfælde.
Idéen med denne tilgang er som følger:
- Vi erklærer en variabel, hvor alle kolonnenavne (@column) skal gemmes. ) og variablen, hvor den komplette forespørgsel skal lagres (@forespørgsel)
- Ved hjælp af SELECT-forespørgslen finder vi alle værdier, der er gemt i kolonnen resultat_tekst. Følgende kode – @column + = QUOTENAME (TRIM (co.outcome_text)) + ,, tilføjer kolonnenavn til listen over alle tidligere kolonnenavne, der returneres af forespørgslen. Resultatet er, at vi har alle kolonnenavne gemt i e variabel @kolonner
- I variablen @query gemmer vi den komplette forespørgsel fra det foregående afsnit, bortset fra den del, hvor kolonner er defineret. Vi får denne del fra variablen @column
- Den sidste ting, vi skal gøre, er at sende den komplette forespørgsel som parameter til SQL Server-systemproceduren sp_executesql
Det endelige resultat er det samme som i det foregående afsnit, men denne gang er vi sikre på, at vores forespørgsel fungerer, selvom vi foretager ændringer til resultatet.outcome_text-værdier. Du kan også nemt ændre denne forespørgsel og beregne andre værdier, f.eks. SUM / AVG-opkaldsvarighed pr. By-udfaldspar.
Konklusion
SQL Server PIVOT-operatøren giver dig en helt nyt overblik over, hvad du kan opnå direkte på databaselaget. Når det kombineres med den dynamiske SQL, går dette endnu længere. Jeg opfordrer dig kraftigt til at lege med det – der er ingen bedre måde at lære end at prøve det selv. I den næste artikel viser vi, hvordan vi kunne bruge SQL-forespørgselsoutput og oprette tabeller og grafer i Excel (dette fungerer ikke kun i SQL Server, men generelt).
Indholdsfortegnelse
Lær SQL: OPRET DATABASE & Opret TABELhandlinger
Lær SQL: INDSÆT I TABEL
Lær SQL: Primær nøgle
Lær SQL: Fremmed nøgle
Lær SQL: SELECT-sætning
Lær SQL: INNER JOIN vs LEFT JOIN
Lær SQL: SQL-scripts
Lær SQL: Relationstyper
Lær SQL: Deltag flere tabeller
Lær SQL: Samlede funktioner
Lær SQL: Hvordan man skriver en kompleks SELECT-forespørgsel?
Lær SQL: INFORMATION_SCHEMA-databasen
Lær SQL: SQL-datatyper
Lær SQL: Sæt teori
Lær SQL: Brugerdefinerede funktioner
Lær SQL: Brugerdefinerede lagrede procedurer
Lær SQL: SQL-visninger
Lær SQL: SQL-udløsere
Lær SQL: Øv SQL-forespørgsler
Lær SQL: SQL-forespørgselseksempler
Lær SQL: Opret en rapport manuelt ved hjælp af SQL-forespørgsler
Lær SQL: SQL Server-dato- og tidsfunktioner
Lær SQL: Opret SQL Server-rapporter ved hjælp af dato- og tidsfunktioner
Lær SQL: SQL Server-pivottabeller
Lær SQL: SQL Server-eksport til Excel
Lær SQL: Introduktion til SQL Server-sløjfer
Lær SQL: SQL Server-markører
Lær SQL: SQL Bedste fremgangsmåder til sletning og opdatering af data
Lær SQL: Navngivningskonventioner
Lær SQL: SQL-relaterede job
Lær SQL: Ikke-Equi-tilslutninger i SQL Server
Lær SQL: SQL Injection
- Forfatter
- Seneste indlæg
Hans tidligere og nuværende opgaver varierer fra databasedesign og kodning til undervisning, konsulentarbejde og skrivning om databaser. Også for ikke at glemme, BI, oprettelse af algoritmer, skak, filateli, 2 hunde, 2 katte, 1 kone, 1 baby …
Du kan finde ham på LinkedIn
Se alle indlæg af Emil Drkusic
- Lær SQL: SQL Injection – 2. november 2020
- Lær SQL: Non-Equi-tilslutninger i SQL Server – 29. september 2020
- Lær SQL: SQL-relaterede job – 1. september 2020