oracle分析函数：三、first,first_value,lag,lead函数
发布时间：2020-12-12 16:18:20 所属栏目：百科 来源：网络整理
导读：/************************************************ **分析函数-First/Last函数 **时间：2016-7-8 *************************************************/ --1 语法 aggregate_function keep ( dense_rank first order by expr desc nulls last) over (query p
|
/************************************************ **分析函数-First/Last函数 **时间：2016-7-8 *************************************************/ --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,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,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,tjrq_q; /************************************************ **分析函数-First_value/Last_value函数 **时间：2016-7-8 **简介： *************************************************/ --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,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; /************************************************ **分析函数-lag/lead函数 **时间：2016-7-8 **简介： *************************************************/ --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),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,default) (ignore nulls| respect nulls) over (query_partition_clause order_by_clause) ------------------------------------------------- --4 例子 SELECT unit_id,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,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,lead(end_time,round((lead(end_time,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; （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |