Analysis of oracle functions (rating functions, window functions, etc.)

1. rating function: used for rating, percentage point, n-part, etc. Function Description RANK () returns the ranking of data items in the group. If the ranking is equal, the vacant DENSE_RANK () is left in the ranking to return the ranking of data items in the group, if the ranking is equal, no vacancy will be left in the ranking. CUME_DIST () returns the specified value. for the position of a group of values, "cumulative distribution" (cumulative distribution) PERCENT_RANK () (return the percentage ranking of a value relative to a group of values NTILE () returns the value ROW_NUMBER () after n parts for each record returns a number www.2cto.com RANK (), DENSE_RANK () RANK () is reserved as null when an element with the same level is displayed. DENSE_RANK () is not. Eg: A product type has two sides tied for the first RANK (): The first two is 1, the third is 3DENSE_RANK (): The first two is 1, the third is 21 SELECT2 column_name, 3 RANK () OVER (order by column_name DESC) AS rank, 4 DENSE_RANK () OVER (order by sum (column_name) DESC) AS dense_rank5 FROM table_nameOVER is required. Note the sequence in parentheses: in order by, desc NULL values are at the top. In ASC, the NULL values are at the end. You can use nulls last and nulls first to control RANK () OVER (order by column_name desc nulls last) partition by grouping order RANK () OVER (partition by month ORD Er by column_name DESC) in this way, it will be divided BY month, that is, the information to be arranged is first grouped BY the value of month, sorted in the group, each GROUP does not interfere with ROLLUP, CUBE, group sets (only show subtotal information), and RANK (). Use the CUME_DIST (), PERCENT_RANK () Inverse percentage function: PERCENTILE_DISC (x ), PERCENTILE_CONT (x) NTILE () ROW_NUMBER () returns a number for each record starting from 1: SELECT www.2cto.com ROW_NUMBER () OVER (order by column_name DESC) AS row_nameFROM table_name; 2. window functions: you can calculate the accumulation and moving average values within a certain range, within a certain value range, or within a period of time. It can be used in combination with Aggregate functions such as SUM () and AVG. You can combine FIRST_VALUE () and LAST_VALUE () to calculate the cumulative sum of the first and last values in the return window. For example, you can calculate the cumulative sales volume from 1 to 12 months, that is, the value of January for January 1, January, february is the SUM of the 1.2 month values, March is the SUM of the 123 month values, and December is the SUM of the month values and SELECTmonth, SUM (amount) month_amount, SUM (amount )) OVER (order by month rows between unbounded preceding and current row) AS cumulative_amountFROM table_nameGROUP BY monthORDER BY month; where: SUM (amount) Internal SUM (amount) for the value to be accumulated, you can replace it with month_amountORDER BY month to sort the records read BY month, that is The sorting rows between unbounded preceding and current row in the window defines the start AND end points. unbounded preceding is the start point, indicating that the current row is the default value starting from the first ROW, this is equivalent to rows unbounded preceding www.2cto.com PRECEDING. FOLLOWING: meaning after. Calculate SUM (amount) OVER (order by month rows between 3 preceding and current row) AS cumulative_amount BETWEEN the first three months. You can also SUM (amount )) OVER (order by month 3 PRECENDING) AS cumulative_amount SUM (amount) OVER (order by month rows between 1 preceding and 1 FOLLOWING) AS cumulative_amount the first AND last values of the form FIRST_VALUE (SUM (amount) OVER (order by month rows between 1 preceding and 1 FOLLOWING) S xxxx; LAST_VALUE (SUM (amount) OVER (order by month rows between 1 preceding and 1 FOLLOWING) AS xxxx; in this way, the values of the previous row AND next row of the row are 3. report function: This function is used to calculate the cross-group and intra-group partitions (the group here should refer to the order by group) SUM (column_name1) OVER (partition by column_name2, the data is grouped BY column_name2, and the sum of column_name1 is obtained. However, when the table is output, the output columns are duplicated in order by format, for example, if the same column_name2 is used, the same column_name1 4.LAG() and LEAD () will be output to obtain the data LAG () of the record relative to the specified distance of the current record as forward, LEAD () for backward www.2cto.com LAG (Column_name1, 1) OVER (order by column_name2) LEAG (column_name1, 1) OVER (order by column_name2) in this way, the data of the first and last rows is obtained 5. FIRST and LAST get the FIRST value in a sorting group and the LAST value in the group. Can be combined with grouping functions. SELECTMIN (month) KEEP (DENSE_RANK first order by sum (amount) AS highest_sales_month, MIN (month) KEEP (DENSE_RANK last order by sum (amount )) AS lows_sales_monthFROM table_nameGROUP BY monthORDER BY month; in this way, you can find the month with the highest and lowest sales volume in the year. Note: The output is a month, but is determined by SUM (amount. 6. Use linear regression functions 7. Author A_zhu using hypothetical ratings and distribution functions