گردش تجمیعی مقادیر عددی

[Arghavan_Reza]

با سلام به کلیه دوستان؛
موضوعی که مطرح می کنم یکی از مشکلات برنامه نویسان سیستم های اطلاعاتی (مثل حسابداری، انبار، فروش و ...) است:
مثلا در سیستم حسابداری
در گزارشاتی مانند دفتر روزنامه، دفتر کل و معین و ... که گزارشی از اتفاقات لحظه ای مالی جهت یک یا چند حساب یا همه حساب های یک دوره مالی یا یک محدوده تاریخی است؛ عناوینی مثل گردش بدهکار، گردش بستانکار و مانده حساب وجود دارد که تابعی از اطلاعات رکورد جاری و اطلاعات تجمیعی رکورد قبلی است. به عبارتی ساده تر:
گردش بدهکار (رکورد جاری) = مبلغ بدهکار (رکورد جاری) + گردش بدهکار (رکورد قبلی)
گردش بستانکار (رکورد جاری) = مبلغ بستانکار (رکورد جاری) + گردش بستانکار (رکورد قبلی)
مانده حساب = تفاضل گردش بدهکار و گردش بستانکار

و یا در سیستم انبار
در گزارشی مانند کارکس مقداری کالا که گزارشی از اتفاقات لحظه ای ورود و خروج کالا (مقداری) جهت یک یا چند کالا یا همه کالاهای یک دوره مالی یا یک محدوده تاریخی است؛ عناوینی مثل گردش وارده (یا گردش تجمیعی وارده / یا جمع وارده)، گردش صادره و مانده (موجودی) وجود دارد که تابعی از اطلاعات رکورد جاری و اطلاعات تجمیعی رکورد قبلی است. به عبارتی ساده تر:
گردش وارده (رکورد جاری) = مقدار وارده (رکورد جاری) + گردش وارده (رکورد قبلی)
گردش صادره (رکورد جاری) = مقدار صادره (رکورد جاری) + گردش صادره (رکورد قبلی)
مانده / موجودی کالا = تفاضل گردش وارده و گردش صادره

یا بطور کلی : برخی از اطلاعات عددی یک رکورد تابعی تجمیعی از اطلاعات آن رکورد و رکوردهای قبلی است.

نوشتن یک SP یا Function و استفاده از Cursor و یا جداول موقت یا مجازی برای نیل به این هدف مشکل نیست ولی منظور از این تاپیک ارایه راهکار مناسب می باشد تا:

1) برای حجم اطلاعات زیاد ، کند نباشد.
2) موجب افزایش حجم DB نگردد.
3) باعث افزایش ترافیک شبکه نگردد.
4) در هر محدوده از اطلاعات (مثلا محدوده تاریخی) قابل اجرا باشد.
5) فیلدی جهت ذخیره سازی فیلدهای تجمیعی فوق تعریف نشود.

با تشکر از همه کسانی که در این گفتگو راهکار مناسب ارایه دهند.

[AminSobati]

چیزی که شما در اصل بهش اشاره کردین، مقوله Ordered Calculation هستش که راجع بهش بحثهای مفصلی بین علمای دیتابیس همواره وجود داشته! چند نمونه هم در سایت خودمون پست شده:
http://www.barnamenevis.org/sh...ad.php?t=21194
http://www.barnamenevis.org/sh...ad.php?t=37909
روش Sub Query چندان خوب عمل نمیکنه در حجم اطلاعات بالا و Cursor ممکنه ازش جلو بیافته. برای جزئیات بیشتر حتما این لینک رو ببینید، بسیار آموزنده و پرمحتواست:
http://www.insidetsql.com/OVER_Claus...lculations.doc

[رضا عربلو]

امین جان لینک http://www.insidetsql.com/OVER_Claus...lculations.doc گویا مشکل دارد (من که نتونستم بازش کنم) ولی به کمک گوگل تونسم نسخه html http://64.233.183.104/search?q=cache...lculations.doc انرا مطالعه کنم.

[Arghavan_Reza]

http://www.barnamenevis.org/sh...ad.php?t=37909

لینک فوق مناسب موضوع نمی باشند.

http://www.barnamenevis.org/sh...ad.php?t=21194

در این لینک از فیلد محاسباتی استفاده شده و مقدار آن نتیجه یک تابع است، مفروضات زیر در نظر گرفته نشده است:

