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

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

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


SELECT
  название_функции(СТОЛБЕЦ_ДЛЯ_ВЫЧИСЛЕНИЙ)
  OVER
  (
    partition BY СТОЛБЕЦ_ДЛЯ_ГРУППИРОВКИ
    ORDER BY СТОЛБЕЦ_ДЛЯ_СОРТИРОВКИ
    ROWS или RANGE выражение для ограничения строк в пределах группы
  )
FROM
  ...
;


SELECT
  EMPLOYEE_ID, FIRST_NAME, LAST_NAME, HIRE_DATE, JOB_ID, SALARY,
  SUM(SALARY) OVER (partition BY JOB_ID ORDER BY HIRE_DATE ROWS unbounded preceding) AS SUM_SALARY
FROM employees
WHERE JOB_ID = 'IT_PROG' OR (JOB_ID = 'SH_CLERK' AND HIRE_DATE >= '07.02.2007')
;


SELECT
  EMPLOYEE_ID, FIRST_NAME, LAST_NAME, HIRE_DATE, JOB_ID, SALARY,
  SUM(SALARY) OVER (partition BY JOB_ID ORDER BY HIRE_DATE ROWS unbounded preceding) AS SUM_SALARY_1,
  SUM(SALARY) OVER (partition BY JOB_ID ORDER BY HIRE_DATE ROWS BETWEEN unbounded preceding AND CURRENT ROW) AS SUM_SALARY_2
FROM employees
WHERE JOB_ID = 'IT_PROG' OR (JOB_ID = 'SH_CLERK' AND HIRE_DATE >= '07.02.2007')
;


SELECT
  EMPLOYEE_ID, FIRST_NAME, LAST_NAME, HIRE_DATE, JOB_ID, SALARY,
  SUM(SALARY) OVER (partition BY JOB_ID ORDER BY HIRE_DATE ROWS BETWEEN unbounded preceding AND CURRENT ROW) AS SUM_SALARY_2,
  SUM(SALARY) OVER (partition BY JOB_ID ORDER BY HIRE_DATE ROWS BETWEEN CURRENT ROW AND unbounded following) AS SUM_SALARY_3
FROM employees
WHERE JOB_ID = 'IT_PROG' OR (JOB_ID = 'SH_CLERK' AND HIRE_DATE >= '07.02.2007')
;


SELECT
  EMPLOYEE_ID, FIRST_NAME, LAST_NAME, HIRE_DATE, JOB_ID, SALARY,
  SUM(SALARY) OVER (partition BY JOB_ID ORDER BY JOB_ID ROWS 2 preceding) AS SLIDING_TOTAL
FROM employees
WHERE JOB_ID = 'IT_PROG' OR (JOB_ID = 'SH_CLERK' AND HIRE_DATE >= '07.02.2007')
;


SELECT
  EMPLOYEE_ID, FIRST_NAME, LAST_NAME, HIRE_DATE, JOB_ID, SALARY,
  SUM(SALARY) OVER (partition BY JOB_ID ORDER BY JOB_ID ROWS 1 preceding) AS SLIDING_TOTAL
FROM employees
WHERE JOB_ID = 'IT_PROG' OR (JOB_ID = 'SH_CLERK' AND HIRE_DATE >= '07.02.2007')
;


SELECT
  EMPLOYEE_ID, FIRST_NAME, LAST_NAME, HIRE_DATE, JOB_ID, SALARY,
  SUM(SALARY) OVER (partition BY JOB_ID ORDER BY JOB_ID ROWS BETWEEN CURRENT ROW AND 1 following) AS SLIDING_TOTAL
FROM employees
WHERE JOB_ID = 'IT_PROG' OR (JOB_ID = 'SH_CLERK' AND HIRE_DATE >= '07.02.2007')
;


SELECT
  EMPLOYEE_ID, FIRST_NAME, LAST_NAME, HIRE_DATE, JOB_ID, SALARY,
  avg(SALARY) OVER (ORDER BY HIRE_DATE ASC ROWS 100 preceding) AS SLIDING_TOTAL
FROM employees
;


SELECT
  EMPLOYEE_ID, FIRST_NAME, LAST_NAME, HIRE_DATE, SALARY,
  avg(SALARY) OVER (ORDER BY HIRE_DATE ROWS 3 preceding) AS AVG_SALARY
FROM employees
WHERE JOB_ID = 'FI_ACCOUNT' OR (JOB_ID = 'SH_CLERK' AND HIRE_DATE < '01.04.2005')
;


SELECT
  EMPLOYEE_ID, FIRST_NAME, LAST_NAME, HIRE_DATE, SALARY,
  avg(SALARY) OVER (ORDER BY HIRE_DATE ROWS 3 preceding) AS AVG_SALARY,
  avg(SALARY) OVER (ORDER BY HIRE_DATE range BETWEEN INTERVAL '3' MONTH preceding AND CURRENT ROW) AS AVG_SALARY_2
FROM employees
WHERE JOB_ID = 'FI_ACCOUNT' OR (JOB_ID = 'SH_CLERK' AND HIRE_DATE < '01.04.2005')
;


SELECT
  EMPLOYEE_ID, FIRST_NAME, LAST_NAME, HIRE_DATE, JOB_ID, SALARY,
  SUM(SALARY) OVER (partition BY JOB_ID ORDER BY HIRE_DATE ROWS BETWEEN unbounded preceding AND CURRENT ROW) AS ROWS_SALARY,
  SUM(SALARY) OVER (partition BY JOB_ID ORDER BY HIRE_DATE range BETWEEN unbounded preceding AND CURRENT ROW) AS RANGE_SALARY
FROM employees
WHERE JOB_ID = 'IT_PROG' OR (JOB_ID = 'SH_CLERK' AND HIRE_DATE >= '07.02.2007')
;


SELECT
  EMPLOYEE_ID, FIRST_NAME, LAST_NAME, HIRE_DATE, SALARY,
  SUM(SALARY) OVER (ORDER BY HIRE_DATE) AS SUM_SALARY
FROM employees
WHERE JOB_ID = 'IT_PROG' OR (JOB_ID = 'SH_CLERK' AND HIRE_DATE >= '07.02.2007')
;


SELECT
  EMPLOYEE_ID, FIRST_NAME, LAST_NAME, HIRE_DATE, SALARY,
  SUM(SALARY) OVER (ORDER BY HIRE_DATE) AS SUM_SALARY,
  SUM(SALARY) OVER (ORDER BY HIRE_DATE range BETWEEN unbounded preceding AND CURRENT ROW) AS SUM_SALARY_2
FROM employees
WHERE JOB_ID = 'IT_PROG' OR (JOB_ID = 'SH_CLERK' AND HIRE_DATE >= '07.02.2007')
;


SELECT
  EMPLOYEE_ID, FIRST_NAME, LAST_NAME, HIRE_DATE, SALARY,
  SUM(SALARY) OVER (ORDER BY HIRE_DATE) AS SUM_SALARY,
  SUM(SALARY) OVER (ORDER BY HIRE_DATE range BETWEEN unbounded preceding AND CURRENT ROW) AS SUM_SALARY_2,
  SUM(SALARY) OVER () AS SUM_SALARY_3
FROM employees
WHERE JOB_ID = 'IT_PROG' OR (JOB_ID = 'SH_CLERK' AND HIRE_DATE >= '07.02.2007')
;