Moving window function

Database
Enthusiast

Moving window function

This query is regarding moving window function.

What to implement? : I need to have next row start date as current row end date in a teradata table. Following are the important DDL's used for same.

create table changeadm_55.tmp(

col1 integer,

col2 integer,

col3 integer,

st_dt timestamp(6),

end_dt timestamp(6))

primary index(col1);

insert into changeadm_55.tmp values(1,2,3,current_timestamp,cast('9999/12/12' as timestamp(6) format 'YYYY/MM/DD' ));

insert into changeadm_55.tmp values(1,3,4,current_timestamp + interval '1' day,cast('9999/12/12' as timestamp(6) format 'YYYY/MM/DD' ));

insert into changeadm_55.tmp values(1,2,3,current_timestamp + interval '3' day,cast('9999/12/12' as timestamp(6) format 'YYYY/MM/DD' ));

insert into changeadm_55.tmp values(2,9,10,current_timestamp + interval '4' day,cast('9999/12/12' as timestamp(6) format 'YYYY/MM/DD' ));

To implement the above logic,  I used the following query.

UPDATE   T1

from changeadm_55.tmp T1,

 (

 select b.col1,  b.col2, b.col3, b.st_dt ,  case when b.st_dt = b.end_dt then cast('9999/12/12' as timestamp(6) format 'YYYY/MM/DD' ) else b.end_dt  end as  end_date

 from (

 SEL a.col1, a.col2,a.col3, a.st_dt,a.end_dt FROM 

  (SELECT col1,col2,col3, st_dt, MAX(st_dt) OVER (PARTITION BY col1 ORDER BY st_dt desc ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS end_dt from changeadm_55.tmp) as a

  ) as b

 ) as T2

set end_dt = T2.end_date

where T1. COL1 = T2.COL1 AND T1.COL2 = T2.COL2 AND T1.COL3 = T2.COL3 and T1.ST_DT = T2.ST_DT AND cast(T1.end_dt as timestamp(6)  format 'YYYY/MM/DD' ) (char(10)) = '9999/12/12'

My question is whether there is an option to get the solution without using the outer query (i.e. the condition T1.ST_DT = T2.ST_DT) and simply use the following query wrapped by update statement.

 SEL a.col1, a.col2,a.col3, a.st_dt,a.end_dt FROM 

  (SELECT col1,col2,col3, st_dt, MAX(st_dt) OVER (PARTITION BY col1 ORDER BY st_dt desc ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS end_dt from changeadm_55.tmp) as a

Appreciate!

2 REPLIES
Senior Apprentice

Re: Moving window function

You should change the update to a merge, which should get a much better plan:

MERGE INTO tmp AS tgt
USING
(
SELECT
col1,col2,col3, ST_DT,
COALESCE(MAX(ST_DT)
OVER (PARTITION BY col1
ORDER BY ST_DT ROWS
BETWEEN 1 FOLLOWING AND 1 FOLLOWING)
, TIMESTAMP '9999-12-12 00:00:00') AS end_date
FROM tmp
) AS src
ON tgt.COL1 = src.COL1
AND tgt.COL2 = src.COL2
AND tgt.COL3 = src.COL3
AND tgt.ST_DT = src.ST_DT
AND tgt.end_dt = TIMESTAMP '9999-12-12 00:00:00'
WHEN MATCHED
THEN UPDATE
SET end_dt = src.end_date

Btw, it should be enough to specify the Primary Key column(s) plus the end_date in the ON clause.

Dieter

Enthusiast

Re: Moving window function

Thanks Dieter, this is very helpful.

Not only the code is efficient, it is short and well written.