Find max value based on sequential events

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.
Highlighted

Find max value based on sequential events

I have the following data set

 

ID            date       error_flag            error_flag1          values1

B             03/04     0                              0                              3456

A             04/04     1                              5                              1234

A             04/04     1                              5                              5678

A             04/04     1                              5                              234        

A             04/04     1                              4                              1267

A             04/04     1                              3                              1679      

A             04/04     1                              2                              235

A             04/04     1                              1                              12

C             04/04     0                              0                              7

 

 

THE DATA NEEDS TO BE GROUPED BY ID AND DATE

FOR EACH ERROR_FLAG=1 AND ERROR_FLAG=5  I WANT TO FIND THE MAX VALUES1 FOR THE NEXT 5 CONSECUTIVE ROWS

 

TO GIVE ME SOMETHING LIKE THIS

 

ID            DATE     ERROR_FLAG     ERROR_FLAG1   VALUES1  MAX(VALUES1)

B             03/04     0                              0                              1                              NULL

A             04/04     1                              5                              1234                       5678

A             04/04     1                              5                              5678                       5678

A             04/04     1                              5                              234                         1679

A             04/04     1                              4                              1267                       NULL

A             04/04     1                              3                              1679                       NULL    

A             04/04     1                              2                              235                         NULL

A             04/04     1                              1                              12                           NULL

C             04/04     0                              0                              7                              NULL

 Sorry formatting a bit off but if you copy the data out you should get the idea....i also have sequence number on the data that might be useful

 

1 REPLY 1
Teradata Employee

Re: Find max value based on sequential events

Hi scottie123,

 

The following query should do the job.

Datas

create multiset volatile table mvt_datas
( seq           integer
, id            char(1)
, dt            date
, error_flag    byteint
, error_flag1   byteint
, values1       integer
)
primary index (id, dt)
unique index (seq)
on commit preserve rows;

insert into mvt_datas values (1, 'B', date '2019-04-03', 0, 0, 3456);
insert into mvt_datas values (2, 'A', date '2019-04-04', 1, 5, 1234);
insert into mvt_datas values (3, 'A', date '2019-04-04', 1, 5, 5678);
insert into mvt_datas values (4, 'A', date '2019-04-04', 1, 5,  234);    
insert into mvt_datas values (5, 'A', date '2019-04-04', 1, 4, 1267);
insert into mvt_datas values (6, 'A', date '2019-04-04', 1, 3, 1679);  
insert into mvt_datas values (7, 'A', date '2019-04-04', 1, 2,  235);
insert into mvt_datas values (8, 'A', date '2019-04-04', 1, 1,   12);
insert into mvt_datas values (9, 'C', date '2019-04-04', 0, 0,    7);

collect stats column (id, dt), column (seq) on mvt_datas;

Query

  select id, dt, error_flag, error_flag1, values1
       , case
           when error_flag = 1 and error_flag1 = 5
           then max(values1) over(partition by id, dt order by seq asc rows between current row and 5 following)
         end as max_values1
    from mvt_datas
order by seq asc;


id  dt         error_flag  error_flag1  values1  max_values1
--  ---------- ----------  -----------  -------  -----------
B   2019-04-03          0            0     3456
A   2019-04-04          1            5     1234         5678
A   2019-04-04          1            5     5678         5678
A   2019-04-04          1            5      234         1679
A   2019-04-04          1            4     1267
A   2019-04-04          1            3     1679
A   2019-04-04          1            2      235
A   2019-04-04          1            1       12
C   2019-04-04          0            0        7