All Forums Database
adishri22 9 posts Joined 09/15
01 Sep 2015
avg sale of quarter with previous quarter sale

I have a table t in which there are various attribute like product,year,qtr,month,sale. I have to calculate the avg_qtr sale and show previous avg_qtr sale. I have used windowing function to show previous avg_qtr sale. I have read about lag but here it is not possible to use as it is not fixed after how many rows it will be repeated. My table structure is like this-

Product Year Qtr Month Sales
P1 2013 1 JAN 2000
P2 2013 2 APR 3000
P3 2013 3 JUL 4000
P4 2013 4 OCT 5000
P2 2014 1 FEB 6000
P3 2014 2 MAY 8000
P1 2014 3 AUG 5000
P4 2014 4 NOV 6000
P4 2015 1 MAR 7000
P1 2015 2 JUN 1000
P1 2015 3 SEP 2000
P3 2015 4 DEC 3000
P1 2013 1 JAN 5000
P2 2013 2 APR 2000
P3 2013 3 JUL 5000
P4 2013 4 OCT 6000
P1 2014 1 FEB 7000
P2 2014 2 MAY 1000
P3 2014 3 AUG 2000
P4 2014 4 NOV 3000
P1 2015 1 MAR 5000
P2 2015 2 JUN 2000
P3 2015 3 SEP 3000
P4 2015 4 DEC 5000
P2 2013 1 JAN 2000
P1 2013 2 APR 3000
P4 2013 3 JUL 4000
P3 2013 4 OCT 5000

 
my query is-

