آموزش Window Functions

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)

برای مشاهده کامل مطلب کلیک کنید