Database

turn on suggestions

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

Showing results for

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

12-21-2016
10:42 AM

12-21-2016
10:42 AM

Query help to calculate sum of delta over groups ordered by time

Hello Experts,

I am new to teradata and trying to figure out how to calcualte sum of delta over groups when data needed to ordered by time.

I am attaching two pictures one with source data and in it I new to calculate a column as shown in green for the delta's and retrieve data as shown in expected result by doing the sum of deltas.

Thanks,

Naveen

**Source Data:**

**Expected Result:**

2 REPLIES

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

12-22-2016
02:40 AM

12-22-2016
02:40 AM

Re: Query help to calculate sum of delta over groups ordered by time

select equipment,group,sum(measurement) over (partition by group) as sumof change from tablename;

Note:

This query written in sql server. Change it according to Teradata

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

12-22-2016
03:47 AM

12-22-2016
03:47 AM

Re: Query help to calculate sum of delta over groups ordered by time

Based on your example data it looks like the measurement is always increasing, then it's a simple MAX-MIN to get the difference:

SELECT equipment, subgroup, group, Max(measurement)-Min(measurement) AS diff FROM tab group BY 1,2,3 HAVING Count(*) > 1

Otherwise you need to find the first and last row within a group and subtract them:

SELECT equipment, subgroup, group, Last_Value(measurement) -- value in last row Over (PARTITION BY equipment, subgroup, group ORDER BY time ROWS BETWEEN Unbounded Preceding AND Unbounded Following) - measurement AS diff FROM tab QUALIFY Row_Number() -- return 1st row only Over (PARTITION PARTITION BY equipment, subgroup, group ORDER BY time) = 1 AND -- if there's more than one row Count(*) Over (PARTITION PARTITION BY equipment, subgroup, group) > 1

Now you got the difference and then it's a simple:

SELECT equipment, group, SUM(diff) FROM (previous select) as dt GROUP BY 1,2