how to find average of each row for each customer

Database
Enthusiast

how to find average of each row for each customer

I have a table like this

    CUST_ID                  purchase_dt    prev_purch_dt    gap   
1    543,278,201           2/25/2016                                      0             
2    543,278,201           3/24/2016    2/25/2016                 27   
3    543,278,201           4/28/2016    3/24/2016                 34 

4    543,278,201           7/27/2016    4/28/2016                 87

5    543,278,201           9/15/2016    7/27/2016                 56

6    543,278,201           9/21/2016    9/15/2016                 6 

 

The column 'gap' is the time in days between the customer;s purchases. I would like to calculate the average gap and standard deviaiton after every purchase and hence have an 'average gap' value for each row. SO, for first row, average gap is 0/1 = 0, for second row it will be (0+27)/2 and so on  Is there a way to do that?

 

Thanks


Accepted Solutions
Apprentice

Re: how to find average of each row for each customer

Even better!

 

So you can use:

SELECT a.*
   ,AVG(gap) OVER(PARTITION BY a.custid ORDER BY a.purchase_dt ASC ROWS UNBOUNDED PRECEDING) AS avg_gap
  ,STDDEV_SAMP(gap)  OVER(PARTITION BY a.custid ORDER BY a.purchase_dt ASC ROWS UNBOUNDED PRECEDING) AS stddev_gap
FROM vt1 AS a

Cheers,

Dave

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
1 ACCEPTED SOLUTION
4 REPLIES
Apprentice

Re: how to find average of each row for each customer

Hi,

Try the following:

SELECT a.*
   ,AVG(gap) OVER(PARTITION BY a.custid ORDER BY a.purchase_dt ASC ROWS UNBOUNDED PRECEDING) AS avg_gap
   ,dt1.stddev_gap
FROM vt1 AS a
INNER JOIN (SELECT custid,STDDEV_SAMP(gap) AS stddev_gap
            FROM vt1
	    GROUP BY 1) AS dt1
  ON a.custid = dt1.custid					   
;

A couple of notes:

- I have used STDDEV_SAMP just as an example. There is also a STDDEV_POP (https://info.teradata.com/HTMLPubs/DB_TTU_15_10/index.html#page/SQL_Reference/B035_1145_151K/STDDEV_...) which one you use is up to you.

- I'm assuming your standard deviation requirement is 'per custid' value. if not you might need to change the derived table.

- The STDDEV_xxx functions are not windowed aggregates and (in this case) mixing that function with the windowed aggregate will not give you what you need - hence the derived table.

 

HTH

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Highlighted
Junior Contributor

Re: how to find average of each row for each customer


DaveWellman wrote:

 

- The STDDEV_xxx functions are not windowed aggregates and (in this case) mixing that function with the windowed aggregate will not give you what you need - hence the derived table.

Hi Dave, 

of course STDDEV_SAMPLE, etc., is available in combination with OVER, only KURTOSIS and SKEW is missing :-)

Apprentice

Re: how to find average of each row for each customer

Even better!

 

So you can use:

SELECT a.*
   ,AVG(gap) OVER(PARTITION BY a.custid ORDER BY a.purchase_dt ASC ROWS UNBOUNDED PRECEDING) AS avg_gap
  ,STDDEV_SAMP(gap)  OVER(PARTITION BY a.custid ORDER BY a.purchase_dt ASC ROWS UNBOUNDED PRECEDING) AS stddev_gap
FROM vt1 AS a

Cheers,

Dave

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: how to find average of each row for each customer

That worked ! Thanks a lot.