oracle分析函數：三、first,first_value,lag,lead函數

--1 文法

aggregate_function keep

( dense_rank first order by expr desc nulls last)

over (query partition clause )

--說明

•DENSE_RANK FIRST or DENSE_RANK LAST indicates that Oracle Database will 

aggregate over only those rows with the minimum (FIRST) or the maximum (LAST) dense rank (also called olympic rank).

--**簡介：

FIRST and LAST are very similar functions. 

Both are aggregate and analytic functions that operate on a set of values from a set of rows that rank as the FIRST or LAST 

with respect to a given sorting specification. 

都聚集和分析功能，給定一個排序的規範，操作一組值從一組行排名第一或最後一個；

If only one row ranks as FIRST or LAST, then the aggregate operates on the set with only one element.

--aggregate functions：

忽略over clause

--analytic functions 

指定over clause

-------------------------------------------------

--2 分析例子

SELECT unit_id,

       unit_code,

       tjrq_q,

       MIN(out_row) KEEP(DENSE_RANK FIRST ORDER BY tjrq_q) OVER(PARTITION BY unit_id) "Worst",

       MAX(out_row) KEEP(DENSE_RANK LAST ORDER BY tjrq_q) OVER(PARTITION BY unit_id) "Best"

  FROM etl_exdw_log

 ORDER BY unit_id, unit_code;

--3 DENSE_RANK FIRST order by expr和dens_rank() over

SELECT unit_id,

       unit_code,

       tjrq_q,

       MIN(out_row) KEEP(DENSE_RANK FIRST ORDER BY tjrq_q) OVER(PARTITION BY unit_id) "Worst",

       MAX(out_row) KEEP(DENSE_RANK LAST ORDER BY tjrq_q) OVER(PARTITION BY unit_id) "Best",

       dense_rank() over(partition by unit_id order by out_row),

       rank() over(partition by unit_id order by out_row)

  FROM etl_exdw_log

 ORDER BY unit_id, unit_code;

-------------------------------------------------

--4 聚集函數例子

SELECT unit_id,

       unit_code,

       tjrq_q,

       MIN(out_row) KEEP(DENSE_RANK FIRST ORDER BY tjrq_q) "Worst",

       MAX(out_row) KEEP(DENSE_RANK LAST ORDER BY tjrq_q) "Best",

       dense_rank(23632) within group(order by out_row desc nulls last)

  FROM etl_exdw_log

 group BY unit_id, unit_code, tjrq_q;

--1 文法

--ANSI format(推薦):

firt_value (expr (respect|ignore) nulls ) over (analytic clause)

或者：

firt_value (expr) (respect|ignore) nulls  over (analytic clause)

-------------------------------------------------

--2 簡介

It returns the first value in an ordered set of values;

If the first value in the set is null, then the function returns NULL unless you specify IGNORE NULLS

-------------------------------------------------

--3 例子一

SELECT unit_id,

       unit_code,

       tjrq_q,

       out_row,

       FIRST_VALUE(out_row) OVER(partition by unit_id,tjrq_q ORDER BY out_row desc ROWS UNBOUNDED PRECEDING) AS greatest_rows

  FROM etl_exdw_log

 ORDER BY unit_id,tjrq_q;

--1 文法1

lag (value_expr,offset,default) (ignore nulls| respect nulls)

over (query_partition_clause order_by_clause)

--2 文法2

lag (value_expr (ignore nulls| respect nulls),offset,default) 

over (query_partition_clause order_by_clause)

-------------------------------------------------

--3 目的

LAG provides access to a row at a given physical offset prior to that position.

已知偏移量，根據給的實體便宜量，lag函數可以獲得另外一個列

Given a series of rows returned from a query and a position of the cursor

在一個查詢結果集中，傳回一個遊标的位置和一組行

可選的偏移量參數,指定一個整數大于零。如果你不指定偏移量,那麼其預設是1。如果偏移量超出視窗範圍可選的預設值(1)傳回;

{RESPECT | IGNORE} NULLS determines whether null values of value_expr are included in or eliminated from the calculation. The default is RESPECT NULLS.

You cannot nest analytic functions by using LAG or any other analytic function for value_expr. 

不能嵌套分析

--lag (value_expr,offset,default) (ignore nulls| respect nulls) over (query_partition_clause order_by_clause)

-------------------------------------------------

--4 例子

SELECT unit_id,

       unit_code,

       start_time,

       end_time,

       LAG(end_time, 1, sysdate) OVER(partition by unit_id ORDER BY end_time) AS prev_endtime,

       round((end_time - LAG(end_time, 1, sysdate)

              OVER(partition by unit_id ORDER BY end_time)) * 24 * 60) as exe_time

  FROM ETL_EXDW_LOG

 order by unit_id, end_time;

------------------------------------------------

--5 lead函數對比：

SELECT unit_id,

       unit_code,

       start_time,

       end_time,

       lead(end_time, 1, sysdate) OVER(partition by unit_id ORDER BY end_time) AS prev_endtime,

       round((lead(end_time, 1, sysdate) OVER(partition by unit_id ORDER BY end_time)-end_time) * 24 * 60) as exe_time

  FROM ETL_EXDW_LOG

 order by unit_id, end_time;