1) فیلد گردش یا تجمیعی برای یک رکورد خاص مقدار ثابت و مشخصی ندارد و بستگی به شرایط تهیه گزارش دارد. مثلا فرض کنید این رکورد برای برج 3 می باشد، لذا در گزارش ماه 3 و گزارش 3 ماه اول مقادیر فیلد گردش برای هر رکورد متفاوت است. و همچنین اگر شروط دیگری اعمال کنیم.
2) ترتیب اطلاعات تابعی از شرایطی مثل تاریخ و شماره سند و نوع سند و ... و الزاما تابع ترتیب ورود اطلاعات (مثل ID یا Radif) نمی باشد.
3) در حجم اطلاعات بالا تابع پاسخگو نیست چرا که برای هر رکورد فیلد محاسباتی برای تمامی رکورد های قبلی مجددا محاسبه میشود.

روش های دیگر مثل Sub Query یا Join در حجم بالا و خصوصا اگر فیلدهای محاسباتی مشابه زیاد باشد کارایی مناسب ندارند.:

بهترین روشی که تا بحال استفاده کرده ام به کارگیری Cursor در SP یا Function است.
اما در این روش هم مجبوریم نتیجه را در جدولی موقت به عنوان خروجی (Output) تابع یا SP ذخیره کنیم. و زمانی به این جدول دسترسی خواهیم داشت که کار تابع یا SP تمام شده باشد. یعنی برای یک Db با یک میلیون رکورد (دارای شرط مطلوب) باید صبر کنیم تا تمام یک میلیون رکورد محاسبه شود!!! (البته نمیدانم! شاید این طور نباشد).


جهت اطلاع:
در بانک اطلاعاتی Interbase خروجی SP در جدول موقت ذخیره نمی شود و هر بار بخواهیم اطلاعاتی به عنوان رکورد خروجی اعلام شود کافی است دستور suspend را بکار ببریم. در این حالت خروجی SP به صورت یک رکورد از یک جدول در اختیار سیستم قرار میگیرد. (مثلا اگر خروجی SP یک میلیون رکورد باشد ابتدا مثلا 50 رکورد و بعد در صورت نیاز سایر بسته های اطلاعات محاسبه و اعلام می شود و ...)

http://64.233.183.104/search?q=cache...lculations.doc

لینک فوق هم مطالعه شد، خوب بود و تمام موارد فوق را و روش های دیگر هم بررسی کرده ولی پاسخی بهتر نیافتم.

لذا از کلیه دوستان دعوت به همکاری می شود. نتیجه بحث برای بسیاری مفید خواهد بود.

[ASKaffash]

با سلام
من بااستفاده از Extended Stored Procudures در SQLServer2000 و با استفاده از زبان VC++‎ اینکار را انجام داده ام متن نمونه C وتوابع UDF را اینجا قرار میدهم اگر کسی بتواند بگوید چطور میتوانم همینجا یک فایل را ضمیمه کنم مقاله ای که در این خصوص نوشته ام را پیوست میکنم :
این کد یک مثال از ایجاد یک متغیر شمارندهای وعمومی در SQLServer است :

# include  < stdafx.h >
unsigned   char   ID = 0 ;
# ifdef  __ cplusplus
extern ″C″ {
# endif
           RVRETCODE  __declspec (dllexport)  Xp_MyID (SRV_PROC   *srvproc) ;
# ifdef  __ cplusplus
            }
# endif
//--- Get New ID ----
SRVRETCODE  __declspec (dllexport)  Xp_MyID (SRV_PROC   *srvproc)
{
if  (ID == 255)  ID = 0 ;
++ ID ;
return  ID ;
}

و کد SQL آن :

Create  Function  GNID ( )
Returns   TinyInt  
As
Begin
Declare   @R   TinyInt
EXEC     @R = master. . Xp_MyID
Return    @R
End

[AminSobati]

لینک فوق مناسب موضوع نمی باشند.

در این لینک از فیلد محاسباتی استفاده شده و مقدار آن نتیجه یک تابع است، مفروضات زیر در نظر گرفته نشده است:

