بهترین روش‌ها با استفاده از SUMMARIZE و ADDCOLUMNS

نوشته شده توسط: تیم فنی نیک آموز

تاریخ انتشار: ۰۷ اسفند ۱۴۰۰

آخرین بروزرسانی: ۱۱ شهریور ۱۴۰۲

زمان مطالعه: 20 دقیقه

مقدمه

همه افرادی که از DAX استفاده می‌کنند احتمالاً به زبان کوئری نویسی SQL نیز عادت دارند. به دلیل شباهت‌های بین مدل‌سازی داده‌های Tabular و مدل‌سازی داده‌های رابطه‌ای، این انتظار وجود دارد که شما بتوانید همان عملیات مجاز در SQL را داخل DAX هم انجام دهید. با این حال، DAX در حال حاضر، همه عملیاتی را که می‌توانید در SQL انجام دهید، مجاز نمی‌داند. تعدادی از محدودیت‌ها ناشی از عدم وجود سینتکس معادل است. برخی دیگر به رفتار متفاوت موتور xVelocity در حافظه بستگی دارد، زمانی که ستون‌های extension در یک کوئری درگیر می‌شوند.

ستون‌های Extension

ستون‌هایExtension ، ستون‌هایی هستند که به جداول موجود اضافه می‌کنید. با استفاده از ADDCOLUMNS و SUMMARIZE می‌توانید ستون‌هایExtension را به دست آورید. به عنوان مثال، کوئری زیر یک ستون Year Production را به ردیف‌های برگشتی از جدول Product اضافه می‌کند.

EVALUATE
ADDCOLUMNS (
Product,
"Year Production", YEAR (Product [Product Start Date] )
)

همچنین می‌توانید با استفاده از SUMMARIZE یک ستونExtension ایجاد کنید. به عنوان مثال، می‌توانید با استفاده از کوئری زیر تعداد محصولات را برای هر دسته محصول بشمارید (توجه داشته باشید که این کوئری بهترین روش نیست و دلیل آن را در ادامه این مقاله خواهید دید).

EVALUATE
SUMMARIZE (
Product,
Product [Product Category Name],
"Products", COUNTROWS (Product)
)

در عمل، یک ستون Extension، یک ستون محاسبه شده بر اساس یک یا چند ستون است که در کوئری ایجاد می‌شود و در نتیجه نمایش داده می شود در حالی که این ستون جز ستون‌های هیچ کدام از جداول پایگاه داده نبوده است.

طرح کوئری

در یک دستور SELECT در SQL، می‌توانید ستونی را که در نتیجه نمایش داده می‌شود انتخاب کنید، در حالی که در DAX فقط می‌توانید با ایجاد ستون‌های Extension، ستون‌ها را به خروجی کوئری اضافه کنید. تنها راه حل موجود استفاده از SUMMARIZE برای گروه‌بندی جدول بر اساس ستون‌هایی است که می‌خواهید در خروجی داشته باشید. تا زمانی که نیازی به دیدن ردیف‌های تکراری در نتیجه نداشته باشید، این راه حل عوارض جانبی خاصی ندارد. به عنوان مثال، اگر می‌خواهید فقط لیست نام محصولات و تاریخ شروع تولید مربوط به آن‌ها را دریافت کنید، می‌توانید کوئری زیر را بنویسید.

EVALUATE
SUMMARIZE (
Product,
Product [Product Name],
Product [Product Start Date]
)

در هر صورت می‌توانید با استفاده از هر دو تکنیک ADDCOLUMNS و SUMMARIZE یک ستون Extension ایجاد کنید، به دلیل کارایی، همیشه بهتر است از ADDCOLUMNS استفاده کنید. به عنوان مثال، می‌توانید با استفاده از یکی از دو تکنیک ADDCOLUMNS و SUMMARIZE، سال شروع تولید را اضافه کنید. ابتدا از SUMMARIZE استفاده می‌کنیم.

EVALUATE
SUMMARIZE (
Product,
Product [Product Name],
Product [Product Start Date],
"Year Production", YEAR (Product [Product Start Date] )
)

در گام دوم، از ADDCOLUMNS استفاده می‌کنیم و ستون تولید سال را به نتیجه SUMMARIZE اضافه می‌کنیم.

EVALUATE
ADDCOLUMNS (
SUMMARIZE (
Product,
Product [Product Name],
Product [Product Start Date]
),
"Year Production", YEAR (Product [Product Start Date])
)

هر دو کوئری نتیجه یکسانی را ایجاد می‌کنند.

