ORDERBY, PARTITIONBY ve MATCHBY işlevlerini anlama

DAX'taki ORDERBY, PARTITIONBY ve MATCHBY işlevleri yalnızca DAX Penceresi işlevleriyle birlikte kullanılabilen özel işlevlerdir: İNDİS, KAYDIR, PENCERE, RANK, SATIRSAY.

ORDERBY, PARTITIONBY ve MATCHBY'nin anlaşılması, Window işlevlerinin başarıyla kullanılması açısından kritik öneme sahiptir. Burada sağlanan örneklerde OFFSET kullanılır, ancak diğer Window işlevleri için de benzer şekilde geçerlidir.

Senaryo

Pencere işlevlerini hiç kullanmayan bir örnekle başlayalım. Aşağıda, takvim yılı başına renk başına toplam satışları döndüren bir tablo gösterilmiştir. Bu tabloyu tanımlamanın birden çok yolu vardır, ancak DAX'ta neler olduğunu anlamakla ilgilendiğimiz için hesaplanan bir tablo kullanacağız. Tablo ifadesi şöyledir:

BasicTable = 
    SUMMARIZECOLUMNS ( 
        DimProduct[Color], 
        DimDate[CalendarYear], 
        "CurrentYearSales", ROUND ( SUM ( FactInternetSales[SalesAmount] ), 0 )
    )

Bu hesaplanan tablo ifadesinin FactInternetSales tablosundaki SalesAmount sütununun SUM değerini, DimProduct tablosundaki Color sütununu ve DimDate tablosundaki CalendarYear sütununu hesaplamak için SUMMARIZECOLUMNS kullandığını göreceksiniz. Sonuç şu şekildedir:

Color	CalendarYear	CurrentYearSales
"Siyah"	2017	393885
"Siyah"	2018	1818835
"Siyah"	2019	3981638
"Siyah"	2020	2644054
"Mavi"	2019	994448
"Mavi"	2020	1284648
"Çoklu"	2019	48622
"Çoklu"	2020	57849
"NA"	2019	207822
"NA"	2020	227295
"Kırmızı"	2017	2961198
"Kırmızı"	2018	3686935
"Kırmızı"	2019	900175
"Kırmızı"	2020	176022
"Gümüş"	2017	326399
"Gümüş"	2018	750026
"Gümüş"	2019	2165176
"Gümüş"	2020	1871788
"Beyaz"	2019	2517
"Beyaz"	2020	2589
"Sarı"	2018	163071
"Sarı"	2019	2072083
"Sarı"	2020	2621602

Şimdi, her renk için yıldan yıla satış farkını hesaplamaya yönelik iş sorusunu çözmeye çalıştığımızı düşünelim. Etkili bir şekilde, önceki yıl aynı renkteki satışları bulmanın ve bunu geçerli yıldaki satışlardan bağlam olarak çıkarmanın bir yolunu bulmalıyız. Örneğin, [Red, 2019] birleşimi için [Red, 2018] için satışları arıyoruz. Bunu yaptıktan sonra geçerli satışlardan çıkararak gerekli değeri döndürebiliriz.

OFFSET kullanma

OFFSET, göreli bir hareket yapmamıza olanak sağladığından, yukarıda açıklanan iş sorusunu yanıtlamak için gereken önceki hesaplama türleriyle tipik karşılaştırma için mükemmeldir. İlk denememiz şu olabilir:

1stAttempt = 
    VAR vRelation = SUMMARIZECOLUMNS ( 
        DimProduct[Color], 
        DimDate[CalendarYear], 
        "CurrentYearSales", ROUND ( SUM ( FactInternetSales[SalesAmount] ), 0 )
        )
    RETURN
    ADDCOLUMNS (
        vRelation,
        "PreviousColorSales",
        SELECTCOLUMNS (
            OFFSET (
                -1,
                vRelation
            ),
            [CurrentYearSales]
        )
    )

Bu ifadede çok şey oluyor. Tabloyu öncekinden PreviousColorSales adlı bir sütunla genişletmek için ADDCOLUMNS kullandık. Bu sütunun içeriği, önceki Renk (OFFSET kullanılarak alınır) için SUM(FactInternetSales[SalesAmount]) olan CurrentYearSales değerine ayarlanır.

Sonuç:

Color	CalendarYear	CurrentYearSales	PreviousColorSales
"Siyah"	2017	393885
"Siyah"	2018	1818835	393885
"Siyah"	2019	3981638	1818835
"Siyah"	2020	2644054	3981638
"Mavi"	2019	994448	2644054
"Mavi"	2020	1284648	994448
"Çoklu"	2019	48622	1284648
"Çoklu"	2020	57849	48622
"NA"	2019	207822	57849
"NA"	2020	227295	207822
"Kırmızı"	2017	2961198	227295
"Kırmızı"	2018	3686935	2961198
"Kırmızı"	2019	900175	3686935
"Kırmızı"	2020	176022	900175
"Gümüş"	2017	326399	176022
"Gümüş"	2018	750026	326399
"Gümüş"	2019	2165176	750026
"Gümüş"	2020	1871788	2165176
"Beyaz"	2019	2517	1871788
"Beyaz"	2020	2589	2517
"Sarı"	2018	163071	2589
"Sarı"	2019	2072083	163071
"Sarı"	2020	2621602	2072083

Bu, hedefimize bir adım daha yakındır, ancak yakından bakarsak tam olarak istediğimizle eşleşmiyor. Örneğin, [Silver, 2017] için PreviousColorSales [Red, 2020] olarak ayarlanmıştır.

ORDERBY ekleme

Yukarıdaki tanım aşağıdakilere eşdeğerdir:

1stAttemptWithORDERBY = 
    VAR vRelation = SUMMARIZECOLUMNS ( 
        DimProduct[Color], 
        DimDate[CalendarYear], 
        "CurrentYearSales", ROUND ( SUM ( FactInternetSales[SalesAmount] ), 0 )
        )
    RETURN
    ADDCOLUMNS (
        vRelation,
        "PreviousColorSales",
        SELECTCOLUMNS (
            OFFSET (
                -1,
                vRelation,
                ORDERBY ([Color], ASC, [CalendarYear], ASC, [CurrentYearSales], ASC)      
            ),
            [CurrentYearSales]
        )
    )

Bu durumda OFFSET çağrısı, tabloyu Color ve CalendarYear'a göre artan düzende sıralamak için ORDERBY kullanır ve bu da döndürülen önceki satırın ne olarak kabul edileceğini belirler.

Bu iki sonucun eşdeğer olmasının nedeni ORDERBY'nin partitionBY'de olmayan ilişkideki tüm sütunları otomatik olarak içermesidir. PARTITIONBY belirtilmediğinden ORDERBY Color, CalendarYear ve CurrentYearSales olarak ayarlanır. Ancak, ilişkideki Color ve CalendarYear çiftleri benzersiz olduğundan CurrentYearSales eklenmesi sonucu değiştirmez. Aslında, Yalnızca ORDERBY'de Color belirtsek bile CalendarYear otomatik olarak eklendiğinden sonuçlar aynıdır. Bunun nedeni, işlevin her satırın ORDERBY ve PARTITIONBY sütunları tarafından benzersiz olarak tanımlandığından emin olmak için ORDERBY'ye gerektiği kadar sütun eklemesidir:

1stAttemptWithORDERBY = 
    VAR vRelation = SUMMARIZECOLUMNS ( 
        DimProduct[Color], 
        DimDate[CalendarYear], 
        "CurrentYearSales", ROUND ( SUM ( FactInternetSales[SalesAmount] ), 0 )
        )
    RETURN
    ADDCOLUMNS(
        vRelation,
        "PreviousColorSales",
        SELECTCOLUMNS (
            OFFSET (
                -1,
                vRelation,
                ORDERBY ([Color])
            ),
            [CurrentYearSales]
        )
    )

PARTITIONBY Ekleme

Şimdi, hemen hemen sonucu almak için, aşağıdaki hesaplanmış tablo ifadesinde gösterildiği gibi PARTITIONBY'yi kullanabiliriz:

UsingPARTITIONBY = 
    VAR vRelation = SUMMARIZECOLUMNS ( 
        DimProduct[Color], 
        DimDate[CalendarYear], 
        "CurrentYearSales", ROUND ( SUM ( FactInternetSales[SalesAmount] ), 0 )
        )
    RETURN
    ADDCOLUMNS (
        vRelation,
        "PreviousColorSales",
        SELECTCOLUMNS (
            OFFSET (
                -1,
                vRelation,
                ORDERBY ([CalendarYear]), 
                PARTITIONBY ([Color])
            ),
            [CurrentYearSales]
        )
    )

ORDERBY, PARTITIONBY'de belirtilmeyen ilişkideki tüm sütunları otomatik olarak içerdiğinden burada ORDERBY belirtmenin isteğe bağlı olduğuna dikkat edin. Bu nedenle, ORDERBY otomatik olarak CalendarYear ve CurrentYearSales olarak ayarlandığından aşağıdaki ifade aynı sonuçları döndürür:

UsingPARTITIONBYWithoutORDERBY = 
    VAR vRelation = SUMMARIZECOLUMNS ( 
        DimProduct[Color], 
        DimDate[CalendarYear], 
        "CurrentYearSales", ROUND ( SUM ( FactInternetSales[SalesAmount] ), 0 )
        )
    RETURN
    ADDCOLUMNS (
        vRelation,
        "PreviousColorSales",
        SELECTCOLUMNS (
            OFFSET (
                -1,
                vRelation,
                PARTITIONBY ([Color])
            ),
            [CurrentYearSales]
        )
    )

Dekont

ORDERBY otomatik olarak CalendarYear ve CurrentYearSales olarak ayarlanmış olsa da, bunların hangi sırayla ekleneceğinin garantisi verilmez. CurrentYearSales, CalendarYear'ın önüne eklenirse, sonuçta elde edilen sıra beklenen sıralamayla satır içi olmaz. Karışıklığı ve beklenmeyen sonuçları önlemek için ORDERBY ve PARTITIONBY'yi belirtirken açık olun.

her iki ifade de peşinde olduğumuz sonucu döndürür:

Color	CalendarYear	CurrentYearSales	PreviousYearSalesForSameColor
"Siyah"	2017	393885
"Siyah"	2018	1818835	393885
"Siyah"	2019	3981638	1818835
"Siyah"	2020	2644054	3981638
"Mavi"	2019	994448
"Mavi"	2020	1284648	994448
"Çoklu"	2019	48622
"Çoklu"	2020	57849	48622
"NA"	2019	207822
"NA"	2020	227295	207822
"Kırmızı"	2017	2961198
"Kırmızı"	2018	3686935	2961198
"Kırmızı"	2019	900175	3686935
"Kırmızı"	2020	176022	900175
"Gümüş"	2017	326399
"Gümüş"	2018	750026	326399
"Gümüş"	2019	2165176	750026
"Gümüş"	2020	1871788	2165176
"Beyaz"	2019	2517
"Beyaz"	2020	2589	2517
"Sarı"	2018	163071
"Sarı"	2019	2072083	163071
"Sarı"	2020	2621602	2072083

Bu tabloda gördüğünüz gibi PreviousYearSalesForSameColor sütunu aynı renkte önceki yılın satışlarını gösterir. [Red, 2020] için [Red, 2019] vb. için satışları döndürür. [Red, 2017] gibi bir önceki yıl yoksa hiçbir değer döndürülmedi.

PARTITIONBY'yi, tabloyu OFFSET hesaplamasının yürütüldüğü bölümlere bölmenin bir yolu olarak düşünebilirsiniz. Yukarıdaki örnekte tablo, her renk için bir tane olmak üzere, renkler kadar çok parçaya ayrılmıştır. Ardından, her bölümde OFSET hesaplanır ve CalendarYear'a göre sıralanır.

Görsel olarak, olan şey şudur:

İlk olarak PARTITIONBY çağrısı, tablonun her Color için bir tane olmak üzere parçalara bölünmesine neden olur. Bu, tablo görüntüsündeki açık mavi kutular ile temsil edilir. Ardından ORDERBY, her bölümün CalendarYear (turuncu oklarla gösterilir) göre sıralandığından emin olur. Son olarak, her satır için sıralanmış her parçada KAYDIR, üstündeki satırı bulur ve PreviousYearSalesForSameColor sütununda bu değeri döndürür. Her bölümdeki her ilk satır için aynı bölümde önceki satır olmadığından PreviousYearSalesForSameColor sütunu için bu satırdaki sonuç boş olur.

Nihai sonucu elde etmek için, OFFSET çağrısı tarafından döndürülen aynı renk için CurrentYearSales değerini önceki yıl satışlarından çıkarmamız yeterlidir. Önceki yılın satışlarını aynı renkle göstermekle ilgilenmediğimizden, yalnızca geçerli yılın satışlarında ve yıldan yıla olan farkı göstermek istiyoruz. Hesaplanmış tablo ifadesinin son hali aşağıdadır:

FinalResult = 
    VAR vRelation = SUMMARIZECOLUMNS ( 
        DimProduct[Color], 
        DimDate[CalendarYear], 
        "CurrentYearSales", ROUND ( SUM ( FactInternetSales[SalesAmount] ), 0 )
        )
    RETURN
    ADDCOLUMNS (
        vRelation,
        "YoYSalesForSameColor",
        [CurrentYearSales] -
        SELECTCOLUMNS (
            OFFSET (
                -1,
                vRelation,
                ORDERBY ([CalendarYear]),
                PARTITIONBY ([Color])
            ),
            [CurrentYearSales]
        )
    )

Bu ifadenin sonucu şu şekildedir:

Color	CalendarYear	CurrentYearSales	YoYSalesForSameColor
"Siyah"	2017	393885	393885
"Siyah"	2018	1818835	1424950
"Siyah"	2019	3981638	2162803
"Siyah"	2020	2644054	-1337584
"Mavi"	2019	994448	994448
"Mavi"	2020	1284648	290200
"Çoklu"	2019	48622	48622
"Çoklu"	2020	57849	9227
"NA"	2019	207822	207822
"NA"	2020	227295	19473
"Kırmızı"	2017	2961198	2961198
"Kırmızı"	2018	3686935	725737
"Kırmızı"	2019	900175	-2786760
"Kırmızı"	2020	176022	-724153
"Gümüş"	2017	326399	326399
"Gümüş"	2018	750026	423627
"Gümüş"	2019	2165176	1415150
"Gümüş"	2020	1871788	-293388
"Beyaz"	2019	2517	2517
"Beyaz"	2020	2589	72
"Sarı"	2018	163071	163071
"Sarı"	2019	2072083	1909012
"Sarı"	2020	2621602	549519

MATCHBY kullanma

MATCHBY belirtmediğimiz fark etmiş olabilirsiniz. Bu durumda gerekli değildir. ORDERBY ve PARTITIONBY'deki sütunlar (yukarıdaki örneklerde belirtilenler için) her satırı benzersiz olarak tanımlamak için yeterlidir. MATCHBY belirtmediğimiz için ORDERBY ve PARTITIONBY'de belirtilen sütunlar her satırı benzersiz olarak tanımlamak için kullanılır, böylece OFFSET işlevinin anlamlı bir sonuç vermesi için karşılaştırılabilir. ORDERBY ve PARTITIONBY içindeki sütunlar her satırı benzersiz olarak tanımlayamazsa, bu fazladan sütunlar her satırın benzersiz olarak tanımlanmasına izin verirse, ORDERBY yan tümcesine ek sütunlar eklenebilir. Bu mümkün değilse bir hata döndürülür. Bu son durumda, MATCHBY'nin belirtilmesi hatayı çözmeye yardımcı olabilir.

MATCHBY belirtilirse, MATCHBY ve PARTITIONBY içindeki sütunlar her satırı benzersiz olarak tanımlamak için kullanılır. Bu mümkün değilse bir hata döndürülür. MATCHBY gerekli olmasa bile herhangi bir karışıklığı önlemek için MATCHBY'yi açıkça belirtmeyi göz önünde bulundurun.

Yukarıdaki örneklerden devam edersek, son ifade şu şekildedir:

FinalResult = 
    VAR vRelation = SUMMARIZECOLUMNS ( 
        DimProduct[Color], 
        DimDate[CalendarYear], 
        "CurrentYearSales", ROUND ( SUM ( FactInternetSales[SalesAmount] ), 0 )
        )
    RETURN
    ADDCOLUMNS (
        vRelation,
        "YoYSalesForSameColor",
        [CurrentYearSales] -
        SELECTCOLUMNS (
            OFFSET (
                -1,
                vRelation,
                ORDERBY ([CalendarYear]),
                PARTITIONBY ([Color])
            ),
            [CurrentYearSales]
        )
    )

Satırların benzersiz olarak nasıl tanımlanması gerektiği konusunda açık olmak istiyorsak, aşağıdaki eşdeğer ifadede gösterildiği gibi MATCHBY'yi belirtebiliriz:

FinalResultWithExplicitMATCHBYOnColorAndCalendarYear = 
    VAR vRelation = SUMMARIZECOLUMNS ( 
        DimProduct[Color], 
        DimDate[CalendarYear], 
        "CurrentYearSales", ROUND ( SUM ( FactInternetSales[SalesAmount] ), 0 )
        )
    RETURN
    ADDCOLUMNS (
        vRelation,
        "YoYSalesForSameColor",
        [CurrentYearSales] -
        SELECTCOLUMNS (
            OFFSET (
                -1,
                vRelation,
                ORDERBY ([CalendarYear]),
                PARTITIONBY ([Color]),
                MATCHBY ([Color], [CalendarYear])
            ),
            [CurrentYearSales]
        )
    )

MATCHBY belirtildiğinden, hem MATCHBY'de hem de PARTITIONBY'de belirtilen sütunlar satırları benzersiz olarak tanımlamak için kullanılır. Color hem MATCHBY hem de PARTITIONBY içinde belirtildiğinden, aşağıdaki ifade önceki ifadeye eşdeğerdir:

FinalResultWithExplicitMATCHBYOnCalendarYear = 
    VAR vRelation = SUMMARIZECOLUMNS ( 
        DimProduct[Color], 
        DimDate[CalendarYear], 
        "CurrentYearSales", ROUND ( SUM ( FactInternetSales[SalesAmount] ), 0 )
        )
    RETURN
    ADDCOLUMNS (
        vRelation,
        "YoYSalesForSameColor",
        [CurrentYearSales] -
        SELECTCOLUMNS (
            OFFSET (
                -1,
                vRelation,
                ORDERBY ([CalendarYear]),
                PARTITIONBY ([Color]),
                MATCHBY ([CalendarYear])
            ),
            [CurrentYearSales]
        )
    )

Şu ana kadar incelediğimiz örneklerde MATCHBY belirtmek gerekli olmadığından MATCHBY gerektiren biraz farklı bir örneğe göz atalım. Bu durumda, sipariş satırlarının bir listesi vardır. Her satır, bir siparişin sipariş satırını temsil eder. Bir siparişte birden çok sipariş satırı olabilir ve 1. sipariş satırı birçok siparişte görünür. Ayrıca, her sipariş satırı için bir ProductKey ve bir SalesAmount değerimiz vardır. Tablodaki ilgili sütunların bir örneği şöyle görünür:

