CODEWITHSUNDEEP
sqloracle-databaseoracle10g
mahen
mahen

Reputation: 31

alternative to lag SQL command

I have a table which has a table like this.

Month-----Book_Type-----sold_in_Dollars
Jan----------A------------ 100
Jan----------B------------ 120
Feb----------A------------ 50
Mar----------A------------ 60
Mar----------B------------ 30

and so on

I have to calculate the expected sales for each month and book type based on the last 2 months sales. So for March and type A it would be (100+50)/2 = 75 For March and type B it is 120/1 since no data for Feb is there.

I was trying to use the lag function but it wouldn't work since there is data missing in a few rows.

Any ideas on this?

Upvotes: 2

Views: 3906

Answers (4)

Mike Meyers
Mike Meyers

Reputation: 2895

Since it plans to ignore missing values, this should probably work. Don't have a database to test it on at the moment but will give it another go in the morning

select 
  month, 
  book_type, 
  sold_in_dollars, 
  avg(sold_in_dollars) over (partition by book_type order by month
    range between interval '2' month preceding and interval '1' month preceding) as avg_sales
from myTable;

This sort of assumes that month has a date datatype and can be sorted on... if it's just a text string then you'll need something else.

Normally you could just use rows between 2 preceding and 1 preceding but but this will take the two previous data points and not necessarily the two previous months if there are rows missing.

You could work it out with lag but it would be a bit more complicated.

Upvotes: 1

Jon Heller
Jon Heller

Reputation: 36902

A partition outer join can help create the missing data. Create a set of months and join those values to each row by the month and perform the join once for each book type. I created the months January through April in this example:

with test_data as
(
  select to_date('01-JAN-2010', 'DD-MON-YYYY') month, 'A' book_type, 100 sold_in_dollars from dual union all
  select to_date('01-JAN-2010', 'DD-MON-YYYY') month, 'B' book_type, 120 sold_in_dollars from dual union all
  select to_date('01-FEB-2010', 'DD-MON-YYYY') month, 'A' book_type, 50 sold_in_dollars from dual union all
  select to_date('01-MAR-2010', 'DD-MON-YYYY') month, 'A' book_type, 60 sold_in_dollars from dual union all
  select to_date('01-MAR-2010', 'DD-MON-YYYY') month, 'B' book_type, 30 sold_in_dollars from dual
)
select book_type, month, sold_in_dollars
  ,case when denominator = 0 then 'N/A' else to_char(numerator / denominator) end expected_sales
from
(
  select test_data.book_type, all_months.month, sold_in_dollars
    ,count(sold_in_dollars) over
      (partition by book_type order by all_months.month rows between 2 preceding and 1 preceding) denominator
    ,sum(sold_in_dollars) over
      (partition by book_type order by all_months.month rows between 2 preceding and 1 preceding) numerator
  from 
    (
      select add_months(to_date('01-JAN-2010', 'DD-MON-YYYY'), level-1) month from dual connect by level <= 4
    ) all_months
    left outer join test_data partition by (test_data.book_type) on all_months.month = test_data.month 
)
order by book_type, month

Upvotes: 0

SteveCav
SteveCav

Reputation: 6729

What about something like (forgive the sql server syntax, but you get the idea):

Select Book_type, AVG(sold_in_dollars)
from MyTable
where Month in (Month(DATEADD('mm'-1,GETDATE)),Month(DATEADD('mm'-2,GETDATE)))
group by booktype

Upvotes: 0

Vincent Savard
Vincent Savard

Reputation: 35927

As far as I know, you can give a default value to lag() :

  SELECT Book_Type, 
         (lag(sold_in_Dollars, 1, 0) OVER(PARTITION BY Book_Type ORDER BY Month) + lag(sold_in_Dollars, 2, 0) OVER(PARTITION BY Book_Type ORDER BY Month))/2 AS expected_sales
    FROM your_table
GROUP BY Book_Type

(Assuming Month column doesn't really contain JAN or FEB but real, orderable dates.)

Upvotes: 0

Related Questions