Total Pageviews

2014/02/11

Oracle Analytic functions

The general syntax of analytic function is:
Function(arg1,..., argn) OVER ( [PARTITION BY <...>] [ORDER BY <....>] [] )

  •  The OVER clause defines a window or user-specified set of rows within a query result set. A window function then computes a value for each row in the window. You can use the OVER clause with functions to compute aggregated values such as moving averages, cumulative aggregates, running totals, or a top N per group results
  • PARTITION BY divides the query result set into partitions. The window function is applied to each partition separately and computation restarts for each partition.
  • ORDER BY clause Defines the logical order of the rows within each partition of the result set. 
According to this SQL statement:
1:  select FYR, --會計年度  
2:        ROC_YM, --年月  
3:        INCM_LOAN --融資財源收入  
4:  from FMS406VC   
5:  where fyr=102 and ROC_YM=10201;  

We can get this result, and we would like to accumulate the value of INCM_LOAD:

We can use partition by clause to accumulate the value of INCM_LOAD
1:  select FYR, --會計年度  
2:        ROC_YM, --年月  
3:        SUM(SUM(INCM_LOAN)) OVER (PARTITION BY FYR ORDER BY ROC_YM) AS CUM_INCM_LOAD --累積融資財源收入  
4:  from FMS406VC   
5:  where fyr=102 and ROC_YM=10201  
6:  GROUP BY FYR ,ROC_YM;  

And we can get the expected result:

No comments: