تاریخچهای از روشهای مختلف صفحه بندی اطلاعات در SQL Server در این مقاله بحث شده به همراه بررسی کارآیی آنها:
Comparing performance for different SQL Server paging methods
LocalDB چیست؟
- هاست اشتراکی باید این بانک اطلاعاتی را پیشتر نصب کرده باشد.
مانند SQL Server CE نیست که با توزیع چند DLL مشکل آن برطرف شود و نیازی به نصب نداشته باشد.
روش کار :
1- دریافت پارامتر ورودی به صورت رشته2- درج عناوین اعداد، ارزش مکانی اعداد صحیح و اعشاری هرکدام در یک جدول
3- جدا کردن ارقام صحیح و اعشاری
4- جداکردن سه رقم سه رقم اعداد صحیح و انتقال آنها به جدول مربوطه
5- Join جداول عناوین و ارقام جدا شده
6- ارسال ارقام اعشاری به همین تابع
7- مشخص کردن ارزش مکانی رقم اعشار
8- اتصال رشته حروف صحیح و اعشاری
بررسی قسمتهای مختلف کد
برای اینکه محدودیتی در تعداد ارقام صحیح و اعشاری نداشته باشیم، پارامتر ورودی را از نوع VARCHAR میگیریم. پس باید ورودی را بررسی کنیم تا رشته عددی باشد.
بررسی رشته ورودی:
-- @pNumber پارامتر ورودی IF LEN(ISNULL(@pNumber, '')) = 0 RETURN NULL IF (PATINDEX('%[^0-9.-]%', @pNumber) > 0) OR (LEN(@pNumber) -LEN(REPLACE(@pNumber, '-', '')) > 1) OR (LEN(@pNumber) -LEN(REPLACE(@pNumber, '.', '')) > 1) OR (CHARINDEX('-', @pNumber) > 1) RETURN 'خطا' IF PATINDEX('%[^0]%', @pNumber) = 0 RETURN 'صفر' IF (CHARINDEX('.', @pNumber) = 1) SET @pNumber='0'+@pNumber DECLARE @Negative AS VARCHAR(5) = ''; IF LEFT(@pNumber, 1) = '-' BEGIN SET @pNumber = SUBSTRING(@pNumber, 2, 100) SET @Negative = 'منفی ' END
- بررسی رشته ورودی برای پیدا کردن کاراکتر غیر عددی، نقطه و منفی. بررسی تعداد علامت منفی و نقطه که بیشتر از یک مورد نباشند، و در نهایت بررسی اینکه علامت منفی در ابتدای رشته ورودی باشد.
- بررسی صفر بودن ورودی(0)، مقدار ورودی شروع شونده با ممیز(0213. ) و مقدار عددی منفی(21210.0021-).
چیز دیگری به ذهنم نرسید!
درج عناوین در جداول مربوطه:
فکر کنم اینجا به علت وجود کاراکترهای فارسی و انگلیسی کد کمی بهم ریخته نمایش داده میشود.
DECLARE @NumberTitle TABLE (val INT,Title NVARCHAR(100)); INSERT INTO @NumberTitle (val,Title) VALUES(0, ''),(1, 'یک') ,(2, 'دو'),(3, 'سه'),(4, 'چهار') ,(5, 'پنج'),(6, 'شش'),(7, 'هفت'),(8, 'هشت') ,(9, 'نه'),(10, 'ده'),(11, 'یازده'),(12, 'دوازده') ,(13, 'سیزده'),(14, 'چهارده'),(15, 'پانزده'),(16, 'شانزده') ,(17, 'هفده'),(18, 'هجده'),(19, 'نوزده'),(20, 'بیست') ,(30, 'سی'),(40, 'چهل'),(50, 'پنجاه'),(60, 'شصت') ,(70, 'هفتاد'),(80, 'هشتاد'),(90, 'نود'),(100, 'صد') ,(200, 'دویست'),(300, 'سیصد'),(400, 'چهارصد'),(500, 'پانصد') ,(600, 'ششصد'),(700, 'هفتصد'),(800, 'هشتصد'),(900, 'نهصد') DECLARE @PositionTitle TABLE (id INT,Title NVARCHAR(100)); INSERT INTO @PositionTitle (id,title) VALUES (1, ''),(2, 'هزار'),(3, 'میلیون'),(4, 'میلیارد'),(5, 'تریلیون') ,(6, 'کوادریلیون'),(7, 'کوینتیلیون'),(8, 'سیکستیلون'),(9, 'سپتیلیون') ,(10, 'اکتیلیون'),(11, 'نونیلیون'),(12, 'دسیلیون') ,(13, 'آندسیلیون'),(14, 'دودسیلیون'),(15, 'تریدسیلیون') ,(16, 'کواتردسیلیون'),(17, 'کویندسیلیون'),(18, 'سیکسدسیلیون') ,(19, 'سپتندسیلیون'),(20, 'اکتودسیلیوم'),(21, 'نومدسیلیون') DECLARE @DecimalTitle TABLE (id INT,Title NVARCHAR(100)); INSERT INTO @DecimalTitle (id,Title) VALUES( 1 ,'دهم' ),(2 , 'صدم'),(3 , 'هزارم') ,(4 , 'ده-هزارم'),(5 , 'صد-هزارم'),(6 , 'میلیون ام') ,(7 , 'ده-میلیون ام'),(8 , 'صد-میلیون ام'),(9 , 'میلیاردم') ,(10 , 'ده-میلیاردم')
جداسازی رقم صحیح و اعشاری:
عدد ورودی ممکن است حالتهای مختلفی داشته باشد مثل: .00002 , 0.000000 , 234.434400000000 , 123.بنابراین براساس ممیز، قسمت صحیح را از اعشاری جدا میکنیم. برای ورودی که با ممیز شروع شود، در ابتدا تابع بررسی میکنیم و عدد صفر را به رشته اضافه میکنیم.
بعد از ممیز و اعداد بزرگتر از یک، با صفرهای بی ارزش چه کنیم؟ شاید اولین چیزی که به ذهن برسد استفاده از حلقه (WHILE) برای حذف صفرهای بی ارزش قسمت ممیز باشد؛ ولی من ترجیح میدهم که از روش دیگری استفاده کنم :
برعکس کردن رشته قسمت اعشاری، پیدا کردن مکان اولین عدد غیر صفر منهای یک ، و کم کردن عدد بدست آمده از طول رشته اعشاری، قسمت مورد نظر ما را برخواهد گرداند:
اما اگر عدد ورودی 20.0 باشد همچنان صفر بی ارزش بعداز ممیز را خواهیم داشت. برای رفع این مشکل کافی است که کاراکتری غیر از صفر را به اول رشته اعشاری اضافه کنیم. من از علامت '?' استفاده کردم. پس به علت اضافه کردن کاراکتر، استارت را از 2 شروع کرده و دیگر نیازی به -1 نخواهیم داشت. با کد زیر قسمت صحیح و اعشاری را بدست میآوریم:SUBSTRING(@DecimalNumber,1, len(@DecimalNumber )-PATINDEX('%[^0]%', REVERSE (@DecimalNumber))-1)
DECLARE @IntegerNumber NVARCHAR(100), @DecimalNumber NVARCHAR(100), @PointPosition INT =case CHARINDEX('.', @pNumber) WHEN 0 THEN LEN(@pNumber)+1 ELSE CHARINDEX('.', @pNumber) END SET @IntegerNumber= LEFT(@pNumber, @PointPosition - 1) SET @DecimalNumber= '?' + SUBSTRING(@pNumber, @PointPosition + 1, LEN(@pNumber)) SET @DecimalNumber= SUBSTRING(@DecimalNumber,2, len(@DecimalNumber )-PATINDEX('%[^0]%', REVERSE (@DecimalNumber))) SET @pNumber= @IntegerNumber
جداد کردن سه رقم سه رقم :
- بدست آوردن یکان، دهگان و صدگان- برای قسمت دهگان، اگر عددی بین 10 تا 19 باشد به صورت کامل (مثلا 15) و در غیر این صورت فقط رقم دهگان. برای بدست آوردن یکان اگر دو رقم آخر بین 10 و 19 بود صفر و در غیر این صورت یکان برگردانده میشود و در جدول MyNumbers درج میگردد.
DECLARE @Number AS INT DECLARE @MyNumbers TABLE (id INT IDENTITY(1, 1), Val1 INT, Val2 INT, Val3 INT) WHILE (@pNumber) <> '0' BEGIN SET @number = CAST(SUBSTRING(@pNumber, LEN(@pNumber) -2, 3)AS INT) INSERT INTO @MyNumbers SELECT (@Number % 1000) -(@Number % 100), CASE WHEN @Number % 100 BETWEEN 10 AND 19 THEN @Number % 100 ELSE (@Number % 100) -(@Number % 10) END, CASE WHEN @Number % 100 BETWEEN 10 AND 19 THEN 0 ELSE @Number % 10 END IF LEN(@pNumber) > 2 SET @pNumber = LEFT(@pNumber, LEN(@pNumber) -3) ELSE SET @pNumber = '0' END
استفاده از JOIN :
JOIN کردن جدول اعداد با عناوین عددی براساس ارزش آنها و JOIN جدول اعداد با جدول ارزش مکانی براساس ID به صورت نزولی(شماره سطر).DECLARE @Str AS NVARCHAR(2000) = ''; SELECT @Str += REPLACE(REPLACE(LTRIM(RTRIM(nt1.Title + ' ' + nt2.Title + ' ' + nt3.title)),' ',' '),' ', ' و ') + ' ' + pt.title + ' و ' FROM @MyNumbers AS mn INNER JOIN @PositionTitle pt ON pt.id = mn.id INNER JOIN @NumberTitle nt1 ON nt1.val = mn.Val1 INNER JOIN @NumberTitle nt2 ON nt2.val = mn.Val2 INNER JOIN @NumberTitle nt3 ON nt3.val = mn.Val3 WHERE (nt1.val + nt2.val + nt3.val > 0) ORDER BY pt.id DESC
Replace بیرونی: جایگزینی فاصلههای خالی با ' و '
همانطور که در بالا اشاره کردم سطرهایی که val2,val1 و val3 آن صفر باشد برای ما بی ارزش هستند، پس آنها را با شرط نوشته شده حذف میکنیم.
بدست آوردن مقدار اعشاری:
خوب! حالا نوبت به عدد اعشاری میرسد. برای بدست آوردن حروف، مقدار اعشاری بدست آمده را به همین تابع ارسال میکنیم و برای بدست آوردن عنوان ارزش مکانی، براساس طول اعشار (ID) آن را در جدول مربوطه پیدا میکنیم.اگر عدد ورودی مثلا 0.355 باشد، تابع باید صفر اول را شناسایی و قسمت عناوین اعشاری را به آن اضافه کند، که این کار با شرط ذیل انجام میشود.
اگر رشته اعشار بدون مقدار باشد، تابع مقدار NULL بر میگرداند (قسمت بررسی رشته ورودی) و هر رشته ای که با NULL جمع شود برابر با NULL خواهد بود. در این صورت با توجه به کد زیر مقداری به رشته Str به عنوان قسمت اعشاری، اضافه نمیگردد.
IF @IntegerNumber='0' SET @Str=CASE WHEN PATINDEX('%[^0]%', @DecimalNumber) > 0 THEN @Negative ELSE '' END + 'صفر' ELSE SET @Str = @Negative + LEFT (@Str, LEN(@Str) -2) DECLARE @PTitle NVARCHAR(100)=ISNULL((SELECT Title FROM @DecimalTitle WHERE id=LEN(@DecimalNumber)),'') SET @Str += ISNULL(' ممیز '+[dbo].[fnNumberToWord_Persian](@DecimalNumber) +' '+@PTitle,'') RETURN @str
مثال: رشته '5445789240.54678000000000'
پنج میلیارد و چهارصد و چهل و پنج میلیون و هفتصد و هشتاد و نه هزار و
دویست و چهل ممیز پنجاه و چهار هزار و ششصد و هفتاد و هشت صد-هزارم دانلود فایل
یک سرویس کوچک ویندوز ان اتی نوشتهام که کارش این است که در پایان هر هفته، تمام دیتابیسهای اس کیوال سرور موجود را یافته و اسکریپت تمام اشیاء آنها را به صورت خودکار تولید میکند (از جداول گرفته تا تریگرها، رویههای ذخیره شده و غیره)، سپس کل مجموعه را فشرده کرده و سپس ایمیل میزند. اینکار برای نگهداری تغییرات انجام شده در طول یک هفته لازم است.
برنامه با استفاده از امکانات SMO تهیه شده است و اگر علاقمند بودید که اینکار را انجام دهید، میتوانید به مقالههای زیر رجوع کنید:
Making a database clone using SMO
Using the SqlServer.Management.Smo
SQL Server: SMO Scripting Basics
با آمدن اس کیوال سرور 2008، اشیاء SMO هم به روز شدهاند و اگر با این اشیاء برنامه نویسی کرده باشید، برنامه بر روی سروری با اس کیوال سرور 2005 اجرا نخواهد شد و پیغام خطای زیر را دریافت خواهید کرد:
خوشبختانه مایکروسافت این کتابخانهها را به صورت مجزا هم برای دریافت قرار داده است و میتوان آنها را نصب نمود تا برنامه بدون اشکال اجرا شود. به صفحه زیر و قسمت Microsoft SQL Server 2008 Management Objects مراجعه نمائید:
اینجا کلیک نمائید
البته همانطور که در صفحه ذکر شده نیز عنوان گردیده است، به MSXML 6.0 هم نیاز میباشد که لینک دریافت آن در ابتدای صفحه فوق موجود است.
- Lead Function:
LEAD ( scalar_expression [ ,offset ] , [ default ] ) OVER ( [ partition_by_clause ] order_by_clause )
- Scalar_expression: در Scalar_expression، نام یک فیلد یا ستون درج میشود، و مقدار برگشتی فیلد مورد نظر، به مقدار تعیین شده offset نیز بستگی دارد. خروجی Scalar_expression فقط یک مقدار است.
- offset: منظور از Offset در این Syntax همانند عملکرد Offset در Syntax مربوط به Over میباشد. یعنی هر عددی برای offset در نظر گرفته شود، بیانگر نقطه آغازین سطر بعدی یا قبلی نسبت به سطر جاری است. به بیان دیگر، عدد تعیین شده در Offset به Sql server میفهماند چه تعداد سطر را در محاسبه در نظر نگیرد.
- Default: زمانی که برای Offset مقداری را تعیین مینمایید، SQL Server به تعداد تعیین شده در Offset، سطرها را در نظر نمیگیرد، بنابراین مقدار خروجی Scalar_expression بطور پیش فرض Null در نظر گرفته میشود، چنانچه بخواهید، مقداری غیر از Null درج نمایید، میتوانید مقدار دلخواه را در قسمت Default وارد کنید.
- (OVER ( [ partition_by_clause ] order_by_clause : در بخش اول بطور کامل توضیح داده شده است.
Create Table TestLead_LAG (SalesOrderID int not null, SalesOrderDetailID int not null , OrderQty smallint not null); GO Insert Into TestLead_LAG Values (43662,49,1),(43662,50,3),(43662,51,1), (43663,52,1),(43664,53,1),(43664,54,1), (43667,77,3),(43667,78,1),(43667,79,1), (43667,80,1),(43668,81,3),(43669,110,1), (43670,111,1),(43670,112,2),(43670,113,2), (43670,114,1),(43671,115,1),(43671,116,2)
مثال:قصد داریم در هر سطر مقدار بعدی فیلد SalesOrderDetailID در فیلد دیگری به نام LeadValue نمایش دهیم، بنابراین Script زیر را ایجاد میکنیم:SELECT s.SalesOrderID,s.SalesOrderDetailID,s.OrderQty, LEAD(SalesOrderDetailID) OVER (ORDER BY SalesOrderDetailID) LeadValue FROM TestLead_LAG s WHERE SalesOrderID IN (43670, 43669, 43667, 43663) ORDER BY s.SalesOrderID,s.SalesOrderDetailID,s.OrderQtyخروجی بصورت زیر خواهد بود:
مطابق شکل، براحتی واضح است، که در هر سطر مقدار بعدی فیلد SalesOrderDetailID در فیلد LeadValue درج و نمایش داده میشود. فقط در سطر 10، چون مقدار بعدی برای فیلد SalesOrderDetailID وجود ندارد، SQL Server مقدار فیلد LeadValue را، Null در نظر میگیرد.در این مثال فقط از آرگومان Scalar_expression، استفاده کردیم، و Offset و Default را مقدار دهی ننمودیم، بنابراین SQL Server بطور پیش فرض هیچ سطری را حذف نمیکند و مقدار Default را Null در نظر میگیرد.مثال دوم: قصد داریم در هر سطر مقدار دو سطر بعدی فیلد SalesOrderDetailID را در فیلد LeadValue نمایش دهیم، و در صورت وجود نداشتن مقدار فیلد SalesOrderDetailID، مقدار پیش فرض صفر ،در فیلد LeadValue قرار دهیم،بنابراین Script آن بصورت زیر خواهد شد:SELECT s.SalesOrderID,s.SalesOrderDetailID,s.OrderQty, LEAD(SalesOrderDetailID,2,0) OVER (ORDER BY SalesOrderDetailID) LeadValue FROM TestLead_LAG s WHERE SalesOrderID IN (43670, 43669, 43667, 43663) ORDER BY s.SalesOrderID,s.SalesOrderDetailID,s.OrderQtyخروجی:
در صورت مسئله بیان کرده بودیم، در هر سطر،مقدار فیلد SalesOrderDetailID دو سطر بعدی، را نمایش دهیم، بنابراین مقداری که برای Offset در نظر میگیریم، برابر دو خواهد بود، سپس گفته بودیم، چنانچه در هر سطر مقدار فیلد SalesOrderDetailID وجود نداشت،بجای مقدار پیش فرض Null،از مقدار صفر استفاده شود، بنابراین به Default مقدار صفر را نسبت دادیم.LEAD(SalesOrderDetailID,2,0)در شکل، مطابق صورت مسئله، مقدار فیلد LeadValue سطر اول برابر است با 78،به بیان سادهتر برای بدست آوردن مقدار فیلد LaedValue هر سطر، میبایست هر سطر را به علاوه 2 (Offset) نماییم، تا سطر بعدی بدست آید، سپس مقدار SalesOrderDetailID را در فیلد LeadValue قرار میدهیم.به سطر 9 و 10 توجه نمایید، که مقدار فیلد LeadValue آنها برابر با صفر است، واضح است، سطر 10 + 2 برابر است با 12( 10+2=12 )، چنین سطری در خروجی نداریم، بنابراین بطور پیش فرض مقدار LeadVaule توسط Sql Server برابر Null در نظر گرفته میشود، اما نمیخواستیم، که این مقدار Null باشد، بنابراین به آرگومان Default مقدار صفر را نسبت دادیم، تا SQL Server ، به جای استفاده از Null، مقدار در نظر گرفته شده صفر را استفاده نماید.اگر چنین فانکشنی وجود نداشت، برای شبیه سازی آن میبایست از Join روی خود جدول استفاده مینمودیم، و یکسری محاسابت دیگر، که کار را سخت مینمود، مثال دوم را با Script زیر میتوان شبیه سازی نمود:WITH cteLead AS ( SELECT SalesOrderID,SalesOrderDetailID,OrderQty, ROW_NUMBER() OVER (ORDER BY SalesOrderDetailID) AS sn FROM TestLead_LAG WHERE SalesOrderID IN (43670, 43669, 43667, 43663) ) SELECT m.SalesOrderID, m.SalesOrderDetailID, m.OrderQty, case when sLead.SalesOrderDetailID is null Then 0 Else sLead.SalesOrderDetailID END as leadvalue FROM cteLead AS m LEFT OUTER JOIN cteLead AS sLead ON sLead.sn = m.sn+2 ORDER BY m.SalesOrderID, m.SalesOrderDetailID, m.OrderQtyجدول موقتی ایجاد نمودیم، که ROW_Number را در آن اضافه کردیم، سپس جدول ایجاد شده را با خود Join کردیم، و گفتیم، که مقدار فیلدLeadValue هر سطر برابر است با مقدار فیلد SalesOrderDetailID دو سطر بعد از آن. و با Case نیز مقدار پیش فرض را صفر در نظر گرفتیم.
- LAG Function:
این فانکشن نیز در SQL Server 2012 ارائه شده است، و امکان دسترسی، به Dataهای سطر قبلی نسبت به سطر جاری را در نتیجه یک پرس و جو (Query)، ارائه میدهد. بدون آنکه از Self-join استفاده نمایید،Syntax آن شبیه به فانکشن Lead میباشد و بصورت زیر است:LAG (scalar_expression [,offset] [,default]) OVER ( [ partition_by_clause ] order_by_clause )Syntax مربوط به فانکشن LAG را شرح نمیدهم، بدلیل آنکه شبیه به فانکشن Lead میباشد، فقط تفاوت آن در Offset است، Offset در فانکشن LAG روی سطرهای ماقبل سطر جاری اعمال میگردد.مثال دوم را برای حالت LAG Function شبیه سازی مینماییم:SELECT s.SalesOrderID,s.SalesOrderDetailID,s.OrderQty, LAG(SalesOrderDetailID,2,0) OVER (ORDER BY SalesOrderDetailID) LAGValue FROM TestLead_LAG s WHERE SalesOrderID IN (43670, 43669, 43667, 43663) ORDER BY s.SalesOrderID,s.SalesOrderDetailID,s.OrderQty goخروجی :
همانطور که گفتیم، LAG Function عکس LEAD Function میباشد. یعنی مقدار فیلد LAGValue سطر جاری برابر است با مقدار SalesOrderDetailID دو سطر ما قبل خود.مقدار فیلد LAGValue دو سطر اول و دوم نیز برابر صفر است، چون دو سطر ماقبل آنها وجود ندارد، و مقدار صفر نیز بدلیل این است که Default را برابر صفر در نظر گرفته بودیم.مثال: در این مثال از Laed Function و LAG Function بطور همزمان استفاده میکنیم، با این تفاوت، که از گروه بندی نیز استفاده شده است:Script زیر را اجرا نمایید:SELECT s.SalesOrderID,s.SalesOrderDetailID,s.OrderQty, Lead(SalesOrderDetailID) OVER (PARTITION BY SalesOrderID ORDER BY SalesOrderDetailID) LeadValue, LAG(SalesOrderDetailID) OVER (PARTITION BY SalesOrderID ORDER BY SalesOrderDetailID) LAGValue FROM TestLead_LAG s WHERE SalesOrderID IN (43670, 43669, 43667, 43663) ORDER BY s.SalesOrderID,s.SalesOrderDetailID,s.OrderQty goخروجی:با بررسی هایی که در مثالهای قبل نمودیم،خروجی زیر را میتوان براحتی تشخیص داد، و توضیح بیشتری نمیدهم.موفق باشید.
برخلاف تصور عموم، کاربرد اصلی دات نت فریم ورک در طی این چندین و چند سالی که از ارائه آن میگذرد، در توسعهی گسترده برنامههای دسکتاپ نبوده است. عمده کاربرد آن در تهیه برنامههای وب است. برای نمونه میتوان به آمارگیری زیر سیستمهای مورد استفاده دات نت در بین برنامه نویسها در سال 2010 مراجعه کرد [^] و کاربردهای وب آن را حداقل باید در جمع استفاده از WebForms ، Ajax و MVC جستجو کرد (البته اگر WCF و ASMX را ندید بگیریم که آنها هم عمده کاربردشان در پروژههای وب است). این اعداد و ارقام سال 2010 را اگر بخواهیم از بیشترین به کمترین لیست کنیم، حاصل آن به صورت زیر درخواهد آمد:
02 - Ajax
03 - WCF
04 - Linq to SQL
05 - MVC
06 - WinForms
07 - ASMX
08 - Silverlight
09 - WPF
10 - ADO DataSets
11 - Entity-Framework (EF)
12 - Workflow
13 - ADO.NET Data Services
14 - DynamicData
15 - CardSpace
مورد دیگری که شاید برای خیلیها جالب توجه باشد، آمار تعداد سایتهایی است که از ASP.NET استفاده میکنند، در مقابل تعداد سایتهایی که بر پایه PHP تهیه شدهاند. مطابق آمار این سایت [^] و [^] در حال حاضر در بین یک میلیون سایت برتر دنیا (سایتهایی که بیشترین ترافیک وب را به خود اختصاص دادهاند) حدود 216 هزار سایت از ASP.NET و 394 هزار سایت از PHP استفاده میکنند. از مابقی وب سایتهای موجود در وب، حدود 27 میلیون سایت از ASP.NET و 26 میلیون سایت از PHP استفاده میکنند. این اعداد و ارقام از این جهت حائز اهمیت هستند که مدت زمان ارائه ASP.NET کمتر از PHP است و همچنین بیشترین کاربرد ASP.NET در سرورهای ویندوزی است، برخلاف PHP که علاوه بر ویندوز، در بین سرورهای لینوکسی نیز گزینهی بسیار محبوبی محسوب میشود.
مروری مختصر بر زبان DMX
برای بسیاری داده کاوی تنها مجموعه ای از تعدادی الگوریتم تعبیر میشود؛ به همان طریقی که در گذشته تصورشان از بانک اطلاعاتی تنها ساختاری سلسله مراتبی به منظور ذخیره دادهها بود. بدین ترتیب داده کاوی به ابزاری تبدیل شده که تنها در انحصار تعدادی متخصص (بویژه PhDهای علم آمار و یادگیری ماشین) قرار دارد که آشنائی با اصطلاحات یک زمینه خاص را دارند. هدف از ایجاد زبان DMX تعریف مفاهیمی استاندارد و گزارهایی متداول است که در دنیای داده کاوی استفاده میشود به شکلی که زبان SQL برای بانک اطلاعاتی این کار را انجام میدهد.
فرضیه اساسی در داده کاوی و همچنین یادگیری ماشین از این قرار است که تعدادی نمونه به الگوریتم نشان داده میشود و الگوریتم با استفاده از این نمونهها قادر است به استخراج الگوها بپردازد. بدین ترتیب به منظور بازبینی و همچنین استنتاج از اطلاعات درباره نمونههای جدید میتواند مورد استفاده قرار گیرد.
ذکر این نکته ضروری است که الگوهای استخراج شده میتوانند مفید، آموزنده و دقیق باشند. تصویر زیر به اختصار مراحل فرآیند داده کاوی را نمایان میسازد:
در گام نخست اقدام به تعریف مسئله و فرموله کردن آن میکنیم که اصطلاحاً Mining Model نامیده میشود. در واقع Mining Model توصیف کننده این است که داده نمونه به چه شکل به نظر میرسد و چگونه الگوریتم داده کاوی باید دادهها را تفسیر کند. در گام بعدی به فراهم کردن نمونههای داده برای الگوریتم میپردازیم، الگوریتم با بهره گیری از Mining Model به طریقی که یک لنز دادهها را مرتب میکند، به بررسی دادهها و استخراج الگوها میپردازد؛ این عملیات را اصطلاحاً Training Model مینامیم. هنگامی که این عملیات به پایان رسید، بسته به اینکه چگونه آنرا انجام داده اید، میتوانید به تحلیل الگوهایی که توسط الگوریتم از روی نمونه هایتان بدست آمده بپردازید. و در نهایت میتوانید اقدام به فراهم کردن دادههای جدید و فرموله کردن آنها، به همان طریقی که نمونهها آموزش دیده اند، به منظور انجام پیش بینی و استنتاج از اطلاعات با استفاده از الگوهای کشف شده توسط الگوریتم پرداخت.
زبان DMX وظیفه تبدیل دادههای موجودتان (سطرها و ستونهای Tables) به دادههای مورد نیاز الگوریتمهای داده کاوی (Cases و Attributes) را دارد. به منظور انجام این تبدیل به Mining Structure و Mining Model (که در قسمت اول به شرح آن پرداخته شد) نیاز است. بطور خلاصه Mining Structure صورت مسئله را توصیف میکند و Mining Model وظیفه تبدیل سطرهای داده ای به درون Caseها و انجام عملیات یادگیری ماشین با استفاده از الگوریتم داده کاوی مشخص شده را بر عهده دارد.
Syntax زبان DMX
مشابه زبان SQL دستورات زبان DMX نیز به محیطی جهت اجرا نیاز دارند که میتوان با استفاده از (SQL Server Management Studio (SSMS به اجرای دستورات DMX اقدام نمود. ایجاد ساختار کاوش (Mining Structure) و مدل کاوشی (Mining Model) مشابه دستورات ایجاد Table در زبان SQL میباشد. همانطور که اشاره شد، گام اول (از سه مرحله اصلی در داده کاوی) ایجاد یک مدل کاوش است؛ شامل تعیین تعداد ستونهای ورودی، ستونهای قابل پیش بینی و مشخص کردن نام الگوریتم مورد استفاده در مدل. گام دوم آموزش مدل که پردازش نیز نامیده میشود و گام سوم مرحله پیش بینی است که نیاز به یک مدل کاوش آموزش دیده و مجموعه اطلاعات جدید دارد. در طول پیش بینی، موتور داده کاوی قوانین (Rules) پیدا شده در مرحلهی آموزش (یادگیری) را با مجموعه اطلاعات جدید تطبیق داده و نتیجه پیش بینی را برای هر Case ورودی انجام میدهد. دو نوع پرس و جوی پیش بینی وجود دارد Batch و Singleton که به ترتیب چند Case ورودی دارد و خروجی در یک جدول ذخیره میشود و دیگری تنها یک Case ورودی دارد و خروجی در زمان اجرا ساخته میشود.
در زبان DMX دو روش برای ساخت مدلهای کاوش وجود دارد:
• ایجاد یک ساختار کاوش و مدل کاوش مربوط به هم و تحت یک نام، زمانی کاربرد دارد که یک ساختار کاوش فقط شامل یک مدل کاوش باشد.
• ایجاد یک ساختار کاوش و سپس اضافه نمودن یک مدل کاوش به ساختار تعریف شده، زمانی کاربرد دارد که یک ساختار کاوش شامل چندین مدل کاوشی باشد. دلایل مختلفی وجود دارد که ممکن است نیاز به این روش باشد، برای مثال ممکن است مدلهای متعددی را با استفاده از الگوریتمهای مختلف ساخت و سپس بررسی نمود که کدام مدل بهتر عمل خواهد کرد و یا مدلهای متعددی را با استفاده از یک الگوریتم ولی با مجموعه پارامترهای متفاوت برای هر مدل ساخت و سپس بهترین را انتخاب نمود.
عناصر سازندهی ساختار کاوش، ستونهای ساختار کاوشی هستند که داده هایی را که منبع اصلی داده فراهم میکند، توصیف میکند. این ستونها شامل اطلاعاتی از قبیل نوع داده (Data Type)، نوع محتوا (Content Type)، ماهیت داده و اینکه داده چگونه توزیع شده است میباشند. نوع محتوا پیوسته و یا گسسته بودن آن را مشخص میکند و بدین ترتیب به الگوریتم راه درست مدل کردن ستون را نشان میدهیم. کلمه کلیدی Discrete برای ماهیت گسسته داده و از کلمه Continuous برای ماهیت پیوسته داده استفاده میشود. مقادیر نوع داده و نوع محتوا به قرار زیر میباشند:
Data Type | کاربرد |
LONG | اعداد صحیح |
DOUBLE | اعداد اعشاری |
TEXT | دادههای رشته ای |
DATE | دادههای تاریخی |
BOOLEAN | دادههای منطقی (True و False) |
TABLE | برای تعریف Nested Case |
Content Type | کاربرد |
KEY | مشخص کننده کلید |
DISCRETE | دادههای گسسته |
CONTINUOUS | دادههای پیوسته |
DISCRETIZED | دادههای گسسته شده |
KEY TIME | کلید زمان، تنها در مدلهای Time Series استفاده میشود |
KEY SEQUENCE | کلید توالی، تنها در بخش Nested Table مدلهای Sequence Clustering استفاده میشود |
همچنین یک مدل کاوش استفاده و کاربرد هر ستون و الگوریتمی که برای ساخت مدل استفاده میشود را تعریف میکند، میتوانید با استفاده از کلمه کلیدی Predict و یا Predict_Only خاصیت پیش بینی را به ستونها اضافه نمود، برای نمونه به دستورات زیر توجه نمائید:
CREATE MINING STRUCTURE [New Mailing] ( CustomerKey LONG KEY, Gender TEXT DISCRETE, [Number Cars Owned] LONG DISCRETE, [Bike Buyer] LONG DISCRETE ) GO ALTER MINING STRUCTURE [New Mailing] ADD MINING MODEL [Naive Bayes] ( CustomerKey, Gender, [Number Cars Owned], [Bike Buyer] PREDICT ) USING Microsoft_Naive_Bayes
به منظور آموزش یک مدل کاوش از دستور Insert به شکل زیر استفاده میشود:
INSERT INTO <mining model name> [<mapped model columns>] <source data query>
در ادامه به شکل عملی میتوانید با طی مراحل و اجرای کوئریهای زیر به بررسی بیشتر موضوع بپردازید.
ابتدا به سرویس SSAS متصل شوید و اقدام به ایجاد یک Database با تنظیمات پیش فرض (مثلاً با نام DM-02) نمائید و در ادامه کوئری XMLA زیر را جهت ایجاد Data Source ای به بانک AdventureWorksDW2012 موجود روی دستگاه تان، اجرا نمائید.
<Create xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"> <ParentObject> <DatabaseID>DM-02</DatabaseID> </ParentObject> <ObjectDefinition> <DataSource xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200" xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300" xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300" xmlns:ddl400="http://schemas.microsoft.com/analysisservices/2012/engine/400" xmlns:ddl400_400="http://schemas.microsoft.com/analysisservices/2012/engine/400/400" xsi:type="RelationalDataSource"> <ID>Adventure Works DW2012</ID> <Name>Adventure Works DW2012</Name> <ConnectionString>Provider=SQLNCLI11.1;Data Source=(local);Integrated Security=SSPI; Initial Catalog=AdventureWorksDW2012</ConnectionString> <ImpersonationInfo> <ImpersonationMode>ImpersonateCurrentUser</ImpersonationMode> </ImpersonationInfo> <Timeout>PT0S</Timeout> </DataSource> </ObjectDefinition> </Create>
/* Step 1 */ CREATE MINING MODEL [NBSample] ( CustomerKey LONG KEY, Gender TEXT DISCRETE, [Number Cars Owned] LONG DISCRETE, [Bike Buyer] LONG DISCRETE PREDICT ) USING Microsoft_Naive_Bayes Go /* Step 2 */ INSERT INTO NBSample (CustomerKey, Gender, [Number Cars Owned], [Bike Buyer]) OPENQUERY([Adventure Works DW2012],'Select CustomerKey, Gender, [NumberCarsOwned], [BikeBuyer] FROM [vTargetMail]') /* */ SELECT * FROM [NBSample].CONTENT /* */ SELECT * FROM [NBSample_Structure].CASES /* Step 3*/ SELECT FLATTENED MODEL_NAME, (SELECT ATTRIBUTE_NAME, ATTRIBUTE_VALUE, [SUPPORT], [PROBABILITY], VALUETYPE FROM NODE_DISTRIBUTION) AS t FROM [NBSample].CONTENT WHERE NODE_TYPE = 26