SQLShack (Svenska)

I de föregående artiklarna i denna serie, Vi har lagt grunden för hur man skapar en rapport. Vi tar ett steg längre och ser hur man använder SQL Server PIVOT-tabelloperatören. Vi börjar från den enkla frågan och fortsätter långsamt mot dynamisk SQL och PIVOT. Låt oss börja.

Datamodellen och den allmänna idén

Datamodellen vi använder är densamma som vi har använt under hela serien.

Vår uppgift idag är att skapa en rapport (med SQL Server PIVOT-operatör) där varje stad kommer att vara i en separat rad , och där vi räknar antalet samtalsresultat relaterade till varje stad. Därför ska alla resultat (alla värden från ordboken) vara kolumner i vår rapport.

Eftersom ”en bild är värd tusen ord” kommer vi också att beskriva vårt slutliga mål med bilden.

För att uppnå detta måste vi använda data från fyra tabeller call, call_outcome, kund och stad. Därför är det skulle vara bra att du tittar närmare på modellen nedan och analyserar hur dessa tabeller är relaterade.

Vi börjar med att undersöka data i tabellerna och flytta ett steg närmare varje fråga önskat resultat (fråga). Den metod som används här är densamma som den som används i Learn SQL: Skapa en rapport manuellt med hjälp av SQL-frågan.

Rapportkategorier och data

den första uppsättningen frågor vi kommer att börja med är den som undersöker data som för närvarande finns i alla fyra tabeller som vi måste använda för att skapa rapporten. Vi gör det så att vi kan bekräfta att den slutliga rapporten returnerade vad den borde. Efter det skapar vi frågor wh som returnerar rapporteringskategorier och rapportdata samt SQL Server PIVOT-tabellfrågor.

Dessa frågor är så enkla som de kunde vara, så det finns inget särskilt att kommentera angående deras syntax. När det gäller de returnerade uppgifterna bör vi vara medvetna om följande:

• Stadstabellen innehåller 6 städer, och därför bör vi ha 6 rader i slutrapporten
• Det finns 3 möjliga resultat i tabellen call_outcome, så vi borde ha 3 kolumner för resultat (totalt 4 kolumner – > en är för stadsnamn)
• Tabellanropet har 10 rader (endast 8 på bilden ovan), så i slutbordet ska summan av alla utfall vara 10 (varje samtal kan ha exakt 1 utfall)

Nästa sak att göra är att förbereda rapporteringskategorier. Vi vill ha en kombination av alla städer och alla möjliga samtalsresultat. För att uppnå detta använder vi CROSS JOIN (kartesisk produkt).

Att ha alla rapporteringskategorier ska garantera att vi får en rad i rapporten, oavsett om det paret har data eller inte. Och det är vad vi vill – att se 0 i den rapporteringskategorin och inte missa den kategorin helt.

Nästa sak att göra är att gå med i alla fyra tabellerna som innehåller de data vi behöver.

Själva frågan är inte komplex. I resultatet bör vi märka att vi har tio rader, samma antal som antalet samtal vi har i databasen. Jag har också använt DATEDIFF-funktionen för att återge varaktigheten för varje samtal på några sekunder. Jag kunde helt enkelt sätta call.id här, men jag ville påminna oss om DATEDIFF-funktionen som vi har nämnt i den här artikeln. Vi kan använda den här varaktigheten om vi behöver en SUM / AVG av sekunder tillbringade per varje rapporteringskategori (stad & samtalsresultat).

Rapportera utan SQL Server PIVOT-TABELL

Nu är vi redo att gå med i kategorier och data. Vi använder båda tidigare nämnda frågor som underfrågor och går med dem med VÄNSTER JOIN (för att alla kategorier ska finnas i den slutliga utdata).

Vi har alla 18 kombinationer (6 olika städer * 3 olika samtalsresultat), och vi har också alla 10 samtal närvarande här (rader med samtalslängden < > NULL).

Låt oss nu skapa SQL Server PIVOT-frågan.

SQL Server PIVOT-TABELL (statisk)

Hittills har vi lyckats dra de data vi behöver, och vi har dem som en lista. Vi kan exportera data till Excel och göra omvandlingar där. Vi gör det faktiskt i den kommande artikeln.Idag vill vi fortfarande lösa detta med SQL Server PIVOT-operatören. I det här avsnittet täcker vi den ”statiska” SQL Server PIVOT. Låt oss ta en titt på den använda frågan och resultatet först.

Vi kan märka att resultatet är exakt det vi ville ha. Vi har varje stad i en rad och alla tre kategorierna för samtalsresultat i separata kolumner. Om det inte finns data för ett visst par-resultatpar, kommer den cellen ska innehålla värdet 0.

Låt oss kommentera SQL Server PIVOT-frågan nu. Det här är några saker som jag tycker är viktiga att nämna här:

• I första delen av frågan – report_data, vi har kopierat in frågan från föregående avsnitt. Den enda ändringen var att vi inte hade ORDER BY och GROUP BY i denna del av frågan eftersom de inte kan tillämpas här (de borde gå efter det slutliga frågeresultatet genereras)
• PIVOT-delen av frågan består av två delar. I den första definierar vi vilken aggregerad funktion vi vill tillämpa. I vårt fall är detta – COUNT ( call_duration). I FOR del av frågan här definierar vi kolumner. Vi listar alla värden som vi vill ha som kolumner. Detta är hårdkodande. Om vi lägger till nytt värde i tabellen påverkar det inte frågan (och den borde)