SalesOrderNumber	SalesOrderLineNumber	ProductKey	SalesAmount
SO51900	1	528	4,99
SO51948	1	528	5,99
SO52043	1	528	4,99
SO52045	1	528	4,99
SO52094	1	528	4,99
SO52175	1	528	4,99
SO52190	1	528	4,99
SO52232	1	528	4,99
SO52234	1	528	4,99
SO52234	2	529	3.99

Satırları benzersiz olarak tanımlamak için SalesOrderNumber ve SalesOrderLineNumber gereklidir.

Her sipariş için SalesAmount tarafından azalan düzende sipariş edilen aynı ürünün (ProductKey ile temsil edilen) önceki satış tutarını iade etmek istiyoruz. Aşağıdaki ifade çalışmayacaktır çünkü vRelation'da OFFSET'e geçirildiği için birden çok satır olabilir:

ThisExpressionFailsBecauseMATCHBYIsMissing = 
    ADDCOLUMNS (
        FactInternetSales,
        "Previous Sales Amount",
            SELECTCOLUMNS (
                OFFSET (
                    -1,
                    FactInternetSales,
                    ORDERBY ( FactInternetSales[SalesAmount], DESC ),
                    PARTITIONBY ( FactInternetSales[ProductKey] )
                ),
                FactInternetSales[SalesAmount]
            )
    )

Bu ifade bir hata döndürür: "KAYDIR'ın İlişki parametresi yinelenen satırlara sahip olabilir ve buna izin verilmez."

Bu ifadenin çalışması için MATCHBY belirtilmelidir ve benzersiz olarak bir satır tanımlayan tüm sütunları içermelidir. Burada MATCHBY gereklidir çünkü FactInternetSales ilişkisi herhangi bir açık anahtar veya benzersiz sütun içermez. Ancak, SalesOrderNumber ve SalesOrderLineNumber sütunları birlikte bileşik bir anahtar oluşturur; burada bunların birlikte varlığı ilişkide benzersizdir ve bu nedenle her satırı benzersiz olarak tanımlayabilir. Her iki sütun da yinelenen değerler içerdiği için Yalnızca SalesOrderNumber veya SalesOrderLineNumber belirtilmesi yeterli değildir. Aşağıdaki ifade sorunu çözer:

ThisExpressionWorksBecauseOfMATCHBY = 
    ADDCOLUMNS (
        FactInternetSales,
        "Previous Sales Amount",
            SELECTCOLUMNS (
                OFFSET (
                    -1,
                    FactInternetSales,
                    ORDERBY ( FactInternetSales[SalesAmount], DESC ),
                    PARTITIONBY ( FactInternetSales[ProductKey] ),
                    MATCHBY ( FactInternetSales[SalesOrderNumber], 
                                FactInternetSales[SalesOrderLineNumber] )
                ),
                FactInternetSales[SalesAmount]
            )
    )

Ve bu ifade gerçekten de peşinde olduğumuz sonuçları döndürür:

SalesOrderNumber	SalesOrderLineNumber	ProductKey	SalesAmount	Önceki Satış Tutarı
SO51900	1	528	5,99
SO51948	1	528	4,99	5,99
SO52043	1	528	4,99	4,99
SO52045	1	528	4,99	4,99
SO52094	1	528	4,99	4,99
SO52175	1	528	4,99	4,99
SO52190	1	528	4,99	4,99
SO52232	1	528	4,99	4,99
SO52234	1	528	4,99	4,99
SO52234	2	529	3.99

İlgili içerik

ORDERBY
PARTITIONBY
EŞLEŞTİ
DİZİN
UZAKLIK
PENCERE
RÜTBE
ROWNUMBER