Joulupähkinä #2 – SQL kumulatiivinen summa

Toinen joulupähkinä on myös kumulatiivinen summa. Nyt SQL kielellä! Tarkoitus on saada sama tulosjoukko aikaiseksi, kuin ensimmäiselläkin kerralla, mutta nyt T-SQL Selectillä. Data on sama kuin ensimmäisellä kerralla, joten tulosjoukon tulisi näyttää samalta.

Tämä blogisarja on siis 24-osainen Microsoft Data joulupähkinä -sarja. Jokaisena joulukuun 24 ensimmäisenä päivänä tällä sivustolla julkaisen joko oman, tai toivottavasti myös muutaman vierailevan henkilön pulman Microsoft Data aiheista. Toivottavasti mahdollisimman moni katsoo pulman ja hieman miettii sitä, silloin tapahtuu hyviä asioita. Jos innostut pulmasta niin, että voisit jopa jakaa vastauksen, voit lähettää sen oheisella lomakkeella. Eniten pähkinöitä palauttaneet palkitaan Microsoftin lahjoittamilla joulumuistamisilla.

Kumulatiivinen summa SQL lauseessa

Pähkinänä on siis ratkaista miten SQL-lauseella saadaan vastattua kysymykseen: “Myynti kunakin vuonna kyseisen kuukauden loppuun”. Summa siis kasvaa joka kuukausi, kunnes taas tammikuussa lähtee alusta.

Lähtökysely on:

select
[CalendarYear],
[MonthNumberOfYear],
SUM(SalesAmount) as SumSalesAmount
from
[dbo].[FactInternetSales] as f
inner join
[dbo].[DimDate] as d
ON
f.OrderDateKey=d.DateKey
group by [CalendarYear],[MonthNumberOfYear]
order by [CalendarYear],[MonthNumberOfYear]

Tämä kysely palauttaa myynnin per kuukausi.

Tämä tulisi kuitenkin saada sellaiseen muotoon, että SalesAmountisssa olisi kumulatiivinen summa. Tavoite tulosjoukko on siis:

Näet että summa kasvaa aina vuoden ensimmäisestä kuukaudesta eteenpäin ja on isoimmillaan joulukuussa, jonka jälkeen se alkaa alusta.

Tehtävän suorittamista varten tarvitset jonkin sovelluksen, jolla saat yhteyden SQL-palvelimeen. Hyviä vaihtoehtoja ovat Azure Data Studio tai SQL Server Management Studio.

Sinulla on käytössä palvelin seuraavilla tiedoilla:

  • Palvelin: qsql.database.windows.net
  • Käyttäjätunnus (sql autentikaatio): joulu
  • Salasana: kalenteri123!
  • Tietokannan nimi: AdventureWorksLT (huomaa että käyttäjällä ei ole oikeutta muihin tietokantoihin, joten sinun tulee merkitä tietokanta jo yhdistämisvaiheessa).

Sinulla on select-oikeudet tarvittaviin tauluihin, mutta muokkaamalla tuota esimerkki-selectiä saa kyllä oikean tuloksen aikaiseksi.

Palauta tuttuun tyyliin Microsoft Forms – lomakkeella tänne.

Pähkinän #1 vastaus

Pähkinään #1 tuli 19 vastausta, mikä on minusta ihan superhienosti! Kiitos. Sivulla yli 4 minuuttia viettäneitä ihmisiä oli 350 kpl, joten aika moni oli ehkä ajatellut tehtävää joka on ihan huippua sekin!

Yleisin vastaus oli oheisen mukainen:

Tämä on varmasti kaikkein helpoin tapa tehdä YTD-summa, koska siihen on valmis funktio ja olin ollut kiltti, kun olin merkinnyt aika-dimension valmiiksi malliin. Oheinen funktio ei toimi, jos aika-dimensiota ei ole merkitty “date table”:ksi.

Minulla on ollut monella asiakkaalla vähän kaikenlaisia kumulatiivisia summia ja usein ne eivät ala kvartaalin alusta, kuukauden alusta tai vuoden alusta. Tällöin se on pakko toteuttaa itse ja muutama oli ajatellut asiaa samalla lailla, kuin miten itsekin lähestyin esimerkkivastaustani. Eli lasketaan se ikkuna, josta summa tulee saada, sen jälkeen vapautetaan aikadimensio sopivasti ja sidotaan taas siihen laskemaamme ikkunaan.

Tämä näyttää ensimmäiseksi vähän monimutkaisemmalta, mutta antaa sinulle aika paljon vapauksia kun voit ihan itse määritellä sitten mistä se summa lasketaan jos asiakkaan tarve on jokin muu kuin sisäänrakennettujen avulla onnistuu. Mutta siis konsultti haluaa päästä vähällä ja ykkösvaihtoehto on varmasti lyhin 🙂

Kiitos kaikille vastauksista.