Oracle 分析函数的窗口子句中的“当前行”和“0 前/后”之间有什么区别？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/379643/
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
Any difference between "current row" and "0 preceding/following" in windowing clause of Oracle analytic functions?
提问by jimmyorr
Some of Oracle's analytic functions allow for a windowing clauseto specify a subset of the current partition, using keywords like "unbounded preceding/following", "current row", or "value_expr preceding/following" where value_expr is a physical or logical offset from the current row or value (depending on whether you have specified ROW or RANGE, respectively).
Oracle 的一些分析函数允许窗口子句指定当前分区的一个子集，使用诸如“无界前/后”、“当前行”或“value_expr 前/后”之类的关键字，其中 value_expr 是物理或逻辑偏移量当前行或值（取决于您是否分别指定了 ROW 或 RANGE）。
Here is an example using scott/tiger that displays employees in dept 30, and a count of the number of employees in their dept hired before them (including themselves):
这是一个使用 scott/tiger 的示例，它显示部门 30 中的员工，以及在他们之前雇用的部门员工（包括他们自己）的数量：
select deptno,
empno,
hiredate,
count(*) over (partition by deptno
order by hiredate
range between unbounded preceding and current row) cnt_hired_before1,
count(*) over (partition by deptno
order by hiredate
range between unbounded preceding and 0 preceding) cnt_hired_before2
from emp
where deptno = 30
order by deptno, hiredate;
...can anyone provide an example or documentation where "current row" is different than "0 preceding/following"? It just seems like syntactic sugar to me...
...谁能提供一个示例或文档，其中“当前行”与“0 前/后”不同？对我来说这似乎是语法糖......
采纳答案by Adam Hawkes
It doesn't really matter which you use. They are two different ways of expressing the windowing, but the optimizer will perform the query the same way. The term "current row" is one that is common to multiple databases with analytic functions, not just Oracle. It's more of a stylistic difference, in the same way that some people prefer count(*) over count(1).
你使用哪个并不重要。它们是表示窗口化的两种不同方式，但优化器将以相同的方式执行查询。术语“当前行”是具有分析功能的多个数据库所共有的，而不仅仅是 Oracle。这更多是一种风格差异，就像有些人更喜欢 count(*) 而不是 count(1) 一样。
回答by Mike Meyers
The Oracle documentation that I have to hand (Oracle 9.2) says:
我必须手头的 Oracle 文档（Oracle 9.2）说：
If you specified RANGE:
- value_expr is a logical offset. It must be a constant or expression that evaluates to a positive numeric value or an interval literal.
如果您指定了 RANGE：
- value_expr 是一个逻辑偏移量。它必须是计算结果为正数值或区间文字的常量或表达式。
This implies that you shouldn't really be using 0 since it isn't a positive numeric value. But, obviously it is possible to use 0 preceding/following since you are.
这意味着您不应该真正使用 0，因为它不是一个正数值。但是，显然可以使用 0 前/后，因为您是。
回答by user34850
It is all about what you're trying to accomplish. You may want to use RANGE BETWEEN/ROWS BETWEEN use it to find LAST_VALUE within the sub-set or compare things within a sub-set. But most certainly you don't need for the example you provided.
这完全是关于你想要完成的事情。您可能希望使用 RANGE BETWEEN/ROWS BETWEEN 使用它来查找子集中的 LAST_VALUE 或比较子集中的事物。但是对于您提供的示例，您肯定不需要。
select deptno,
empno,
hiredate,
count(*) over (partition by deptno, trunc(hiredate,'mm')) cnt_same_month
from emp
where deptno = 30
order by deptno, hiredate
