SQLShack

In de vorige paar artikelen in deze serie, we hebben de basis gelegd voor het maken van een rapport. We gaan nog een stap verder en zien hoe u de SQL Server PIVOT-tabeloperator gebruikt. We beginnen bij de eenvoudige query en gaan langzaam over naar dynamische SQL en PIVOT. Laten we beginnen.

Datamodel en het algemene idee

Het datamodel dat we zullen gebruiken, is hetzelfde dat we in deze reeks hebben gebruikt.

Onze taak vandaag is om een rapport te maken (met behulp van de SQL Server PIVOT-operator) waarin elke stad in een aparte rij staat , en waar we het aantal oproepresultaten voor elke stad tellen. Daarom zullen alle resultaten (alle waarden uit het woordenboek) kolommen in ons rapport zijn.

Aangezien “een foto meer zegt dan duizend woorden”, zullen we ons uiteindelijke doel ook met de foto beschrijven.

Om dit te bereiken, hebben we gegevens uit 4 tabellen nodig: call, call_outcome, customer en city. Daarom zou goed zijn als u onderstaand model nader bekijkt en analyseert hoe deze tabellen aan elkaar gerelateerd zijn.

We beginnen met het onderzoeken van de gegevens in de tabellen en gaan bij elke zoekopdracht een stap dichter bij het gewenste resultaat (query). De benadering die hier wordt gebruikt, is dezelfde als die wordt gebruikt in Learn SQL: maak handmatig een rapport met behulp van het artikel SQL-queries.

Rapportcategorieën en gegevens

De De eerste reeks zoekopdrachten waarmee we beginnen, is de vraag die de gegevens onderzoekt die momenteel aanwezig zijn in alle vier de tabellen die we moeten gebruiken om het rapport te maken. We zullen het doen zodat we kunnen bevestigen dat het eindrapport heeft geretourneerd wat het zou moeten doen. Daarna maken we queries wh ich rapportagecategorieën en de rapportgegevens retourneren, evenals SQL Server PIVOT-tabelquerys.

Deze zoekopdrachten zijn zo eenvoudig als ze zouden kunnen zijn, dus er is niets speciaals te zeggen over hun syntaxis. Met betrekking tot de geretourneerde gegevens moeten we rekening houden met het volgende:

• De stadstabel bevat 6 steden, en daarom zouden we 6 rijen moeten hebben in het eindrapport
• Er zijn 3 mogelijke uitkomsten in de call_outcome-tabel, dus we zouden 3 kolommen moeten hebben voor uitkomsten (4 kolommen totaal – > een is voor plaatsnaam)
• De tabeloproep heeft 10 rijen (slechts 8 op de afbeelding hierboven), dus in de finaletafel zou de som van alle uitkomsten 10 moeten zijn (elke oproep kan precies 1 uitkomst hebben)

Het volgende dat je moet doen is het voorbereiden van rapportagecategorieën. We willen een combinatie hebben van alle steden en alle mogelijke oproepuitkomsten. Om dat te bereiken, gebruiken we CROSS JOIN (Cartesiaans product).

Het hebben van alle rapportagecategorieën garandeert dat we hebben een rij in het rapport, ongeacht of dat paar gegevens heeft of niet. En dat is wat we willen – 0 in die rapportagecategorie zien, en die categorie niet helemaal missen.

Het volgende dat je moet doen, is alle 4 de tabellen samenvoegen met de gegevens die we nodig hebben.

De zoekopdracht zelf is niet complex. In het resultaat zouden we moeten opmerken dat we 10 rijen hebben, hetzelfde aantal als het aantal oproepen dat we in de database hebben. Ik heb ook de DATEDIFF-functie gebruikt om de duur van elke oproep in seconden te retourneren. Ik zou gewoon call.id hier kunnen plaatsen, maar ik wilde onszelf herinneren aan de DATEDIFF-functie die we in dit artikel hebben genoemd. We zouden deze duur kunnen gebruiken als we een SUM / AVG van seconden nodig hebben voor elke rapportagecategorie (stad & oproepresultaat).

Rapport zonder SQL Server DRAAITABEL

Nu zijn we klaar om categorieën en gegevens samen te voegen. We gebruiken beide eerder genoemde queries als subquerys en voegen ze samen met LEFT JOIN (om alle categorieën in de uiteindelijke uitvoer te hebben).

We hebben alle 18 combinaties (6 verschillende steden * 3 verschillende oproepuitkomsten), en we hebben ook alle 10 oproepen hier aanwezig (rijen met de oproepduur < > NULL).

Laten we nu de SQL Server PIVOT-query maken.

SQL Server PIVOT TABLE (statisch)

Tot nu toe zijn we erin geslaagd de gegevens op te halen die we nodig hebben, en we hebben ze als lijst. We zouden gegevens naar Excel kunnen exporteren en daar transformaties kunnen maken. We zullen dit daadwerkelijk doen in het komende artikel.Toch willen we dit vandaag oplossen met behulp van de SQL Server PIVOT-operator. In deze sectie behandelen we de “statische” SQL Server PIVOT. Laten we eerst eens kijken naar de gebruikte query en het resultaat.

We kunnen zien dat het resultaat precies is wat we wilden. We hebben elke stad in één rij en alle drie de categorieën voor gespreksresultaten in aparte kolommen. Als er geen gegevens zijn voor een bepaald stad-uitkomstpaar, is die cel zal de waarde 0 bevatten.

Laten we nu commentaar geven op de SQL Server PIVOT-query. Dit zijn een paar dingen die ik belangrijk vind om hier te vermelden:

• In het eerste deel van de query – report_data, we hebben de query uit de vorige sectie gekopieerd. De enige verandering was dat we geen ORDER BY en GROUP BY hadden in dit deel van de query, omdat ze hier niet kunnen worden toegepast (ze zouden moeten gaan na het uiteindelijke queryresultaat wordt gegenereerd)
• Het PIVOT-gedeelte van de query bestaat uit 2 delen. In het eerste gedeelte definiëren we welke geaggregeerde functie we willen toepassen. In ons geval is dit – COUNT ( call_duration) In FOR deel van de qu eh, we zullen kolommen definiëren. We vermelden literair alle waarden die we als kolommen willen hebben. Dit is hard coderen. Als we nieuwe waarde aan de tabel toevoegen, heeft dat geen invloed op de zoekopdracht (en dat zou ook moeten)

Deze zoekopdracht doet zijn werk, en het zal perfect werken als we nooit veranderen (voeg nieuwe , delete, update hun namen) call resultaten in de gerelateerde tabel. Dat kan het probleem blijken te zijn in gevallen waarin we wijzigingen in het woordenboek moeten aanbrengen. We willen niet nadenken als er een vraag is die niet werkt zoals verwacht. Om dit op te lossen, moeten we een nieuw concept gebruiken – dynamische SQL.

Dynamic SQL Server DRAAITABEL

Laten we, voordat we naar de code gaan, kort uitleggen wat de dynamische SQL werkelijk is is (we zullen er in de komende artikelen een veel gedetailleerdere bespreking van geven). De eenvoudigste verklaring is dat u in SQL uw querys kunt bouwen als strings, en die string vervolgens kunt doorgeven als parameter voor de opgeslagen procedure van SQL Server. Deze procedure voert de SQL-instructie (s) uit die in die string zijn opgeslagen (natuurlijk , als de syntaxis in orde is).

Hoewel dit klinkt als iets dat niet zo vaak wordt gebruikt, heeft het nogal wat plaatsen waar het uw leven een stuk gemakkelijker maakt. Een SQL Server PIVOT-tabelquery maken met een onbekende aantal kolommen is precies zon geval.

Het idee van deze benadering is als volgt:

