Last_value窗口功能无法正常工作 [英] Last_value window function doesn't work properly

问题描述

Last_value窗口功能doesn't正常工作.

Last_value window function doesn't work properly.

CREATE TABLE EXAMP2
(
  CUSTOMER_ID  NUMBER(38)                       NOT NULL,
  VALID_FROM   DATE                             NOT NULL
);


Customer_id      Valid_from
-------------------------------------
 9775             06.04.2013 01:34:16
 9775             06.04.2013 20:34:00
 9775             12.04.2013 11:07:01
--------------------------------------

select DISTINCT LAST_VALUE(VALID_FROM) 
  OVER (partition by customer_id ORDER BY VALID_FROM ASC) rn 
from   examp1;

当我使用LAST_VALUE时，我得到以下行:

When I use LAST_VALUE then I get following rows:

06.04.2013 20:34:00
06.04.2013 01:34:16
12.04.2013 11:07:01

当我使用FIRST_VALUE时，我得到以下行:

When I use FIRST_VALUE then I get following rows:

select  DISTINCT FIRST_VALUE(VALID_FROM) 
OVER (partition by customer_id ORDER BY VALID_FROM DESC) rn 
from   examp1;

4/12/2013 11:07:01 AM

First_value查询给出正确的输出.我希望从这些查询中获得相同的输出.为什么我有2 different results?

First_value query gives correct output. I hoped to get same output from these queries. Why do I have 2 different results?

推荐答案

在解析函数中，您需要指定窗口范围.默认情况下，它是between unbounded preceding and current row，我认为这是不言自明的.

In analytic functions you need to specify window range. By default it is between unbounded preceding and current row, which I assume to be self-explanatory.

基本上，这是您指定partition by customer_id order by valid_from asc时发生的情况:

Basically, this is what happens when you specify partition by customer_id order by valid_from asc:

1. Oracle获取与当前行的customer id
相匹配的所有行
2. 它按valid_from
的升序排列
3. 它形成一个以最小valid_from日期开始，以当前行的valid_from结尾的窗口.
4. 它计算last_value，该返回当前行的valid_from.

1. Oracle takes all rows matching current row's customer id
2. It orders them in an ascending order by valid_from
3. It forms a window starting with minimum valid_from date, and ending with current row's valid_from.
4. It evaluates last_value, which returns your current row's valid_from.

您需要做的是指定一个持续范围:

What you need to do is specify an ongoing range:

16:53:00 SYSTEM@sandbox> ed
Wrote file S:\spool\sandbox\BUFFER_SYSTEM_38.sql

  1  select last_value(VALID_FROM) OVER (
  2    partition by customer_id
  3    ORDER BY VALID_FROM asc
  4    range between current row and unbounded following
  5  ) rn
  6* from   t
16:53:21 SYSTEM@sandbox> /

RN
---------------------------------------------------------------------------
04-DEC-13 11.07.01.000000 AM
04-DEC-13 11.07.01.000000 AM
04-DEC-13 11.07.01.000000 AM

Elapsed: 00:00:00.01

这篇关于Last_value窗口功能无法正常工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！