Database
Highlighted
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
Senior 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
Senior 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
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 :-)

Senior 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.