在其他谓词（Oracle 分析函数）之前评估分析函数上的 WHERE 谓词
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5438210/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Evaluate WHERE predicates on analytic functions before other predicates (Oracle analytic functions)
提问by J. Christian
Background
背景
Sample data set
样本数据集
#Employee
Id | Period | Status
---------------------
1 | 1 | L
1 | 2 | G
2 | 3 | L
I want a simple select query to yield employees' latest record (by period) only if the status='L'.
我想要一个简单的选择查询，仅当 status='L' 时才产生员工的最新记录（按期间）。
The results would look like this:
结果如下所示：
#Desired Results
Id | Period | Status | Sequence
-------------------------------
2 | 3 | L | 1
Naive attempt
幼稚的尝试
Obviously, my naive attempt at a query does not work:
显然，我对查询的幼稚尝试不起作用：
#select query
SELECT *, RANK() OVER (PARTITION BY id ORDER BY period ASC) sequence
FROM employees
WHERE status = 'L'
AND sequence = 1
Which results in the following:
结果如下：
#Naive (incorrect) Results
ID | Period | Status | Sequence
-------------------------------
1 | 1 | L | 1
2 | 3 | L | 1
Knowing the order that clauses are evaluated in SQL explains why it doesn't work. Here is how my query is evaluated:
知道在 SQL 中计算子句的顺序可以解释为什么它不起作用。以下是我的查询的评估方式：
- Isolate rows where status='L'
- Rank the rows
- Isolate top rank row
- 隔离 status='L' 的行
- 对行进行排名
- 隔离排名靠前的行
I want the following:
我想要以下内容：
- Rank rows
- Isolate the top ranked rows
- Isolate where status='L'
- 排名行
- 隔离排名靠前的行
- 隔离 where status='L'
Questions
问题
Is possible--with only a simple modification to the SELECT/WHERE clauses and using only basic predicate operators--to ensure that predicates based on analytic functions in the WHERE clause get evaluated before the non-aggregate predicates?
Anyone have other solutions that can be implemented as an end-user in Oracle Discoverer Plus?
是否有可能——仅对 SELECT/WHERE 子句进行简单修改并仅使用基本谓词运算符——以确保在非聚合谓词之前评估基于 WHERE 子句中的分析函数的谓词？
任何人都有其他解决方案可以作为最终用户在 Oracle Discoverer Plus 中实施？
Thanks!
谢谢！
回答by a_horse_with_no_name
Is it possible to do this without a sub-query
是否可以在没有子查询的情况下执行此操作
Technically the following is not a sub-query but a derived table
从技术上讲，以下不是子查询而是派生表
SELECT *
FROM (
SELECT *,
RANK() OVER (PARTITION BY id ORDER BY period ASC) sequence
FROM employees
) t
WHERE status = 'L'
AND sequence = 1
I can't think of a different solution to your problem.
我想不出不同的解决方案来解决你的问题。
回答by RichardTheKiwi
The classic Group by
经典的 Group by
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'
Exists
存在
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)
回答by Ronnis
I'll probably have to do a "Dobby" and slam my ear in the oven door and iron my hands for this...
我可能不得不做一个“多比”并将我的耳朵砰地一声放在烤箱门上并为此熨烫我的手......
You can create a function which evaluates the current row.
Note that this is inherently non-scalable. But I guess it's better than nothing.
您可以创建一个评估当前行的函数。
请注意，这本质上是不可扩展的。但我想这总比没有好。
Create the sample data:
创建示例数据：
--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 the slowest function in the database:
在数据库中创建最慢的函数：
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;
/
Demonstrates the use of the function:
演示函数的使用：
select id, period, status
from employee
where i_am_slow(id, period) = 'Y';
ID PERIOD STATUS
---------- ---------- ------
2 3 L
Rushes towards the oven...
冲向烤箱……
