All Forums Database
quest 20 posts Joined 12/09
10 Feb 2013
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!

 

dnoeth 4628 posts Joined 11/04
11 Feb 2013

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

Dieter

quest 20 posts Joined 12/09
11 Feb 2013

Thanks Dieter, this is very helpful.
Not only the code is efficient, it is short and well written.

You must sign in to leave a comment.