Den här frågan gör sitt jobb och den fungerar perfekt om vi aldrig ändrar (lägg till nytt , ta bort, uppdatera deras namn) samtalsresultat i den relaterade tabellen. Det kan visa sig vara problemet i de fall vi behöver göra ändringar i ordboken. Vi vill inte tänka om det finns en fråga som inte fungerar som förväntat. För att lösa detta måste vi använda ett nytt koncept – dynamisk SQL.

Dynamic SQL Server PIVOT TABLE

Innan vi går till koden, låt oss kort förklara vad den dynamiska SQL egentligen är (vi ger en mycket mer detaljerad genomgång av den i kommande artiklar). Den enklaste förklaringen är att i SQL kan du ”bygga” dina frågor som strängar och sedan skicka den strängen som en parameter för den lagrade SQL Server-proceduren. Denna procedur ska köra SQL-sats (er) lagrade i den strängen (naturligtvis , om syntaxen är OK).

Även om det här låter som något som inte är så vanligt, har det en hel del platser där det gör ditt liv mycket enklare. Skapa en SQL Server PIVOT-tabellfråga med en okänd antalet kolumner är exakt ett sådant fall.

Idén med detta tillvägagångssätt är som följer:

• Vi förklarar en variabel där alla kolumnnamn (@column) ska lagras ) och variabeln där den fullständiga frågan ska lagras (@query)
• Med hjälp av SELECT-frågan hittar vi alla värden som är lagrade i kolumnen result_text. Följande kod – @column + = QUOTENAME (TRIM (co.outcome_text)) + ,, ska lägga till kolumnnamn i listan över alla tidigare kolumnnamn som returneras av frågan. Resultatet är att vi har alla kolumnnamn lagrade i e variabel @kolumner
• I variabeln @query lagrar vi hela frågan från föregående avsnitt, förutom den del där kolumner är definierade. Vi får den här delen från variabeln @column
• Det sista vi behöver göra är att skicka hela frågan som parameter till SQL Server-systemproceduren sp_executesql

Det slutliga resultatet är detsamma som i föregående avsnitt, men den här gången är vi säkra på att vår fråga fungerar även om vi gör ändringar i utfallet. resultat_textvärden. Du kan också enkelt ändra denna fråga och beräkna andra värden, t.ex. SUM / AVG-samtalslängd per stad-utfallspar.

Slutsats

SQL Server PIVOT-operatören ger dig en helt ny bild av vad du kan uppnå direkt på databaslagret. I kombination med dynamisk SQL går detta ännu längre. Jag uppmuntrar dig starkt att leka med det – det finns inget bättre sätt att lära sig än att prova det själv. I nästa artikel visar vi hur vi kan använda SQL-frågeutdata och skapa tabeller och grafer i Excel (detta ska inte bara fungera i SQL Server utan i allmänhet).

Innehållsförteckning

Lär dig SQL: SKAPA DATABAS & SKAPA TABELLåtgärder

Lär dig SQL: INSÄTTA I TABELL

Lär dig SQL: Primär nyckel

Lär dig SQL: Utländsk nyckel

Lär dig SQL: SELECT-uttalande

Lär dig SQL: INNER JOIN vs LEFT JOIN

Lär dig SQL: SQL-skript

Lär dig SQL: Typer av relationer

Lär dig SQL: Gå med flera tabeller

Lär dig SQL: Aggregerade funktioner

Lär dig SQL: Hur man skriver en komplex SELECT-fråga?

Lär dig SQL: INFORMATION_SCHEMA-databasen

Lär dig SQL: SQL-datatyper

Lär dig SQL: Ställ in teori

Lär dig SQL: Användardefinierade funktioner

Lär dig SQL: Användardefinierade lagrade procedurer

Lär dig SQL: SQL-vyer

Lär dig SQL: SQL-utlösare

Lär dig SQL: Öva SQL-frågor

Lär dig SQL: SQL-frågeexempel

Lär dig SQL: Skapa en rapport manuellt med SQL-frågor

Lär dig SQL: SQL Server-datum- och tidsfunktioner

Lär dig SQL: Skapa SQL Server-rapporter med hjälp av datum- och tidsfunktioner

Lär dig SQL: SQL Server-pivottabeller

Lär dig SQL: SQL Server-export till Excel

Lär dig SQL: Introduktion till SQL Server-slingor

Lär dig SQL: SQL Server-markörer

Lär dig SQL: SQL bästa metoder för att radera och uppdatera data

Lär dig SQL: Namngivningskonventioner

Lär dig SQL: SQL-relaterade jobb

Lär dig SQL: Icke-Equi-anslutningar i SQL Server

Lär dig SQL: SQL-injektion

• Författare
• Senaste inlägg

Emil är databasproffs med 10+ års erfarenhet av allt som rör databaser. Under åren arbetade han inom IT- och finansbranschen och arbetar nu som frilansare.
Hans tidigare och nuvarande uppdrag varierar från databasdesign och kodning till undervisning, konsultation och skrivning om databaser. För att inte glömma, BI, skapa algoritmer, schack, filateli, 2 hundar, 2 katter, 1 fru, 1 bebis …
Du hittar honom på LinkedIn
Visa alla inlägg av Emil Drkusic

Senaste inlägg av Emil Drkusic (se alla)

• Lär dig SQL: SQL-injektion – 2 november 2020
• Lär dig SQL: Non-Equi-anslutningar i SQL Server – 29 september 2020
• Lär dig SQL: SQL-relaterade jobb – 1 september 2020