با این حال، همیشه بهتر است از نسخه ADDCOLUMNS استفاده کنید. هرگز نباید با استفاده از SUMMARIZE، ستون‌های Extension را اضافه کنید، مگر اینکه به دلیل حداقل یکی از شرایط زیر مجبور به استفاده از SUMMARIZE باشید:

اگر می‌خواهید از ROLLUP بر روی یک یا چند ستون گروه‌بندی برای به دست آوردن جمع‌های فرعی استفاده کنید.
اگر از عبارات جدولی non-trivial در ستون‌های Extension استفاده می‌کنید.

بهترین روش این است که، در صورت امکان، به جای نوشتن کد زیر:

SUMMARIZE(<table>, <group_by_column>, <column_name>, <expression>)

از این کد استفاده کنید:

ADDCOLUMNS (
SUMMARIZE(<table>, <group by column>),
<column_name>, CALCULATE(<expression>)
)

CALCULATE را که می‌توانید در الگوی کدهای بالا مشاهده کنید، همیشه مورد نیاز نیست، اما هر زمان که <expression> حاوی یک تابع تجمعی باشد، به آن نیاز دارید. دلیل آن این است که ADDCOLUMNS در یک محتوای ردیفی عمل می‌کند و به طور خودکار در یک محتوای فیلتر منتشر نمی‌شود، در حالی که همان <expression> در یک SUMMARIZE در یک محتوای فیلتر، مطابق با مقادیر ستون‌های گروه‌بندی شده، اجرا می‌شود. مثال‌های قبلی از یک عبارت اسکالر روی ستونی استفاده می‌کردند که در خروجی SUMMARIZE گنجانده شده بود، بنابراین ارجاع به مقدار ستون در محتوای ردیفی معتبر بود. اکنون، کوئری زیر را که قبلاً در ابتدای این مقاله مشاهده کرده‌اید، در نظر بگیرید.

EVALUATE
SUMMARIZE (
Product,
Product [Product Category Name],
"Products", COUNTROWS (Product)
)

اگر در کوئری بالا، ستون‌های Products را از SUMMARIZE بردارید و در یک تابع ADDCOLUMNS بازنویسی کنید، کوئری زیر به دست می‌آید که نتیجه اشتباهی ایجاد می‌کند. این اتفاق به این دلیل می‌افتد که به جای برگرداندن تعداد محصولات برای هر دسته، تعداد ردیف‌های موجود در کل جدول Products را برای هر ردیف از نتیجه برمی‌گرداند.

EVALUATE
ADDCOLUMNS (
SUMMARIZE (
Product,
Product [Product Category Name]
),
"Products", COUNTROWS (Product)
)

برای به دست آوردن نتیجه‌ای که می‌خواهید، باید عبارت ستون Extension محصولات یعنی COUNTROWS (Product) را داخل تابع CALCULATE قرار دهید. به این ترتیب محتوای ردیف، برای نام دسته محصول به یک محتوای فیلتر تبدیل می‌شود و تابع COUNTROWS فقط محصولات متعلق به دسته ردیف فعلی را در نظر می‌گیرد. کوئری به صورت زیر خواهد بود:

EVALUATE
ADDCOLUMNS (
SUMMARIZE (
Product,
Product [Product Category Name]
),
"Products", CALCULATE (COUNTROWS (Product))
)

بنابراین، به عنوان یک قاعده کلی، هر زمان که یک ستون Extension را مانند مثال فوق، از SUMMARIZE به یک عبارت ADDCOLUMN منتقل می‌کنید، هر عبارت ستون Extension را در یک تابع CALCULATE قرار دهید.

گروه‌بندی بر اساس ستون‌های Extension

یک محدودیت غیرمستقیم در DAX این است که می‌توانید بر اساس ستون‌های extension گروه‌بندی کنید، اما نمی‌توانید گروه‌بندی محاسباتی معنا‌داری را بر اساس ستون‌های extension داشته باشید. به عنوان مثال، یک ستون Extension را در نظر بگیرید که به جدول Internet Sales اضافه شده است که محدوده قیمت‌های واحد را با یک عبارت لگاریتمی برمی‌گرداند. در عمل، هر فروش با قیمت واحد بین ۰ و ۱ به عنوان ۱، بین ۱ تا ۱۰ به عنوان ۱۰، بین ۱۰ تا ۱۰۰ به عنوان ۱۰۰ گروه‌بندی می‌شود و به همین ترتیب.

EVALUATE
ADDCOLUMNS (
'Internet Sales',
"Price Level", POWER (10, 1 + INT (LOG10(‘Internet Sales'[Unit Price])))
)

می‌توانید با استفاده از ستون extension برایPrice Level در عبارت SUMMARIZE، داده‌ها را گروه‌بندی کنید تا بتوانید ببینید گروه‌ها برای فروش‌های موجود چگونه هستند.

EVALUATE
SUMMARIZE (
ADDCOLUMNS (
'Internet Sales',
"Price Level", POWER (10, 1 + INT (LOG10(‘Internet Sales'[Unit Price])))
),
[Price Level]
)
ORDER BY [Price Level]

با این حال، ستون‌های extension که می‌توانید در یک عبارت SUMMARIZE استفاده کنید، بخشی از محتوای فیلتر نیستند. بنابراین، اگر بخواهید یک ستون extension را به عبارت SUMMARIZE اضافه کنید که بر اساس Price Level گروه‌بندی شود، عبارت را نمی‌توان بر این اساس گروه‌بندی کرد و یک نتیجه غیرمنتظره تولید می‌کند.

EVALUATE
SUMMARIZE (
ADDCOLUMNS (
'Internet Sales',
"Price Level", POWER (10, 1+INT (LOG10(‘Internet Sales'[Unit Price])))
),
[Price Level],
"Total Sales", SUM (‘Internet Sales'[Sales Amount])
)
ORDER BY [Price Level]

تلاش برای استفاده از CALCULATE و ADDCOLUMNS مانند کوئری زیر، همان نتیجه کوئری قبلی را ایجاد می‌کند، اما دقیقا خروجی‌ای که انتظار داریم را می توانیم داشته باشیم.

EVALUATE
ADDCOLUMNS (
SUMMARIZE (
ADDCOLUMNS (
'Internet Sales',
"Price Level", POWER (10, 1 + INT (LOG10(‘Internet Sales'[Unit Price])))
),
[Price Level]
),
"Total Sales", CALCULATE (SUM (‘Internet Sales'[Sales Amount]))
)
ORDER BY [Price Level]

از آنجایی که رابطه‌ای بین دو جدول، Internet Sales و جدول مجازی برای Price Level وجود ندارد، باید یک شرط فیلتر را در عبارت CALCULATE وارد کنیم. این کار به این علت انجام می‌شود تا فقط ردیف‌هایی در Internet Salesدر نظر گرفته شود که قیمت آن‌ها در سطح تعریف شده توسط Price Level در نظر گرفته شده است. یک راه ساده برای انجام این کار تکرار عبارتی است که Price Level را در عبارت فیلتر در نظر می‌گیرد، مانند کوئری زیر:

EVALUATE
ADDCOLUMNS (
SUMMARIZE (
ADDCOLUMNS (
'Internet Sales',
"Price Level", POWER (10, 1 + INT (LOG10(‘Internet Sales'[Unit Price])))
),
[Price Level]
),
"Total Sales",
CALCULATE (
SUM (‘Internet Sales'[Sales Amount]),
FILTER (
'Internet Sales',
[Price Level]
= POWER (10, 1 + INT (LOG10(‘Internet Sales'[Unit Price])))
)
)
)
ORDER BY [Price Level]

برای جلوگیری از تکراری شدن یک عبارت، می‌توانید از سینتکس DEFINE MEASURE استفاده کنید.

DEFINE
MEASURE 'Internet Sales'[Price Band]
= POWER (10, 1 + INT (LOG10(VALUES (‘Internet Sales'[Unit Price]))))
EVALUATE
ADDCOLUMNS (
SUMMARIZE (
ADDCOLUMNS (
'Internet Sales',
"Price Level", [Price Band]
),
[Price Level]
),
"Total Sales",
CALCULATE (
SUM (‘Internet Sales'[Sales Amount] ),
FILTER (‘Internet Sales', [Price Level] = [Price Band])
)
)
ORDER BY [Price Level]

هر دو کوئری قبلی نتیجه مورد انتظار را برمی‌گردانند و مجموع Sales Amount را برای هر سطح قیمت نشان می‌دهند.

بررسی سینتکس Measure

ممکن است این سوال برای شما ایجاد شده باشد که چرا ما از برای هر دو مورد، معیار محلی و نام ستون‌های extension، از یک نامPrice Level استفاده نکرده‌ایم. دلیل آن این است که حتی اگر امکان‌پذیر باشد، خواندن کوئری را سخت‌تر می‌کند. در واقع برای نام گذاری معیار محلی می‌توانید کوئری قبلی را با استفاده از Price Level به جای Price Band به صورت زیر بازنویسی کنید.

DEFINE
MEASURE 'Internet Sales'[Price Level]
= POWER (10, 1 + INT (LOG10(VALUES (‘Internet Sales'[Unit Price]))))
EVALUATE
ADDCOLUMNS (
SUMMARIZE (
ADDCOLUMNS (
'Internet Sales',
"Price Level", [Price Level]
),
[Price Level]
),
"Total Sales",
CALCULATE (
SUM (‘Internet Sales'[Sales Amount]),
FILTER (‘Internet Sales', [Price Level] = [Price Level])
)
)
ORDER BY [Price Level]

با این حال، کوئری بالا که به این شکل نوشته شده است، به درستی کار نمی‌کند، زیرا شرط هایلایت شده در دستور فیلتر، همیشه True را برمی‌گردد و در نتیجه، خروجی نادرست را ایجاد می‌کند.در این مورد، عبارت EARLIER نیز به شما کمکی نمی‌کند. مشکل این است که، به عنوان بهترین روش، معمولاً به معیاری اشاره می‌کنیم، بدون اینکه نام جدولی که در آن تعریف شده است را مشخص کنیم. حذف نام جدول باعث می‌شود معیار به راحتی در یک کوئری قابل تشخیص باشد، زیرا ما همیشه از نام جدول برای ارجاع به یک ستون استفاده می‌کنیم. اما در مثال قبلی، شما از یک نام برای معیار محلی با عبارت DEFINE MEASURE و یک ستون extension با استفاده از ADDCOLUMNS استفاده می‌کنید. هنگامی که داده‌ها با استفاده از SUMMARIZE گروه‌بندی می‌شوند، از ستون extension استفاده می‌شود، اما در عبارت FILTER، سینتکس Price Level به ستون extension اشاره می‌کند و نه به معیار.

بنابراین، در این مثال، برای تمایز بین ستون extension و معیار محلی، باید از نام جدول (Internet Sales) برای ارجاع به معیار محلی استفاده کنید. یک ستون extension به هیچ جدولی تعلق ندارد. فقط می‌توان از طریق نام ستون بدون نام جدول، با استفاده از سینتکسی که بهترین روش برای ارجاع معیارها در نظر گرفته می‌شود، ارجاع داد. به همین دلیل، ما باید هنگام اشاره به معیار، نام جدول را نیز اشاره کنیم. کوئری زیر نتیجه صحیح را برمی‌گرداند.

DEFINE
MEASURE 'Internet Sales'[Price Level]
= POWER (10, 1 + INT (LOG10(VALUES (‘Internet Sales'[Unit Price]))))
EVALUATE
ADDCOLUMNS (
SUMMARIZE (
ADDCOLUMNS (
'Internet Sales',
"Price Level", [Price Level]
),
[Price Level]
),
"Total Sales",
CALCULATE (
SUM (‘Internet Sales'[Sales Amount]),
FILTER (‘Internet Sales', [Price Level] = 'Internet Sales'[Price Level])
)
)
ORDER BY [Price Level]

محتوای فیلتر در SUMMARIZE و ADDCOLUMNS

در قسمت ارائه الگوی ایجاد ستون‌های extension با استفاده از ADDCOLUMNS به جای SUMMARIZE، اشاره کردیم که شرایطی وجود دارد که در آن شما نمی‌توانید این جایگزینی را انجام دهید و نتیجه نادرست تولید خواهد کرد. به عنوان مثال، وقتی فیلترهایی را روی ستون‌هایی اعمال می‌کنید که در ستون گروه‌بندی ‌شده گنجانده نشده‌اند و سپس عبارت ستون extension را با استفاده از داده‌های حاصل از جداول مرتبط محاسبه می‌کنید، محتوای فیلتر بین SUMMARIZE و ADDCOLUMNS متفاوت خواهد بود.

کوئری زیر، بر اساس Product Category و Customer Education، سود حاصل از ۲ مشتری برتر برای هر محصول را برمی‌گرداند. بنابراین، یک دسته ممکن است شامل ۰، ۱ یا ۲ مشتری باشد:

EVALUATE
SUMMARIZE (
GENERATE (
Product,
TOPN (
۲,
Customer,
CALCULATE (SUM (‘Internet Sales'[Sales Amount]))
)
),
Product [Product Category Name],
Customer[Education],
"Profit",
SUM (‘Internet Sales'[Gross Profit])
)
ORDER BY [Profit] DESC

در این مورد، اعمال الگوی انتقال ستون‌های extension از یک SUMMARIZE به یک ADDCOLUMNS کار نمی‌کند، زیرا GENERATE که به عنوان پارامتر SUMMARIZE استفاده می‌شود، تنها چند محصول و مشتری را برمی‌گرداند، در حالی که SUMMARIZE فقط فروش‌های مربوط به ترکیبی از محصولات و مشتریان را برمی‌گرداند.

کوئری زیر و نتیجه آن را در نظر بگیرید، لطفاً توجه داشته باشید که دستور GENERATE در یک عبارت CALCULATETABLE گنجانده شده است، به طوری که محتوای ردیفی عبارت ADDCOLUMNS را به یک محتوای فیلتر برای اجرای دستور GENERATE فقط برای محصولات دسته فعلی، تبدیل می‌کند:

EVALUATE
ADDCOLUMNS (
SUMMARIZE (
GENERATE (
Product,
TOPN (
۲,
Customer,
CALCULATE (SUM (‘Internet Sales'[Sales Amount]))
)
),
Product [Product Category Name],
Customer[Education]
),
"Profit",
CALCULATE (
SUM (‘Internet Sales'[Gross Profit]),
CALCULATETABLE (
GENERATE (
Product,
TOPN (
۲,
ALL(Customer[CustomerKey]),
CALCULATE (SUM (‘Internet Sales'[Sales Amount]))
)
)
)
)
)
ORDER BY [Profit] DESC

همان طور که می‌بینید، نتایج متفاوت است زیرا Profit بالاتر از نتیجه اولیه است. دلیل آن این است که این کوئری ۲ مشتری برتر را برای هر Customer Education و به ازای یک دسته محصول (Product Category) در نظر می‌گیرد، در حالی که کوئری اولیه ۲ مشتری برتر را برای هر محصول با Customer Education متفاوت در نظر می‌گیرد و اگر دو مشتری برای هر Customer Education یکسان بودند در نتیجه نهایی یکی را در نظر می‌گیرد.

اگر SUMMARIZE را در داخل یک ADDCOLUMNS به کار ببرید، ستون‌های extension ایجاد شده در ADDCOLUMNS روی یک محتوای فیلتر تعریف‌ شده توسط Product Category و Customer Education کار می‌کند و تعداد رکورد فروش بسیار بیشتری نسبت به مواردی که در کوئری اولیه استفاده می‌شد در نظر می‌گیرد. بنابراین، برای ایجاد نتیجه معادل با استفاده از ADDCOLUMNS، لازم است عملیات GENERATE را در یک عبارت CALCULATETABLE تکرار کنیم.

اما از آنجایی که ما باید Product Category و Customer Education را در خروجی لحاظ کنیم، باید GENERATE اصلی را نیز تغییر دهیم تا بخشی از محتوای فیلتر را که ممکن است محاسبات استفاده شده توسط TOPN را تغییر دهد، حذف کنیم. کوئری نهایی به صورت زیر خواهد بود:

EVALUATE
ADDCOLUMNS (
SUMMARIZE (
GENERATE (
Product,
TOPN (
۲,
Customer,
CALCULATE (SUM (‘Internet Sales'[Sales Amount]))
)
),
Product [Product Category Name],
Customer[Education]
),
"Profit",
CALCULATE (
SUM (‘Internet Sales'[Gross Profit]),
CALCULATETABLE (
GENERATE (
Product,
TOPN (
۲,
ALL(Customer[CustomerKey]),
CALCULATE (
SUM (‘Internet Sales'[Sales Amount]),
ALL(Customer[Education])
)
)
)
)
)
)
ORDER BY [Profit] DESC

نتیجه‌گیری

باید توجه داشته باشید که GENERATE داخلی از تک ستون Customer[CustomerKey] به جای جدول Customer استفاده می‌کند، زیرا برای ایجاد نتیجه دقیق لازم است با محتوای فیلتر خارجی تعامل داشته باشید. توضیح تمام جزئیات این کوئری می‌تواند طولانی‌تر باشد، اما از حد این مقاله خارج است. به طور خلاصه، نتیجه این است که اگر جدول مورد استفاده در SUMMARIZE فیلترهای خاصی داشته باشد و عبارت ستون extension از ستون‌هایی استفاده کند که بخشی از خروجی نیستند، ستون‌های extension در یک عبارت SUMMARIZE نباید به یک ADDCOLUMNS منتقل شوند. حتی اگر می‌توانید یک کوئری معادل ADDCOLUMNS ایجاد کنید، نتیجه بسیار پیچیده‌تر است و هیچ مزیت عملکردی در این بازنویسی وجود ندارد. کوئری پیچیده‌تر دقیقاً همان عملکرد (نه چندان بهتر) کوئری SUMMARIZE را دارد. هر دو کوئری در این بخش تقریباً به ۲۰ ثانیه نیاز دارند تا در Adventure Works 2012 Tabular اجرا شوند.