Функции FIRST и LAST

понедельник, 10 августа 2009 г. Оставить комментарий

Очень редко пользуюсь этими функциями, так как область применения их достаточно узкая. Но появился случай, где эти функции можно применить. Особенность этих функций является то, что они как бы создают свое "подокно", внутри "окна" данных, которое определено для этих функций. Давай-те рассмотри на примере. В примере смотрим на табличку EMP в схему SCOTT. Задача такая: в каждом отделе нужно найти сотрудника с максимальным окладом (поле SAL имеет максимальное значение), который дольше всех работает на предприятии (поле HIREDATE имеет минимальное значение).


select emp.deptno, emp.ename, emp.sal, emp.hiredate,
first_value(emp.hiredate) over (partition by emp.deptno order by emp.sal desc, emp.hiredate asc rows between unbounded preceding and unbounded following) as fv1,
min(emp.hiredate)
keep (dense_rank last order by emp.sal) over (partition by emp.deptno) as hd1,
count(1)
keep (dense_rank last order by emp.sal) over (partition by emp.deptno) as hd2
from emp;


DEPTNOENAMESAL HIREDATE FV1 HD1 HD2
1 10 KING 5000,00 17.11.1981 17.11.1981 17.11.1981 1
2 10 CLARK 2450,00 09.06.1981 17.11.1981 17.11.1981 1
3 10 MILLER 1300,00 23.01.1982 17.11.1981 17.11.1981 1
4 20 FORD 3000,00 03.12.1981 03.12.1981 03.12.1981 2
5 20 SCOTT 3000,00 19.04.1987 03.12.1981 03.12.1981 2
6 20 JONES 2975,00 02.04.1981 03.12.1981 03.12.1981 2
7 20 ADAMS 1100,00 23.05.1987 03.12.1981 03.12.1981 2
8 20 SMITH 800,00 17.12.1980 03.12.1981 03.12.1981 2
9 30 BLAKE 2850,00 01.05.1981 01.05.1981 01.05.1981 1
10 30 ALLEN 1600,00 20.02.1981 01.05.1981 01.05.1981 1
11 30 TURNER 1500,00 08.09.1981 01.05.1981 01.05.1981 1
12 30 WARD 1250,00 22.02.1981 01.05.1981 01.05.1981 1
13 30 MARTIN 1250,00 28.09.1981 01.05.1981 01.05.1981 1
14 30 JAMES 950,00 03.12.1981 01.05.1981 01.05.1981 1

C помощью partition by emp.deptno создаем окно по подразделениям (окно, когда когда подразделения равен 20, выделено серым цветом). Внутри этого окна функция dense_rank last order by emp.sal формирует ранк по величине оклада и создает свое “подокно” (оно выделено жирным шрифтом). Уже внутри этого окна применяется функция min(emp.hiredate). Тот же результат можно получить с помощью функции first_value.



Почитать про эти функции можно здесь FIRST и LAST.



P.S. Редкое использование этих функций привело к тому, что Oracle не исправил такой интересный баг. Вместо слова order можно написать любую ерунду.



select emp.deptno, emp.ename, emp.sal, emp.hiredate,
first_value(emp.hiredate) over (partition by emp.deptno order by emp.sal desc, emp.hiredate asc rows between unbounded preceding and unbounded following) as fv1,
min(emp.hiredate)
keep (dense_rank last TRAMPARARAM by emp.sal) over (partition by emp.deptno) as hd1,
count(1)
keep (dense_rank last QQ by emp.sal) over (partition by emp.deptno) as hd2
from emp;

1 коммент. »

  • Анонимно пишет:  

    Функция действительно очень редкая, и поэтому информацию по ней найти для меня было довольно сложно.
    Спасибо за описание.

    С уважением, KV1s.

  • Оставьте Ваш комментарий