oracle's analytical function issue

Question

Please let me know if the following is off topic, or not clear, or too specific, or too complex to understand. I think the following is a challenge to describe, understand and solve.

CIF=cost, insurance, frieght (basically it is the import value)

The simiplified version of input table (Import) looks like this:

enter image description here So from January to June the value 1 is assigned to SixMonthPeriod column, and the rest of the months are given the value 2.

I then want to calculate unit price for a six period, thus I use

select SixMonthPeriod, ProductDescrip, Sum(weight), Sum (CIF), (Sum (CIF))/(Sum(weight)) as UnitPrice
from Import
group by SixMonthPeriod, ProductDescrip;

This is fine, but I then want to calculate inflation for each product (over a six month period )where I need to use lag (an oracle analytical function). The six month period has to be fixed. Thus, if the previous period for a particular product is missing, then the unit price should be zero. I want to re-begin/begin the calculation of inflation for each product. The unit price and inflation equations looks like the following, respectively:

unit price = (Sum(weight) over a six month period)/(Sum (CIF) over a six month period) inflation = (Current Unit price - previous unit price)/(previous unit price)

I use the following SQL to calculate inflation for a six month period for each product, where the calculation begins again for each product:

select Yr, SixMthPeriod, Product, UnitPrice, LagUnitPrice, ((UnitPrice -LagUnitPrice)/LagUnitPrice) as inflation
from (select Year as Yr, SixMonthPeriod as SixMthPeriod, 
ProductDescrip as product, (Sum (CIF))/(Sum(weight)) as UnitPrice, 
lag((Sum (CIF))/(Sum(weight)))
over (partition by ProductDescrip order by YEAR, SixMonthPeriod) as LagUnitPrice
From Import
group by Year, SixMonthPeriod, ProductDescrip)

The problem is the inflation period is not fixed.

For example, for the result, I get the following:

enter image description here The first two rows are fine and there should be null values because they are my first line, thus there is no LagUnitPrice and inflation.

The third line has a problem where it has taken 0.34 as the LagUnitPrice but actually it is zero (for the period 2016 where SixMthPeriod=1 for the product barley). the oracle analytical functions does not take into account missing rows (e.g. for the period 2016 where SixMthPeriod=1 for the product barley).

how do I fix this problem (if you understand me)?

I have 96 rows, thus I can export the file to excel, and use excel's formulas to fix these exceptions.

Please do not post sample data as images - copy/paste it as text and format it nicely or (even better) post the DDL/DML statements to create the tables and sample data [SQLFiddle](http://sqlfiddle.com/#!4) is helpful for this (but other online Oracle databases are available). — MT0, Apr 25 '18 at 08:30

score 0 · Answer 1 · answered Apr 25 '18 at 11:33

0

You can autogenerate missing periods with nullable price, attach them to your data and do the rest as you did:

select product, year, smp, price, prev_price, (price - prev_price) / prev_price inflation 
  from (    
    select product, year, smp, price, 
           lag(price) over (partition by product order by year, smp) prev_price
      from ( 
        select year, ProductDescrip product, SixMonthPeriod smp, sum(CIF)/sum(weight) price
          from Import
          group by year, SixMonthPeriod, ProductDescrip) a
      full join (
        select distinct year, productdescrip product, column_value smp
          from import cross join table(sys.odcinumberlist(1, 2))) b
      using (product, year, smp))
  order by product, year, smp

^{SQLFiddle demo}

Subquery b is responsible for generating all periods, you can run it separately to see what it produces.

answered Apr 25 '18 at 11:33

Ponder Stibbons

14,723
2
21
24

thanks for the reply. sorry for the slow response. I had lots of work and slow internet because I am in Ethiopia. – Elias Kellow May 05 '18 at 08:52
I looked at the SQLFiddle Demo. Please look at between the sixth row and the seventh row. The period 2016 for smp=2 (between July & December) is missing. I wanted to keep the period fixed. the interest calculation should consider the current smp period and the previous smp period. – Elias Kellow May 05 '18 at 09:16
SQL does not work for this calculation, because the previous period (in our example is period 2016 for smp=2 (between July & December)) is zero. You cannot divide by zero. remember the infation calculation = (current price - previous price)/previous price. – Elias Kellow May 05 '18 at 09:17
I fixed this issue by cleaning the data, i.e. when doing analysis using microStrategy (i do not work for them). When entering data for analysis, you can prepare data (it is easy to do). visit https://www.youtube.com/watch?v=E6b6gLESXiY – Elias Kellow May 05 '18 at 09:21
i fixed the issue (using microstrategy's wrangle) and removing rows for analysis. thus, where the previous period is missing, I removed the current period. my calculations now have fixed time – Elias Kellow May 05 '18 at 09:37
really sorry for the slow response. – Elias Kellow May 05 '18 at 09:41

oracle's analytical function issue

1 Answers1