Расширенные возможности SQL - Команды SQL в рамках урока #12

-- Оконные функции в SQL (Аналитические функции)
-- https://youtu.be/66PF_Ajn3XI

-------------------------------------------
-- Расширенные возможности SQL. Примеры команд SQL в рамках урока #12.


SELECT
  название_функции(СТОЛБЕЦ_ДЛЯ_ВЫЧИСЛЕНИЙ)
  OVER (partition BY СТОЛБЕЦ_ДЛЯ_ГРУППИРОВКИ ORDER BY СТОЛБЕЦ_ДЛЯ_СОРТИРОВКИ)
FROM
  ...
;


SELECT
  EMPLOYEE_ID, FIRST_NAME, LAST_NAME, JOB_ID, SALARY,
  FIRST_VALUE(FIRST_NAME) OVER (PARTITION BY JOB_ID ORDER BY SALARY DESC) AS HIGHEST_PAID_EMPLOYEE
FROM employees
WHERE JOB_ID IN ('FI_ACCOUNT', 'IT_PROG', 'PU_CLERK')
;


SELECT
  EMPLOYEE_ID, FIRST_NAME, LAST_NAME, JOB_ID, SALARY,
  FIRST_VALUE(FIRST_NAME ||' '|| LAST_NAME) OVER (PARTITION BY JOB_ID ORDER BY SALARY DESC) AS HIGHEST_PAID_EMPLOYEE
FROM employees
WHERE JOB_ID IN ('FI_ACCOUNT', 'IT_PROG', 'PU_CLERK')
;


SELECT
  EMPLOYEE_ID, FIRST_NAME, LAST_NAME, JOB_ID, SALARY,
  FIRST_VALUE(FIRST_NAME ||' '|| LAST_NAME) OVER (PARTITION BY JOB_ID ORDER BY SALARY ASC) AS LOWEST_PAID_EMPLOYEE
FROM employees
WHERE JOB_ID IN ('FI_ACCOUNT', 'IT_PROG', 'PU_CLERK')
;


SELECT
  EMPLOYEE_ID, FIRST_NAME, LAST_NAME, JOB_ID, SALARY,
  FIRST_VALUE(FIRST_NAME ||' '|| LAST_NAME) OVER (PARTITION BY JOB_ID ORDER BY SALARY ASC) AS LOWEST_PAID_EMPLOYEE,
  LAST_VALUE(FIRST_NAME ||' '|| LAST_NAME) OVER (PARTITION BY JOB_ID ORDER BY SALARY DESC) AS LOWEST_PAID_EMPLOYEE_2
FROM employees
WHERE JOB_ID IN ('FI_ACCOUNT', 'IT_PROG', 'PU_CLERK')
;


SELECT
  EMPLOYEE_ID, FIRST_NAME, LAST_NAME, JOB_ID, SALARY,
  FIRST_VALUE(FIRST_NAME ||' '|| LAST_NAME) OVER (PARTITION BY JOB_ID ORDER BY SALARY ASC) AS LOWEST_PAID_EMPLOYEE,
  LAST_VALUE(FIRST_NAME ||' '|| LAST_NAME) OVER (PARTITION BY JOB_ID ORDER BY SALARY DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS LOWEST_PAID_EMPLOYEE_2
FROM employees
WHERE JOB_ID IN ('FI_ACCOUNT', 'IT_PROG', 'PU_CLERK')
;


SELECT
  EMPLOYEE_ID, FIRST_NAME, LAST_NAME, JOB_ID, SALARY,
  FIRST_VALUE(FIRST_NAME ||' '|| LAST_NAME) OVER (PARTITION BY JOB_ID ORDER BY SALARY DESC) AS HIGHEST_PAID_EMPLOYEE
FROM employees
WHERE JOB_ID IN ('FI_ACCOUNT', 'IT_PROG', 'PU_CLERK')
;


SELECT
  EMPLOYEE_ID, FIRST_NAME, LAST_NAME, JOB_ID, SALARY,
  NTH_VALUE(FIRST_NAME ||' '|| LAST_NAME, 1) OVER (PARTITION BY JOB_ID ORDER BY SALARY DESC) AS HIGHEST_PAID_EMPLOYEE
FROM employees
WHERE JOB_ID IN ('FI_ACCOUNT', 'IT_PROG', 'PU_CLERK')
;


SELECT
  EMPLOYEE_ID, FIRST_NAME, LAST_NAME, JOB_ID, SALARY,
  NTH_VALUE(FIRST_NAME ||' '|| LAST_NAME, 2) OVER (PARTITION BY JOB_ID ORDER BY SALARY DESC) AS HIGHEST_PAID_EMPLOYEE
FROM employees
WHERE JOB_ID IN ('FI_ACCOUNT', 'IT_PROG', 'PU_CLERK')
;


SELECT
  EMPLOYEE_ID, FIRST_NAME, LAST_NAME, JOB_ID, SALARY,
  NTH_VALUE(FIRST_NAME ||' '|| LAST_NAME, 2) OVER (PARTITION BY JOB_ID ORDER BY SALARY DESC RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS HIGHEST_PAID_EMPLOYEE
FROM employees
WHERE JOB_ID IN ('FI_ACCOUNT', 'IT_PROG', 'PU_CLERK')
;


SELECT
  JOB_ID,
  NTH_VALUE(SALARY, 3) OVER (PARTITION BY JOB_ID ORDER BY SALARY DESC RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS HIGHEST_PAID_EMPLOYEE
FROM employees
WHERE JOB_ID IN ('FI_ACCOUNT', 'IT_PROG', 'PU_CLERK')
;


SELECT
  DISTINCT JOB_ID,
  NTH_VALUE(SALARY, 3) OVER (PARTITION BY JOB_ID ORDER BY SALARY DESC RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS HIGHEST_PAID_EMPLOYEE
FROM employees
WHERE JOB_ID IN ('FI_ACCOUNT', 'IT_PROG', 'PU_CLERK')
;


SELECT
  EMPLOYEE_ID, FIRST_NAME, LAST_NAME, JOB_ID, SALARY,
  LAG(FIRST_NAME, 1) OVER (PARTITION BY JOB_ID ORDER BY SALARY) AS MIN_SALARY
FROM employees
WHERE JOB_ID IN ('FI_ACCOUNT', 'IT_PROG', 'PU_CLERK')
;


SELECT
  EMPLOYEE_ID, FIRST_NAME, LAST_NAME, JOB_ID, SALARY,
  LAG(FIRST_NAME, 2) OVER (PARTITION BY JOB_ID ORDER BY SALARY) AS MIN_SALARY
FROM employees
WHERE JOB_ID IN ('FI_ACCOUNT', 'IT_PROG', 'PU_CLERK')
;


SELECT
  EMPLOYEE_ID, FIRST_NAME, LAST_NAME, JOB_ID, SALARY,
  LAG(FIRST_NAME, 2, '-') OVER (PARTITION BY JOB_ID ORDER BY SALARY) AS MIN_SALARY
FROM employees
WHERE JOB_ID IN ('FI_ACCOUNT', 'IT_PROG', 'PU_CLERK')
;


SELECT
  EMPLOYEE_ID, FIRST_NAME, LAST_NAME, JOB_ID, SALARY,
  LEAD(FIRST_NAME, 2, '-') OVER (PARTITION BY JOB_ID ORDER BY SALARY) AS MAX_SALARY
FROM employees
WHERE JOB_ID IN ('FI_ACCOUNT', 'IT_PROG', 'PU_CLERK')
;


SELECT
  EMPLOYEE_ID, FIRST_NAME, LAST_NAME, JOB_ID, SALARY,
  LEAD(FIRST_NAME, 1, '-') OVER (PARTITION BY JOB_ID ORDER BY SALARY) AS MAX_SALARY
FROM employees
WHERE JOB_ID IN ('FI_ACCOUNT', 'IT_PROG', 'PU_CLERK')
;


SELECT
  EMPLOYEE_ID, FIRST_NAME, LAST_NAME, JOB_ID, SALARY,
  RATIO_TO_REPORT(SALARY) OVER (PARTITION BY JOB_ID) AS RATIO_SALARY
FROM employees
WHERE JOB_ID IN ('FI_ACCOUNT', 'IT_PROG')
;


SELECT
  EMPLOYEE_ID, FIRST_NAME, LAST_NAME, JOB_ID, SALARY,
  RATIO_TO_REPORT(SALARY) OVER (PARTITION BY JOB_ID ORDER BY SALARY) AS RATIO_SALARY
FROM employees
WHERE JOB_ID IN ('FI_ACCOUNT', 'IT_PROG')
;


SELECT
  EMPLOYEE_ID, FIRST_NAME, LAST_NAME, JOB_ID, SALARY,
  RATIO_TO_REPORT(SALARY) OVER () AS RATIO_SALARY
FROM employees
WHERE JOB_ID IN ('FI_ACCOUNT', 'IT_PROG')
;


SELECT
  EMPLOYEE_ID, FIRST_NAME, LAST_NAME, DEPARTMENT_ID, JOB_ID, SALARY,
  RATIO_TO_REPORT(SALARY) OVER (PARTITION BY DEPARTMENT_ID, JOB_ID) AS RATIO_SALARY
FROM employees
WHERE JOB_ID IN ('FI_ACCOUNT', 'IT_PROG')
;