ExcelStore

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

May 9th, 2021
2,139
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. --Расширенные возможности SQL
  2. --Команды SQL в рамках урока #11
  3.  
  4.  
  5. SELECT
  6.   название_функции(СТОЛБЕЦ_ДЛЯ_ВЫЧИСЛЕНИЙ)
  7.   OVER (partition BY СТОЛБЕЦ_ДЛЯ_ГРУППИРОВКИ ORDER BY СТОЛБЕЦ_ДЛЯ_СОРТИРОВКИ)
  8. FROM
  9.   ...
  10. ;
  11.  
  12.  
  13. SELECT
  14.   FIRST_NAME, LAST_NAME, JOB_ID, SALARY,
  15.   ROW_NUMBER() OVER (PARTITION BY JOB_ID ORDER BY SALARY) AS ORDER_SALARY
  16. FROM employees
  17. ;
  18.  
  19.  
  20. SELECT
  21.   FIRST_NAME, LAST_NAME, JOB_ID, SALARY,
  22.   ROW_NUMBER() OVER (PARTITION BY JOB_ID ORDER BY SALARY DESC) AS ORDER_SALARY
  23. FROM employees
  24. ;
  25.  
  26.  
  27. SELECT
  28.   FIRST_NAME, LAST_NAME, JOB_ID, SALARY,
  29.   ROW_NUMBER() OVER (PARTITION BY JOB_ID ORDER BY SALARY) AS FN_ROW_NUMBER,
  30.   RANK() OVER (PARTITION BY JOB_ID ORDER BY SALARY) AS FN_RANK,
  31.   DENSE_RANK() OVER (PARTITION BY JOB_ID ORDER BY SALARY) AS FN_DENSE_RANK
  32. FROM employees
  33. ;
  34.  
  35.  
  36. SELECT
  37.   FIRST_NAME, LAST_NAME, JOB_ID, SALARY,
  38.   NTILE(10) OVER (PARTITION BY JOB_ID ORDER BY SALARY) AS FN_NTILE
  39. FROM
  40.   (
  41.     SELECT * FROM employees
  42.     WHERE SALARY BETWEEN 6000 AND 7300
  43.     AND JOB_ID = 'SA_REP'
  44.   )
  45. ;
  46.  
  47.  
RAW Paste Data