SQLShack (Magyar)

A sorozat előző néhány cikkében megalapoztuk a jelentés készítésének módját. Tegyünk egy lépést tovább, és megtudjuk, hogyan kell használni az SQL Server PIVOT tábla operátort. Kiindulunk az egyszerű lekérdezésből, és lassan haladunk a dinamikus SQL és a PIVOT felé. Kezdjük.

Adatmodell és általános elképzelés

Az általunk használt adatmodell ugyanaz, amelyet ebben a sorozatban használtunk.

A mai feladatunk egy jelentés létrehozása (az SQL Server PIVOT operátor használatával), ahol minden város külön sorban lesz , és ahol megszámoljuk az egyes városokkal kapcsolatos összes hívási eredmény számát. Ezért az összes eredménynek (a szótár összes értéke) oszlopnak kell lennie a jelentésünkben.

Mivel “egy kép ezer szót ér”, a képpel is leírjuk végső célunkat.

Ehhez 4 tábla hívás, hívás_kimenet, ügyfél és város adatait kell felhasználnunk. Ezért jó lenne, ha alaposabban megvizsgálja az alábbi modellt, és elemzi, hogy ezek a táblák hogyan kapcsolódnak egymáshoz.

Kezdjük azzal, hogy megvizsgáljuk a táblázatok adatait, és minden egyes lekérdezésnél egy lépéssel közelebb kerülünk a a kívánt eredményt (lekérdezést). Az itt használt megközelítés megegyezik az SQL használatának módszerével: Hozzon létre jelentést manuálisan az SQL lekérdezések cikk használatával.

Jelentéskategóriák és adatok

az első lekérdezéssorozat az, amely mind a 4 táblázatban jelen lévő adatokat megvizsgálja, amelyeket a jelentés elkészítéséhez használnunk kell. Meg fogjuk csinálni, hogy megerősítsük, hogy a végső jelentés megkapta a kívánt eredményt. Ezt követően létrehozunk lekérdezéseket wh ich visszaadja a jelentési kategóriákat és a jelentés adatait, valamint az SQL Server PIVOT tábla lekérdezéseit.

Ezek a lekérdezések a lehető legegyszerűbbek, ezért nincs különösebb megjegyzés a szintaxisukkal kapcsolatban. A visszaküldött adatokkal kapcsolatban a következőkre kell figyelnünk:

• A várostábla 6 várost tartalmaz, ezért a zárójelentésben 6 sort kell tartalmaznunk.
• Vannak 3 lehetséges kimenet a call_outcome táblázatban, ezért 3 oszloppal kell rendelkeznünk az eredményekről (összesen 4 oszlop – > az egyik a város nevéhez tartozik)
• A tábla hívás 10 sor (csak 8 a fenti képen), tehát a végső táblázatban az összes eredmény összegének 10-nek kell lennie (minden hívásnak pontosan 1 eredménye lehet)

A következő tennivaló jelentési kategóriák előkészítése. Az összes város és az összes lehetséges hívási eredmény kombinációját szeretnénk elérni. Ennek eléréséhez a CROSS JOIN-t (derékszögű termék) fogjuk használni.

Az összes jelentési kategória megléte garantálja, hogy sorunk lesz a jelentésben, függetlenül attól, hogy annak a párnak vannak-e adatai vagy sem. És ezt szeretnénk – 0-t látni abban a jelentési kategóriában, és nem hagyni ki teljesen ezt a kategóriát.

A következő tennivaló a mind a 4 táblázat összekapcsolása, amelyek a szükséges adatokat tartalmazzák.

Maga a lekérdezés nem összetett. Ennek eredményeként észre kell vennünk, hogy 10 sorunk van, ugyanannyi, mint az adatbázisban lévő hívások száma. Ezenkívül a DATEDIFF funkciót használtam az egyes hívások időtartamának másodpercekben történő visszaadásához. Egyszerűen ide tehetném a call.id-t, de emlékeztetni akartam magunkat a DATEDIFF függvényre, amelyet ebben a cikkben említettünk. Akkor használhatjuk ezt az időtartamot, ha az egyes jelentési kategóriákra SUM / AVG-ra van szükségünk (város & hívás eredménye).

Jelentés az SQL Server PIVOT TABLE nélkül

Most már készen állunk a kategóriák és adatok összekapcsolására. Mindkét korábban említett lekérdezést al lekérdezésként fogjuk használni, és a LEFT JOIN használatával összekapcsoljuk őket (annak érdekében, hogy az összes kategória jelen legyen a végső kimenetben).

