SQLShack (Suomi)

Tämän sarjan muutamissa edellisissä artikkeleissa Olemme asettaneet perustan raportin luomiselle. Otamme yhden askeleen pidemmälle ja katsomme, kuinka SQL Server PIVOT -taulukko-operaattoria käytetään. Aloitamme yksinkertaisesta kyselystä ja etenemme hitaasti kohti dynaamista SQL: ää ja PIVOTia. Aloitetaan.

Datamalli ja yleinen ajatus

Käytettävä tietomalli on sama, jota olemme käyttäneet tässä sarjassa.

Tänään tehtävämme on luoda raportti (käyttäen SQL Server PIVOT -operaattoria), jossa kukin kaupunki tulee olemaan erillinen rivi , ja missä laskemme kuhunkin kaupunkiin liittyvien kaikkien puhelutulosten määrän. Siksi kaikki tulokset (kaikki sanakirjan arvot) ovat sarakkeita raportissamme.

Koska ”kuva on tuhannen sanan arvoinen”, kuvailemme myös lopullista tavoitettamme kuvalla.

Tämän saavuttamiseksi meidän on käytettävä 4 taulukon puhelun, puhelun_tuloksen, asiakkaan ja kaupungin tietoja. Siksi se olisi hyvä, että tarkastelet tarkemmin alla olevaa mallia ja analysoit, miten nämä taulukot liittyvät toisiinsa.

Aloitamme tutkimalla taulukoissa olevia tietoja ja siirtymällä jokaisen kyselyn kohdalla yhden askeleen lähemmäksi haluttu tulos (kysely). Tässä käytetty lähestymistapa on sama kuin Learn SQL: ssä: Luo raportti manuaalisesti SQL-kyselyartikkelin avulla.

Raporttiluokat ja tiedot

ensimmäinen kyselyjoukko, josta aloitamme, on se, että tutkitaan tietoja, jotka ovat tällä hetkellä kaikissa 4 taulukossa, joita meidän on käytettävä raportin luomiseen. Teemme sen, jotta voimme vahvistaa, että loppuraportti palautti sen, mitä sen pitäisi. Sen jälkeen luomme kyselyjä wh ich palauttaa raportointiluokkia ja raporttitiedot sekä SQL Server PIVOT -taulukokyselyt.

Nämä kyselyt ovat niin yksinkertaisia kuin voisivat olla, joten niiden syntaksissa ei ole mitään erityistä kommentoitavaa. Palautettujen tietojen osalta meidän tulisi olla tietoisia seuraavista:

• Kaupunkitaulukossa on 6 kaupunkia, joten loppuraportissa pitäisi olla 6 riviä
• On olemassa Kolme mahdollista tulosta call_outcome-taulukossa, joten tuloksilla pitäisi olla 3 saraketta (yhteensä 4 saraketta – > yksi on kaupungin nimi)
• Taulukko kutsu on 10 riviä (vain 8 yllä olevassa kuvassa), joten lopullisessa taulukossa kaikkien tulosten summan tulisi olla 10 (jokaisella puhelulla voi olla täsmälleen yksi tulos)

Seuraava tehtävä on laatia raportointiluokkia. Haluamme yhdistää kaikki kaupungit ja kaikki mahdolliset puhelutulokset. Tämän saavuttamiseksi käytämme CROSS JOIN -tekniikkaa (suorakaiteen muotoinen tuote).

Kaikkien raportointiluokkien saaminen takaa, että raportissa on rivi riippumatta siitä, onko kyseisellä parilla tietoja vai ei. Ja juuri sitä me haluamme – nähdä 0 raportointiluokassa ja olla jättämättä tätä luokkaa kokonaan väliin.

Seuraava tehtävä on liittyä kaikkiin 4 taulukkoon, jotka sisältävät tarvitsemamme tiedot.

Itse kysely ei ole monimutkainen. Tuloksena meidän pitäisi huomata, että meillä on 10 riviä, sama numero kuin tietokannassa olevien puheluiden määrä. Olen myös käyttänyt DATEDIFF-toimintoa palauttaaksesi jokaisen puhelun keston sekunteina. Voisin yksinkertaisesti laittaa call.id: n tänne, mutta halusin muistuttaa itseämme tässä artikkelissa mainitsemastamme DATEDIFF-toiminnosta. Voisimme käyttää tätä kestoa, jos tarvitsemme SUM / AVG sekunneista kullekin raportointiluokalle (kaupunki & puhelutulos).