1) فیلد گردش یا تجمیعی برای یک رکورد خاص مقدار ثابت و مشخصی ندارد و بستگی به شرایط تهیه گزارش دارد. مثلا فرض کنید این رکورد برای برج 3 می باشد، لذا در گزارش ماه 3 و گزارش 3 ماه اول مقادیر فیلد گردش برای هر رکورد متفاوت است. و همچنین اگر شروط دیگری اعمال کنیم.
2) ترتیب اطلاعات تابعی از شرایطی مثل تاریخ و شماره سند و نوع سند و ... و الزاما تابع ترتیب ورود اطلاعات (مثل ID یا Radif) نمی باشد.
3) در حجم اطلاعات بالا تابع پاسخگو نیست چرا که برای هر رکورد فیلد محاسباتی برای تمامی رکورد های قبلی مجددا محاسبه میشود.

روش های دیگر مثل Sub Query یا Join در حجم بالا و خصوصا اگر فیلدهای محاسباتی مشابه زیاد باشد کارایی مناسب ندارند.:

بهترین روشی که تا بحال استفاده کرده ام به کارگیری Cursor در SP یا Function است.
اما در این روش هم مجبوریم نتیجه را در جدولی موقت به عنوان خروجی (Output) تابع یا SP ذخیره کنیم. و زمانی به این جدول دسترسی خواهیم داشت که کار تابع یا SP تمام شده باشد. یعنی برای یک Db با یک میلیون رکورد (دارای شرط مطلوب) باید صبر کنیم تا تمام یک میلیون رکورد محاسبه شود!!! (البته نمیدانم! شاید این طور نباشد).


جهت اطلاع:
در بانک اطلاعاتی Interbase خروجی SP در جدول موقت ذخیره نمی شود و هر بار بخواهیم اطلاعاتی به عنوان رکورد خروجی اعلام شود کافی است دستور suspend را بکار ببریم. در این حالت خروجی SP به صورت یک رکورد از یک جدول در اختیار سیستم قرار میگیرد. (مثلا اگر خروجی SP یک میلیون رکورد باشد ابتدا مثلا 50 رکورد و بعد در صورت نیاز سایر بسته های اطلاعات محاسبه و اعلام می شود و ...)


لینک فوق هم مطالعه شد، خوب بود و تمام موارد فوق را و روش های دیگر هم بررسی کرده ولی پاسخی بهتر نیافتم.

لذا از کلیه دوستان دعوت به همکاری می شود. نتیجه بحث برای بسیاری مفید خواهد بود.

انتظار معجزه نداشته باشید. ضعف Subquery و برتری Cursor رو در پست دوم گوشزد کرده بودم. روشهایی که در لینک پست شماره 3 ارائه شده، بعنوان راه حل نهایی تا نسخه 2005 از SQL Server هستند. میبایست بهترینش رو برای خودتون Customize کنین.

[Arghavan_Reza]

قسمت کلیدی موضوع مجددا در ذیل ذکر می شود:

بهترین روشی که تا بحال استفاده کرده ام به کارگیری Cursor در SP یا Function است.
اما در این روش هم مجبوریم نتیجه را در جدولی موقت به عنوان خروجی (Output) تابع یا SP ذخیره کنیم. و زمانی به این جدول دسترسی خواهیم داشت که کار تابع یا SP تمام شده باشد. یعنی برای یک Db با یک میلیون رکورد (دارای شرط مطلوب) باید صبر کنیم تا تمام یک میلیون رکورد محاسبه شود!!! (البته نمیدانم! شاید این طور نباشد).

جهت اطلاع:
در بانک اطلاعاتی Interbase خروجی SP در جدول موقت ذخیره نمی شود و هر بار بخواهیم اطلاعاتی به عنوان رکورد خروجی اعلام شود کافی است دستور suspend را بکار ببریم. در این حالت خروجی SP به صورت یک رکورد از یک جدول در اختیار سیستم قرار میگیرد. (مثلا اگر خروجی SP یک میلیون رکورد باشد ابتدا مثلا 50 رکورد و بعد در صورت نیاز سایر بسته های اطلاعات محاسبه و اعلام می شود و ...)

انتظار معجزه نداشته باشید. ضعف Subquery و برتری Cursor رو در پست دوم گوشزد کرده بودم. روشهایی که در لینک پست شماره 3 ارائه شده، بعنوان راه حل نهایی تا نسخه 2005 از SQL Server هستند. میبایست بهترینش رو برای خودتون Customize کنین.

