استفاده از تکنیک self join در تهیه گزارش whatsup gold

[tadeh2010]

سلام دوستان من می خواهم با استفاده از اطلاعات ذخیره شده در پایگاه داده ی برنامه ی WhatsUp Gold
که برای مانیتورینگ شبکه می باشد گزارش قطعی ها را تهیه نمایم.
با استفاده از کد نویسی در برنامه توانسته ام حلش بکنم ولی سرعت بسیار کمی دارد.
با تحقیقاتی که انجام دادم فهمیدم با استفاده از تکنیک Self Join می توان سرعت گزارش را صدها مرتبه بالاتر برد.
اما با این تکنیک آشنایی کاملی ندارم و از شما کمک می خواهم جدول من به شکل زیر می باشد
دیتابیس database نمونه ای را برای مثال تهیه کرده ام همچنین فایل اکسل excel پایگاه نیز که در واقع همین جدول زیرین می باشد را قرار داده ام. 97795 , 97796

97798

برای روز اول ژوئن 2013 بدست بیاورم
این چنین :
1-دیوایس 390 در ساعت 7:21 قطع شده و در ساعت 7:40 وصل شده است یعنی 19 دقیقه قطع بوده.
2-دیوایس 379 در ساعت 16:07 قطع شده و همچنان قطع می باشد.
3-دیوایس 513 در ساعت 7:45 قطع شده و در ساعت 8:05 وصل شده است یعنی 20 دقیقه قطع بوده.
4-دیوایس 537 در ساعت 00:00قطع شده و در ساعت 00:07 بامداد وصل شده یعنی در روز اول 2013 برای 7 دقیقه قطع بوده است .
5-دیوایس 537 در ساعت 07:45 قطع شده و در ساعت 08:24 وصل شده است یعنی 39 دقیقه قطع بوده.
و....
موارد 1و2و3 را با استفاده از T-SQL پیاده سازی کرده ام.
ولی موارد 4 و بخصوص 5 که پایان قطعی در رکورد دوم و در بعضی موارد در رکورد سوم می باشد را با برنامه نویسی و حلقه ها توانسته ام پیاده سازی بکنم که همانطور که گفته ام سرعت بسیار کمی دارد.
ممنون می شوم شما دوستان محترم کمکم بکنید.

[محمد سلیم آبادی]

4-دیوایس 537 در ساعت 00:00قطع شده و در ساعت 00:07 بامداد وصل شده یعنی در روز اول 2013 برای 7 دقیقه قطع بوده است .
همچین داده ای در جدولتون وجود نداره!
dstartTime مربوط به سطر 4 مقدارش برابر است با 17:01:49 نه 00:00.

داده های ورودیتون همین جدول هست؟
شما اختلاف زمان بین dstartTime و dEndTime رو میخواهین بدست بیارین؟
زمانی که مقدار DEndTime و DStartTieme چند سطر متوالی (با شماره Device برابر) دو به دو (سطر قبلی با سطر فعلی) برابر باشند شما میخواهین این مقادیر ادغام بشه و اختلاف اولین DstartTime و آخرین DEndTime رو بدست بیارین درسته؟
که چند نمونه در همین جدولتون وجود داره مثلا:
دستگاه شماره 714 سطر 8 و 9 (اختلاف زمان برابر است با تفاضل مقدار DEndTime سطر 9 بر مقدار DStartTime سطر 8)
دستگاه شمراه 136 سطر 13 و 14
دستگاه شماره 209 سطر 16 و 17
دستگاه شماره 144 سطر 20 و 21

داستان DStateName چیه ایجا؟

[tadeh2010]

