Time Difference in Hours and Seconds Over a Partition Window in Teradata (Sessionizing Records)

Time difference in hours and seconds over a partition window in Teradata (Sessionizing Records)

There's no LAG in Teradata, but you can rewrite it:

SELECT
  t.*
  , (time)
    - min(time) 
      OVER (PARTITION BY cust_id 
            ORDER BY time
            ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) SECOND(4)
FROM
  table_01 as t

When you try to get seconds you will encouter "Interval Overflow" errors, i.e. more than 9999 seconds. Either change to DAY(4) TO SECOND or use this SQL UDF I wrote a few years ago for calculating the difference of two timestamps in seconds:

REPLACE FUNCTION TimeStamp_Diff_Seconds
(
   ts1 TIMESTAMP(6)
  ,ts2 TIMESTAMP(6)
)
RETURNS DECIMAL(18,6)
LANGUAGE SQL
CONTAINS SQL
RETURNS NULL ON NULL INPUT
DETERMINISTIC
SQL SECURITY DEFINER
COLLATION INVOKER
INLINE TYPE 1
RETURN
(CAST((CAST(ts2 AS DATE)- CAST(ts1 AS DATE)) AS DECIMAL(18,6)) * 60*60*24)
      + ((EXTRACT(  HOUR FROM ts2) - EXTRACT(  HOUR FROM ts1)) * 60*60)
      + ((EXTRACT(MINUTE FROM ts2) - EXTRACT(MINUTE FROM ts1)) * 60)
      +  (EXTRACT(SECOND FROM ts2) - EXTRACT(SECOND FROM ts1))
;

Get values from first and last row per group

This is a bit of a pain, because Postgres has the nice window functions first_value() and last_value(), but these are not aggregation functions. So, here is one way:

select t.name, min(t.week) as minWeek, max(firstvalue) as firstvalue,
       max(t.week) as maxWeek, max(lastvalue) as lastValue
from (select t.*, first_value(value) over (partition by name order by week) as firstvalue,
             last_value(value) over (partition by name order by week) as lastvalue
      from table t
     ) t
group by t.name;

Related Topics