خانه SQL Server روشهای مختلف محاسبه Running Total SQL Server دستورات SQL نوشته شده توسط: تیم فنی نیک آموز تاریخ انتشار: ۱۷ بهمن ۱۴۰۰ آخرین بروزرسانی: ۱۹ مهر ۱۴۰۲ زمان مطالعه: 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 نیز همین حالت را داشته باشد. من شما را تشویق می کنم که به هر کوئری در طرح کوئری نگاه کنید. زمانی که شروع به انجام این کار کنید، کمی در مورد SQL یاد خواهید گرفت. منبع https://www.essentialsql.com/calculate-running-total-sql/ برای بدست آوردن اطلاعات بیشتر در مورد دیگر دستورات SQL ، به مقاله زیر مراجعه کنید. چه رتبه ای میدهید؟ میانگین ۵ / ۵. از مجموع ۱ اولین نفر باش دانلود مقاله روشهای مختلف محاسبه Running Total فرمت PDF 8 صفحه حجم 1 مگابایت دانلود مقاله معرفی نویسنده مقالات 366 مقاله توسط این نویسنده محصولات 0 دوره توسط این نویسنده تیم فنی نیک آموز معرفی محصول ایمان باقری دوره آموزشی کوئری نویسی پیشرفته 1.450.000 تومان مقالات مرتبط ۰۸ شهریور SQL Server اهمیت مانیتورینگ در SQL Server چیست؟ | تمام آنچه که باید از مانیتورینگ بدانید تیم فنی نیک آموز ۰۳ شهریور SQL Server اعمال گواهینامه SSL روی SQL Server تیم فنی نیک آموز ۳۱ مرداد SQL Server دستور Rank در SQL Server ؛ کلید حل معمای رتبه بندی تیم فنی نیک آموز ۲۵ مرداد SQL Server نرم افزار (Microsoft SQL Server Management Studio (SSMS چیست؟ تیم فنی نیک آموز دیدگاه کاربران لغو پاسخ دیدگاه نام و نام خانوادگی ایمیل ذخیره نام، ایمیل و وبسایت من در مرورگر برای زمانی که دوباره دیدگاهی مینویسم. موبایل برای اطلاع از پاسخ لطفاً مرا با خبر کن ثبت دیدگاه Δ