دوست عزیز
من مجددا پایگاهی را که در سایت Attach کرده بودم را دانلود و Extract و Attach کردم و دیدم داخل تنها جدول Database یعنی ActiveMonitoring اطلاعات همین فایل اکسل موجود است.(البته بانک از SQL Server 2012 Deattach شده است.)
در مورد سطر چهارم حق با شما است ولی من گزارش را برای روز 01/01/2013 گرفته ام یعنی باید در گزارشی که از من خواسته شده این چنین بیاید:
شروع قطعی از ساعت 00:00 بایستی باشد، مهم نیست که دیوایس در چه تاریخی قطع شده است ،تنها مهم این است ،که در تاریخی که برایش گزارش تهیه می کنیم ،قطعی چه زمانی را پوشش می دهد.
مثلا در سطر 8 قطعی از 4روز قبل شروع شده و همچنان ادامه دارد یعنی ، اگر ما در تاریخ 03/01/2013 گزارش را برای تاریخ01/01/2013 تهیه کرده ایم بایستی در گزارش چنین بیاید:
X-دیوایس 658 در ساعت 00:00 قطع شده و در ساعت 23:59 پایان یافته(همچنان قطع است) یعنی در تاریخ 01/01/2013 به مدت 24 ساعت قطع بوده.
نکته بعدی دیتابیس واتز آپ که یک برنامه مانیتورینگ شبکه است یک دیتابیس با حدود 40 تا 50 جدول می باشد و این جدولی که من به عنوان مثال مطرح کرده ام این چنین نمی باشد
بلکه کلیدهای خارجی زیادی دارد و این در واقع از ادغام چندین (حدود 6)جدول دیگر استخراج شده است
ولی درواقع من مشکلم را در قالب یک view بیان کرده ام که نکته مهمش این می باشد که در view ای که من طراحی کرده ام و خروجی اش را به شما در قالب اکسل یا جدول داده ام
بر روی فیلدهای nDeviceID ,dStartTime مرتب سازی را انجام داده ام تا قطعی های یک دیوایس پشت سر هم بیاید، ولی اگر اشتباه می باشد، می شود order by را تغییر داد.

نکته بعدی درواقع داستان DStateName این می باشد که ، آن قسمت از برنامه واتزآپ که وظیفه چک کردن دیوایسها را دارد هر 5 دقیقه یکبار دیوایسها را چک کرده و چنانچه قطع باشند ابتدا یک log
Down در جدول با ابتدای زمان قطعی و انتهای Null می اندازد، و سپس بعد از 5 دقیقه ی دیگر یک بار دیگر دیوایسهای Downای که انتهای Null دارند را چک می کند اگر دیوایسیUp شده باشد،در فیلد dEndTime دیوایس مورد نظر زمان UP را جایگذاری کرده،و همچنین یک رکورد دیگر با DStateName آپ (Up) درج می کند که من چون در این گزارش قطعی ها برایم مهم است Up ها را فیلتر کرده ام یعنی

WHERE DStateName LIKE '%down%'
حالا اگر دوباره دیوایس مورد نظر 5 دقیقه دوم هم قطع بود با بررسی که برنامه انجام می دهد در بانک لاگ اش مقدار انتهایی Down را زمان چک کردن دومی می نویسد و همچنین یک
رکورد با مقدار ابتدای زمان چک کردن دوم و انتهای Null و مقدار Down at least 5 min
آخرین مقداری که برنامه برای یک دیوایس قطع شده چک می کند Down at least 20 min می باشد و دیگر تا زمان آپ شدنش لاگی نمی اندازد
همچنین بین دو مقدار Down at least 5 min و Down at least 20 min لاگ دیگری نمی باشد ، یعنی ما لاگی با عنوان کمتر از 10 یا 15 نداریم
بازهم سوالی بود درخدمتم
البته یک نکته را بگویم
اگر فکر می کنید با فیلتر کردن Up ها راحتتر به جواب می رسم ،باید بگویم لاگ Up غیر منظم تر از Down ها می باشد ، ممکن است یک دیوایس در ساعت خاصی چون Packet Lost دارد لاگ Up بیندازد و پکت لاستها جزء قطعی ها نمی باشند
البته می توانیم از ادغام Down , Up استفاده بکنیم ولی چون هر قطعی در پایانش مقدار Up را دارد فکر بکنم اینجوری راحتتر باشیم
البته اگر نیازی به داده های مختلط بود بگوئید.
ممنونم.:کف:

