سلام به همگی بعد از مدتها با یه مطلب کاربردی خدمتتون اومدم و می خواهم در مورد جدول های شمارشی یا Tally Table با شما صحبت کنم .
ابتدا بگذارید صورت مسئله را مطرح کنم ، فرض کنیم از یک پایگاه داده دیگه ای مثل اکسل یا اکسس یا ... جدولی با نام IMP را به SQL ایمپورت کردید که دارای ستون ردیف با نام Rn هست و شما متوجه می شید که شماره ردیفها با تعداد رکوردها مطابقت نداره چرا که بعضی از ردیفها حذف شدن ! مثلا شماره ردیفها از 1 تا یک میلیون هست اما وقتی نتیجه Import را بررسی می کنید یا روی جدول مورد نظر Select Count(*) را اجرا می کنید ، می بینید که تعداد اونها 999,996 هست و چهار تا رکورد کم داره و باید شماره ردیفهایی که نیست را پیدا کنیم .
در عمل و محیط تجاری و کاری ممکنه با این مشکل در شماره سریالهای فاکتور یک شرکت که باید ردیف و پشت سرهم باشه ، اما تعدادی از اونها نیست و بخاطر ملاحظات قانونی باید پیداشون کرد ، نبودن بعضی از شماره اسناد حسابداری در جداول بایگانی یا پیدا کردن ردیفهای حذف شده یک جدول بر اساس فیلد یکتای ( Identity ) آن و ... مواجه می شید و باید مسئله را حل کنید .
خب حالا چطوری شماره ردیف هایی که نیست را پیدا کنیم ؟ جواب اینه که باید این جدول را با جدول دیگه ای که شماره ردیفهاش کامله ( اسم این جدول را می گذاریم SRC و فیلد ردیف اون را Line می نامیم ) جوین کرد تا ردیفهای مفقودی را پیدا کنیم مثلا :
Select src.Line
From SRC
Left join IMP on src.Line=IMP.RN
Where IMP.Rn is null
توضیح تکمیلی اینکه اون جدول SRC با ستون Line همان Tally Table یا جدول شمارشی هست و برای حل مسئله باید در پایگاه داده خودمون چنین جدولی داشته باشیم و حالا اگه نداریم چکار کنیم ؟
دو تا راهکار هست که مشکل گشاست
راه کار اول : با فرض اینکه تعداد ردیفهای جدول IMP کم هست مثلا زیر هزار تا و می خوایم ردیف های حذف شده را پیدا کنیم ، از یک جدول دیگه که تعداد ردیفهای مورد نظر ما را داره و با کمک تابع Row_number جدول SRC را بصورت موقت می سازیم ، مثلا من اینجا از جدول sys.sysobjects خود SQL استفاده می کنم :
Select Src.Line
From (
Select Line=Row_number()over(order by O1.Id)
From sys.sysobjects O1
) SRC
Left join IMP on IMP.Rn=Src.Line
where IMP.Rn is null
راه کار دوم : اگه تعداد ردیفهای جدول IMP زیاد بود مثلا یک میلیون و ما جدولی با تعداد رکورد بالا نداریم یا نمی شناسیم تا از اون کمک بگیریم ، Cross Join را به کمک می طلبیم :
Select Src.Line
From (
Select top 1000000
Line=Row_number()over(order by O1.Id)
From sys.sysobjects O1
Cross Join Sys.sysobjects O2
Order by O1. id
) SRC
left join IMP on IMP.Rn=Src.Line
where IMP.Rn is null
نکته اول : اگه جدول Sys.Sysobjects دارای 2000 تا رکورد باشه با اون Cross join تعداد ردیفها ضرب دکارتی می شه و می شه 4 میلیون رکورد !
نکته دوم : آیا به جای sys.sysobjects نمی تونیم از خود جدول IMP و Cross Join استفاده کنیم ؟ اوه اوه ⛔ ، مراقب cross join باشید چون اگه چنین کاری کنیم و جدول IMP شما یک میلیون رکورد داشته باشه و بخواهیم در Cross Join دوبار از این جدول استفاده کنیم، سرور SQL برای آماده سازی جدول SRC ، ابتدا باید در جدول داخلی یک تریلیون رکورد را تولید کنه ( Cross join و ضرب دکارتی )، اون تعداد رکورد را مرتب کنه (order by) ، یک میلیونیم اون را بیرون بکشه ( Top 1000000 ) و با IMP بیرونی جوین کنه و در این بین حتما سکته خواهد کرد .
نکته سوم : اگه جدول sysobjects فقط 100 تا رکورد داشت چی ؟ می تونید چند بار اون را با خودش Cross join کنید !
امیدوارم این پست مفید بوده باشه و ارزش وقت شما را داشته باشه .💛
