Oracle分析函数的窗口子句中“当前行”和“0前/后”之间的任何区别？

Some of Oracle's analytic functions allow for a windowing clause to 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 before / following”之类的关键字,其中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前/后”不同的示例或文档吗?这对我来说似乎是语法糖...

3 个解决方案

#1

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)。

#2

The Oracle documentation that I have to hand (Oracle 9.2) says:

我必须提供的Oracle文档(Oracle 9.2)说:

If you specified RANGE:

如果您指定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.

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之前/之后,因为你。

#3

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

#1

#2