[محمد سلیم آبادی]

دوست عزیز،
من یه چنتا سوال ساده پرسیدم فقط! شما برام مقاله نوشتین در جواب:لبخندساده:
راجب تکنیک selft join بگم که، بله میشه از این تکنیک حل کرد این چنین مسائل رو. من دقیق تکنیک ها و منطق هایی که قبلا یادگرفته بودم رو به خاطر نمیارم ولی به هر شکل مساله رو حل کردم.
امید وارم با الهام گرفتن از کد زیر مشکلتون حل بشه.
موفق باشید.
declare @t table
(g int, s int, e int)

insert into @t
values (1,5,15),
(1,20,25),(1,25,30),(1,30,35),
(1,50,55),(1,55,60),(1,60,70),(1,70,71),
(1,90,110);
/* Result
g start_point end_point
----------- ----------- -----------
1 5 15
1 20 35
1 50 71
1 90 110
*/
with
ranking as
(
select g,s,e,
row_number() over(partition by g order by s) rnk
from @t
), cte as
(
select t1.*, t2.s as t2s, t2.e t2e,
case when t1.e=t2.s then 1 else 0 end as c
from ranking t1
left join ranking t2
on t1.rnk + 1 = t2.rnk
where (t1.rnk = 1 or t1.e=t2.s)
or t2.e is null
)
select g,
min(s) start_point,
max(t2e) end_point
from (select *, rnk-ROW_NUMBER()over(order by rnk)rk
from cte)d
where c=1
group by g,rk
union
select g, s, e from cte where c=0

[tadeh2010]

دوست عزیز محبت کردید

script شما را بررسی کردم مسئله را حل کرده بودید
ولی من هنوز روی پایگاه برنامه ام اجرایش نکرده ام باید خیلی تغییرات درش بدهم
ولی منطق درستی داشت
اگر مشکل داشتم در همین مورد بازهم مزاحمتان می شوم
خیلی ممنونم.
به امیدهمکاری بیشتر.:تشویق:

[tadeh2010]

سلام دوست عزیز
من از کدی که نوشته اید فهمیدم از طریق رنکینگ و دادن رتبه به هر سطر و نیز گرفتن min ,max از مقدار اولیه و انتهایی مسئله را حل کرده اید.درست است؟
ممنون می شوم یکم در موردش توضیح بدهید.
چون فکر بکنم نیازی به رنکینگ نمی باشد(چون در view من جدول را بر اساس DeviceId و StartTime مرتب کرده ام)-
با این حال دارم می گم من نفهمیدم چه کرده اید هان بعدا ناراحت نشوید.

[محمد سلیم آبادی]

سلام،
دستور ROW_NUMBER اول با توضیحی که دادین (قبلا کار مرتب سازی انجام شده در view) نیازی نیست. اما دومین ROW_NUMBER دیگه ارتباطی به اون موضوع نداره و جزء راه حل است.
همانطور که گفته شد تکنیک self join بکارگیری شده. بعد از self join یکسری جزایر بوجود اومدن که با یک تکنیک دیگه من جزایر را گروه بندی کردم و با کمک دو تابع min و max کوچکترین و بزرگترین مقادیر رو بدست آوردم.

[tadeh2010]

با سلام مجدد خدمت اساتید محترم
در این مورد باز به مشکل خورده ام
1-اس کیو ال سرور 2000 می باشد که فانکشن row_number() را نمی شناسد
2-طرز کار رو نفهمیده ام بنابراین نمی توانم روی فیلدهای بیشتر و جدیدتر پیاده سازیش بکنم.
کسی می تونه کمکم بکند؟
ممنون می شوم.