 |
|
Advanced Oracle SQL
FIRST_VALUE
and LAST_VALUE
Oracle Tips by Laurent Schneider
|
Laurent
Schneider is considered one of the top Oracle SQL experts, and
he is the author of the book "Advanced
Oracle SQL Programming" by Rampant TechPress. The following
is an excerpt from the book.
FIRST_VALUE and LAST_VALUE
The discrete bounds of the current window are
returned by the FIRST_VALUE and LAST_VALUE functions.
SELECT
ENAME,
HIREDATE,
FIRST_VALUE(ENAME||'('||HIREDATE||')')
OVER
(
ORDER BY
HIREDATE
RANGE BETWEEN
30 PRECEDING
AND
30 FOLLOWING
) FIRST,
LAST_VALUE(ENAME||'('||HIREDATE||')')
OVER
(
ORDER BY
HIREDATE
RANGE BETWEEN
30 PRECEDING
AND
30 FOLLOWING
) LAST
FROM
EMP;
ENAME
HIREDATE FIRST LAST
---------- --------- --------------------- ---------------------
SMITH 17-DEC-80 SMITH(17-DEC-80) SMITH(17-DEC-80)
ALLEN 20-FEB-81 ALLEN(20-FEB-81) WARD(22-FEB-81)
WARD 22-FEB-81 ALLEN(20-FEB-81) WARD(22-FEB-81)
JONES 02-APR-81 JONES(02-APR-81) BLAKE(01-MAY-81)
BLAKE 01-MAY-81 JONES(02-APR-81) BLAKE(01-MAY-81)
CLARK 09-JUN-81 CLARK(09-JUN-81) CLARK(09-JUN-81)
TURNER 08-SEP-81 TURNER(08-SEP-81) MARTIN(28-SEP-81)
MARTIN 28-SEP-81 TURNER(08-SEP-81) MARTIN(28-SEP-81)
KING 17-NOV-81 KING(17-NOV-81) JAMES(03-DEC-81)
FORD 03-DEC-81 KING(17-NOV-81) JAMES(03-DEC-81)
JAMES 03-DEC-81 KING(17-NOV-81) JAMES(03-DEC-81)
MILLER 23-JAN-82 MILLER(23-JAN-82) MILLER(23-JAN-82)
SCOTT 19-APR-87 SCOTT(19-APR-87) SCOTT(19-APR-87)
ADAMS 23-MAY-87 ADAMS(23-MAY-87) ADAMS(23-MAY-87)
The window starts 30 days before the current hire
date and ends 30 days after the current hire date. Within this period,
the first and last discrete values are returned. King was hired
November 17, 1981. Within the period October 18 and December 17, the
first record is King and the last record is James. Note that
LAST_VALUE returns James because his name is alphabetically greater
than Ford.
When using physical offset, duplicate entries are
sorted randomly. With logical offset, FIRST_VALUE returns the lowest
and LAST_VALUE the highest of the duplicate values.
In 10g and later, the IGNORE NULLS clause returns
the first and last non-null values.
SELECT
ENAME,
SAL,
COMM,
LAST_VALUE(COMM IGNORE NULLS)
OVER
(
ORDER BY
SAL
ROWS BETWEEN
UNBOUNDED PRECEDING
AND
1 PRECEDING
) PREVIOUS,
FIRST_VALUE(COMM IGNORE NULLS)
OVER
(
ORDER BY
SAL
ROWS BETWEEN
1 FOLLOWING
AND
UNBOUNDED FOLLOWING
) NEXT
FROM
EMP;
ENAME
SAL COMM PREVIOUS NEXT
---------- ---------- ---------- ---------- ----------
SMITH 800 1400
JAMES 950 1400
ADAMS 1100 1400
MARTIN 1250 1400 500
WARD 1250 500 1400 0
MILLER 1300 500 0
TURNER 1500 0 500 300
ALLEN 1600 300 0
CLARK 2450 300
BLAKE 2850 300
JONES 2975 300
FORD 3000 300
SCOTT 3000 300
KING 5000 300
The last non-null value of the preceding rows and
the first non-null value of the following rows are returned.
LAST_VALUE IGNORE NULLS can group consecutive
periods.
-- Id :
$Id: tabletime.sql,v 1.1 2008/07/09 17:16:36 Laurent Exp $
-- Author : $Author: Laurent $
-- Date : $Date: 2008/07/09 17:16:36 $
--
-- Create TIMETABLE Table in current schema
--
WHENEVER
SQLERROR EXIT FAILURE
EXEC BEGIN EXECUTE IMMEDIATE 'DROP TABLE TIMETABLE'; EXCEPTION WHEN
OTHERS THEN NULL; END
CREATE TABLE
TIMETABLE
(
EMPNO NUMBER REFERENCES EMP,
BEGINTIME TIMESTAMP,
ENDTIME TIMESTAMP,
PRIMARY KEY (EMPNO, BEGINTIME, ENDTIME)
);
INSERT INTO
TIMETABLE
(
EMPNO,
BEGINTIME,
ENDTIME
)
VALUES
(
7499,
TIMESTAMP '2008-07-09 08:30:00',
TIMESTAMP '2008-07-09 12:30:00'
);
INSERT INTO
TIMETABLE
(
EMPNO,
BEGINTIME,
ENDTIME
)
VALUES
(
7521,
TIMESTAMP '2008-07-09 06:30:00',
TIMESTAMP '2008-07-09 11:30:00'
);
INSERT INTO
TIMETABLE
(
EMPNO,
BEGINTIME,
ENDTIME
)
VALUES
(
7654,
TIMESTAMP '2008-07-09 13:00:00',
TIMESTAMP '2008-07-09 18:00:00'
);
INSERT INTO
TIMETABLE
(
EMPNO,
BEGINTIME,
ENDTIME
)
VALUES
(
7844,
TIMESTAMP '2008-07-09 13:30:00',
TIMESTAMP '2008-07-09 17:30:00'
);
INSERT INTO
TIMETABLE
(
EMPNO,
BEGINTIME,
ENDTIME
)
VALUES
(
7521,
TIMESTAMP '2008-07-09 14:30:00',
TIMESTAMP '2008-07-09 17:00:00'
);
COMMIT;
EMPNO BEGINTIME ENDTIME
---------- ---------------- ----------------
7499 2008-07-09 08:30 2008-07-09 12:30
7521 2008-07-09 06:30 2008-07-09 11:30
7521 2008-07-09 14:30 2008-07-09 17:00
7654 2008-07-09 13:00 2008-07-09 18:00
7844 2008-07-09 13:30 2008-07-09 17:30
The salesman timetable is displayed.
SELECT
DISTINCT
LAST_VALUE(PERIODBEGIN IGNORE NULLS) OVER
(ORDER BY BEGINTIME) BEGINTIME,
FIRST_VALUE(PERIODEND IGNORE NULLS) OVER
(ORDER BY ENDTIME RANGE BETWEEN CURRENT ROW AND UNBOUNDED
FOLLOWING)
ENDTIME
FROM
(
SELECT
EMPNO,
BEGINTIME,
ENDTIME,
CASE
WHEN BEGINTIME <= MAX(ENDTIME) OVER
(ORDER BY BEGINTIME ROWS BETWEEN
UNBOUNDED PRECEDING AND 1 PRECEDING)
THEN NULL
ELSE BEGINTIME
END PERIODBEGIN,
CASE
WHEN ENDTIME >=
MIN(BEGINTIME) OVER
(ORDER BY ENDTIME ROWS BETWEEN
1 FOLLOWING AND UNBOUNDED FOLLOWING)
THEN NULL
ELSE ENDTIME
END PERIODEND
FROM
TIMETABLE
);
BEGINTIME ENDTIME
--------------- ----------------
2008-07-09 06:30 2008-07-09 12:30
2008-07-09 13:00 2008-07-09 18:00
6:30 and 13:00 are the lower bounds; there is no
salesman working at 6:29 or at 12:59. 12:30 and 18:00 are the upper
bounds; no salesman is working at 12:31 or 18:01.
The inner query sets PERIODBEGIN to NULL when the
current BEGINTIME is smaller than the highest ENDTIME before the
current row, ordered by BEGINTIME. PERIODEND is set to NULL when the
current ENDTIME is bigger than the lowest BEGINTIME after the current
row, ordered by ENDTIME.
The outer query replaces a null PERIODBEGIN by the
last non-null value preceding and a null PERIODEND by the first
non-null value following.
DISTINCT suppresses duplicates.
Summary
An analytic function is calculated over multiple
rows and returns the result in the current row. Analytic functions
use PARTITION BY, not GROUPED BY, which is used with aggregate
functions. However, when combining analytics with aggregation, all
expressions in the ORDER BY clause and column expressions have to be
either part of the GROUP BY expressions or aggregate functions.
Various clauses that are used in analytic functions, such as the OVER,
KEEP, ROWS BETWEEN and RANGE are covered in more detail in this
chapter. Also, other functions such as ranking functions, FIRST_VALUE
and LAST_VALUE are illustrated.
 |
Expert Remote DBA
BC is America's oldest and largest Remote DBA Oracle support
provider. Get real Remote DBA experts, call
BC Remote DBA today. |
 |
|
