Дни рождения сотрудников. Задача 2

добавлено: 22 мар 13
понравилось:0
просмотров: 1631
комментов: 3

теги:

Автор: Павел Воронцов

Решил продолжить этот блог.

По наводке с сервера PL/SQL Challenge попал на блог Martin Giffy D'Souza и задачку в нем. Результатов объявленного соревнования пока нет, но меня удивил уровень предложенных решений. Представляю здесь свое решение с объяснением почему я считаю его лучшим.

Задача звучит так: выбрать из таблицы EMP имена сотрудников, для которых годовщина их принятия на работу придется на следующие за текущим 15 дней. Идет отсылка к стандартной для Оракла схеме HR. Для ясности приведу скрипт создания этой таблицы и получившийся у меня запрос.

 
CREATE TABLE EMP(EMPNO NUMBER(4) NOT NULL
,ENAME VARCHAR2(10)
,JOB VARCHAR2(9)
,MGR NUMBER(4)
,HIREDATE DATE
,SAL NUMBER(7, 2)
,COMM NUMBER(7, 2)
,DEPTNO NUMBER(2));


select ename
from emp e join
(select TRUNC(SYSDATE) - numtoyminterval( level-1, 'YEAR') as sdate
,TRUNC(SYSDATE) - numtoyminterval(level-1,'YEAR')+15 as edate
from dual connect by level <= (select extract(year from SYSDATE) - extract(year from min(ee.hiredate))+1 from emp ee)) a
on e.hiredate between a.sdate and a.edate;


Итак, идея заключается в создании "на лету" набора данных с периодами времени от текущей даты на 15 дней вперед для всех встречающихся в таблице лет приема на работу сотрудников и затем выбор тех сотрудников, у кого дата приема на работу в один из этих периодов попадает.

Я не проводил экспериментов, но из общих соображений понятно, что при большом объеме данных и наличии индекса по колонке HIREDATE этот запрос будет гораздо выгодней, чем, например, такой:
SELECT a.empno, a.ename, a.hiredate
FROM employee a
WHERE (TO_CHAR(a.hiredate, 'DDD') - TO_CHAR(SYSDATE, 'DDD') between 0 and 29) OR
(TO_CHAR(a.hiredate, 'DDD') - TO_CHAR(SYSDATE, 'DDD') < 29 - 365) -- for next year


Update:

Автор блога выбрал двух победителей, причем просто два случайных правильных ответа. Вот варианты запроса-победители.
Belly
define no_of_days=30

SELECT ename
, hiredate
FROM emp
WHERE (ADD_MONTHS(hiredate 
,12 * GREATEST ((EXTRACT (YEAR FROM SYSDATE)
-
EXTRACT (YEAR FROM hiredate)
)
,1
)
)
-
TRUNC(SYSDATE)
) BETWEEN 0 AND &no_of_days
;

Iudith Mentzel
SELECT e.empno, 
e.ename, 
e.hiredate, 
ADD_MONTHS(e.hiredate, 12 * y.num_years) AS myday,
y.num_years AS hire_age
FROM
emp e,
( SELECT ROWNUM num_years
FROM dual
CONNECT BY LEVEL <= 100 ) y
WHERE 
ADD_MONTHS(e.hiredate, 12 * y.num_years) BETWEEN TRUNC(SYSDATE) AND TRUNC(SYSDATE) + &N - 1
/

Комментарии


  • Все три решения не верны. Они выводят список пользователей чья дата приема на работу начинается с текущей даты, а должны выводить тех у кого дата приема between trunc(sysdate)+1 and trunc(sysdate)+17

    Возможно так будет проще и понятнее:
    SELECT ename, hiredate,
    FROM scott.emp
    WHERE to_number(to_char(hiredate,'ddd'))-to_number(to_char(sysdate,'ddd'))-1 BETWEEN 0 AND 15;

  • 03 декабря 2015, 17:27 Павел Воронцов

    Jarod: Насчет правильности решений - в оригинальный блог, там, кажется, были обсуждения.

  • 04 декабря 2015, 13:10 Павел Воронцов

    Jarod: кстати, твое решение лажает если есть переход через год (в период с 17 декабря до 14 января примерно). Ну и о производительности подумай.



Необходимо войти на сайт, чтобы оставлять комментарии