FIRST and LAST Analytic Functions

原文链接:ORACLE-BASE - FIRST and LAST Analytic Functions

This article gives an overview of the FIRST and LAST analytic functions. If you are new to analytic functions you should probably read this introduction to analytic functions first.

Most of the time I find myself using FIRST_VALUE and LAST_VALUE Analytic Functions in preference to FIRST and LAST. Pick which feels best for your use case.

Content

Setup

FIRST Analytic Function

LAST Analytic Function

Quick Links


Related articles.

 

Setup

The examples in this article require the following table.

--drop table emp purge;

create table emp (
  empno    number(4) constraint pk_emp primary key,
  ename    varchar2(10),
  job      varchar2(9),
  mgr      number(4),
  hiredate date,
  sal      number(7,2),
  comm     number(7,2),
  deptno   number(2)
);

insert into emp values (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,null,20);
insert into emp values (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
insert into emp values (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
insert into emp values (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,null,20);
insert into emp values (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
insert into emp values (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,null,30);
insert into emp values (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,null,10);
insert into emp values (7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87','dd-mm-rr')-85,3000,null,20);
insert into emp values (7839,'KING','PRESIDENT',null,to_date('17-11-1981','dd-mm-yyyy'),5000,null,10);
insert into emp values (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
insert into emp values (7876,'ADAMS','CLERK',7788,to_date('13-JUL-87', 'dd-mm-rr')-51,1100,null,20);
insert into emp values (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,null,30);
insert into emp values (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,null,20);
insert into emp values (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,null,10);
commit;

 

FIRST Analytic Function

The FIRST analytic function can be used to return the first value from an ordered sequence. Say we want to display the salary of each employee, along with the lowest salary within their department we may use something like.

select empno,
       deptno,
       sal,
       min(sal) keep (dense_rank first order by sal) over (partition by deptno) as lowest
from   emp
order by deptno, sal;

     EMPNO     DEPTNO        SAL     LOWEST
---------- ---------- ---------- ----------
      7934         10       1300       1300
      7782         10       2450       1300
      7839         10       5000       1300
      7369         20        800        800
      7876         20       1100        800
      7566         20       2975        800
      7788         20       3000        800
      7902         20       3000        800
      7900         30        950        950
      7654         30       1250        950
      7521         30       1250        950
      7844         30       1500        950
      7499         30       1600        950
      7698         30       2850        950

SQL>

The MIN function is almost irrelevant here as it's FIRST and KEEP that are picking the row whose value will be used. We can demonstrate this by using MAX for the low value.

select empno,
       deptno,
       sal,
       max(sal) keep (dense_rank first order by sal) over (partition by deptno) as lowest
from   emp
order by deptno, sal;

     EMPNO     DEPTNO        SAL     LOWEST
---------- ---------- ---------- ----------
      7934         10       1300       1300
      7782         10       2450       1300
      7839         10       5000       1300
      7369         20        800        800
      7876         20       1100        800
      7566         20       2975        800
      7788         20       3000        800
      7902         20       3000        800
      7900         30        950        950
      7654         30       1250        950
      7521         30       1250        950
      7844         30       1500        950
      7499         30       1600        950
      7698         30       2850        950

SQL>

We get the same result.

We could also achieve the same result using FIRST_VALUE or MIN as basic analytic functions. In practice I don't use FIRST very often.

 

LAST Analytic Function

The LAST analytic function can be used to return the last value from an ordered sequence. Say we want to display the salary of each employee, along with the highest within their department we may use something like.

select empno,
       deptno,
       sal,
       max(sal) keep (dense_rank last order by sal) over (partition by deptno) as highest
from   emp
order by deptno, sal;

     EMPNO     DEPTNO        SAL    HIGHEST
---------- ---------- ---------- ----------
      7934         10       1300       5000
      7782         10       2450       5000
      7839         10       5000       5000
      7369         20        800       3000
      7876         20       1100       3000
      7566         20       2975       3000
      7788         20       3000       3000
      7902         20       3000       3000
      7900         30        950       2850
      7654         30       1250       2850
      7521         30       1250       2850
      7844         30       1500       2850
      7499         30       1600       2850
      7698         30       2850       2850

SQL>

The MAX function is almost irrelevant here as it's LAST and KEEP that are picking the row whose value will be used. We can demonstrate this by using MIN for the high value.

select empno,
       deptno,
       sal,
       min(sal) keep (dense_rank last order by sal) over (partition by deptno) as highest
from   emp
order by deptno, sal;

     EMPNO     DEPTNO        SAL    HIGHEST
---------- ---------- ---------- ----------
      7934         10       1300       5000
      7782         10       2450       5000
      7839         10       5000       5000
      7369         20        800       3000
      7876         20       1100       3000
      7566         20       2975       3000
      7788         20       3000       3000
      7902         20       3000       3000
      7900         30        950       2850
      7654         30       1250       2850
      7521         30       1250       2850
      7844         30       1500       2850
      7499         30       1600       2850
      7698         30       2850       2850

SQL>

We get the same result.

We could also achieve the same result using LAST_VALUE or MAX as basic analytic functions. In practice I don't use LAST very often.

 

Quick Links

The "*" indicates the function supports the full analytic syntax, including the windowing clause.

AVG *BIT_AND_AGG *BIT_OR_AGG *BIT_XOR_AGG *CHECKSUM *
CLUSTER_DETAILSCLUSTER_DISTANCECLUSTER_IDCLUSTER_SETCORR *
COUNT *COVAR_POP *COVAR_SAMP *CUME_DISTDENSE_RANK
FEATURE_DETAILSFEATURE_IDFEATURE_SETFEATURE_VALUEFIRST
FIRST_VALUE *KURTOSIS_POP *KURTOSIS_SAMP *LAGLAST
LAST_VALUE *LEADLISTAGGMATCH_RECOGNIZEMAX *
MEDIANMIN *NTH_VALUE *NTILEPERCENT_RANK
PERCENTILE_CONTPERCENTILE_DISCPREDICTIONPREDICTION_COSTPREDICTION
PREDICTION_COSTPREDICTION_DETAILSPREDICTION_PROBABILITYPREDICTION_SETRANK
RATIO_TO_REPORTREGR_ (Linear Regression) Functions *ROW_NUMBERSKEWNESS_POP *SKEWNESS_SAMP *
STDDEV *STDDEV_POP *STDDEV_SAMP *SUM *VAR_POP *
VAR_SAMP *VARIANCE *String AggregationTop-N Queries 

For more information see:

Hope this helps. Regards Tim...

 


我阅读了这篇文章,这篇文章呢,作者自己也说Most of the time I find myself using FIRST_VALUE and LAST_VALUE Analytic Functions in preference to FIRST and LAST. Pick which feels best for your use case.

但是,好处有以下:

  1.  写法固定,避免了讨厌的order by,如果某个人不小心写了order by之后,发现结果为什么不对,那是因为window clause的default action是range between unbounded preceding and current row,这样只扫描了当前行及其之前的所有行,但是经我测试下来,发现如果你在query partition clause之后写order by,会报错:ORA-30487: ORDER BY not allowed here。code如下
    select empno,
           deptno,
           sal,
           min(sal) keep (dense_rank first order by sal) over (partition by deptno order by sal) as lowest
    from   emp
    order by deptno, sal;
  2. dense_rank能不能改?答案是不能,改了会报错:ORA-02000: missing DENSE_RANK keyword。
  3. 文章中用到的aggregate function - min or max 能不能修改?答案是可以的,因为last和first已然指定,那么用任意一个function都是不搭嘎的。
  4. 但是已有的order by clause后面能加desc,只要需求一致就行,比如debuff的debuff是0
  5. 关于null,这里没有像first_value那样的开关,也就是说,不能决定忽略它还是考虑它

 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值