Oracle Analytical Queries Examples

This post as nothing to do with Tableau, here my intention is only to create repository of  Oracle Analytical Queries.

1) OVER PARTITION BY :

cOMPANY  Weeknum TDATE         OPENPRICE HIGHPRICE LOWPRICE CLOSEPRICE  
IIMLC    27      30-JUN-2014   123.4     25.8      23.4     25.05    
IIMLC    27      01-JUL-2014   25.3      25.85     24.9     25.05    
IIMLC    27      02-JUL-2014   25.75     26.4      25.35    25.5    
IIMLC    27      03-JUL-2014   25.55     25.75     24.95    25.25    
IIMLC    27      04-JUL-2014   25.25     25.7      24.65    25    

Above are the Records in dummy table, Our task is to create out as given below:
it as daily average (AVG=(highPrice+lowprice)/2) and weekly average of close (Weekly avg(closeprice)) in all rows

cOMPANY  Weeknum TDATE        OPENPRICE HIGHPRICE LOWPRICE CLOSE AVG  WEEKLY 
IIMLC    27      30-JUN-2014  123.4     25.8      23.4     25.05 24.6 25.17
IIMLC    27      01-JUL-2014  25.3      25.85     24.9     25.05 25.3 25.17   
IIMLC    27      02-JUL-2014  25.75     26.4      25.35    25.5  25.8 25.17
IIMLC    27      03-JUL-2014  25.55     25.75     24.95    25.25 25.3 25.17 
IIMLC    27      04-JUL-2014  25.25     25.7      24.65    25    25.1 25.17

Solution Query:

    SELECT COMPANY,TDATE,to_char(TDATE,'IW')
    WEEKNUM,OPENPRICE,HIGHPRICE,LOWPRICE,
    CLOSEPRICE,(HIGHPRICE+LOWPRICE)/2 AS DAILYAVG,
    ROUND(AVG(CLOSEPRICE) OVER (PARTITION BY to_char(TDATE,'IW')),2) WEEKLYAVG,
    FROM MARKETMOVEMENT;

Explanation:  In above query

AVG(CLOSEPRICE) OVER (PARTITION BY to_char(TDATE,'IW')


This line is doing all the work for us it is actually calculating the average by partitioning the out on the basis of the week number to get the weekly average. 

Click more about Oracle Analytic functions