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.
select equipment,group,sum(measurement) over (partition by group) as sumof change from tablename;
This query written in sql server. Change it according to Teradata
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