oracle 分析函数的使用（Oracle analysis of the use of function）.docVIP

oracle 分析函数的使用（Oracle analysis of the use of function） Analysis of grammatical function FUNCTION_NAME (argument, argument...) OVER (partition-clauseorder-by-clausewindowing clause) For example: Sum (SAL) over (partition by deptno order by ename new_alias) Over is a keyword that identifies the analysis function, otherwise the query analyzer cannot distinguish (sum) and (sum) aggregation function analysis function Partition by deptno is an optional partitioning clause, if there is any partition clause, all the results can be regarded as a single large area Order by ename order is an optional by clause, some functions need it, some are not sorted. Rely on those functions, such as data access for the results from the LAG and LEAD, a line of the previous line and must be used, other functions, such as AVG, you do not need to have any window function. In the ranking, this clause is mandatory, it is specified in the calculation function within a set of data is how to sort, in the order by nulls last can be added after the words, such as order by comm desc nulls last, said the sort column empty rows ignore comm. Analysis of function is executed in a query in the last set of operations, you cannot use where or having in the above sentence. - Example DEMO@Wesley customer_id, region_id select, sum (customer_sales) total, 2 (rank) over (order by sum desc rank (customer_sales)), 3 (dense_rank) over (order by sum desc dense_rank (customer_sales)), 4 (row_number) over (order by sum desc row_number (customer_sales)) 5 from user_order 6 group by region_id, customer_id; REGION_ID CUSTOMER_ID TOTAL RANK DENSE_RANK ROW_NUMBER ------------------------------------------------------------- 9252232703111 8171944281222 7141929774333 541878275444 10261808949555 661788836666 8201413722777 10271322747888 7131310434999 7151255591101010 8 18 1253840 11 11 11 -------------------------------------------------------------- + + | 9 23 1224992 12 12 12 | | 9 24 1224992 12 12 13 | | 5 2 1224992 12 12