select product,year,month,sales,qtr,av, lag (av) over (order by QTR) from (
select product,year,month,sales ,qtr,round (avg(sales) over (partition by qtr,year),2) as av from t

but as said it won't work in pre_avg_sale order by qtr,year); I want output in this format-

product year qtr month sales qtr_avg prv_qtr_avg
Actually I want previous avg_sale such as first quarter of 2014 contains in previous avg_Sale of last quarter of 2013 Is there is way to do this please help!!!
 

kirthi 65 posts Joined 02/12
01 Sep 2015
Create Volatile table test 
AS 
(
SEL 
 PRODUCT
,YR
,QTR
,AVG ( SALES) AS AVG_SALE
,ROW_NUMBER () OVER (ORDER BY PRODUCT,YR,QTR) AS R_NUM
FROM  SANDBOX.TABLE_SRC 
GROUP BY 1,2,3
) WITH DATA 
ON COMMIT PRESERVE ROWS

SEL S.* , T.AVG_SALE, T2.AVG_SALE  AS PRV_QTR_AVG_SALE 
FROM 
SANDBOX.TABLE_SRC  S
INNER JOIN 
TEST  T
ON  S.PRODUCT = T.PRODUCT
AND S.YR  = T.YR
AND S.QTR   = T.QTR  
LEFT  JOIN 
TEST  T2
ON  T.PRODUCT = T2.PRODUCT
AND T.R_NUM -1 = T2.R_NUM
ORDER BY S.YR, S.QTR

Please try this option.
 
 

adishri22 9 posts Joined 09/15
01 Sep 2015

can you expalin what exactly you are doing...I am not undersatnding how this will work...I want to know the approch

adishri22 9 posts Joined 09/15
01 Sep 2015

when I do left join then how can I do on that condition
T.R_NUM -1 = T2.R_NUM 
this column is not in test it is in another table

adishri22 9 posts Joined 09/15
01 Sep 2015

I understand the above join condition bt the data I want is not that I am getting also many products are shown dupliacte, also row_number-1 that condition is not working correctly 

adishri22 9 posts Joined 09/15
02 Sep 2015

anyone can please help me...this is really important for me...I have heard that this forum reply fast to your solution...I need help really :(

kirthi 65 posts Joined 02/12
02 Sep 2015
CREATE VOLATILE TABLE TEST 
AS 
(
SEL 
 PRODUCT
,YR
,QTR
,AVG ( SALES) AS AVG_SALE
,ROW_NUMBER () OVER (ORDER BY YR,QTR) AS R_NUM
FROM  SANDBOX.TEST_SKV 
GROUP BY 1,2,3
) WITH DATA 
ON COMMIT PRESERVE ROWS

-- On this step we create table to store average at product, year, Qtr level
Also we store the row number at Year quarter level

SEL 
  S.PRODUCT
, S.YR, S.QTR
, S.AVG_SALE
, S1.AVG_SALE AS PRV_AVG_SALE
FROM
(
  SEL 
   T1.* 
  ,MAX(T2.R_NUM) AS PRV_RNUM
  FROM TEST T1
  LEFT JOIN TEST T2
  ON T1.PRODUCT = T2.PRODUCT
  AND T1.R_NUM >  T2.R_NUM
  GROUP BY 1,2,3,4,5
) S 
-- this derived table S stores the row number of prev quarter for -- that product, only glitch here is if the previous qtr has no 
-- sales for the product it will go and search next qtr in prev.
 LEFT JOIN test S1
ON S.PRV_RNUM = S1.R_NUM
ORDER BY 1,2,3

-- In total this query gives the summary information of avg sale  
-- ,prev average sales at product, year, qtr, grain

-- If this this doesn't solve your problem, please give sample 
-- data and your expected output. 

 

adishri22 9 posts Joined 09/15
02 Sep 2015

--handles when the current quarter being viewed is 2,3,or 4 because those would still be in the same year when looking at the previous quarter
select t1.product,
t1.year,
t1.month,
t1.sales ,
t1.qtr,
round(avg(t1.sales) over (partition by t1.qtr,t1.year),2) as av,
t2.prev_av
from one t1
left join ( select
product,
year,
month,
sales ,
qtr,
round(avg(sales) over (partition by qtr,year),2) as prev_av
from one
) t2
on t1.year = t2.year
and (t1.qtr - 1) = t2.qtr
where t1.qtr in (2,3,4)
union
--handles the 1st quarter of the year when you need to grab the 4th quarter of the previous year for the previous avg
select t3.product,
t3.year,
t3.month,
t3.sales ,
t3.qtr,
round(avg(t3.sales) over (partition by t3.qtr,t3.year),2) as av,
t4.prev_av
from one t3
left join ( select
product,
year,
month,
sales,
qtr,
round(avg(sales) over (partition by qtr,year),2) as prev_av
from one
) t4
on (t3.year - 1) = t4.year
and t4.qtr = 4
where t3.qtr = 1;

 
I want something like this but its too heavy but I cant eliminate select statements can you?
 

adishri22 9 posts Joined 09/15
02 Sep 2015

The glitch in your code is what I don't want as it will chnage my ouput I want output like this-

quarter year current_avg previous_avg
1 2013 2000 (null) (as there no entrey before that
2 2013 1000 2000
3 2013 3000 1000
4 2013 2000 3000
1 2014 4000 2000

adishri22 9 posts Joined 09/15
03 Sep 2015

WITH AvgSales
AS (SELECT
region,
product,
year,
qtr,
ROUND(AVG(sales), 2) AS avg_Sale
FROM one
GROUP BY region,
product,
year,qtr
 )
SELECT
s.region,
s.product,
s.year,
s.month,
s.sales,
avg.qtr,
avg.avg_Sale AS Qtr_Avg_Sale,
prev.avg_sale AS Prev_Qtr_Avg_Sale
FROM one s
JOIN AvgSales avg
ON s.region = avg.region
AND s.product = avg.product
AND s.QTR = avg.qtr
AND s.year = avg.year
LEFT JOIN AvgSales prev
ON  (s.region = prev.region
AND s.product = prev.product
AND s.year - 1 = prev.year
AND prev.qtr = 4) or
(s.region = prev.region
AND s.product = prev.product
AND s.year = prev.year
AND s.qtr - 1 = prev.qtr) ;

I have made this and its getting me right result but I am not sure will this condition runs always correct as in this condition it is also satisfying for quarter 2,3,4
ON  (s.region = prev.region
AND s.product = prev.product
AND s.year - 1 = prev.year
AND prev.qtr = 4) 
 

You must sign in to leave a comment.