🔸window functions

در این بخش قصد دارم راجع به توابع پنجره ای (توابع تحلیلی) مطالبی را ارائه دهم

 

در ابتدا به معرفی توابع تحلیلی پرداخته و در ادامه با استفاده از مثالهایی به معرفی قابلیت ها و گزینه های موجود خواهم پرداخت

توابع تحلیلی چیست ؟

توابع تحلیلی از دسته توابع SQL هستند که امکان پردازش در داده های خروجی یک دستور پرس و جو را میدهند ، به سوالات زیر توجه کنید

سوال1: دستور پرس و جویی بنویسید تا در ستونی جداگانه مجوع حقوق تمامی کارمندان را نمایش دهد؟

سوال2: شناسه و نام دومین کارمندی که در هر سازمان بیشترین حقوق را دریافت میکند را در ستونی جداگانه نمایش دهید؟

سوال3: کمترین و بیشترین تاریخ استخدام کارمندان شاغل در هر سازمان را در ستونهای جداگانه نمایش دهید؟

سوال4: حقوق قبلی هر کارمند را بر اساس ترتیب تاریخ استخدام نمایش دهید؟

سوال5: به ازای کارمندان هر سازمان و به ترتیب تاریخ استخدام شماره ردیفی نمایش دهید که در هر سازمان از عدد 1 شروع شود؟

سوال6: مجموع حقوق کارمندان قبلی را تا کارمندی جاری در ستونی جداگانه نمایش دهید؟

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

✅ میخواهیم تعداد رکوردهای پرس و جوی اصلی حفظ شود و در ستونی جداگانه نتیجه تحلیل نمایش داده شود

(بر خلاف دستور group by که تعداد رکوردهای خروجی را گروه بندی میکند)

✅ پیاده سازی سوال مورد نظر با دستور پرس و جوی معمولی بسیار پیچیده است

✅ میخواهیم در هر رکورد به مقداری از رکورد(های) قبلی یا بعدی دسترسی پیدا کنیم

✅ و …

 

🖌توابع پنجره ای (تحلیلی) دارای بخش های زیر است:🖌

1️⃣ نام تابع تحلیلی:
یکی از توابع min ، max ، count(*) ، first_value ، last_value ، lag ، lead ، rank و ….

2️⃣ آرگومان ورودی

3️⃣ کلمه کلیدی over()

4️⃣عباراتی که در داخل over نوشته میشود مانند:
🔹 partition by: برای گروه بندی استفاده میشود و اختیاری است
🔹 order by: برای مرتب سازی دامنه عملکرد استفاده میشود که در برخی از توابع اختیاری و در برخی دیگر اجباری است
🔹 rows between … folowing and … preceding: به منظور محدود کردن دامنه عملکرد استفاده میشود

قبل از پاسخ به سوالات مطرح شده به خروجی جدول کارمندان دقت کنید 👇👇👇👇👇👇👇

سوال1: دستور پرس و جویی بنویسید تا در ستونی جداگانه مجوع حقوق تمامی کارمندان را نمایش دهد؟
پاسخ سوال1:

select emp.employee_id,
emp.first_name,
to_char(emp.hire_date, ‘yyyy/mm/dd’, ‘nls_calendar=persian’) per_hire_date,
emp.salary,
emp.department_id,
sum(emp.salary) over() sum_emp

from employees emp
where emp.department_id in (10,20,30,40,60, 70 , 100 , 110)

سوال2: شناسه و نام دومین کارمندی که در هر سازمان بیشترین حقوق را دریافت میکند را در ستونی جداگانه نمایش دهید؟
پاسخ سوال 2:

select *
from (select emp.employee_id,
emp.first_name,
to_char(emp.hire_date, ‘yyyy/mm/dd’, ‘nls_calendar=persian’) per_hire_date,
emp.salary,
emp.department_id,
row_number() over(partition by emp.department_id order by salary desc) rn

from employees emp
where emp.department_id in (10, 20, 30, 40, 60, 70, 100, 110)) tbl_data
where rn = 2

سوال3: کمترین و بیشترین تاریخ استخدام کارمندان شاغل در هر سازمان را در ستونهای جداگانه نمایش دهید؟
پاسخ سوال3:

select emp.employee_id,
emp.first_name,
to_char(emp.hire_date, ‘yyyy/mm/dd’, ‘nls_calendar=persian’) per_hire_date,
emp.salary,
emp.department_id,
to_char(min(emp.hire_date) over(partition by emp.department_id),
‘yyyy/mm/dd’,
‘nls_calendar=persian’) min_hire_date_at_department,
to_char(max(emp.hire_date) over(partition by emp.department_id),
‘yyyy/mm/dd’,
‘nls_calendar=persian’) max_hire_date_at_department

from employees emp
where emp.department_id in (10, 20, 30, 40, 60, 70, 100, 110)

سوال4: حقوق قبلی هر کارمند را بر اساس ترتیب تاریخ استخدام نمایش دهید؟
پاسخ سوال 4:

select emp.employee_id,
emp.first_name,
to_char(emp.hire_date, ‘yyyy/mm/dd’, ‘nls_calendar=persian’) per_hire_date,
emp.salary,
emp.department_id,
lag(emp.salary, 1, null) over(order by emp.hire_date) emp_salary_previous

from employees emp
where emp.department_id in (10, 20, 30, 40, 60, 70, 100, 110)

سوال5: به ازای کارمندان هر سازمان و به ترتیب تاریخ استخدام شماره ردیفی نمایش دهید که در هر سازمان از عدد 1 شروع شود؟
پاسخ سوال 5:

select emp.employee_id,
emp.first_name,
to_char(emp.hire_date, ‘yyyy/mm/dd’, ‘nls_calendar=persian’) per_hire_date,
emp.salary,
emp.department_id,
row_number() over(partition by emp.department_id order by emp.hire_date) rn

from employees emp
where emp.department_id in (10,20,30,40,60, 70 , 100 , 110)

سوال6: مجموع حقوق کارمندان قبلی را تا کارمندی جاری در ستونی جداگانه نمایش دهید؟
پاسخ سوال 6:

select emp.employee_id,
emp.first_name,
to_char(emp.hire_date, ‘yyyy/mm/dd’, ‘nls_calendar=persian’) per_hire_date,
emp.salary,
emp.department_id,
sum(emp.salary) over(order by emp.hire_date rows between unbounded preceding and current row) sum_from_first_to_current_row

from employees emp
where emp.department_id in (10, 20, 30, 40, 60, 70, 100, 110)

 

دانلود فایل pdf