کاربرد SQL Window Functions

در زبان SQL توابع پنجره (Window Functions) توابعی هستند که از مقادیر یک یا چند سطر برای برگرداندن مقداری برای هر سطر استفاده می‌کنند. مثلا مقادیری مانند ردیف، کمترین، بیشترین، میانگین و یا مجموع یک ستون در دسته خاصی از سطرها را برای ستون مورد نظر محاسبه می کنند.

سیستم اطلاعات جغرافیایی, سامانه اطلاعات جغرافیایی,سامانه اطلاعات مکانی, نرم افزار, ژئوپرتال, GIS, WebGIS, Web-GIS,Software, GIS-Software, Geoportal, Mobile-GIS, SQL Window Functions, Quey, Report

مسئله:

فرض کنید که یک لایه قطعه زمین (Parcel) داریم که نوع کاربری قطعات آن در یک ستون (Landuse)ذخیره شده است، می خواهیم سه قطعه بزرگ هر کاربری را در این لایه انتخاب کنیم.
به روش زیر می توانیم این کار را انجام دهیم:

SELECT [OBJECTID],[Postal_Code] ,[Landuse],[Status],[SHAPE],SHAPE.STArea() as Area
FROM [test].[dbo].[Parcel] p1
WHERE p1.[OBJECTID] IN (
    SELECT TOP 3 [OBJECTID]
    FROM [test].[dbo].[Parcel] p2
    WHERE p2.[Landuse] = p1.[Landuse]
    ORDER BY p2.SHAPE.STArea() DESC
 )
ORDER BY [Landuse],Area DESC

نمونه جستجو

در این روش به ازای مقدار ستون Landuse مربوط به هر سطر یک جستجوی مجزا انجام می شود و نتایج به ترتیب نزولی مساحت قطعه مرتب می¬شوند و سپس سه رکورد ابتدای آن انتخاب می¬شود. چنانچه شناسه رکورد جاری در مجموعه نتایج این جستجو باشد آن رکورد انتخاب می¬شود. اگر بخواهیم از این روش در انتخاب عوارض لایه قطعه در نرم افزار ArcMap استفاده کنیم می¬توانیم به چند روش عمل کنیم:

• از جستویی شبیه این به عنوان عبارت Query Definition استفاده کنیم
• در پایگاه داده یک ویو (View) بسازیم و آن را به عنوان یک لایه جدید به نقشه اضافه کنیم.
• مستقیما از عبارت جستجو برای فیلتر در ابزار Select By Attributes استفاده کنیم. برای این کار می توانیم از عبارتی شبیه زیر در بخش Where استفاده کنیم:

[OBJECTID] IN 
(
 SELECT [OBJECTID]
  FROM [test].[dbo].[Parcel] p1
  WHERE p1.[OBJECTID] IN (
     SELECT TOP 3 [OBJECTID]
     FROM [test].[dbo].[Parcel] p2
     WHERE p2.[Landuse] = p1.[Landuse]
     ORDER BY p2.SHAPE.STArea() DESC
   )
)

اعمال در ابزار جستجو

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

استفاده از تابع پنجره ROW_NUMBER

تابع ROW_NUMBER شماره سطر در دسته مورد نظر را بر می گرداند. برای مثال عبارت زیر:

SELECT [OBJECTID]
	,ROW_NUMBER() OVER ( PARTITION BY Landuse ORDER BY SHAPE.STArea() DESC) AS area_rank  
	,[Postal_Code]
      ,[Landuse]
      ,[Status]
      ,[SHAPE]
      ,SHAPE.STArea() AS Area
  FROM [test].[dbo].[Parcel]
  ORDER BY [Landuse]

یک ستون به نام area_rank به نتایج اضافه می کند که رتبه هر قطعه زمین از نظر بزرگی مساحت را در گروه رکوردهایی که مقادیر Landuse یکسان دارند را بر می گرداند. برای انتخاب سه قطعه بزرگ هر کاربری می توانیم به صورت زیر عمل کنیم:

SELECT * from
(
  SELECT [OBJECTID]
	  ,ROW_NUMBER() OVER ( PARTITION BY Landuse ORDER BY SHAPE.STArea() DESC) AS area_rank  
	  ,[Postal_Code]
      ,[Landuse]
      ,[Status]
      ,[SHAPE]
	  ,SHAPE.STArea() AS Area
    FROM [test].[dbo].[Parcel]
) t
WHERE area_rank < 4
ORDER BY [Landuse]

استفاده از تابع پنجره Row_Number

شبیه حالت قبل می توانیم از عبارتی شبیه زیر در ابزار Select By Attributes استفاده کنیم:

[OBJECTID] IN 
(SELECT [OBJECTID] from
	(
	  SELECT [OBJECTID]
		  ,ROW_NUMBER() OVER ( PARTITION BY Landuse ORDER BY SHAPE.STArea() DESC) AS area_rank  
		  
		FROM [test].[dbo].[Parcel]
	) t
WHERE area_rank < 4
)

اعمال تابع پنجره Row_Number در ابزار جستجو

اعمال در سامانه اطلاعات مکانی Geopack WebGIS

در سامانه اطلاعات مکانی تحت وب ژئوپک (Geopack WebGIS) امکان افزودن ستون های محاسباتی پیچیده به مجموعه نتایج یک جستجو وجود دارد. بنابراین بعد از جستجوی قطعات با شرایط دلخواه در محدوده دلخواه می توان مانند شکل زیر یک ستون براساس تابع پنجره ROW_NUMBER ساخت.

افزودن Row_Number به ستون های جدول نتایج

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

اضافه شدن ستون رتبه مساحت به جدول نتایج

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

نتیجه نهایی - انتخاب سه قطعه بزرگ از هر کاربری

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