Raportti ilman SQL Server PIVOT -TAULUKKOA

Nyt olemme valmiita yhdistämään luokkia ja tietoja. Käytämme molempia aiemmin mainittuja kyselyjä alakyselyinä ja yhdistämme ne VASEN JOIN -toiminnon avulla (jotta kaikki luokat olisivat mukana lopullisessa tuotoksessa).

Meillä on kaikki 18 yhdistelmää (6 eri kaupunkia * 3 erilaista puhelutulosta), ja täällä on myös kaikki 10 puhelua (rivit, joiden kesto on < > NULL).

Luodaan nyt SQL Server PIVOT -kysely.

SQL Server PIVOT -TAULUKKO (staattinen)

Toistaiseksi olemme onnistuneet hankkimaan tarvitsemamme tiedot, ja meillä on ne luettelona. Voisimme viedä tietoja Exceliin ja tehdä siellä muutoksia. Teemme tämän itse asiassa tulevassa artikkelissa.Silti tänään haluamme ratkaista tämän käyttämällä SQL Server PIVOT -operaattoria. Tässä osiossa käsitellään ”staattista” SQL Server PIVOT -ohjelmaa. Tarkastellaan ensin käytettyä kyselyä ja tulosta.

Voimme huomata, että tulos on juuri se, mitä halusimme. Meillä on kukin kaupunki yhdellä rivillä ja kaikki 3 luokkaa puhelutuloksille erillisissä sarakkeissa. Jos tietylle kaupungin tulosparille ei ole tietoja, kyseinen solu sisältää arvon 0.

Kommentoimme nyt SQL Server PIVOT -kyselyä. Nämä ovat muutamia asioita, jotka pidän tärkeinä mainita tässä:

• Ensimmäisen osan kysely – report_data, olemme kopioineet ja liittäneet kyselyn edellisestä osiosta. Ainoa muutos oli, että kyselyn tässä osassa ei ollut ORDER BY- tai GROUP BY -määriä, koska niitä ei voida käyttää täällä (niiden pitäisi mennä jälkeen lopullinen kyselytulos luodaan)
• Kyselyn PIVOT-osa koostuu 2 osasta. Ensimmäisessä määritetään, mitä aggregaattitoimintoa haluamme käyttää. Meidän tapauksessamme tämä on – COUNT ( puhelun kesto) määritämme sarakkeet. Kirjallisuusluettelossa luetellaan kaikki arvot, jotka haluamme olla sarakkeina. Tämä on kovakoodausta. Jos lisäämme taulukkoon uutta arvoa, se ei vaikuta kyselyyn (ja sen pitäisi)

Tämä kysely tekee tehtävänsä ja se toimii täydellisesti, jos emme koskaan muutu (lisää uusi , poista, päivitä heidän nimensä) puhelutulokset vastaavassa taulukossa. Se voi osoittautua ongelmaksi tapauksissa, joissa meidän on tehtävä muutoksia sanakirjaan. Emme halua ajatella, onko kysely, joka ei toimi odotetulla tavalla. Tämän ratkaisemiseksi meidän on käytettävä uutta käsitettä – dynaaminen SQL.

Dynaaminen SQL Server PIVOT-TAULUKKO

Ennen kuin siirryt koodiin, selitetään lyhyesti, mitä dynaaminen SQL todella tarkoittaa on (annamme paljon tarkemman katsauksen siitä tulevissa artikkeleissa). Yksinkertaisin selitys on, että SQL: ssä voit ”rakentaa” kyselysi merkkijonoina ja välittää tämän merkkijonon parametriksi SQL Serverin tallennettuun menettelyyn.Tämä toimenpide suorittaa kyseiseen merkkijonoon tallennetut SQL-käskyt (tietysti , jos syntaksi on kunnossa).

Vaikka tämä saattaa kuulostaa siltä, että jotain ei ole niin yleisesti käytetty, siinä on melko monta paikkaa, missä se helpottaa elämääsi. SQL Server PIVOT -taulukko kyselyn luominen tuntemattomalla sarakemäärä on täsmälleen yksi tällainen tapaus.

Tämän lähestymistavan idea on seuraava:

