Оценивать, где предикаты аналитических функций предшествуют другим предикатам (аналитические функции Oracle).

Question

Фон

Примерный набор данных

#Employee
Id | Period | Status 
---------------------
1  |  1 |   L    
1  |  2 |   G    
2  |  3 |   L    

Я хочу, чтобы простой запрос на выборку выдавал последнюю запись сотрудников (по периодам), только если статус ='L'.

Результаты будут выглядеть так:

#Desired Results
Id | Period | Status | Sequence
-------------------------------
2  |  3     |   L    |   1

Наивная попытка

Очевидно, моя наивная попытка запроса не работает:

#select query
SELECT *, RANK() OVER (PARTITION BY id ORDER BY period ASC) sequence
FROM employees
WHERE   status = 'L' 
 AND    sequence = 1

Что приводит к следующему:

#Naive (incorrect) Results
ID | Period | Status | Sequence
-------------------------------
1  |  1 |   L    |   1
2  |  3 |   L    |   1

Знание порядка, в котором предложения оцениваются в SQL, объясняет, почему это не работает. Вот как оценивается мой запрос:

• Изолировать строки, где статус = 'L'
• Ранжировать строки
• Изолировать строку высшего ранга

Я хочу следующее:

• Ранговые строки
• Выделите строки с самым высоким рейтингом
• Изолировать где статус = 'L'

Вопросы

• Возможно ли - только с простой модификацией предложений SELECT/WHERE и с использованием только базовых операторов предикатов - обеспечить, чтобы предикаты, основанные на аналитических функциях в предложении WHERE, оценивались до неагрегированных предикатов?

• У кого-нибудь есть другие решения, которые могут быть реализованы как конечный пользователь в Oracle Discoverer Plus?

Спасибо!

Answer 1

Можно ли сделать это без подзапроса

Технически следующее не подзапрос, а производная таблица

SELECT * 
FROM (
    SELECT *, 
           RANK() OVER (PARTITION BY id ORDER BY period ASC) sequence
    FROM employees
) t
WHERE status = 'L' 
  AND sequence = 1

Я не могу придумать другое решение вашей проблемы.

Answer 2

Классическая группа

SELECT e.id, e.period, e.status, 1 sequence
FROM
(
    SELECT id, min(period) period
    FROM employees
    GROUP BY id
) X
JOIN employees e on e.period=X.period and e.id=X.id
WHERE e.status = 'L'

Существует

select e.id, e.period, e.status, 1 sequence
FROM employees e
WHERE e.status = 'L'
  AND NOT EXISTS (select *
                  from employees e2
                  where e2.id=e.id and e2.period>e.period)

Answer 3

Вероятно, мне придется сделать "Добби" и хлопнуть ухом в дверце духовки и гладить мои руки для этого...

Вы можете создать функцию, которая оценивает текущую строку.
Обратите внимание, что это по сути не масштабируется. Но я думаю, что это лучше, чем ничего.

Создайте пример данных:

--drop table employee purge;

create table employee(
    id     number  not null
   ,period number  not null
   ,status char(1) not null
   ,constraint employee_pk primary key(id, period)
);

insert into employee(id,period, status) values(1, 1, 'L');
insert into employee(id,period, status) values(1, 2, 'G');
insert into employee(id,period, status) values(2, 3, 'L');

commit;

Создайте самую медленную функцию в базе данных:

create or replace function i_am_slow(
    ip_id     employee.id%type
   ,ip_period employee.period%type
)
return varchar2
as
   l_count number := 0;
begin
    select count(*)
      into l_count
      from employee e
     where e.id     = ip_id
       and e.period = ip_period
       and e.status = 'L'
       and not exists(
            select 'x'
              from employee e2
             where e2.id = e.id
               and e2.period > e.period);

    if l_count = 1 then
        return 'Y';
    end if;

    return 'N';
end;
/

Демонстрирует использование функции:

select id, period, status
  from employee
 where i_am_slow(id, period) = 'Y';

        ID     PERIOD STATUS
---------- ---------- ------
         2          3 L

Мчится к духовке...

Answer 4

выберите * из (SELECT a.*, rank() OVER (ORDER BY Period ASC) последовательности из (выберите * из ( выберите 1 идентификатор, 1 период, статус «L» из двойного объединения, все выберите 1 идентификатор, 2 периода, «G 'статус из двойного союза все выберите 2 идентификатора, 3 точки, статус 'L' из двойного) где статус = 'L') а) где последовательность = 1