The magical function of Oracle's over sub-functions

Summary

• Oracle's over-child functions can be sorted by the specified field grouping, sorting the result set for the same grouping field, where partition by is the grouping field, and order by specifies the sort field, which is an unexpected effect on the statistical analysis of such problems.

1. The magical functions of over function

Example 1: Cumulative summation

Select fdate Date, total amount, tax, sum  Over (order by FDate) cumulative amount,sum over (order by
From
Select fildate fdate,sumtotal,sumfromgroupby 
Order  by FDate;

Example 2: Cumulative summation Month Group statistics

Selectfdate Date, total amount, tax,sum(total) Over(Order  byFDate) Cumulative amount,sum(tax) Over(Order  byfdate) accumulated tax,sum(total) Over(Partition byYymmOrder  byFDate) This month's cumulative amount,
sum(tax) Over(Partition byYymmOrder  byfdate) Accumulated tax this month, Yymm from (SelectFildate Fdate,to_char (Fildate,'yyyy-mm') Yymm,sumTotal,sum(tax) fromStkin TGroup  byfildate)
Order  byFdate,yymm;

Example 3: Proportion calculation

Selectfdate Date, total amount, tax,sum(total) Over(Order  byFDATE,YYMM) Cumulative amount,sum(tax) Over(Order  byfdate,yymm) accumulated tax,sum(total) Over(Partition byYymmOrder  byFDATE,YYMM) This month's cumulative amount,sum(tax) Over(Partition byYymmOrder  byfdate,yymm) Accumulated tax this month,sum(total) Over() cumulative amount, -*round(Total/sum(total) Over(),4) accounted for, -*round(Total/sum(total) Over(Partition byYYMM),4) monthly ratio, yymm from (SelectFildate Fdate,to_char (Fildate,'yyyy-mm') Yymm,sumTotal,sum(tax) fromStkin TGroup  byfildate)
Order  byFdate,yymm;

Note 1:partition by denotes grouping

Note 2: In the "... from EMP;" do not add the ORDER BY clause, using the Analytic function (partition by yymm order by Fdade,yymm) in the ordered statement, if the end of the sentence added to the sort clause, the same fall, not consistent, The result is laborious.

2. Use Lag (), lead () to take the upper and lower N record values

Example 1:

Selectfdate Date, total amount, tax value, lag (total,1,NULL) Over(Order  byfdate) on 1 record amount, lead (total,1,NULL) Over(Order  byfdate) Under 1 record amount, yymm from (SelectFildate Fdate,to_char (Fildate,'yyyy-mm') Yymm,sumTotal,sum(tax) fromStkin TGroup  byfildate)Order  byFdate,yymm;

The magical function of Oracle's over sub-functions