<style>
body { overflow-x: hidden; color: #312928; background: #f5f3ed url(https://i.imgur.com/B8wy02N.png) top left repeat } :root { --color-primary: #004d40; --color-primary-dark: #1c1c1e; --color-primary-darker: #111; --color-primary-light: #39796b; --color-secondary: #00838f; --color-secondary-dark: #005662; --color-secondary-light: #4fb3bf; --color-accent: #9d76ff; --border-color: #080808; --text-color-light: #8effff; --text-color-bright: #fff; --text-color-dark: #333 } #doc { display: block; width: 100%; margin: 31px auto 31px; background: #f5f3ed url(https://i.imgur.com/S2fW9vg.jpg) top left repeat; border-image-width: 100px; border: 32px solid; border-image: url(https://i.imgur.com/XzF7kg2.png) 38 round round } .tool-bar>a.fa { color: var(--color-primary-light) !important; border: none } .tool-bar>a.fa:hover { color: var(--text-color-bright) !important } .ui-infobar__user-info>ul>li>div>.flex>span { color: var(--text-color-light) !important } .markdown-body h1, .markdown-body h2, .markdown-body h3, .markdown-body h4, .markdown-body h5, .markdown-body h6 { font-weight: 500; text-align: left; border: none; text-align: center; font-size: 80px; color: #424242; font-family: cursive } .markdown-body h1, .markdown-body h2, .markdown-body h3, .markdown-body h4 { padding-top: 35px; text-align: center }} .markdown-body h1 { font-size: 3.7em; text-align: center } .markdown-body h2 { font-size: 1.5em } .markdown-body h3 { font-size: 1.4em } .markdown-body h4 { font-size: 1.3em } .markdown-body h5 { font-size: 1.2em } .markdown-body h6 { font-size: .8em } .markdown-body hr { background-color: var(--color-secondary); margin: 1.25em 0; height: 1.1px } .markdown-body blockquote, .markdown-body blockquote blockquote { border-left-color: var(--color-secondary); color: #999; padding: 0 !important; padding-right: 5em !important } .markdown-body blockquote:before { color: var(--color-secondary) } .markdown-body blockquote small, .markdown-body blockquote i.fa { color: var(--color-accent); opacity: .8 } .markdown-body table { display: table; padding: 1em; margin-left: 1em; width: calc(100% - 2em) } .markdown-body table th, .markdown-body table td, .markdown-body table tr { border: none !important } .markdown-body table tr { background-color: transparent !important; border-bottom: 1px solid rgba(255, 255, 255, .3) !important } .markdown-body .sequence-diagram rect[stroke=none] { fill: #000; opacity: .2; filter: invert(1) } .markdown-body .graphviz polygon { fill: transparent } .markdown-body .vega svg { background-color: transparent !important } .markdown-body .fretboard .svg_wrapper { background-color: transparent } .markdown-body .alert { color: #fff; position: relative; z-index: 1; padding: 0; border: none; font-weight: 500 } .markdown-body p { padding: 0 1em } .markdown-body .alert-success:before, .markdown-body .alert-info:before, .markdown-body .alert-warning:before, .markdown-body .alert-danger:before { content: ' '; position: absolute; z-index: -1; width: 100%; height: 100% } h1:after, h2:after, h3:after, h4:after, h3.md-focus:before, h4.md-focus:before, h3.md-focus:after, h4.md-focus:after { content: ''; display: block; background-repeat: no-repeat; background-position: top center; background-size: auto 80%; width: 100%; z-index: 1; margin: 0; padding: 20px 30px; border: 0 } h1:after, h2:after, h3:after, h4:after { background-image: url("https://lh3.googleusercontent.com/wJsGW_MA3V4aC2clzv0Z0OAVWwdXE11CFoA2LjG-1AavRukDFaCBQU5HqJKe9oi57buAMrsCEkd9XJeIBfGjP4h7HzOKhIjANL94EnNrD8GADHG7I-3GFLdHVq_bF1DJSYCPj9tu5CPzv4Lb4an0Ef93_CRbZ--6QyqFrNWzHz5XL-iRrzbrrA") } table { width: 550px; margin: 24px auto; border-collapse: separate; border-spacing: 4px } tr:nth-child(odd) { background-color: #f0f8ff } tr:nth-child(even) { background-color: #ffb6c1 } td { vertical-align: top; padding: 8px 16px } td:first-child { text-align: center } td:not(:first-child) { text-align: left } #doc pre { background-color: #f4f3ee !important; border-image-width: 100px !important; border: 32px solid !important; border-image: url(https://i.imgur.com/XzF7kg2.png) 38 round round !important } .ui-infobar { margin: 0; max-width: none; width: 100%; height: 80px; display: flex; justify-content: flex-end; align-items: center; box-shadow: 0 1px 10px rgb(0 0 0/20%), 0 1px 1px rgb(0 0 0/20%); box-sizing: border-box; margin-bottom: 100px; position: relative; background: #f5f3ed url(https://i.imgur.com/D3JHgvv.png) top left repeat } .ui-infobar:after { content: ''; position: absolute; z-index: -1; bottom: -300px; display: block; width: 100%; height: 300px; background: url(https://i.imgur.com/kZDzETE.png) no-repeat, url(https://i.imgur.com/visfeOY.png) repeat-x }
#doc, .toc { z-index: 100; } .container-fluid { padding-right: 0; padding-left: 15px; margin-right: auto; margin-left: auto } #ui-toc-affix { z-index: 1000; font-size: 1.2em } .ui-comment-app { right: 100px !important; top: 100px } .ui-toc-dropdown .nav>li>a { font-size: 25px } .ui-comment-container { margin-left: 55px !important } .ribbon-container { background: 0 0 !important; position: relative; z-index: 1; top: -30px } .ribbon { background: #f6a655 url(http://portfolio.queenofwhisks.com/demo/overlay/noise.png); box-shadow: 2px 6px 12px rgba(0, 0, 0, .5); font-family: Charter, serif; font-size: 16px; line-height: 1; text-align: center; margin: 0 auto; padding: .25em 0; position: relative; width: 86%; top: 80px } .ribbon:before, .ribbon:after { border: 1.5em solid #ee9e4f; content: ''; display: block; position: absolute; bottom: -1em; z-index: -1 } .ribbon:before { left: -2em; border-right-width: 1.5em; border-left-color: transparent; box-shadow: 2px 2px 2px rgba(0, 0, 0, .5) } .ribbon:after { right: -2em; border-left-width: 1.5em; border-right-color: transparent; box-shadow: -2px 2px 2px rgba(0, 0, 0, .5) } .ribbon-content { border-top: .0625em dashed #d5700b; border-bottom: .0625em dashed #d5700b; box-shadow: inset 0px 1px 1px rgba(0, 0, 0, .08), inset 0px -1px 1px rgba(255, 255, 255, .08), 0px -1px 1px rgba(255, 255, 255, .2), 0px 1px 1px rgba(0, 0, 0, .08); display: block; padding: .6875em 0 } .ribbon-content:before, .ribbon-content:after { content: ''; display: block; position: absolute; border-style: solid; border-color: #b86614 transparent transparent; bottom: -1em } .ribbon-content:before { left: 0; border-width: 1em 0 0 1em } .ribbon-content:after { right: 0; border-width: 1em 1em 0 0 } .ribbon a { color: #c2660a; text-decoration: none; text-shadow: 1px 1px 1px rgba(255, 255, 255, .25) }
body { counter-reset: h1 } h1 { counter-reset: h2 } h2 { counter-reset: h3 } h3 { counter-reset: h4 } h2:before { counter-increment: h2; content: counter(h2) ". " } h3:before { counter-increment: h3; content: counter(h2) "." counter(h3) ". " } h4:before { counter-increment: h4; content: counter(h2) "." counter(h3) "." counter(h4) ". " }
</style>
# Заметки
<!-- <details> -->
:::info
<br>
:bulb: Часть 1
<br>
:::
## Символьные функции
Символьные функции: принимают на входе символьные данные, а возвращают как символьные так и числовые значения.
Числовые функции: принимают на входе числовые данные и возвращают числовые значения.
Функции даты: работают с значениями типа DATE. Все функции для работы с датами возвращают значение типа DATE за исключением функции MONTHS_BETWEEN, которая возвращает число.
Функции преобразования: преобразуют значение из одного типа данных в другой.
Общие функции
– NVL
– NVL2
– NULLIF
– COALSECE
– CASE
– DECODE
Однострочные символьные функции принимают на входе символьные данные, а возвращают символьное или числовое значение.
• функции преобразования регистра символов,
• функции манипулирования символами.
<center>
<img src="https://i.imgur.com/MfLdORS.png">
</center>
### Функции преобразования регистра символов:
- LOWER: преобразует строку символов верхнего регистра или обоих регистров в символы нижнего регистра.
- UPPER: преобразует строку символов нижнего регистра или обоих регистров в символы верхнего регистра.
- INITCAP: Преобразует первую букву каждого слова в заглавную, а остальные – в строчные.
### Функции манипулирования символами
| | |
|--------------------------|-----------------------------------------------------------------------------------------------------------------------------------|
| **CONCAT** | соединяет значения. Для CONCAT можно использовать не более двух параметров. |
| **SUBSTR(S,pos,[,len])** | выделяет в строке S подстроку длиной len, начиная с позиции pos LENGTH: возвращает количество символов в виде числового значения. |
| **INSTR** | возвращает номер позиции указанного символа. |
| **LPAD(S,N[,A])** | возвращает строку S, дополненную слева симолами A до числа символов N. Символ | наполнитель по умолчанию – пробел.|
| **RPAD(S,N[,A])** | возвращает строку S, дополненную справа симолами A до числа символов N. Символ | наполнитель по умолчанию – пробел.|
| **TRIM** | Усекает начальные или конечные символы (или оба) из строки символов. (Если trim_character или trim_source являются символьными литералами, вы должны заключить из в апострофы).|
| **LTRIM(S,[S1])** | Возвращает усеченную слева строку S. Символы удаляются до тех пор, пока удаляемый символ входит в строку | шаблон S1 (по умолчанию | пробел).|
| **RTRIM(S,[S1])** | Возвращает усеченную справа строку S. Символы удаляются до тех пор, пока удаляемый символ входит в строку | шаблон S1 (по умолчанию – пробел).|
| **TRANSLATE(S,S1,S2)** | Возвращает строку S, в которой все вхождения строки S1 замещены строкой S2. Если S1 <> S2, то символы, которым нет соответствия, исключаются из результирующей строки.|
| **REPLACE(S,S1,[,S2])** | Возвращает строку S, для которой все вхождения.|
<center>
<img src="https://i.imgur.com/rofG7TT.png">
</center>
### Числовые функции
| | | |
|---------------|---------------|-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| **ROUND (column \| expression,n)** | округляет столбец, выражение или значение до n десятичных разрядов. Если n отрицательно, округляются разряды слева от десятичнойточки. |
| **TRUNC (column \| expression,n)** | Усекает столбец, выражение или значение до n десятичных разрядов, а если n опущено, то до целого. Если n отрицательно, усекаются до нуля разряды слева от десятичной точки. |
| **MOD (m,n)** | Возвращает остаток от деления m на n.
<center>
<img src="https://i.imgur.com/EO6w2Gw.png">
</center>
### Общие функции
Эти функции работают с любыми типами данных с NULL значениями.
Функции для обработки NULL-значений
Функция | Описание
-------------|--------------------------------------------------------------------------------------------------------------------------------------
**NVL** | Используется для преобразования неопределенного значения(NULL) в действительное.
**NVL2** | Если expr1 - not null, NVL2 возвращает expr2. Если expr1 is null, NVL2 возвращает expr3. Аргумент expr1 может иметь любой тип данных.
**NULLIF** | Сравнивает два выражения и возвращает NULL, если они равны или первое выражение, если не равны.
**COALESCE** | Возвращает первое non-null выражение в списке выражений.
### Функция NVL:
Функция NVL используется для преобразования неопределенного значения(NULL) в действительное.
Синтаксис: _NVL (expr1, expr2)_
- где expr1 исходное значение или выражение, которое содержит неопределенное значение;
- expr2 конечное значение для преобразования неопределенного значения.
Функцию NVL можно использовать для преобразования данных любого типа, но тип данных возвращаемого значения всегда такой, как у expr1.
Тип данных | Пример преобразования
------------------|-------------------------------------
NUMBER | NVL(number_column,9)
DATE | NVL(date_column, ’01-JAN-95’)
CHAR или VARCHAR2 | NVL(character_column, ‘Unavailable’)
#### Пример 1.
Для вычисления годового дохода служащих необходимо умножить их оклад на 12, а затем прибавить сумму комиссионных.
```sql
SELECT last_name
,salary
,commission_pct
,salary*12 + (salary*12*commission_pct) AN_SAL
FROM employees;
```
Следует отметить, что годовой доход вычисляется только для служащих, зарабатывающих комиссионные. Если какой-либо столбец в выражении содержит неопределенное значение, результатом также будет неопределенное значение. Чтобы вычислить результаты по всем служащим,
необходимо преобразовать неопределенное значение в число прежде, чем применять арифметический оператор. В следующем примере функция NVL используется для преобразования неопределенных значений в ноль.
```sql
SELECT last_name
,salary
,NVL(commission_pct,0)
,salary*12 + (salary*12*NVL(commission_pct,0)) AN_SAL
FROM employees;
```
#### Пример 2.
Гарантированный выбор. Написать запрос к таблице EMPLOYEES, который вернёт зарплату сотрудника (значение в столбце SALARY) с указанным в переменной :ID номером (EMPLOYEE_ID = :ID), а в случае отсутствия сотрудника с указанным номером - вернёт 0 (ноль).
```sql
SELECT NVL(( SELECT salary
FROM employees
WHERE employee_id = :id), 0)
FROM dual
```
### Функция NVL2
Функция NVL2 проверяет первое выражение. Если первое выражение не равно NULL, то NVL2 возвращает второе выражение. Если первое выражение равно NULL, то возвращается третье выражение.
**Синтаксис** _NVL2 (expr1, expr2, expr3)_
- где expr1 исходное значение или выражение, которое может содержать неопределенное значение;
- expr2 конечное значение, если expr1 равно NULL;
- expr3 конечное значение, если expr1 не равно NULL.
Аргумент expr1 может иметь любой тип данных. Аргументы expr2 и expr3 могут иметь любой тип данных за исключением LONG.
В ниже приведенном примере проверяется колонка COMMISSION_PCT. Если значение обнаружено, возвращается второе выражение SAL+COMM. Если колонка COMMISSION_PCT содержит NULL значение, то возвращается третье выражение SAL.
```sql
SELECT last_name
,salary
,commission_pct
,NVL2(commission_pct,'SAL+COMM','SAL') income
FROM employees
WHERE department_id IN (50, 80);
```
### Функция NULLIF
Функция NULLIF сравнивает два выражения. Если они равны, функция возвращает NULL. Если не равны, функция возвращает первое выражение. Вы не можете задать литерал NULL для первого выражения.
**Синтаксис:** _NULLIF(expr1, expr2)_
- где expr1 исходное значение сравниваемое с expr2;
- expr2 исходное значение сравниваемое с expr1. (Если оно не равно expr1, возвращается expr1.)
```sql
SELECT first_name
,LENGTH(first_name) "expr1"
,last_name
,LENGTH(last_name) "expr2"
,NULLIF(LENGTH(first_name),LENGTH(last_name)) result
FROM employees;
```
Примечание: Функция NULLIF логически эквивалентна следующему выражению CASE.
### Функция COALESCE
Функция NULLIF возвращает первое NOT NULL выражение из списка
**Синтаксис:** _NULLIF(expr1, expr2, ... exprn)_
- где expr1 возвращает expr1, если оно не равно NULL;
- expr2 возвращает expr2, если оно не равно NULL и expr1 равно NULL;
- exprn возвращает exprn, если оно не равно NULL и все предыдущие выражения равны NULL.
Использование функции COALESCE
- Преимущество функции COALESCE по сравнению с функцией NVL состоит в том, что функция COALESCE может обрабатывать несколько альтернативных значений.
- Если первое выражение определенно, функция возвращает это выражение; в противном случает она проверяет оставшиеся выражения.
- Функция NVL - это частный случай COALESCE.
В примере, если значение комиссионных COMM не равно null, оно
показывается. Если значение COMMISSION PCT равно null, то показывается
зарплата SAL ARY . Если зна чения COMMISSION PCT и SALARY равны null,
то выдается значение 10
```sql
SELECT last_name
,employee_id
,COALESCE(
TO_CHAR(commission_pct)
,TO_CHAR(manager_id)
,'No commission and no manager'
) comm
FROM employees;
```
<!-- </details> -->
<!-- <details> -->
:::info
<br>
:bulb: Часть 2
<br>
:::
## Использование функции DECODE
Формирование отчета, в котором содержимое столбца разворачивается в ряд столбцов отчета. Отчет должен содержать по строкам список сотрудников (номера и имена служащих), по столбцам наименования отделов, а на пересечении знак ‗+‘, если служащий работает в соответствующем отделе (только для отделов 10, 20, 30)
| EMP_ID | LAST_NAME | ACC_NEW_YORK | RES_DALLAS | SAL_CHICAGO |
|--------|------------|--------------|------------|-------------|
| 200 | Whalen | + | - | - |
| 201 | Hartstein | - | + | - |
| 202 | Fay | - | + | - |
| 114 | Raphaely | - | - | + |
| 115 | Khoo | - | - | + |
| 116 | Baida | - | - | + |
| 117 | Tobias | - | - | + |
| 118 | Himuro | - | - | + |
| 119 | Colmenares | - | - | + |
```sql
SELECT
employee_id,
last_name,
(
SELECT DECODE(department_id, 10, ' + ', NULL)
FROM departments
WHERE employees.department_id = departments.department_id
) acc_new_york,
(
SELECT DECODE(department_id, 20, ' + ', NULL)
FROM departments
WHERE employees.department_id = departments.department_id
) res_dallas,
(
SELECT DECODE(department_id, 30, ' + ', NULL)
FROM departments
WHERE employees.department_id = departments.department_id
) sal_chicago
FROM
employees
WHERE
department_id in(10, 20, 30)
```
## Сегментирование
Если ясна концепция группировки и использования агрегатов в SQL, разобраться с оконными функциями просто. Оконные функции, как и агрегатные функции, выполняют агрегацию заданного набора (группы) строк, но вместо того чтобы возвращать по одному значению на группу, оконные функции могут возвращать несколько значений для каждой группы. Группа строк, подвергающаяся агрегации, – это окно (отсюда название «оконные функции»).
**Простой пример**
Скажем, требуется подсчитать общее число служащих во всех отделах.
Традиционно для этого используется запрос с функцией COUNT(\*) ко
всей таблице hr.employees:
<style type="text/css">
#wrap {
width:600px;
margin:0 auto;
}
.wrap {
display: -ms-flex;
display: -webkit-flex;
display: flex;
width: 100%;
}
.left_col {
min-width: 75px;
flex-grow: 0;
}
.right_col {
flex-grow: 1;
}
</style>
<div class="wrap">
<div class="left_col">
```sql
SELECT
COUNT(*) AS cnt
FROM
hr.employees
```
</div>
<div class="right_col">
Результат:
</div>
</div>
Это довольно просто, но часто возникает необходимость получить такие агрегатные данные из строк, которые не входят в данную группировку. Оконные функции упрощают решение подобных задач. Например, следующий запрос показывает, как с помощью оконной функции можно получить агрегатные данные (общее число служащих) из заданных строк (по одной на каждого служащего):
<div class="wrap">
<div class="left_col">
```sql
SELECT last_name,
department_id,
COUNT(*) over()
FROM
hr.employees
```
</div>
<div class="right_col">
Результат:
</div>
</div>
В этом примере COUNT(*) OVER() – это вызов оконной функции. Присутствие ключевого слова OVER показывает, что COUNT будет рассматриваться не как агрегатная, а как оконная функция. В общем, стандарт SQL допускает использование всех агрегатных функций в качестве оконных, а ключевое слово OVER является отличительным признаком оконных функций.
Важно отметить, что оконные функции выполняются как последний шаг в обработке SQL перед оператором ORDER BY. В качестве примера порядка выполнения оконных функций возьмем запрос из предыдущего раздела и применим предикат WHERE, чтобы отфильтровать служащих 10 и 20го отделов (DEPTNO 10 и 20):
<div class="wrap">
<div class="left_col">
```sql
SELECT
last_name,
department_id,
COUNT(*) over() AS cnt
FROM hr.employees
WHERE department_id in (10, 20)
ORDER BY 2
```
</div>
<div class="right_col">
Результат:
</div>
</div>
Значение поля CNT во всех строках теперь не 107, а 3. В этом примере тремя строками результирующее множество ограничивает предикат WHERE, поэтому оконная функция обрабатывает только три строки (на момент обработки части SELECT запроса оконная функция имеет доступ всего к трем строкам). На этом примере мы видим, что оконные функции выполняются после обработки таких операторов, как WHERE и GROUP BY.
### Сегменты
Для определения сегмента или группы строк, подвергающихся агрегации, используется оператор PARTITION BY. Как мы видели ранее, при использовании пустых круглых скобок сегментом, агрегат которого будет вычислять оконная функция, является все результирующее множество. Оператор PARTITION BY можно рассматривать как «скользящий GROUP BY», потому что в отличие от обычного GROUP BY группы, создаваемые PARTITION BY, в результирующем множестве не являются уникальными. PARTITION BY может использоваться для вычисления агрегата заданной группы строк (отсчет начинается заново для каждой новой группы), и тогда будут представлены все экземпляры этого значения в таблице (все члены каждой группы), а не одна группа. Рассмотрим следующий запрос:
```sql
SELECT
last_name,
department_id,
count(*) over(partition by department_id) as cnt
FROM
hr.employees
order by 2
```
<center>
<img src="https://i.imgur.com/bwTOkk8.jpg">
</center>
<br>
Этот запрос по-прежнему возвращает 107 строк, но теперь в результате применения PARTITION BY DEPTNO функция COUNT выполняется для каждого отдела. Значение поля CNT для всех служащих одного отдела (одного сегмента) будет одинаковым, потому что агрегация выполняется по отделам (отсчет начинается заново для нового отдела). Кроме членов каждой группы, мы получаем информацию о каждой группе. Предыдущий запрос можно рассматривать как более рациональную версию следующего запроса:
```sql
SELECT
e.last_name,
e.department_id,
(
SELECT
COUNT(*)
FROM
hr.employees d
WHERE
e.department_id = d.department_id
) AS cnt
FROM hr.employees e
ORDER BY 2;
```
Оператор PARTITION BY замечателен также тем, что выполняет вычисления независимо от других оконных функций, осуществляя сегментирование по другим столбцам в том же выражении SELECT. Рассмотрим следующий запрос, в результате которого для каждого служащего возвращается такая информация: его имя, отдел, количество служащих в этом отделе, его должность и количество служащих, занимающих эту должность:
```sql
SELECT
last_name,
department_id,
COUNT(*) over(partition by department_id) AS dept_cnt,
job_id,
COUNT(*) over(partition by job_id) AS job_cnt
FROM
hr.employees e
ORDER BY 2
```
<center>
<img src="https://i.imgur.com/hL8HVYG.png">
</center>
<br>
Как видите, оконные функции обладают исключительной мощью, поскольку позволяют создавать запросы, содержащие как детальную, так и обобщенную информацию. Запросы, в которых применяются оконные функции, короче, но при этом эффективнее, чем запросы, использующие несколько рефлексивных объединений и/или скалярных подзапросов. Рассмотрим следующий запрос, который без труда отвечает на все поставленные вопросы: «Сколько служащих в каждом отделе? Сколько служащих, занимающих ту или иную должность, в каждом отделе (например, сколько клерков в 10!м отделе)? Сколько всего служащих в таблице EMP?»
```sql
SELECT
department_id,
job_id,
COUNT(*) over (partition by department_id) AS emp_cnt,
COUNT(job_id) over (partition by department_id, job_id) AS job_cnt,
COUNT(*) over () AS total
FROM
hr.employees
ORDER BY 1, 2
```
Чтобы получить такое же результирующее множество без применения оконных функций, придется поработать немного больше:
```sql
SELECT
a.department_id, a.job_id,
(
SELECT COUNT(*)
FROM hr.employees b
WHERE b.department_id = a.department_id
) AS emp_cnt,
(
SELECT COUNT(*)
FROM hr.employees b
WHERE b.department_id = a.department_id
AND b.job_id = a.job_id
) AS job_cnt,
(
SELECT COUNT(*)
FROM hr.employees
) AS total
FROM hr.employees a
ORDER BY 1, 2
```
В результирующем множестве можно увидеть, что служащие одного отдела имеют одно значение поля DEPT_CNT и что значение поля JOB_CNT одинаковое для служащих, занимающих одну и ту же должность.
<hr>
Кроме повышения удобства чтения и производительности, оконные функции создают «основу» для более сложных запросов в «стиле отчета». Например, рассмотрим следующий запрос в «стиле отчета», во вложенном запросе которого используются оконные функции, а затем во внешнем запросе выполняется агрегация результатов. Оконные функции позволяют получать как детальные, так и обобщенные данные, что пригодится для отчетов. Показанный ниже запрос использует оконные функции для подсчета значений разных сегментов. Поскольку выполняется агрегация многих строк, вложенный запрос возвращает все строки таблицы EMP, которые с помощью внешних выражений CASE могут быть транспонированы для создания форматированного отчета:
```sql
SELECT
department_name AS department,
emp_cnt AS emp_cnt,
total,
MAX(case WHEN job_title LIKE '%Clerk%'
THEN job_cnt else 0 end) AS clerks,
MAX(case WHEN job_title LIKE '%Manager%'
THEN job_cnt else 0 end) AS mgrs,
MAX(case WHEN job_title LIKE '%President%'
THEN job_cnt else 0 end) AS prez,
MAX(case WHEN job_title LIKE '%Accountant%'
THEN job_cnt else 0 end) AS accoun,
MAX(case WHEN job_title LIKE '%Representative%'
THEN job_cnt else 0 end) AS reprez
FROM
(
SELECT
department_name,
job_id,
job_title,
COUNT(*) over (partition by department_name) AS emp_cnt,
COUNT(job_id) over (partition by department_name, job_id) AS job_cnt,
COUNT(*) over () AS total
FROM
hr.employees
JOIN hr.jobs USING (job_id)
JOIN hr.departments USING (department_id)
) x
GROUP BY department_name, emp_cnt, total
ORDER BY 2 DESC
```
Приведенный выше запрос возвращает все отделы, общее число слу!
жащих в каждом из отделов, общее число служащих в таблице EMP
и распределение служащих по должностям для каждого отдела. Все
это делает один запрос, без дополнительных объединений или времен!
ных таблиц!
<!-- </details> -->
<!-- <details> -->
:::info
<br>
:bulb: Часть 3
<br>
:::
## Оконная функция ROW_NUMBER()
Существуют различные оконные функции. ROW_NUMBER() OVER может быть использована для простого ограничения числа возвращаемых строк. Например, для возврата не более десяти строк:
```sql
SELECT *
FROM
(
SELECT ROW_NUMBER()
OVER (ORDER BY KEY ASC ) AS rownumber,
COLUMNS
FROM tablename
) AS foo
WHERE row_number <= 10
```
ROW_NUMBER может быть недетерминированным: если key не уникален, каждый раз при выполнении запроса возможно присвоение разных номеров строкам, у которых key совпадает. Когда key уникален, каждая строка будет всегда получать уникальный номер строки.
Оконная функция RANK()
Рассмотрим функцию RANK() OVER, которая работает почти так же, как ROW_NUMBER, но может вернуть более чем n строк при определённых условиях. RANK() вычисляет относительный ранг каждой возвращаемой запросом строки по отношению к другим строкам, основываясь на значениях выражений в конструкции ORDER BY Функция DENSE_RANK()работает аналогично, но не пропускает значений в отличие от RANK.
#### Пример. Ранжирование результатов
<div class="wrap">
<div class="left_col">
Требуется ранжировать заработные платы в таблице EMP, учитывая при этом дублирующиеся значения. Должно быть получено следующее результирующее множество:
**Предлагаемые решения:**
Ранжирующие функции чрезвычайно упрощают ранжирующие запросы. Особенно полезны в данном случае три функции: DENSE_RANK OVER, ROW_NUMBER OVER и RANK OVER.
```sql
SELECT
dense_rank() over(order by salary) rnk,
salary
FROM hr.employees
```
</div>
<div class="right_col" >
Результат:
<img src="https://i.imgur.com/6kf4Gbh.png" style="min-width:140px;">
</div>
</div>
Здесь всю работу выполняет ранжирующая функция DENSE_RANK OVER. В скобках после ключевого слова OVER располагается конструкция ORDER BY, определяющая порядок ранжирования строк. В решении используется выражение ORDER BY SAL, таким образом, строки таблицы EMP ранжируются в порядке возрастания заработной платы.
#### Пример.
Получим top - 4 недавно нанятых работников:
<div class="wrap">
<div class="left_col">
```sql
SELECT *
FROM (
SELECT
RANK() OVER (ORDER BY HIRE_DATE DESC) AS rank,
employee_id,
last_name,
hire_date
FROM
hr.employees
)
WHERE
rank <= 10;
```
</div>
<div class="right_col">
Результат:
</div>
</div>
Таблица, для сравнения функий ранжирования (сотрудникам раздаются места в порядке убывания/возрастания зарплат):
```sql
SELECT last_name
,salary
,ROW_NUMBER () OVER (ORDER BY salary DESC) AS salbacknumber
,ROW_NUMBER () OVER (ORDER BY salary) AS salnumber
,RANK() OVER (ORDER BY salary) AS salrank
,DENSE_RANK() OVER (ORDER BY salary) AS saldenserank
FROM hr.employees;
```
<center>
</center>
#### Пример.
В примере используется таблица HR.EMPLOYEES.
Вывести из таблицы HR.EMPLOYEES информацию о сотрудниках отделов с номерами 10, 30, 50, 90. Вывод должен быть оформлен в таблицу, содержащую столбцы:
1. Сквозной порядковый номер сотрудника.
2. Порядковый номер сотрудника внутри отдела.
3. Номер отдела для данного сотрудника (Department_id).
4. Должность сотрудника (Job_id).
5. Фамилия сотрудника (Last_name).
6. Оклад (Salary).
7. Ранг зарплаты сотрудника в отделе (1-й самый высокооплачиваемый).
Строки в выводимой таблице должны удовлетворять следующим условиям:
1. Строки, представляющие сотрудников одного отдела, должны располагаться друг за другом.
2. Строки, представляющие сотрудников одного отдела, должрасполагаться в порядке убывания окладов.
| ID | ID_IN_DEPT | DEP_ID | JOB_ID | LAST_NAME | SALARY | SALARY_RANK |
|----|------------|--------|----------|------------|--------|-------------|
| 1 | 1 | 10 | AD_ASST | Whalen | 4400 | 1 |
| 2 | 1 | 30 | PU_MAN | Raphaely | 11000 | 1 |
| 3 | 2 | 30 | PU_CLERK | Khoo | 3100 | 2 |
| 4 | 3 | 30 | PU_CLERK | Baida | 2900 | 3 |
| 5 | 4 | 30 | PU_CLERK | Tobias | 2800 | 4 |
| 6 | 5 | 30 | PU_CLERK | Himuro | 2600 | 5 |
| 7 | 6 | 30 | PU_CLERK | Colmenares | 2500 | 6 |
| 8 | 1 | 50 | ST_MAN | Fripp | 8200 | 1 |
```sql
SELECT
rownum id,
DENSE_RANK() OVER (
partition by department_id
ORDER BY
rn1
) id_in_dept,
department_id,
job_id,
last_name,
salary,
salary_rank
FROM
(
SELECT
rownum rn1,
department_id,
job_id,
last_name,
salary,
salary_rank
FROM
(
SELECT
department_id,
job_id,
last_name,
salary,
DENSE_RANK() OVER (
partition by department_id
ORDER BY
salary DESC
) salary_rank
FROM
HR.employees
WHERE
department_id in(10, 30, 50, 90)
) a
) b
ORDER BY
department_id,
id_in_dept
```
### Расширения оператора GROUP BY
Операторы ROLLUP, CUBE и GROUPING SETS являются расширениями предложения GROUP BY. Операторы ROLLUP, CUBE и GROUPING SETS могут сформировать такой же результирующий набор, который получится в результате использования оператора UNION ALL для объединения одиночных запросов группирования, однако использование одного из операторов предложения GROUP BY обычно является более эффективным.
Группировка ROLLUP приводит к созданию промежуточных итогов по столбцу назначения. Промежуточные итоги вычисляются от самого низкого уровня до общей суммы. То есть, включая ROLLUP во фразе GROUP BY, мы указываем Oracle, чтобы он просуммировал данные по уровнях указанных столбцов и подвел общий итог.
<center>
<img src="https://i.imgur.com/RyfnLNe.png">
</center>
Пример. Запрос просуммировал данные по зарплатам на уровнях по отделам, должностям и подвел общий итог:
```sql
SELECT
department_id,
job_id,
SUM(salary)
FROM
employees
WHERE
department_id < 60
GROUP BY
ROLLUP(department_id, job_id)
```
<center>
<img src="https://i.imgur.com/4Jr4Q1V.png">
</center>
#### Задача. Посчитать сотрудников, которые никем не руководят
Одной командой SELECT посчитать количество сотрудников в каждом подразделении, которые никем не руководят.
В результат вывести:
* столбец 1: идентификатор подразделения, к которому приписан сотрудник,
* столбец 2: количество сотрудников подразделения, которые никем не руководят
В последней дополнительной строке результата
* столбец 1: слово "ВСЕГО",
* столбец 2: общее количество сотрудников компании, которые никем не руководят.
Результат отсортировать по возрастанию по полю:
* идентификатор подразделения, к которому приписан сотрудник.
Пример вывода (значения получены на другом наборе данных):
```sql
DEPT CNT
------ ---
10 3
20 5
30 1
40 2
... ...
110 4
ВСЕГО 36
```
Было дано устное разъяснение, что в выборку не следует включать сотрудников, которые
не приписаны ни к какому подразделению (employees.department_id IS NOT NULL). Это
упростило задачу, избавив от необходимости использования функции GROUPING.
**Предлагаемые решения:**
* Решение с использованием аггрегирующей функции COUNT и функции ROLLUP
```sql
WITH t AS (
SELECT d.department_id dep, COUNT(1) cnt
FROM hr.employees e, hr.departments d
WHERE e.department_id = d.department_id
GROUP BY ROLLUP(d.department_id)
)
SELECT NVL(TO_CHAR(dep),'ВСЕГО:') depm, cnt
FROM t
ORDER BY dep NULLS LAST
```
#### Задача. Доход сотрудников по странам
Используются таблицы стандартной демо-схемы HR.
Месячный доход сотрудника складывается из оклада, установленного сотруднику, и установленного для сотрудника размера комиссионных (в процентах от оклада; например, значению 0,2 соответствует сумма комиссионных, равная 20% от оклада).
Если для сотрудника не указан размер комиссионных, то следует считать, что комиссионные данному сотруднику не выплачиваются.
Одной командой SELECT вывести сведения о месячном доходе сотрудников по странам.
В результат вывести два столбца:
1. Название страны, в которой расположено подразделение компании, к которому приписан сотрудник
2. Сумма месячных доходов сотрудников, приписанных к подразделениям компании, расположенных в данной стране.
Результат упорядочить по названиям стран по возрастанию.
В предпоследней строке вывести сумму месячных доходов сотрудников, для которых невозможно определить страну. Вместо названия страны в этой строке вывести строку 'N/A' (в верхнем регистре).
Последней строкой вывести общую сумму месячных доходов всех сотрудников. Вместо названия страны в этой строке вывести строку 'TOTAL' (в верхнем регистре).
Все суммы следует округлить до целых значений (ноль знаком после запятой).
Пример результата:
```sql
COUNTRY_NAME MONTH_INCOME
----------------- ------------
Argentina 12345
Jamaica 67890
Russian Federation 93456
United Kingdom 987654
N/A 8050
TOTAL 1169395
```
**Предлагаемые решения:**
* Решение с использованием одностороннего соединения таблиц и функции ROLLUP
```sql
SELECT DECODE (GROUPING_ID(c.country_name),0,NVL(c.country_name,'N/A'),'TOTAL') country_name,
ROUND(SUM(e.salary + NVL(e.commission_pct,0)* salary),0) sum_salary
FROM hr.employees e, hr.departments d, hr.locations l, hr.countries c
WHERE e.department_id = d.department_id (+)
AND d.location_id = l.location_id (+)
AND l.country_id = c.country_id (+)
GROUP BY ROLLUP(c.country_name)
ORDER BY c.country_name NULLS LAST
```
### Функция CUBE
Оператор CUBE формирует результирующий набор, представляющий собой многомерный куб. То есть, результирующий набор содержит все возможные комбинации значений указанных столбцов вместе со значениями статистических вычислений соответствующих строк, которые совпадают с комбинацией значений указанных столбцов.
Если запрос с CUBE возвращает больше значений, чем вам нужно, то лишнее можно спрятать в представление или вложенный запрос.
В дополнение к групповым подытогам и общим итогам, созданным ROLLUP, CUBE автоматически вычисляет все возможные комбинации возможных подытогов. Это предоставляет агрегированную, просуммированную информацию для каждой категории.
<center>
<img src="https://i.imgur.com/kuZ4POv.png">
</center>
Пример ниже показывает запрос, ранее уже использованный, но с функцией CUBE для получения дополнительной агрегированной информации:
```sql
SELECT
department_id,
job_id,
SUM(salary)
FROM
employees
WHERE
department_id < 60
GROUP BY
CUBE (department_id, job_id)
```
<center>
<img src="https://i.imgur.com/pdyQaJC.png">
</center>
**Различия между CUBE и ROLLUP**: CUBE создает результирующий набор, содержащий статистические выражения для всех комбинаций значений заданных столбцов. ROLLUP создает результирующий набор, содержащий статистические выражения иерархии значений в заданных столбцах.
### Функция GROUPING
Для того, чтобы однозначно определить промежуточные итоги и чтобы улучшить обработку NULL-значений в строках, созданных ROLLUP (и CUBE - это обсуждается позже), корпорация Oracle представила новую функцию GROUPING, которая возвращает значение 1, если строка - это подытог, созданная ROLLUP или CUBE, и 0 в противном случае. Пример ниже показывает запрос, уже использованный ранее, с функциями DECODE и GROUPING, применение которых позволяет более элегантно обрабатывать null-значения, созданные ROLLUP и CUBE. (Внимание: данные примера не содержат null-значения, результаты этого запроса и предыдущего одни и те же).
<center>
<img src="https://i.imgur.com/3H69fPp.png">
</center>
<center>
<img src="https://i.imgur.com/ZXjL3tM.png">
</center>
### Функция GROUPING SETS
GROUPING SETS является дальнейшее расширение предложения GROUP BY, которые можно использовать для указания нескольких групп данных. Это способствует эффективной агрегации и, следовательно, облегчает анализ данных по нескольким измерениям. Простой операторSELECT теперь может быть написан с использованием GROUPING SETS для задания различных группировок (которые также могут включать ROLLUP или CUBE операторы), а не множественного выбора заявления объединены UNION ALL операторов.
```sql
SELECT
department_id,
job_id,
manager_id,
AVG(salary)
FROM
employees
GROUP BY
GROUPING SETS (
(department_id, job_id, manager_id),
(department_id, manager_id),
(job_id, manager_id)
);
```
Сравните предыдущий пример со следующей альтернативой:
```sql
SELECT
department_id,
job_id,
manager_id,
AVG(salary)
FROM
employees
GROUP BY
CUBE(department_id, job_id, manager_id);
```
Следующий запрос вычисляет все 8 (2 * 2 * 2) группировки, но только по группам:
```sql
SELECT
department_id,
job_id,
manager_id,
AVG(salary)
FROM
employees
GROUP BY
department_id,
job_id,
manager_id 182
UNION
ALL
SELECT
department_id,
NULL,
manager_id,
AVG(salary)
FROM
employees
GROUP BY
department_id,
manager_id
UNION
ALL
SELECT
NULL,
job_id,
manager_id,
AVG(salary)
FROM
employees
GROUP BY
job_id,
manager_id;
```
### Объединение группировок
Объединение групп - краткий путь для создания полезной комбинации группировок. Каскадной группировки указано, перечислив несколько наборов группировки, кубы и свертки и разделяя их запятой.
Пример. Рассмотрим пример объединенной группировки:
- (job_id, manager_id) (1)
- (department_id,job_id, manager_id) (2)
- (job_id)(3)
- (department_id,manager_id)(4)
- (department_id) (5)
Рассчитывается общая зарплата для каждой из этих групп. Запрос и результат представлены на рисунке
<center>
<img src="https://i.imgur.com/vMi0Urh.png">
</center>
### Использование конструкции WITH
Oracle допускает вынесение определений подзапросов из тела основного запроса с помощью особой фразы WITH. WITH (формально известный как subquery_factoring_clause) позволяет многократно использовать один и тот же блок запроса в инструкции SELECT, когда она встречается более одного раза в сложном запросе.
Фраза WITH используется в двух целях:
- для придания запросу формулировки, более понятной программисту (просто subquery factoring) и
- для записи рекурсивных запросов (recursive subquery factoring).
Используя оператор WITH, можно определить блок запроса, прежде чем использовать его в запросе. Это особенно удобно, если запрос имеет несколько ссылок на один и тот же блок запросов и в нем есть объединения и агрегатные функции.
Использование WITH, сервер Oracle получает результаты запроса блока и сохраняет его во временном табличном пользователя. Это может улучшить производительность.
Фраза WITH предшествует фразе SELECT и позволяет привести сразу несколько предварительных формулировок подзапросов для ссылки на них в ниже формулируемом основном запросе. Общая схема употребления демонстрируется следующей схемой:
<center>
<img src="https://i.imgur.com/y5rHoJb.png">
</center>
#### Пример 1.
Необходимо выдать список департаментов, имеющих фонд заработной платы больший, чем 1/8 фонда заработной платы всего предприятия.
| DEPARTMENT | DEPT_TOTAL |
|------------|------------|
| Sales | 304500 |
| Shipping | 156400 |
```sql
WITH summary AS (
SELECT
d.department_name AS department,
SUM(e.salary) AS dept_total
FROM
employees e,
departments d
WHERE
e.department_id = d.department_id
GROUP BY
d.department_name
)
SELECT
department,
dept_total
FROM
summary
WHERE
dept_total > (
SELECT
SUM(dept_total) * 1 / 8
FROM
summary
)
ORDER BY
dept_total DESC;
```
#### Пример 2.
Запрос создает запросы DEPT_COSTS и AVG_COST, а затем использует их в теле основного запроса. Результат аналогичен.
```sql
WITH dept_costs AS (
SELECT
d.department_name,
SUM(e.salary) AS dept_total
FROM
employees e
JOIN departments d ON e.department_id = d.department_id
GROUP BY
d.department_name
),
avg_cost AS (
SELECT
SUM(dept_total) / COUNT(*) AS dept_avg
FROM
dept_costs
)
SELECT
*
FROM
dept_costs
WHERE
dept_total > (
SELECT
dept_avg
FROM
avg_cost
)
ORDER BY
department_name
```
#### Пример 3.
Выдать фамилию сотрудников, у которых зарплата больше средней по компании, их зарплату и департамент (два метода):
```sql
SELECT
e1.Last_name,
e1.salary,
e1.department_id,
e2.average
FROM
employees e1,
(
SELECT
ROUND(AVG(salary)) AS average
FROM
employees
) e2
WHERE
e1.salary > e2.average
ORDER BY
salary;
-----с оператором------------WITH------------------
WITH t1 AS (
SELECT
last_name,
salary
FROM
employees
),
t2 AS (
SELECT
ROUND(AVG(salary)) AS average
FROM
employees
)
SELECT
last_name,
salary,
average
FROM
t1,
t2
WHERE
salary > average
ORDER BY
salary;
```
### Формулирование рекурсивных запросов
С версии Oracle 11.2 фраза WITH может использоваться для формулирования рекурсивных запросов, в соответствии (неполном) со стандартом SQL:1999. В этом качестве она способна решать ту же задачу, что и CONNECT BY, однако (а) делает это похожим с СУБД других типов образом, (б) обладает более широкими возможностями, (в) применима не только к запросам по иерархии и (г) записывается значительно более замысловато.
Общий алгоритм вычисления фразой WITH таков:
<center>
<img src="https://i.imgur.com/WQtkpQ3.png">
</center>
Предложение SELECT для исходного множества строк Oracle называет опорным (anchor) членом фразы WITH. Предложение SELECT для получения добавочного множества строк Oracle называют рекурсивным членом. Обратите внимание, что для вычитания множеств строк Oracle использует здесь не собственное обозначение MINUS, а стандартное EXCEPT.
Приведем простой пример употребления фразы WITH для построения рекурсивного запроса:
```sql
WITH numbers (n) AS (
SELECT 1 AS nFROM dual -- исходное множество -- одна строка
UNION ALL -- символическое "объединение" строк
SELECT n + 1 AS n -- рекурсия: добавок к предыдущему результату
FROM numbers -- предыдущий результат в качестве источника данных
WHERE n < 5 -- если не ограничить, будет бесконечная рекурсия )
)
SELECT n FROM numbers -- основной запрос;
```
Результат:
N
----
1
2
3
4
5
#### Задача. Формирование последовательности числовых значений
Хотелось бы иметь в своем распоряжении «генератор строк». Генера торы строк пригодятся для запросов, в которых требуется выполнить разворачивание. Например, стоит задача получить результирующее множество, подобное приведенному ниже, с любым заданным количеством строк:
ID
1
2
3
4
5
4
...
В данном решении показано, как получить 10 строк, в которых располагаются числа по возрастанию, начиная с 1. Это решение можно без труда адаптировать для получения любого количества строк.
Возможность получать ряд последовательных возрастающих значе ний, начиная с 1, открывает путь ко многим другим решениям. Например, можно генерировать числа и, добавляя их к датам, получать последовательности дней. Кроме того, можно использовать такие ряды чисел для синтаксического разбора строк.
**Предлагаемые решения:**
1) Рекурсивный оператор WITH увеличивает ID (начиная с 1) до тех пор, пока не будет выполнен предикат WHERE. Для начала необходимо получить одну строку со значением 1. Сделать это можно с помощью таблицы, состоящей из одной строки (dual).
```sql
WITH x (id) AS (
SELECT 1
FROM dual
UNION ALL
SELECT id + 1
FROM x
WHERE id + 1 <= 10
)
SELECT *
FROM x
```
2) В этом решении CONNECT BY помещен в конструкцию WITH. Строки будут формироваться до тех пор, пока не выполнится предикат WHERE. Oracle автоматически увеличивает значение псевдостолбца LEVEL.
```sql
WITH x AS
(
SELECT level id
FROM dual
connect by level <= 10
)
SELECT *
FROM x
```
#### Задача. Числа Фибоначчи
Числа Фибоначчи — элементы числовой последовательности
0, 1, 1, 2, 3, 5, 8, 13, 21, 34, 55, 89, 144, 233, 377, 610, 987, …
в которой каждое последующее число равно сумме двух предыдущих чисел.
(Названы по имени средневекового математика Леонардо Пизанского, известного как Фибоначчи).
Более формально, последовательность чисел Фибоначчи {Fn} задается линейным рекуррентным соотношением:
F(0) = 0, F(1) = 1, F(n) = F(n-1) + F(n-2), n >=2.
То есть:
```sql
F(2) = F(0) + F(1) = 0 + 1 = 1
F(3) = F(1) + F(2) = 1 + 1 = 2
F(4) = F(2) + F(3) = 1 + 2 = 3
F(4) = F(2) + F(3) = 2 + 3 = 5
. . .
```
Используя только таблицу DUAL вычислить и вывести в результат все числа Фибоначчи в диапазоне от 1 до 1000 (1 <= n_fib <= 1000). Числа в результате не должны повторяться и должны быть отсортированы по возрастанию.
То есть в результате должен получиться столбец из 15 чисел, отсортированных по возрастанию:
```sql
N_FIB
-----
1
2
3
5
8
13
21
34
55
89
144
233
377
610
987
```
Внимание! Решения, подобные приведенному ниже, засчитаны не будут.
- Решение методом подгонки под ответ
```sql
SELECT 1 AS n_fib FROM dual UNION ALL
SELECT 2 FROM dual UNION ALL
SELECT 3 FROM dual UNION ALL
. . .
SELECT 987 FROM dual;
```
**Предлагаемые решения:**
* Решиние с использованием рекурсивного запроса
```sql
WITH fib (f1, f2) AS
(
SELECT 1 f1, 1 f2 FROM dual
UNION ALL
SELECT f1+f2, f1
FROM fib
WHERE f1+f2 <= 1000
)
SELECT f1 FROM fib
```
* Решение с использованием конструкции MODEL
```sql
SELECT ch FROM dual
MODEL
DIMENSION BY(1 n)
MEASURES (CAST(0 AS NUMBER) ch)
RULES ITERATE(1000) UNTIL (ch[iteration_number-1] + ch[iteration_number] > 1000)(
ch[iteration_number] = CASE WHEN iteration_number = 0 THEN 1
WHEN iteration_number = 1 THEN 2
ELSE ch[iteration_number-2] + ch[iteration_number-1]
END
)
ORDER BY ch
```
#### Задача. Количество новых сотрудников с 2002 по 2010
Вычислить какое количество сотрудников ежегодно поступает на работу с 2002 по 2010 гг.
YEAR | COUNT(E.EMPLOYEE_ID)
-----|---------------------
2003 | 6
2002 | 7
2004 | 10
2008 | 11
2007 | 19
2006 | 24
2005 | 29
```sql
WITH period (year) AS (
SELECT
2002 AS year
FROM
dual
UNION
ALL
SELECT
year + 1 AS year
FROM
period
WHERE
year < 2010
)
SELECT
p.year,
COUNT (e.employee_id)
FROM
employees e
RIGHT OUTER JOIN period p ON p.year = EXTRACT (
YEAR
FROM
e.hire_date
)
GROUP BY
p.year
ORDER BY
p.year;
```
#### Буквы встречающиеся в записи римских чисел от 1 до 3999
Для записи чисел римскими цифрами (roman numerals) используются буквы латинского алфавита.
Одной командой SELECT, используя только таблицу DUAL, вычислите какие буквы встречаются в записи чисел от 1 до 3999 и сколько раз.
В результат выведите 2 столбца:
* Буква латинского алфавита, используемая для записи римскими цифрами чисел от 1 до 3999.
* Количество раз использования этой буквы для записи римскими цифрами чисел от 1 до 3999.
Результат упорядочить по столбцу (1) по возрастанию.
**Предлагаемые решения:**
* Решение с использованием рекурсивного запроса и аггрегирующей функции
```sql
WITH t(ch,sym,l) AS (
SELECT TO_CHAR(LEVEL,'fmRN') ch, SUBSTR(TO_CHAR(LEVEL,'fmRN'),1,1) sym, 1 l FROM dual CONNECT BY LEVEL < 4000
UNION ALL
SELECT ch, SUBSTR(ch,l+1,1) sym, l+1 l FROM t WHERE l < LENGTH(ch)
)
SELECT sym, COUNT(1) kol
FROM t GROUP BY sym
```
#### Задача. Генерация дат заданного года
Сгенерировать все даты текущего года
**Предлагаемые решения:**
* Решение через генерацию нужных дат иерархическим запросом
```sql
SELECT TRUNC(SYSDATE, 'YYYY') + LEVEL - 1 dt FROM dual
CONNECT BY TO_CHAR(TRUNC(SYSDATE, 'YYYY') + LEVEL - 1, 'YYYY') = TO_CHAR(TRUNC(SYSDATE, 'YYYY'), 'YYYY')
```
* Решение через генерацию дат иерархическим запросом и фильтрацией результата
```sql
WITH t AS (
SELECT TO_DATE('01.01.2012','DD.MM.YYYY') dat FROM dual
)
SELECT dat + lev - 1 dat2 FROM t,
(SELECT LEVEL lev FROM dual CONNECT BY LEVEL < 400) t2
WHERE TO_CHAR(dat,'YYYY') = TO_CHAR(dat+lev -1,'YYYY')
```
### Древовидные запросы
В Oracle реализованы так называемые запросы, предназначенные для работы с данными, организованными в виде дерева. Для реализации дерева в виде таблицы в ней должно быть дополнительных два поля: id узла и id родительского узла. Также должен быть корень (корни).
<center>
<img src="https://i.imgur.com/7KdMoMK.png">
</center>
Для реализации древовидных запросов имеются два дополнительных предложения:
<center>
<img src="https://i.imgur.com/RvrtYMd.png">
</center>
Необязательный оператор **START WITH** (начать с) - если задано, то позволяет указать, с какой вершины (строки) или вершин (строк) начать построение дерева. То есть задает корневую (вые) вершины (строки). Если в заданной иерархии обнаруживается петля, то Oracle возвращает сообщение об ошибке. Условие может быть практически любым, можно даже использовать функции или внутренние запросы: pid is null, или id = 1, или даже substr(title, 1, 1) = ‘Р’.
Обязательный оператор **CONNECT BY**. Условие после CONNECT BY (соединить по) - задает зависимость между родительскими и дочерними вершинами (строками) иерархии. Тут надо сказать Ораклу, как долго продолжать цикл. Что-то в духе while в обычных языках программирования. Например, мы можем попросить достать нам 10 строк: **ROWNUM** <=10 – он и создаст нам в цикле ровно 10 одинаковых строк. Почему одинаковых? Да потому что мы указали какую строку выбрать первой, а как найти следующую нет – вот он и выдает 1-ую строку нужное количество раз. Как уже говорилось, **ROWNUM** это "псевдостолбец", в котором нумеруются строки, начиная от 1 в порядке их выдачи. Его можно использовать не только в иерархических запросах. Но это уже другая история.
Фигурирующие в документации по Oracle "псевдостолбцы" подобны "системным переменным", но в отличие от них способны давать в запросах на разных строках разные значения, которые вычисляются по мере выполнения определенных фаз обработки запроса и доступны для использования на последующих фазах обработки, образуя как бы дополнительный "столбец".
| Псевдостолбец | Описание | |
|---------------|-----------------------------------------------------------------------------------------------------------------------------------------------------------|---|
| LEVEL | Номер уровня выдаваемой строки в предложении SELECT с использованием CONNECT BY | |
| ROWNUM | NUMBER Последовательный номер строки в результате SELECT LEVEL NUMBER Номер уровня выдаваемой строки в предложении SELECT с использованием CONNECT BYText | |
Чтобы получить нормальную иерархию нужно использовать специальный оператор, который называется PRIOR. Это обычный унарный оператор, точно такой же как + или -. “Позвоните родителям” – говорит он, заставляя Оракл обратиться к предыдущей записи. В примере задано направление от родителя к потомку (прочитать эту часть запроса следует так: предшестаующим столбцу EMPLOYEE_id является столбец MANAGER_ID). Направлению от потомка к родителю соответствовало бы условие EMPLOYEE_id = PRIOR manager_id или PRIOR manager_id =EMPLOYEE_id. ( как говорится, от перестановки мест…).
#### Пример.
Требуется найти всех служащих, которые прямо или косвенно (т. е. являются подчиненными того, кто подчиняется Greenberg) подчиняются Greenberg. Список подчиненных Greenberg показан ниже (Greenberg включен в результирующее множество):
<div class="wrap">
<div class="left_col">
```sql
SELECT last_name
FROM
hr.employees
START WITH last_name = 'Greenberg'
CONNECT BY PRIOR employee_id = manager_id
```
Оператор START WITH указывает запросу сделать JONES корневым узлом. Условие в операторе CONNECT BY управляет обходом дерева, который будет продолжаться до тех пор, пока условие истинно.
</div>
<div class="right_col">
Результат:
</div>
</div>
#### Пример
Оператор SELECT, осуществляющий древовидный запрос, может использовать псевдостолбец LEVEL, содержащий уровень вложенности для каждой строки. Для коренных записей LEVEL=1, для потомков коренных записей LEVEL=2 и и.д.
<center>
<img src="https://i.imgur.com/egEvdI9.png">
</center>
<center>
<img src="https://i.imgur.com/Dq8qlHq.png">
</center>
```sql
START WITH employee_id = 100;
SELECT
LPAD(' ', 2 *(LEVEL -1)) || last_name org_chart,
department_id,
manager_id,
job_id
FROM
employees
START WITH job_id = 'AD_PRES'
CONNECT BY PRIOR employee_id = manager_id;
```
#### Пример
Напишем запрос, который возвращал бы из таблицы hr.employees информацию:
- имя сотрудника;
- фамилию сотрудника;
- уровень подчиненности (самый высокий уровень – главный начальник, который никому не подчиняется — уровень 0);
- путь подотчетности в формате /руководитель1/руководитель2/ сотрудник.
Отчет должен быть отсортирован по уровню подчиненности
| NAME | LAST_NAM | Level | Podotchetn |
|---------|-----------|-------|--------------------------------|
| Steven | King | 0 | /Steven King |
| Neena | Kochhar | 1 | /Steven King/Neena Kochhar |
| Michael | Hartstein | 1 | /Steven King/Michael Hartstein |
| Eleni | Zlotkey | 1 | /Steven King/Eleni Zlotkey |
| Gerald | Cambrault | 1 | /Steven King/Gerald Cambrault |
| Alberto | Errazuriz | 1 | /Steven King/Alberto Errazuriz |
| Karen | Partners | 1 | /Steven King/Karen Partners |
| John | Russell | 1 | /Steven King/John Russell |
| Kevin | Mourgos | 1 | /Steven King/Kevin Mourgos |
```sql
SELECT
first_name "NAME",
last_name "LAST_NAM",
LEVEL - 1 AS "Level podchin",
SYS_CONNECT_BY_PATH (first_name || ' ' || last_name, '/') AS "Podotchetn"
FROM
employees START WITH employee_id = 100
CONNECT BY PRIOR employee_id = manager_id
ORDER BY
LEVEL;
```
#### Задача. Иерархия зарплат у руководителей
Используются таблицы стандартной демо-схемы HR.
Одной командой SELECT вывести два столбца:
* Фамилии сотрудников-руководителей, выстроив их в соответствии с иерархической структурой предприятия, выделяя каждый следующий уровень подчинения сдвигом фамилии на два пробела вправо (идентификатор непосредственного руководителя сотрудника указан в поле MANAGER_ID таблицы EMPLOYEES).
* Сумму окладов всех сотрудников, подчиненных данному сотруднику (оклад самого сотрудника при суммировании не учитывать).
В выборке не должно быть сведений о сотрудниках, которые никем не руководят.
В построенной иерархии строки должны быть упорядочены в рамках каждого уровня по фамилии сотрудника-руководителя по возрастанию.
Пример результата (данные условные):
```sql
TREE SALARY
----------- -------
Kingman 987654
Carbofoss 64321
De De Te 23456
Hunourik 12345
Errorlevin 43210
. . .
Kochegar 99999
Groomwall 34567
Vickings 8765
Marquez 23456
. . .
```
**Предлагаемые решения:**
* Решение с использованием иерархического запроса и конструкции EXISTS
```sql
SELECT LPAD(' ', LEVEL* 2)||e1.last_name TREE,
(SELECT sum(e2.salary)
FROM hr.employees e2
START WITH e2.manager_id = e1.employee_id
CONNECT BY e2.manager_id = prior e2.employee_id
) sal
FROM hr.employees e1
WHERE EXISTS (SELECT 1 FROM hr.employees e2 WHERE e2.manager_id = e1.employee_id)
CONNECT BY e1.manager_id = prior e1.employee_id
START WITH e1.manager_id IS NULL
ORDER SIBLINGS BY e1.last_name
```
* Решение с использованием иерархического запроса и псевдостолбца CONNECT_BY_ISLEAF
```sql
SELECT LPAD(' ',(LEVEL-1)* 2) || last_name last_name,
(SELECT SUM(e2.salary)
FROM hr.employees e2
START WITH e2.employee_id = e1.employee_id
CONNECT BY PRIOR e2.employee_id = e2.manager_id
) - e1.salary sum_salary
FROM hr.employees e1
WHERE CONNECT_BY_ISLEAF = 0
START WITH e1.manager_id IS NULL
CONNECT BY PRIOR e1.employee_id = e1.manager_id
ORDER SIBLINGS BY e1.last_name ASC
```
#### Задача. Объединение точек в интервалы
```sql
WITH t AS (
SELECT
CASE
WHEN level = 4 THEN 'C'
WHEN level IN (3, 8) THEN 'B'
ELSE 'A'
END AS x,
level AS y
FROM
duaL connect by level <= 9
)
```
```sql
X Y
- -
A 1
A 2
B 3
C 4
A 5
A 6
A 7
B 8
A 9
```
Надо получить:
```sql
X Y
- ------------------------------
A 1-2,5-7,9
B 3,8
C 4
```
Считаем, что точки одной группы уникальны.
**Предлагаемые решения:**
* Решение при помощи иерархического запроса и функции LISTAGG
```sql
WITH t AS (
SELECT CASE WHEN LEVEL = 4 THEN 'C' WHEN LEVEL IN (3, 8) THEN 'B' ELSE 'A' END AS x, LEVEL AS y
FROM dual CONNECT BY LEVEL <= 9
), t2 AS(
SELECT x, MIN(CONNECT_BY_ROOT y) s_p, y e_p
FROM t
WHERE CONNECT_BY_ISLEAF = 1
CONNECT BY PRIOR y + 1 = y AND PRIOR x = x
GROUP BY x,y
)
SELECT DISTINCT x, SUBSTR(LISTAGG(CASE WHEN s_p = e_p THEN ',' || s_p ELSE ',' || s_p || '-' || e_p END)
WITHIN GROUP (ORDER BY s_p) OVER (PARTITION BY x),2) y
FROM t2
ORDER BY x
```
<!-- </details> -->
Sign in with Wallet
Connect another wallet