• We declareren een variabele waar alle kolomnamen moeten worden opgeslagen (@columns ), en de variabele waar de volledige query moet worden opgeslagen (@query)
• Met behulp van de SELECT-query vinden we alle waarden die zijn opgeslagen in de kolom uitkomst_tekst. De volgende code – @columns + = QUOTENAME (TRIM (co.outcome_text)) + ,, zal kolomnaam toevoegen aan de lijst met alle vorige kolomnamen die door de zoekopdracht zijn geretourneerd. Het resultaat is dat we alle kolomnamen hebben opgeslagen in th e-variabele @columns
• In de @query-variabele slaan we de volledige query uit de vorige sectie op, behalve het gedeelte waar kolommen zijn gedefinieerd. We halen dit deel uit de variabele @columns
• Het laatste wat we moeten doen is de volledige query als parameter doorgeven aan de SQL Server-systeemprocedure sp_executesql

Het uiteindelijke resultaat is hetzelfde als in het vorige gedeelte, maar deze keer weten we zeker dat onze zoekopdracht zal werken, zelfs als we wijzigingen aanbrengen in de result.outcome_text waarden. U kunt deze query ook gemakkelijk aanpassen en andere waarden berekenen, bijv. SUM / AVG-aanroepduur per stad-uitkomstpaar.

Conclusie

De SQL Server PIVOT-operator geeft u een volledig nieuwe kijk op wat u direct op de databaselaag kunt bereiken. In combinatie met de dynamische SQL gaat dit zelfs nog verder. Ik moedig je sterk aan om ermee te spelen – er is geen betere manier om te leren dan het zelf te proberen. In het volgende artikel laten we zien hoe we SQL-queryuitvoer kunnen gebruiken en tabellen en grafieken kunnen maken in Excel (dit werkt niet alleen in SQL Server, maar in het algemeen).

Inhoudsopgave

SQL leren: DATABASE MAKEN & TABELbewerkingen MAKEN

SQL leren: INSERT IN TABLE

SQL leren: primaire sleutel

SQL leren: externe sleutel

SQL leren: instructie SELECT

SQL leren: INNER JOIN versus LEFT JOIN

Leer SQL: SQL-scripts

Leer SQL: soorten relaties

Leer SQL: doe mee meerdere tabellen

SQL leren: geaggregeerde functies

SQL leren: hoe schrijf je een complexe SELECT-query?

SQL leren: de INFORMATION_SCHEMA-database

SQL leren: SQL-gegevenstypen

SQL leren: set-theorie

SQL leren: door de gebruiker gedefinieerde functies

SQL leren: door de gebruiker gedefinieerde opgeslagen procedures

SQL leren: SQL-weergaven

SQL leren: SQL-triggers

SQL leren: SQL-querys oefenen

SQL leren: SQL-queryvoorbeelden

SQL leren: handmatig een rapport maken met SQL-querys

SQL leren: SQL Server-datum- en tijdfuncties

SQL leren: SQL Server-rapporten maken datum- en tijdfuncties gebruiken

SQL leren: SQL Server-draaitabellen

SQL leren: SQL Server exporteren naar Excel

SQL leren: inleiding tot SQL Server-lussen

SQL leren: SQL Server-cursors

SQL leren: SQL-best practices voor het verwijderen en bijwerken van gegevens

SQL leren: naamgevingsconventies

SQL leren: SQL-gerelateerde taken

SQL leren: niet-Equi-joins in SQL Server

SQL leren: SQL-injectie

• Auteur
• Recente berichten

Emil is een databaseprofessional met meer dan 10 jaar ervaring in alles wat met databases te maken heeft. Gedurende de jaren heeft hij in de IT- en financiële sector gewerkt en werkt nu als freelancer.
Zijn vroegere en huidige opdrachten variëren van database-ontwerp en codering tot lesgeven, adviseren en schrijven over databases. Ook niet te vergeten, BI, algoritmes maken, schaken, filatelie, 2 honden, 2 katten, 1 vrouw, 1 baby …
Je kunt hem vinden op LinkedIn
Bekijk alle berichten van Emil Drkusic

Laatste berichten door Emil Drkusic (bekijk alle)

• SQL leren: SQL-injectie – 2 november 2020
• SQL leren: niet-Equi joins in SQL Server – 29 september 2020
• SQL leren: SQL-gerelateerde vacatures – 1 september 2020