#oracle #rownum
#Oracle #rownum
Вопрос:
Я надеюсь, вы сможете помочь мне с домашним заданием 🙂
Нам нужно создать запрос, который выводит N самых высокооплачиваемых сотрудников.
Моя версия работает отлично.
Например, верхние 3:
SELECT name, salary
FROM staff
WHERE salary IN ( SELECT *
FROM ( SELECT salary
FROM staff
ORDER BY salary DESC )
WHERE ROWNUM <= 3 )
ORDER BY salary DESC
;
Обратите внимание, что в результате будут выведены сотрудники, которые входят в топ-3 и также имеют одинаковую зарплату.
1: Майк, 4080
2: Стив, 2800
2: Сьюзан, 2800
2: Джек, 2800
3: Хлоя, 1400
Но теперь наш учитель не разрешает нам использовать ROWNUM .
Я искал повсюду и не нашел ничего полезного.
Мое второе решение благодаря подсказке Джастина Кейвса.
Сначала я попробовал это:
SELECT name, salary, ( rank() OVER ( ORDER BY salary DESC ) ) as myorder
FROM staff
WHERE myorder <= 3
;
Сообщение об ошибке: «myorder: недопустимый идентификатор»
Благодаря DCookie теперь все понятно:
«[…] Аналитика применяется ПОСЛЕ вычисления предложения where, поэтому вы получаете сообщение об ошибке, что myorder является недопустимым идентификатором».
Обтекание ВЫБОРКИ решает эту проблему:
SELECT *
FROM ( SELECT name, salary, rank() OVER ( ORDER BY salary DESC ) as myorder FROM staff )
WHERE myorder <= 3
;
Мой учитель снова наносит удар и не разрешает такие экзотические аналитические функции.
3-е решение от @Justin Caves.
«Если аналитические функции также запрещены, другой вариант, который я мог бы представить — тот, который вы бы никогда, никогда, никогда не написали на практике, был бы чем-то вроде»
SELECT name, salary
FROM staff s1
WHERE (SELECT COUNT(*)
FROM staff s2
WHERE s1.salary < s2.salary) <= 3
Ответ №1:
Поскольку это домашнее задание, это скорее подсказка, чем ответ. Вы захотите использовать аналитические функции. ROW_NUMBER, RANK или DENSE_RANK могут работать в зависимости от того, как вы хотите обрабатывать связи.
Если аналитические функции также запрещены, другой вариант, который я мог бы представить — тот, который вы бы никогда, никогда, никогда не написали на практике, был бы чем-то вроде
SELECT name, salary
FROM staff s1
WHERE (SELECT COUNT(*)
FROM staff s2
WHERE s1.salary < s2.salary) <= 3
Что касается производительности, я бы не стал полагаться на количество ЗАТРАТ из плана запроса — это всего лишь оценка, и, как правило, невозможно сравнить стоимость между планами для разных инструкций SQL. Вам гораздо лучше посмотреть на что-то вроде количества согласованных результатов, которые фактически выполняет запрос, и рассмотреть, как производительность запроса будет масштабироваться по мере увеличения количества строк в таблице. Третий вариант будет радикально менее эффективным, чем два других, просто потому, что для него необходимо дважды просмотреть таблицу персонала.
У меня нет вашей таблицы ПЕРСОНАЛА, поэтому я буду использовать таблицу EMP из схемы СКОТТА
Решение аналитической функции фактически выполняет 7 последовательных get, как и решение ROWNUM
Wrote file afiedt.buf
1 select ename, sal
2 from( select ename,
3 sal,
4 rank() over (order by sal) rnk
5 from emp )
6* where rnk <= 3
SQL> /
ENAME SAL
---------- ----------
smith 800
SM0 950
ADAMS 1110
Execution Plan
----------------------------------------------------------
Plan hash value: 3291446077
--------------------------------------------------------------------------------
-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
-
| 0 | SELECT STATEMENT | | 14 | 672 | 4 (25)| 00:00:01
|* 1 | VIEW | | 14 | 672 | 4 (25)| 00:00:01
|* 2 | WINDOW SORT PUSHED RANK| | 14 | 140 | 4 (25)| 00:00:01
| 3 | TABLE ACCESS FULL | EMP | 14 | 140 | 3 (0)| 00:00:01
--------------------------------------------------------------------------------
-
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RNK"<=3)
2 - filter(RANK() OVER ( ORDER BY "SAL")<=3)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
668 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
3 rows processed
SQL> select ename, sal
2 from( select ename, sal
3 from emp
4 order by sal )
5 where rownum <= 3;
ENAME SAL
---------- ----------
smith 800
SM0 950
ADAMS 1110
Execution Plan
----------------------------------------------------------
Plan hash value: 1744961472
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 105 | 4 (25)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | VIEW | | 14 | 490 | 4 (25)| 00:00:01 |
|* 3 | SORT ORDER BY STOPKEY| | 14 | 140 | 4 (25)| 00:00:01 |
| 4 | TABLE ACCESS FULL | EMP | 14 | 140 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=3)
3 - filter(ROWNUM<=3)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
668 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
3 rows processed
Однако решение COUNT (*) на самом деле выполняет 99 последовательных get и должно выполнять полное сканирование таблицы дважды, поэтому оно более чем в 10 раз менее эффективно. И это будет масштабироваться намного хуже по мере увеличения количества строк в таблице
SQL> select ename, sal
2 from emp e1
3 where (select count(*) from emp e2 where e1.sal < e2.sal) <= 3;
ENAME SAL
---------- ----------
JONES 2975
SCOTT 3000
KING 5000
FORD 3000
FOO
Execution Plan
----------------------------------------------------------
Plan hash value: 2649664444
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 140 | 24 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL | EMP | 14 | 140 | 3 (0)| 00:00:01 |
| 3 | SORT AGGREGATE | | 1 | 4 | | |
|* 4 | TABLE ACCESS FULL| EMP | 1 | 4 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( (SELECT COUNT(*) FROM "EMP" "E2" WHERE
"E2"."SAL">:B1)<=3)
4 - filter("E2"."SAL">:B1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
99 consistent gets
0 physical reads
0 redo size
691 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
5 rows processed
Комментарии:
1. Да, я посмотрел RANK () и теперь я нашел свое второе решение.
2. @Justin Я показал своему учителю второе решение. Теперь он также не допускает такие экзотические аналитические функции. У вас есть вторая подсказка для меня?
3. Спасибо @Justin, это решение довольно неожиданное, и я все еще не понимаю его. Собираюсь просмотреть это сегодня дома.
4. Теперь я понял. Не лучше ли моего первого решения? Затраты: Решение 1: 8; Решение 2: 4; Решение 3: 6
5. @Pew — Обновлено с более подробным обсуждением последствий для производительности.
Ответ №2:
Причина, по которой вы должны обернуть инструкцию другим select, заключается в том, что оператор outer select ограничивает ваш результирующий набор желаемыми номерами строк. Вот полезная ссылка на analytics. Если вы запустите внутренний select самостоятельно, вы поймете, почему вы должны это делать. Аналитика применяется ПОСЛЕ вычисления предложения where, поэтому вы получаете сообщение об ошибке, что myorder является недопустимым идентификатором.
Ответ №3:
Oracle? Как насчет оконных функций?
select * from
(SELECT s.*, row_number over (order by salary desc ) as rn FROM staff s )
where rn <=3
Комментарии:
1. Вы не применяли никаких оконных функций.
2. Хорошо, ok. Аналитическая функция. Не имеет значения.
3. Я не пробовал это, потому что я искал рейтинг из-за подсказки @JustinCave. Но теперь у меня есть такое же решение. Но почему я должен обернуть SELECT вокруг него, чтобы использовать значение rn?
Ответ №4:
При использовании count(distinct <exp>) одинаковые верхние зарплаты в рейтинге будут рассматриваться как равные ранги.
select NAME, SALARY
from STAFF STAFF1
where 3 >= ( select count(distinct STAFF2.SALARY) RANK
from STAFF STAFF2
where STAFF2.SALARY >= STAFF1.SALARY)
Ответ №5:
Вы могли бы решить это в Oracle 12c
select NAME, SALARY
from STAFF
order by SALARY DESC
FETCH FIRST 3 ROWS ONLY
(Синтаксис FETCH FIRST является новым в Oracle 12c)
Комментарии:
1. Объяснение, почему это ответ, также было бы полезно для OP.
2. Да, ок и приношу извинения за код. Просто думаю об объяснении
3. Каждый раз, когда вы используете «Top N» в инструкции SELECT, это работает в зависимости от предложения «ORDER BY». В предложении WHERE часть в скобках упорядочена по убыванию зарплаты, поэтому первые три из них являются самыми высокими зарплатами. Поскольку зарплаты сгруппированы вместе, в инструкции не имеет значения, сколько раз они могут появляться. Затем я запрашиваю любые имена с такой суммой зарплаты.
4. Это недопустимый синтаксис для Oracle.
5. @russ Отредактируйте свой пост вместо комментария/