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

Database
Visitor

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:

Source Data.jpg

Expected Result:

Expected Result.jpg

2 REPLIES
Enthusiast

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

Junior Contributor

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