Oracle analysis Functions (5) Other

1. In addition to using numbers to specify the window range, we can also use date types such as:

2.lead and Lag, returns a record in the current window that is n from the current record. Lag for forward record, lead for backward fetch record

SELECT prod_id,  
  lag (prod_list_price,1) over (order by prod_id) Pre_1,  
  lag (prod_list_price,2) over (order by prod _id) pre_2,  
  Prod_list_price, lead  
  (prod_list_price,1) over (order by prod_id) Lead_1, lead  
  (prod_list_ price,2) over (order by prod_id) Lead_2 from the products  
;

 prod_id pre_1 pre_2 prod_list_price lead_1 lead_2------------------------------------------                     --------------------13 899.99 999.99 999.99 14 899.99     999.99 999.99 299.99 15 999.99 899.99 999.99 299.99 1099.99 16      999.99 999.99 299.99 1099.99 1299.99 17 299.99 999.99 1099.99 1299.99 55.99 18 1099.99 299.99 1299.99 55.99 599.99 19 1299.99 1099.99 55.9 9 599.99 899.99 20 55.99 1299.99 599.99 899.99 24.99 21 599.99-55      .99 899.99 24.99 21.99 22 899.99 599.99 24.99 21.99 45.99 23     24.99 899.99 21.99 45.99 112.99 24 21.99 24.99 45.99 112.99 149.99 25 45.99 21.99 112.99 149.99 44.99 26 112.99 45.99 149.99 44.99 199.9     9 27 149.99 112.99 44.99 199.99 499.99 28 44.99 149.99 199.99            499.99 9.99 29 199.99 44.99 499.99 9.99 8.99 30 499.99 199.99       9.99 8.99 67.99 31 9.99 499.99 8.99 67.99 44.99 32 8.99 9.99 67.99 44.99 39.99 33 67.99 8.99 44.99 39.99 49      .99 34 44.99 67.99 39.99 49.99 44.99 35 39.99 44.99 49.99 44.99 54.99 49.99 39.99 44.99 54.99 29.99 

3.frist_value Last_value returns the first and last record of the current window

SELECT prod_id,  
  first_value (Prod_list_price) over (order by prod_id rows BETWEEN 1 preceding and 1 following) Pre_prod _price,  
  Prod_list_price,  
  last_value (Prod_list_price) over (order by prod_id rows BETWEEN 1 preceding and 1 follow ing) Next_prod_price from products  
;

 prod_id pre_prod_price prod_list_price next_prod_price---------------------------------------------------         13 899.99 899.99 999.99 14 899.99 999.99 999.99 15 999.99 999.99 299.99 16 999.99 299.99 1099.99 17 29           9.99 1099.99 1299.99 18 1099.99 1299.99 55.99 19 1299.99 55.99 599.99 20 55.99 599.99 899.99 21 599.99 89           9.99 24.99 22 899.99 24.99 21.99 23 24.99 21.99 45.99 24 21.99 45.99 112.99 25 45.99 112.99 14  
     9.99 26 112.99 149.99 44.99 27 149.99 44.99 199.99   28 44.99       199.99 499.99 29 199.99 499.99 9.99 30 499.99           9.99 8.99 31 9.99 8.99 67.99 32 8.99 67.99           44.99 33 67.99 44.99 39.99 34 44.99 39.99  
     49.99 35 39.99 49.99 44.99 36 49.99 44.99 54.99 Notoginseng 44.99 54.99 29.99 

4. Performance Tips

If we use SUM (col2) over (the order by col1 range between unbounded preceding and unbounded following), then each record is statistically calculated and executed efficiently, and if The use of sum (col2) over () is only counted once, the execution efficiency will be improved. The following example shows an efficiency of 1/3

Sql>/  
      
  COUNT (*)  
----------  
    918843  
      
time:  00:00:00.81  
sql> Select COUNT (*) from (  
Select Prod_id,sum (Amount_sold) over () from sales)  
;  2    3    
      
  COUNT (*)  
----------  
    918843  
      
time:  00:00:00.27

5.ration_to_report Statistics The percentage of the current record in the current window

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/