روش‌های مختلف محاسبه Running Total

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

تاریخ انتشار: ۱۷ بهمن ۱۴۰۰

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

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

مقدمه

در این مقاله قصد داریم روش های مختلف محاسبه Running total مورد بررسی قرار دهیم: Running Totalیا همان Running total در SQL را می توان به روش های مختلفی محاسبه کرد. این مقاله دو روش را پوشش می دهد: استفاده از Inner Join , Window Function

ابتدا نحوه محاسبه Running Total را با استفاده از JOIN داخلی بررسی خواهیم کرد. با انجام این کار، نه تنها در مورد شرایط پیوستن اطلاعات بیشتری کسب خواهید کرد، بلکه نحوه گرفتن نتیجه و خلاصه کردن آن برای به دست آوردن Running Total را خواهید دید.

هنگامی که نحوه انجام آن را به سبک قدیمی مشاهده کردید، از عبارت OVER برای محاسبه Running Total با استفاده از یک window aggregation function استفاده می کنیم. استفاده از این روش جدیدتر و مختصرتر است.

Running Total چیست؟

هدف ما این است که running total را محاسبه کنیم که هر زمان که TransactionDate تغییر کند بازنشانی می شود. مجموع TransactionAmount را می‌گیریم. برای هر فاکتور بعدی در تاریخ تراکنش، RunningTotal باید برابر با running total ID Invoice یا شناسه فاکتو ربه اضافه TransactionAmount فعلی باشد.

در مثال زیر این عمل را مشاهده خواهید کرد. RunningTotal برای فاکتور ۳، RunningTotal قبلی ۳۱۱۰.۷۵ به اضافه مبلغ تراکنش فاکتور شماره ۳ ۱۰۳.۵۰ است.

روش ۱. محاسبه Running Total در SQL Server با استفاده از مفهوم inner join

ابتدا running total با استفاده از inner join محاسبه می کنیم. این روش مکانیزم running total محاسبه running totalرا بیشتر از استفاده از PARTITION نشان می دهد. به این ترتیب، به شما فرصت دیگری برای درک inner join و اعمال آن مفاهیم در مورد استفاده دیگر می دهد.

برای حل این مشکل سه مرحله وجود دارد:

ردیف هایی را برای Running Total دریافت کنید
جزئیات را برای Running Total با استفاده از inner join تنظیم کنید.
Running Total را با خلاصه کردن داده ها محاسبه کنید.

گام اول) دریافت ردیف ها برای اجرای Running Total

برای محاسبه Running Total کوئری را روی جدول Customer Transaction می زنیم ما فیلدهای invoice id, transaction date, transaction amount در کوئری می آوریم به شرطی که کد تراکنش برابر یک باشد و مرتبسازی براساس فیلد تاریخ تراکنش transaction date باشد البته running total براساس فیلد transaction amount محاسبه می شود:

در اینجا کوئری برای بدست آوردن فیلدهای داده های اولیه به شرح زیر است.

SELECT InvoiceID
,TransactionDate
,TransactionAmount
FROM Sales.CustomerTransactions
WHERE TransactionTypeID = 1
ORDER BY TransactionDate

خروجی کوئری فوق به شرح زیر می باشد

در واقع، این مرحله به منظور آشنایی شما با اطلاعات اولیه است. واقعاً نیازی به انجام آن نیست. با این حال، من گاهی اوقات می خواهم پرس و جوی اصلی را فقط برای دیدن داده ها توصیه کنم. علاوه بر این، برای اطمینان از اینکه هیچ ناهنجاری یا موقعیت خاصی وجود ندارد که باید با آنها کنار بیایم

گام دوم) پیاده سازیRunning Total با استفاده از inner join

برای این مرحله، جزئیات را تنظیم می کنیم تا بتوانیمrunning Total ا را محاسبه کنیم. برای انجام این کار، ما به هر شناسه فاکتور مبلغ تراکنش و تمام مبالغ تراکنش قبلی را دریافت می کنیم.

برای اینکه بتوانیم این کار را انجام دهیم، جدول CustomerTransactions را به خودش ملحق می کنیم. یا inner join می زنیم اگر این کار را بدون شرط الحاق انجام دهیم، هر ترکیبی از تراکنش ها را دریافت می کنیم، این چیزی نیست که ما می خواهیم.

برای اطمینان از اینکه ترکیب مناسبی از سطرها از هر جدول را بدست می آوریم، دو شرط الحاق اضافه می کنیم. یکی برای دریافت هر فاکتور و فاکتورهای قبل از آن (سبز).

دوم تضمین می کند که ما فقط فاکتورها را در همان تاریخ معامله (قرمز) درج می کنیم.

برای این منظور کوئری زیر را می نویسیم:

SELECT T1.InvoiceID
,T2.InvoiceID
,T1.TransactionDate
,T1.TransactionAmount
,T2.TransactionAmount
FROM Sales.CustomerTransactions T1
INNER JOIN Sales.CustomerTransactions T2
ON T1.InvoiceID >= T2.InvoiceID
AND T1.TransactionDate = T2.TransactionDate
WHERE T1.TransactionTypeID = 1
ORDER BY T1.InvoiceID, T1.TransactionAmount

بیایید ببینیم این چگونه عمل می کند.

ساده ترین شرط برای درک این است که کجا TransactionDate را مطابقت می دهیم. این اطمینان حاصل می کند که مطابقت فاکتورها دارای یک تاریخ معامله مشترک است. اگر این تنها عضویتی بود که انجام دادیم، برای همه تراکنش‌ها در یک تاریخ، یک زیرمجموع یا subtotal محاسبه می‌کردیم.

از آنجایی که می خواهیم running total را محاسبه کنیم، باید به نحوی برای هر InvoiceID TransactionAmount برای فاکتور و تمام فاکتورهای قبل از آن را بدست آوریم. به عبارت دیگر، تمام ردیف‌های منطبق را که فاکتور بزرگ‌تر یا برابر با فاکتورهای مربوطه است که می‌خواهیم جمع کنیم، برگردانید.اگر به نتیجه بالا نگاه کنید، خواهید دید که برای هر فاکتور فهرست شده در ستون اول (T1.InvoiceID)، بزرگتر یا برابر با InvoiceID در ستون دوم است (T2.InvoiceID).

این نتیجه شرطjoin T1.InvoiceID >= T2.InvoiceID است.

نتیجه این join و شرایطjoin این است که اکنون الزامات اولیه برای محاسبه running total داریم به نحوه تکرار ستون های اول، سوم و چهارم توجه کنید. ما می توانیم از این به نفع خود استفاده کنیم تا نتیجه را خلاصه کنیم تا به running total برسیم.

گام سوم) محاسبه Running Total به وسیله خلاصه سازی ردیف ها:

با اطلاعات دقیق در دست، مرحله نهایی خلاصه کردن سطرها است. انجام این کار به ما امکان می دهد تا running total را محاسبه کنیم.

SELECT T1.InvoiceID
,T1.TransactionDate
,T1.TransactionAmount
,Sum(T2.TransactionAmount) RunningTotal
FROM Sales.CustomerTransactions T1
INNER JOIN Sales.CustomerTransactions T2
ON T1.InvoiceID >= T2.InvoiceID
AND T1.TransactionDate = T2.TransactionDate
WHERE T1.TransactionTypeID = 1
GROUP BY T1.InvoiceID
,T1.TransactionDate
,T1.TransactionAmount
ORDER BY T1.InvoiceID
,T1.TransactionAmount

توجه کنید که چگونه بر اساس T1InvoiceID، T1.TransactionDate و T1.TransactionAmount گروه بندی می کنیم. اینها مقادیری هستند که در داده های دقیق ما در مرحله ۲ تکرار شده اند.

Running total از T2.TransactionAmount مشتق شده است. به یاد بیاورید که این مقادیر TransactionAmount از تمام فاکتورها قبل از نمایش فاکتور هستند. به عبارت دیگر فاکتور نمایش داده شده بزرگتر یا مساوی با آنهاست.این به ما امکان می دهد تا یک Running total بسازیم.

هر فاکتور بعدی در لیست، ارزش RunningTotal خود را با جمع‌بندی تمام TransactionAmount از فاکتور خود و موارد قبل از آن محاسبه می‌کند.اکنون که روشی سنتی برای رسیدن به running total دیده اید و شاید درک بیشتری از نحوه استفاده از joinشرایط join برای حل آن به دست آورده اید، بیایید به یکی از ویژگی های جدیدتر SQL یعنی پارتیشن ها نگاهی بیندازیم و ببینیم آنها چگونه هستند. می توان برای رسیدن به همان نتیجه استفاده کرد.

محاسبه running total با استفاده از over در SQL Server

عبارت over این امکان را به ما می دهد که مجموعه ای از ردیف ها که مجموعه از نتایج روی آن تاثیر می گذارد تعریف کنید

مانند OFFSET و FETCH که به ما امکان می دهد محدوده خاصی از ردیف ها را از یک مجموعه نتیجه بازیابی کنیم، عبارت OVER به ما اجازه می دهد تا عملیات مشابهی را نسبت به ردیف فعلی برای یک ستون خاص انجام دهیم.

با استفاده از OVER، می‌توانیم پنجره‌ای را روی یک مجموعه مشخص از ردیف‌ها تعریف کنیم که می‌توانیم توابعی مانند sum را روی آن اعمال کنیم.

برای اینکه بتوانید مفهوم را درک کنید، این را به دو مرحله تقسیم می کنیم:

پارتیشن بندی داده ها با استفاده از Over
پارتیشن بندی و Ordering

گام ۱) پارتیشن بندی داده ها با استفاده از Over Clause

وقتی می‌گوییم می‌خواهیم، می‌خواهیم یک running total برای همه صورت‌حساب‌های یک TransactionDate ایجاد کنیم، می‌خواهیم داده‌های خود را بر اساس TransactionDate تقسیم کنیم. برای پارتیشن بندی داده ها می توانیم از عبارت over استفاده کنیم.

در عبارت زیر، ما مقدار TransactionAmount را جمع می کنیم و بعد از SUM یک بند OVER وجود دارد.همچنین توجه داشته باشید که هیچ بند GROUP BY وجود ندارد. این تعجب آور است، معمولا توابعwindow aggregation function ، مانند SUM، به یک بند GROUP BY نیاز دارند. چرا این مورد است؟ از آنجایی که ما از عبارت OVER استفاده می کنیم، SUM یک تابع window function در نظر گرفته می شود – بر روی هر ردیفی که در عبارت OVER تعریف شده است عمل می کند.

در اینجا تابع Window Functionاست که ما استفاده خواهیم کرد. به شرح زیر می باشد:

حال در ادامه کوئری می نویسیم که فیلدهای invoice id, transaction date, transaction amoubt و فیلد amount به صورت window aggregation function تعریف می شود که پارتیشن بندی براساس تاریخ تراکنش transaction date می باشد به شرطی که رکوردهایی را می خواهیم type id تراکنش برابر یک باشد و مرتبسازی براساس فیلد invoice id, یا شناسه فاکتور و براساس مقدار دلاری تراکنش می باشد برای این منظور کوئری زیر را می نویسیم:

SELECT InvoiceID
,TransactionDate
,TransactionAmount
,SUM(TransactionAmount) OVER(PARTITION BY TransactionDate) RunningTotal
FROM Sales.CustomerTransactions T1
WHERE TransactionTypeID = 1
ORDER BY InvoiceID
,TransactionAmount

خروجی کوئری فوق مطابق تصویر زیر می باشد:

گام ۲) مرتبسازی پارتیشن با استفاده از order by

برای انجام این کار می‌توانیم از ORDER BY در عبارت OVER برای تعریف “scope” window function استفاده کنیم. ORDER BY ترتیب منطقی عملکرد window function را مشخص می کند.

در اینجا ما window function زیر را استفاده خواهیم کرد:

SUM(TransactionAmount) OVER(PARTITION BY TransactionDate ORDER BY InvoiceID) RunningTotal

تفاوت این window function آن از مرحله اول، ORDER BY InvoiceID است. این ترتیب منطقی پردازش در پارتیشن را مشخص می کند.

بدون ORDER BY، ترتیب منطقی این است که منتظر بمانیم تا در انتهای window باشیم تا مجموع را محاسبه کنیم. با مشخص شدن ORDER BY، ترتیب منطقی محاسبه مجموع برای هر ردیف شامل مقادیر قبلی TransactionAmount در window است.

حال در ادامه کوئری می نویسیم که فیلدهای invoice id, transaction date, transaction amoubt و فیلدamount به صورت window aggregation function تعریف می شود که پارتیشن بندی براساس تاریخ تراکنش transaction date می باشد وordering براساس شناسه فاکتور باشد به شرطی که رکوردهایی را می خواهیم type id تراکنش برابر یک باشد و مرتبسازی براساس فیلدinvoice id, یا شناسه فاکتور و براساس مقدار دلاری تراکنش می باشد برای این منظور کوئری زیر را می نویسیم.

برای این منظور کوئری زیر را می نویسیم:

SELECT InvoiceID
,TransactionDate
,TransactionAmount
,SUM(TransactionAmount) OVER(PARTITION BY TransactionDate ORDER BY InvoiceID) RunningTotal
FROM Sales.CustomerTransactions T1
WHERE TransactionTypeID = 1
ORDER BY InvoiceID
,TransactionAmount

خروجی کوئری مطابق تصویر زیر می باشد:وقتی این کوئری را اجرا کردید متوجه شدید که چقدر سریعتر از موردی که از JOINS داخلی استفاده می کند اجرا می شود؟ شگفت زده شدم. من می‌دانم که عملیات JOIN داخلی منابع زیادی را با بزرگ شدن ترکیب‌ها یا ردیف‌ها مصرف می‌کند، اما فکر می‌کردم برای راه‌حل با استفاده از OVER نیز همین حالت را داشته باشد.