• Ilmoitamme muuttujan, johon kaikki sarakkeiden nimet (@columns) tallennetaan. ) ja muuttuja, johon koko kysely tallennetaan (@query)
• SELECT-kyselyn avulla löydämme kaikki arvot result_text -sarakkeeseen. Seuraava koodi – @columns + = QUOTENAME (TRIM (co.outcome_text)) + ,, lisää sarakkeen nimi kaikkien kyselyn palauttamien sarakkeiden nimien luetteloon. Tuloksena on, että kaikki sarakkeiden nimet on tallennettu e muuttuja @columns
• @query -muuttujaan tallennetaan edellisen osan koko kysely lukuun ottamatta sitä osaa, jossa sarakkeet on määritelty. Saamme tämän osan muuttujasta @columns
• Viimeinen asia, joka meidän on tehtävä, on välittää täydellinen kysely parametrina SQL Server -järjestelmän menettelyyn sp_executesql

Lopputulos on sama kuin edellisessä osassa, mutta tällä kertaa olemme varmoja, että kyselymme toimii, vaikka teemme muutoksia tulos.tulos_teksti-arvot. Voit myös helposti muokata tätä kyselyä ja laskea muut arvot, esimerkiksi SUM / AVG-puhelun kesto kaupunkitulosparia kohti.

Päätelmä

SQL Server PIVOT -operaattori antaa sinulle täysin uusi näkymä siitä, mitä voit saavuttaa suoraan tietokantatasolla. Yhdistettynä dynaamiseen SQL: ään tämä menee vielä pidemmälle. Kehotan voimakkaasti pelaamaan sillä – ei ole parempaa tapaa oppia kuin kokeilla itse. Seuraavassa artikkelissa näytetään, kuinka voisimme käyttää SQL-kyselylähtöä ja luoda taulukoita ja kaavioita Exceliin (tämä toimii paitsi SQL Serverissä myös yleensä).

Sisällysluettelo

Opi SQL: Luo tietokanta & Luo taulukon toiminnot

Opi SQL: LISÄÄ TAULUKKOON

Opi SQL: ensisijainen avain

Opi SQL: Vieras avain

Opi SQL: SELECT-käsky

Opi SQL: INNER JOIN vs VASEN JOIN

Opi SQL: SQL-komentosarjat

Opi SQL: Suhteiden tyypit

Opi SQL: Liity useita taulukoita

Opi SQL: Aggregate Functions

Opi SQL: Kuinka kirjoittaa monimutkainen SELECT-kysely?

Opi SQL: INFORMATION_SCHEMA-tietokanta

Opi SQL: SQL-tietotyypit

Opi SQL: Aseta teoria

Opi SQL: Käyttäjän määrittelemät toiminnot

Opi SQL: Käyttäjän määrittelemät tallennetut menettelyt

Opi SQL: SQL-näkymät

Opi SQL: SQL-käynnistimet

Opi SQL: Harjoittele SQL-kyselyjä

Opi SQL: Esimerkkejä SQL-kyselyistä

Opi SQL: Luo raportti manuaalisesti SQL-kyselyjen avulla

Opi SQL: SQL Serverin päivämäärä- ja aikatoiminnot

Opi SQL: Luo SQL Server -raportit päivämäärä- ja aikatoimintojen käyttäminen

Opi SQL: SQL Server Pivot -taulukot

Opi SQL: SQL Server viedään Exceliin

Opi SQL: johdanto SQL Server -silmukkoihin

Opi SQL: SQL Server -kohdistimet

Opi SQL: SQL: n parhaat käytännöt tietojen poistamiseen ja päivittämiseen

Opi SQL: nimeämiskäytännöt

Opi SQL: SQL-työtehtävät

Opi SQL: Muut kuin Equi-liittymät SQL Serveriin

Opi SQL: SQL Injection

• Kirjoittaja
• Viimeisimmät viestit

Emil on tietokannan ammattilainen yli 10 vuoden kokemus kaikesta tietokantoihin liittyvästä. Vuosien varrella hän työskenteli IT- ja finanssialalla ja työskentelee nyt freelancerina.
Hänen aikaisemmat ja nykyiset tehtävänsä vaihtelevat tietokantojen suunnittelusta ja koodauksesta opetukseen, konsultointiin ja tietokantojen kirjoittamiseen. Älä unohda, BI, algoritmien luominen, shakki, filatelia, 2 koiraa, 2 kissaa, 1 vaimo, 1 vauva …
Löydät hänet LinkedInistä
Näytä kaikki Emil Drkusicin viestit

Emil Drkusic uusimmat viestit (katso kaikki)

• Opi SQL: SQL Injection – 2. marraskuuta 2020
• Opi SQL: Muut kuin Equi-jäsenet SQL Serverissä – 29. syyskuuta 2020
• Opi SQL: SQL-työtehtävät – 1. syyskuuta 2020