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

-- Оконные функции в SQL (Ранжирующие функции)
-- https://youtu.be/lCbAl-pWveQ

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


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


SELECT
  FIRST_NAME, LAST_NAME, JOB_ID, SALARY,
  ROW_NUMBER() OVER (PARTITION BY JOB_ID ORDER BY SALARY) AS ORDER_SALARY
FROM employees
;


SELECT
  FIRST_NAME, LAST_NAME, JOB_ID, SALARY,
  ROW_NUMBER() OVER (PARTITION BY JOB_ID ORDER BY SALARY DESC) AS ORDER_SALARY
FROM employees
;


SELECT
  FIRST_NAME, LAST_NAME, JOB_ID, SALARY,
  ROW_NUMBER() OVER (PARTITION BY JOB_ID ORDER BY SALARY) AS FN_ROW_NUMBER,
  RANK() OVER (PARTITION BY JOB_ID ORDER BY SALARY) AS FN_RANK,
  DENSE_RANK() OVER (PARTITION BY JOB_ID ORDER BY SALARY) AS FN_DENSE_RANK
FROM employees
;


SELECT
  FIRST_NAME, LAST_NAME, JOB_ID, SALARY,
  NTILE(10) OVER (PARTITION BY JOB_ID ORDER BY SALARY) AS FN_NTILE
FROM
  (
    SELECT * FROM employees
    WHERE SALARY BETWEEN 6000 AND 7300
    AND JOB_ID = 'SA_REP'
  )
;