در Interbase معجزه ای صورت نگرفته، اصولا در برنامه نویسی انتظار معجزه نداریم، بلکه به دنبال راهکار مناسبی هستیم تا به بسیاری از سوالات برنامه نویسان پاسخ مناسب داده شود. در این میان اکثر مشکلات دوستان ناشی از عدم اطلاع آنها از امکانات و توانایی های یک ابزار است. نوشتن این تاپیک صرفا برای رفع مشکل خودم نیست چرا که از طریق Sp و با استفاده از Cusror مشکل خودم را تا حد ممکن Optimize کرده ام (هر چند بدلایل گفته شده چندان راضی نیستم). ولی ممکن است شما یا سایر دوستان توابعی یا ابزارهایی یا راهکاری بشناسید که برای بهینه کردن این موضوع به من و سایر برنامه نویسان بتواند کمک کند.

در نهایت از دوستان درخواست می شود در رابطه با موضوع تاپیک اگر و تنها اگر مطلب مفیدی دارند ارائه نمایند.
ضمنا در صورت نیاز نمونه SP جهت تفهیم یا استفاده ارایه خواهد شد.

[AminSobati]

هر بانک اطلاعاتی و ایضا Interbase راهکارهای خودشون رو برای این موضوع دارند.
بنده با نظر شما برای تبادل افکار موافقم. اما با توجه به اینکه چند سال پیش برای اولین بار با این مشکل مواجه شدم و تمام مقالات Ordered Calculation از افراد سرشناس رو در اینترنت مطالعه کردم، قصد داشتم شما از اتلاف وقت و سعی-و-خطا در امان باشید. دوستان دیگه هم اگر روشی بهتر از آقای Itzik Ben-Gan دارند استفاده میکنیم..

[ASKaffash]

هر بانک اطلاعاتی و ایضا Interbase راهکارهای خودشون رو برای این موضوع دارند.
بنده با نظر شما برای تبادل افکار موافقم. اما با توجه به اینکه چند سال پیش برای اولین بار با این مشکل مواجه شدم و تمام مقالات Ordered Calculation از افراد سرشناس رو در اینترنت مطالعه کردم، قصد داشتم شما از اتلاف وقت و سعی-و-خطا در امان باشید. دوستان دیگه هم اگر روشی بهتر از آقای Itzik Ben-Gan دارند استفاده میکنیم..

دوستان حتما این مقاله رابخوانید چون روش تجمیع مقادیر عددی را میتوانید انجام دهید :

[AminSobati]

دوستان حتما این مقاله رابخوانید چون روش تجمیع مقادیر عددی را میتوانید انجام دهید :

جناب کفاش، موضوع تولید Rank نیست

[ASKaffash]

سلام به تمام دوستان ازجمله جناب AminSobati
دوستان در پاسخ به سئوال اصلی یعنی ایجاد تجمیع در سیستم های انبار و حسابداری راه حل استفاده از ESP را ارائه دادم وبرای اثبات واقعی بودن موضوع یک مقاله که با استفاده از ESP یک Dll به SQLServer اضافه میکند که قابلیت شمارنده عمومی دارد را در مقاله پیوست آن ارائه کردم که نشان میدهدکاملا با تغییرات کمی این روش برای ایجاد فیلد تجمیع بدون کرسر و سایر روشها شدنی است

[Arghavan_Reza]

در پاسخ به سئوال اصلی یعنی ایجاد تجمیع در سیستم های انبار و حسابداری راه حل استفاده از ESP را ارائه دادم وبرای اثبات واقعی بودن موضوع یک مقاله که با استفاده از ESP یک Dll به SQLServer اضافه میکند که قابلیت شمارنده عمومی دارد را در مقاله پیوست آن ارائه کردم که نشان میدهدکاملا با تغییرات کمی این روش برای ایجاد فیلد تجمیع بدون کرسر و سایر روشها شدنی است

جناب آقای کفاش ضمن تشکر از حضورتان در این تاپیک، لطفا مثالی عملی مرتبط با موضوع ارایه فرمایید.

[ASKaffash]

سلام
این مقاله که در پیوست ردیف 9# قرار داده ام مثال واقعی به همراه سورس است لطفا مطالعه کنید ودر گام بعدی به شما خواهم گفت که چقدر ساده این مثال برای نیاز شما قابل پیاده سازی است.