Mind a 18 kombinációnk van (6 különböző város * 3 különböző hívási eredmény), és itt van mind a 10 hívás (a hívás időtartamával rendelkező sorok < > NULL).

Most hozzuk létre az SQL Server PIVOT lekérdezést.

SQL Server PIVOT TABLE (statikus)

Eddig sikerült megszereznünk a szükséges adatokat, és ezek szerepelnek a listán. Exportálhatnánk az adatokat az Excelbe, és ott átalakíthatnánk. Ezt valójában a következő cikkben fogjuk megtenni.Ennek ellenére ma ezt meg akarjuk oldani az SQL Server PIVOT operátor segítségével. Ebben a szakaszban kitérünk a “statikus” SQL Server PIVOT-ra. Először nézzük meg a használt lekérdezést és az eredményt.

Észrevehetjük, hogy az eredmény pontosan az, amit szerettünk volna. Minden város egy sorban van, és mindhárom kategória különálló oszlopokban van a hívás kimeneteléhez. Ha nincs adat egy adott város-eredmény párra, akkor az a cella tartalmaznia kell a 0 értéket.

Kommentáljunk most az SQL Server PIVOT lekérdezéséről. Ezeket néhány dolgot fontosnak tartom itt megemlíteni:

• Az első részben a lekérdezés – report_data, a lekérdezést átmásoltuk az előző szakaszból. Az egyetlen változás az volt, hogy a lekérdezés ezen részén nem volt ORDER BY és GROUP BY, mert itt nem lehet őket alkalmazni (utánuk kell menni a lekérdezés végeredménye létrejön)
• A lekérdezés PIVOT része 2 részből áll. Az elsőben meghatározzuk, hogy melyik összesített függvényt akarjuk alkalmazni. Esetünkben ez – COUNT ( call_duration). A FOR FOR részében a qu Erősen meghatározunk oszlopokat. Irodalmi helyen felsoroljuk az összes értéket, amelyet oszlopként szeretnénk elérni. Ez nehézkódolás. Ha új értéket adunk a táblához, az nem befolyásolja a lekérdezést (és ennek is kellene lennie)

Ez a lekérdezés elvégzi a dolgát, és tökéletesen fog működni, ha soha nem változtatunk (új hozzáadása , törölje, frissítse a nevüket) hívási eredményeket a kapcsolódó táblázatban. Ez bizonyulhat a problémának azokban az esetekben, amikor változtatnunk kell a szótárban. Nem akarunk arra gondolni, hogy van-e olyan lekérdezés, amely nem a várt módon működik. Ennek megoldásához új koncepciót kell használnunk – dinamikus SQL.

Dinamikus SQL Server PIVOT TÁBLÁZAT

Mielőtt a kódra lépnénk, röviden magyarázzuk el, mi is a dinamikus SQL valójában (sokkal részletesebb áttekintést adunk róla a következő cikkekben). A legegyszerűbb magyarázat az, hogy az SQL-ben karaktersorozatként “felépítheti” a lekérdezéseket, majd továbbíthatja ezt a karaktersorozatot az SQL Server tárolt eljárásának paramétereként. Ez az eljárás végrehajtja az adott karaktersorozatban tárolt SQL utasításokat (természetesen) , ha a szintaxis rendben van).

Bár ez úgy hangozhat, mintha valami nem olyan gyakran használt dolog lenne, jó néhány helyen van, ahol ez sokkal könnyebbé teszi az életét. SQL Server PIVOT tábla lekérdezés létrehozása ismeretlen az oszlopok száma pontosan egy ilyen eset.

Ennek a megközelítésnek az ötlete a következő:

• Deklarálunk egy változót, ahol az összes oszlop nevét tároljuk (@columns) ), és az a változó, ahová a teljes lekérdezést tárolni kell (@query)
• A SELECT lekérdezés használatával megtaláljuk az result_text oszlopban tárolt összes értéket. A következő kód – @columns + = QUOTENAME (TRIM (co.outcome_text)) + ,, az oszlop nevét hozzáfűzi a lekérdezés által visszaadott összes korábbi oszlopnév listájához. Ennek eredményeként az összes oszlopnév a e @columns változó
• A @query változóban az előző szakasz teljes lekérdezését tároljuk, kivéve azt a részt, ahol az oszlopok meg vannak határozva. Ezt a részt a @columns változóból kapjuk.
• Az utolsó dolog, amit meg kell tennünk, hogy a teljes lekérdezést paraméterként át kell adnunk az SQL Server rendszer sp_executesql

A végeredmény ugyanaz, mint az előző részben, de ezúttal biztosak vagyunk abban, hogy a lekérdezésünk akkor is működni fog, ha változtatunk a az eredmény.kimenet_szöveg értékeket. Emellett könnyedén módosíthatja ezt a lekérdezést, és kiszámolhat bármilyen más értéket, például SUM / AVG hívás időtartamát város-eredmény páronként.

Következtetés

Az SQL Server PIVOT operátor teljesen új nézet arról, hogy mit érhet el közvetlenül az adatbázis-rétegen. A dinamikus SQL-sel kombinálva ez még tovább megy. Erősen bátorítom, hogy játsszon vele – nincs jobb tanulási mód, mint hogy kipróbálja magát. A következő cikkben bemutatjuk, hogyan használhatnánk az SQL lekérdezés kimenetét, és hogyan hozhatunk létre táblázatokat és grafikonokat az Excel programban (ez nem csak az SQL Server rendszerben működik, hanem általában is).

Tartalomjegyzék

Tanuljon SQL-t: DATABÁZIS LÉTREHOZÁSA & TABLE műveletek létrehozása

Az SQL elsajátítása: INSERT IN TABLE

Tanuljon SQL: Elsődleges kulcs

Tanuljon SQL: Idegen kulcs

Tanuljon SQL: SELECT utasítást

Tanuljon SQL: INNER JOIN vs LEFT JOIN

Ismerje meg az SQL-t: SQL parancsfájlok

Ismerje meg az SQL-t: Kapcsolattípusok

Ismerje meg az SQL-t: Csatlakozás több táblázat

Ismerje meg az SQL-t: összesített függvények

Ismerje meg az SQL-t: Hogyan kell írni egy komplex SELECT lekérdezést?

Ismerje meg az SQL-t: Az INFORMATION_SCHEMA adatbázis

Ismerje meg az SQL-t: SQL adattípusok

Ismerje meg az SQL-t: Állítsa be az elméletet

Ismerje meg az SQL-t: Felhasználó által definiált függvények

Ismerje meg az SQL-t: Felhasználó által meghatározott tárolt eljárások

Ismerje meg az SQL-t: SQL-nézetek

Ismerje meg az SQL-t: SQL-triggerek

Ismerje meg az SQL-t: Gyakorolja az SQL-lekérdezéseket

Ismerje meg az SQL-t: SQL-lekérdezések példái

Ismerje meg az SQL-t: Hozzon létre jelentést manuálisan SQL-lekérdezések segítségével

Tanulja meg az SQL-t: SQL Server dátum- és időfüggvényei

Ismerje meg az SQL-t: Hozzon létre SQL Server-jelentéseket dátum és idő függvények használata

Ismerje meg az SQL-t: SQL Server pivot-táblák

Ismerje meg az SQL-t: SQL Server-exportálás Excelbe

Ismerje meg az SQL-t: Bevezetés az SQL Server-ciklusokba

Ismerje meg az SQL-t: SQL Server-kurzorok

Ismerje meg az SQL-t: Az SQL legjobb gyakorlata az adatok törléséhez és frissítéséhez

Ismerje meg az SQL-t: Elnevezési konvenciók

Ismerje meg az SQL-t: SQL-hez kapcsolódó munkák

Ismerje meg az SQL-t: Non-Equi csatlakozik az SQL Server-hez

Ismerje meg az SQL-t: SQL injekció

• Szerző
• Legutóbbi bejegyzések

Emil egy adatbázis-szakember több mint 10 éves tapasztalattal rendelkezik az adatbázisokkal kapcsolatban. Az évek során az informatikai és pénzügyi iparban dolgozott, és most szabadúszóként dolgozik.
Korábbi és jelenlegi feladatai az adatbázis-tervezéstől és a kódolástól az oktatásig, a tanácsadásig és az adatbázisokról szóló írásig változnak. Ne feledkezzünk meg a BI-ről sem, algoritmusok, sakk, filatélia, 2 kutya, 2 macska, 1 feleség, 1 baba …
Megtalálhatja őt a LinkedIn oldalon
Druskic Emil összes bejegyzésének megtekintése

Drkusic Emil legújabb üzenetei (az összes megtekintése)

• Tanuljon SQL: SQL Injection – 2020. november 2.
• Tanuljon SQL-t: Nem Equi-tagok csatlakoznak az SQL Server-hez – 2020. szeptember 29.
• Tanuljon SQL-t: SQL-hez kapcsolódó munkák – 2020. szeptember 1.