Oracle分析函數-等級函數

標籤：

SELECT b.*,
RANK() OVER(PARTITION BY b.dname ORDER BY b.sal) AS rank_num,
Dense_rank() OVER(PARTITION BY b.dname ORDER BY b.sal) AS dence_rank,
MIN(sal) KEEP(dense_rank FIRST ORDER BY b.sal) OVER(PARTITION BY b.dname) AS first_val,
MAX(sal) KEEP(DENSE_rank LAST ORDER BY b.sal) OVER(PARTITION BY b.dname) AS last_val,
LAG(sal, 1, 0) OVER(PARTITION BY b.dname ORDER BY b.sal) AS pre_val,
LEAD(sal, 1, 0) OVER(PARTITION BY b.dname ORDER BY b.sal) AS following_val,
first_value(b.sal) OVER(PARTITION BY b.dname ORDER BY b.sal) AS first_val,
first_value(b.sal) OVER(PARTITION BY b.dname ORDER BY b.sal DESC) AS first_val_desc,
last_value(b.sal) OVER(PARTITION BY b.dname ORDER BY b.sal) AS last_val,
last_value(b.sal) OVER(PARTITION BY b.dname ORDER BY b.sal DESC) AS last_val_desc,
row_number() OVER(PARTITION BY b.dname ORDER BY b.sal) AS row_num
FROM B

Oracle分析函數-等級函數