SQL> select * from temp2; NAME SORCE ---------- ---------- 1 43 2 23 3 42 4 87 5 12 <span style="font-size: 14px;"><strong><span style="color: #ff0000;">1、数据累加</span></strong></span> SQL> SELECT NAME, sum(sorce) OVER(ORDER BY NAME) 2 FROM temp2 3 ORDER BY NAME; NAME SUM(SORCE)OVER(ORDERBYNAME) ---------- --------------------------- 1 43 2 66 3 108 4 195 5 207 <span style="font-size: 14px;"><strong><span style="color: #ff0000;">2、去掉最大值和最小值</span></strong></span> SQL> SELECT NAME, 2 sorce, 3 LAG(sorce) over(order by sorce) Lag_List, 4 LEAD(sorce) over(order by sorce) Lead_List 5 FROM temp2; NAME SORCE Lag Lead ---------- ---------- ---------- ---------- 5 12 23 2 23 12 42 3 42 23 43 1 43 42 87 4 87 43 |
1、著名分析函数--排序
SQL> SELECT name, 2 value, 3 RANK() OVER(order by value) RANK_SORT, 4 DENSE_RANK() OVER(order by value) DENSE_SORT, 5 ROW_NUMBER() OVER(order by value) ROW_SORT 6 FROM sorce; NAME VALUE RANK_SORT DENSE_SORT ROW_SORT ---------- ------ ---------- ---------- ---------- wu 21 1 1 1 zhang 60 2 2 2 Li 70 3 3 3 xue 119 5 5 5 <span style="color: #ff0000;">wang 130 6 6 6 chen 130 6 6 7 sun 175 8 7 8</span> zhao 285 9 8 9 su 359 10 9 10 Li 480 11 10 11<br> |
