LEAD ( scalar_expression [ ,offset ] , [ 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

خروجی:

با بررسی هایی که در مثالهای قبل نمودیم،خروجی زیر را می‌توان براحتی تشخیص داد، و توضیح بیشتری نمی‌دهم.

موفق باشید.

اگر دایمنشنی بیشتر از یک ساختار سلسله مراتبی باشد ، باید حتما مشخص شود که اعضای کدام ساختار سلسله مراتبی مورد نظر ما می باشد . بنابراین کوئری‌های زیر دارای خطا می‌باشند.

Select
[Customer].members on columns
From [Adventure Works]
GO
Select
[Scenario].members on columns
From [Adventure Works]

Select
[Measures].[Internet Sales Amount] on columns,
[Customer].[Customer Geography].members on rows
From [Adventure Works]

Select
[Measures].[Internet Sales Amount] on columns,
[Customer].[Customer Geography].[Country].members on rows
From [Adventure Works]

Select
[Measures].[Internet Sales Amount] on columns,
[Customer].[Customer Geography].[Country].[France].members on rows
From [Adventure Works]

Select
[Measures].[Internet Sales Amount] on columns,
[Customer].[Customer Geography].[Country].[France].children on rows
From [Adventure Works]

برای بدست آوردن بچه های یک عضو در یک ساختار سلسله مراتبی، از  children استفاده می شود .

دقت داشته باشید، همانگونه که عضو، دارای Members نمی‌باشد، یک بچه هم دارای Children نمی‌باشد. بنابراین کوئری زیر با خطا مواجه می‌شود.

Select
[Measures].[Internet Sales Amount] on columns,
[Customer].[Customer Geography].[Country].[France].children.children on rows
From [Adventure Works]

البته شاید تصور می‌شد که باید به عنوان یک ساختار سلسله مراتبی بعد از کشور، استان و سپس شهر‌ها واکشی شوند. اما برای واکشی ساختار سلسله مراتبی باید به صورت زیر عمل کرد.

Select
[Measures].[Internet Sales Amount] on columns,
descendants(
[Customer].[Customer Geography].[Country].[France],
[Customer].[Customer Geography].[City]
) on rows
From [Adventure Works]

عملا برای یافتن شهر‌های کشور فرانسه، باید دو سطح در ساختار سلسله مراتبی پایین برویم. برای این منظور از تابع descendants () استفاده کرده‌ایم و به خاطر داشته باشید که نوشتن [France].children.children مجاز نمی‌باشد. همچنین در تابع فوق دو پارامتر ورودی داریم؛ اولی مشخص کننده‌ی یک عضو از سطح مبدا می‌باشد و دومین پارامتر مشخص کننده‌ی سطحی است که باید واکشی در آن صورت بگیرد.

برای مشخص‌تر شدن موضوع به مثال زیر توجه کنید

Select
[Measures].[Internet Sales Amount] on columns,
descendants(
[Customer].[Customer Geography].[Country].[France],
[Customer].[Customer Geography].[Customer]
) on rows
From [Adventure Works]

در این مثال واکشی در چهار سطح پایین‌تر انجام شده است و به این معنی می‌باشد که ما مشتریان کشور فرانسه را در ردیف‌ها واکشی کرده‌ایم.

عدم وجود یک Member :

در صورت عدم وجود یک عضو در واکشی، خروجی خالی می‌باشد و خطا نمایش داده نمی‌شود.

Select
[Customer].[Customer Geography].[Customer].[Crystal Zhen] on columns
From [Adventure Works]

مشتری با نام Crystal Zhen وجود ندارد؛ بنابراین نتیجه خالی می‌باشد.

پیدا کردن یک عضو در مجموعه ایی از اعضا، البته اگر این عضو درآ  ن مجموعه وجود نداشته باشد خروجی خالی می باشد.

Select
exists(
[Customer].[Customer Geography].[Customer].[Crystal Zheng],
[Customer].[Customer Geography].members
  )on columns
From [Adventure Works]

در نوشتن کوئری‌ها دقت کنید که حتما بین دایمنشن‌ها و شاخص‌ها در SSAS ارتباط ایجاد شده باشد. در غیر این صورت خروجی نامعتبر خواهد بود. به عنوان مثال در کوئری زیر بین شاخص [Measures].[Reseller Sales Amount] و مشتری ارتباطی وجود ندارد (این ارتباط باید در هنگام ساخت Cube  در SSAS  ایجاد می‌گردید تا در هنگام Deploy  کردن محاسبات لازم صورت بگیرد) بنابر این در خروجی شاهد نمایش شاخص پیش فرض Cube می‌باشیم.

Select
[Measures].[Reseller Sales Amount] on columns,
[Customer].[Customer Geography].[Customer].members on rows
From [Adventure Works]

قبلا گفته شد که هر سطح دارای Members می‌باشد و میتوان برای هر عضو Children‌های آن عضو را نمایش داد، با این وجود دقت کنید که اعضای آخرین سطح ساختار سلسله مراتبی دارای Children نمی‌باشند.

Select
[Measures].[Internet Sales Amount] on columns,
[Customer].[Customer Geography].[Customer].[Crystal Zheng].children on rows
From [Adventure Works]

خروجی خالی می‌باشد.

بدست آوردن پدر یک عضو در ساختار سلسله مراتبی :

کوئری زیر را اجرا کنید

Select
[Measures].[Internet Sales Amount] on columns,
[Customer].[Customer Geography].[Customer].[Crystal Zheng].parent on rows
From [Adventure Works]

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

برای بدست آوردن پدربزرگ یک ساختار سلسله مراتبی داریم :

Select
[Measures].[Internet Sales Amount] on columns,
[Customer].[Customer Geography].[Customer].[Crystal Zheng].parent.parent on rows
From [Adventure Works]

و برای بدست آوردن پدر در 4 سطح بالاتر داریم

Select
[Measures].[Internet Sales Amount] on columns,
[Customer].[Customer Geography].[Customer].[Crystal Zheng].parent.parent.parent.parent
on rows
From [Adventure Works]

در صورتیکه با بالا رفتن از سطوح یک ساختار سلسله مراتبی از ریشه رد شویم ، خروجی خالی بر می‌گردد .

Select
[Measures].[Internet Sales Amount] on columns,
[Customer].[Customer Geography].[Customer].[Crystal Zheng].parent
.parent.parent.parent.parent.parent
on rows
From [Adventure Works]

برای بدست آوردن نیا می‌توانیم از تابع ancestor() استفاده کنیم.

بدست آوردن نیا برای یک مشتری در سطح شهر

Select
[Measures].[Internet Sales Amount] on columns,
ancestor(
[Customer].[Customer Geography].[Customer].[Crystal Zheng],
[Customer].[Customer Geography].[City]
)on rows
From [Adventure Works]

بدست آوردن نیا برای یک مشتری در سطح شهر

Select
[Measures].[Internet Sales Amount] on columns,
ancestor(
[Customer].[Customer Geography].[Customer].[Crystal Zheng],
[Customer].[Customer Geography].[City]
)on rows
From [Adventure Works]

پارامتر دوم در تابع ancestor می‌تواند هم مشخص کننده‌ی یک سطح باشد توسط نام آن سطح و هم می‌تواند یک عدد باشد که مشخص کننده‌ی تعداد سطحی است که باید در واکشی اطلاعات از ساختار سلسله مراتبی بالا برویم.

Select
[Measures].[Internet Sales Amount] on columns,
ancestor(
[Customer].[Customer Geography].[Customer].[Crystal Zheng],
2
) on rows
From [Adventure Works]

بدست آوردن نیا برای یک مشتری در دو سطح بالاتر

در صورتی که بخواهیم تمامی سطوح بالا‌تر از یک سطح را داشته باشیم و بعد هم سر جمع داشته باشیم، به صورت زیر عمل می‌کنیم و از تابع ascendants استفاده می‌کنیم.

Select
[Measures].[Internet Sales Amount] on columns,
ascendants(
[Customer].[Customer Geography].[Customer].[Crystal Zheng]
  ) on rows
From [Adventure Works]

دقت داشته باشید که در این تابع تمامی سطوح از سطح مشخص شده با سمت ریشه باز گردانده می‌شوند. در حالیکه در تابع ancestor فقط سطح مشخص شده در ساختار سلسله مراتبی به سمت ریشه باز گردانده می‌شد.

مرتب سازی اعضای یک ساختار سلسله مراتبی از بالا به پایین به صورت نزولی

select
[Measures].[Internet Sales Amount] on columns,
hierarchize(
ascendants(
[Customer].[Customer Geography].[Customer].[Crystal Zheng]
)
) on rows
From [Adventure Works]