Ogledali si bomo ta trenutek najkompleksnejšo, a tudi najzmogljivejšo Window funkcijo, ki se imenuje WINDOW.

V drugem delu te serije člankov smo si ogledali funkcijo INDEX, s pomočjo katere v obliki tabele dobimo podmnožico podatkov, ki glede na vrednost v nekem stolpcu spadajo na n-to mesto v tabeli, ki smo jo določili kot osnovo. Tokrat si bomo ogledali ta trenutek najkompleksnejšo, a tudi najzmogljivejšo Window funkcijo, ki se imenuje WINDOW. V primerih bomo zopet uporabljali AdventureWorksDW2019.

Funkcija WINDOW

Pri analizah podatkov pogosto želimo zajeti podatke iz nekega »okna«. Najbolj tipičen primer bi bilo časovno okno, na primer vsota prodaj od določenega datuma 30 dni naprej in 30 dni nazaj. Ali pa kumulativne prodaje znotraj enega leta. Kot izhaja že iz imena funkcije, lahko to »okno« zgradimo s pomočjo funkcije WINDOW.

Sama po sebi nam WINDOW funkcija vrne tabelo s podmnožico podatkov iz prvotne tabele. Sama po sebi nam ta tabela verjetno ne bo zanimiva, pač pa jo uporabljamo za spreminjanje konteksta filtra v kompleksnejših izračunih. Poglejmo prvi primer, ki smo ga prej omenili, le da bomo zaradi poenostavitve primera gledali vsoto prodaj od določenega datuma za 1 dan naprej in 1 dan nazaj. Za osnovo bomo potrebovali mero _TotalSales, ki nam bo seštela vse prodaje:

_TotalSales =

SUMX (

    FactInternetSales,

    FactInternetSales[OrderQuantity] * FactInternetSales[UnitPrice]

)

 

S pomočjo funkcije WINDOW lahko potem definiramo časovno okno prodaj, ki jih želimo zajeti skupaj:

_TotalSales3DayWindow =

CALCULATE (

    [_TotalSales],

    WINDOW (

        -1,

        REL,

        1,

        REL,

        SUMMARIZE (

            ALLSELECTED ( FactInternetSales ),

            DimDate[FullDateAlternateKey]

        ),

        ORDERBY ( DimDate[FullDateAlternateKey], ASC )

    )

)

 

S pomočjo funkcije CALCULATE spremenimo kontekst filtra, v katerem se vrednoti _TotalSales mera. Kontekst je definiran preko WINDOW funkcije na sledeč način:

• Prvi argument, -1, definira kje se naše podatkovno okno začne, v našem primeru torej 1 dan nazaj.
• Drugi argument, REL, definira, naj se začetni položaj gleda relativno, glede na trenutni položaj v vizualizaciji.
• Tretji argument, 1, definira koliko pozicij naprej se naše okno konča, v našem primeru 1 dan v prihodnosti.
• Četrti argument, REL, definira, naj se končni položaj gleda relativno, zopet glede na trenutni položaj v vizualizaciji.
• Peti argument je tabela, iz katere sestavljamo naše okno. V našem primeru smo sestavili skupaj tabeli FactInternetSales ter stolpec DimDate[FullDateAlternateKey], ki vsebuje datume prodaj.
• Šesti argument definira vrstni red sortiranja te sestavljene tabele. Sortirali smo po DimDate[FullDateAlternateKey] naraščajoče (ASC). Ta korak je v našem primeru pomemben, saj nam WINDOW funkcija v nasprotnem primeru lahko vrne napačno okno, če si datumi ne sledijo v pravem vrstnem redu.

Na sliki spodaj je prikaz delovanja te mere. Na enostavno vizualizacijo tabele sem dodal DimDate[FullDateAlternateKey], ter meri _TotalSales  in _TotalSales3DayWindow. Vidimo, da je rezultat prodaj pri enem datumu enak vsoti prodaj tistega dneva, prejšnjega dneva in prihodnjega dneva.

Poglejmo še en zanimiv primer, in sicer kumulativno prodajo znotraj enega leta:

_YearlyRunningTotal =

CALCULATE (

    [_TotalSales],

    WINDOW (

        1,

        ABS,

        0,

        REL,

        SUMMARIZE (

            ALLSELECTED ( FactInternetSales ),

            DimDate[FullDateAlternateKey],

            DimDate[CalendarYear]

        ),

        ORDERBY ( DimDate[FullDateAlternateKey], ASC ),

        PARTITIONBY ( DimDate[CalendarYear] )

    )

)

 

V tem primeru zopet uporabljamo funkcijo WINDOW kot modifikator filtra znotraj CALCULATE. V primerjavi s prejšnjo funkcijo lahko opazimo sledeče razlike:

• Prvi in drugi argument sta sedaj 1 in ABS. To pomeni, naj se okno začne na prvem mestu znotraj naših podatkov, gledano absolutno, torej ne glede na trenuten položaj znotraj vizualizacije. Ker želimo gledati kumulativno prodajo znotraj enega leta, bomo seveda začeli s prvim datumom. Problem pa je, če naši datumi obsegajo več let. To pa rešujemo z argumentom na sedmem mestu.
• Tretji in četrti argument sta 0 in REL. To pomeni, naj se naše okno zaključi na tistem mestu, kjer se trenutno nahajamo tudi na vizualizaciji. Ker želimo računati kumulativno prodajo znotraj enega leta, bomo zajemali prodaje do datuma, ki je v neki vrstici prikazan na vizualizaciji.
• S petim argumentom smo zopet sestavili tabelo, le da smo tokrat zaradi potreb sedmega argumenta dodali še DimDate[CalendarYear].
• S šestim argumentom smo podatke zopet spravili v pravilen vrstni red za potrebe grajenja našega okna
• S sedmim argumentom smo zgradili particije, po katerih funkcija gradi naša okna. Ker želimo kumulativno prodajo računati znotraj vsakega leta posebej, smo s funkcijo PARTITIONBY definirali, naj se vsako koledarsko leto (DimDate[CalendarYear]) obravnava kot ločena celota.

Kot vidimo na sliki spodaj, se z novim letom kumulativna vsota prodaj začne računati z ničle (oziroma je enaka vsoti prodaj na prvi dan leta).

Za zadnji primer pokažimo še računanje premikajočega se povprečja (Moving Average), v našem primeru za prodaje zadnjih 180 dni:

_180DayMovingAverage =

AVERAGEX (

    WINDOW (

        -179,

        REL,

        0,

        REL,

        SUMMARIZE ( ALLSELECTED ( FactInternetSales ), DimDate[FullDateAlternateKey] ),

        ORDERBY ( DimDate[FullDateAlternateKey], ASC )

    ),

    [_TotalSales]

)

 

Tokrat smo uporabili funkcijo AVERAGEX, ki bo iterirala po tabeli, dobljeni s funkcijo WINDOW, in računala povprečje _TotalSales. Kot vidimo, smo v tem primeru začetek in konec okna definirali relativno, glede na naš trenuten položaj v podatkih. Za nek datum gleda podatke za do 179 dni v preteklost in 0 v prihodnost, da dobimo okno 180 dni.

Rezultat takšne mere je zanimivo prikazati hkrati z rezultati _TotalSales mere, da dobimo občutek, ali prihaja v naših prodajnih obdobjih do občutnih nihanj.

 

 

 

Klemen Vončina

Microsoft Office Specialist Master
predavatelj, MCT
klemen.voncina@kompas-xnet.si