Database

turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

2 weeks ago

2 weeks ago

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

Solved! Go to Solution.

Labels:

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

2 weeks ago

2 weeks ago

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

www: http://www.ward-analytics.com

1 ACCEPTED SOLUTION

4 REPLIES

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

2 weeks ago

2 weeks ago

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

www: http://www.ward-analytics.com

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

2 weeks ago

2 weeks ago

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 :-)

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

2 weeks ago

2 weeks ago

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

www: http://www.ward-analytics.com

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

2 weeks ago

2 weeks ago

That worked ! Thanks a lot.