moving coordinates without iterative query

Database
Highlighted

moving coordinates without iterative query

My dataset is basically a list of x and y coordinates for moves between locations - people move along the y axis with a cart.  

 

The logic i am struggling to put into a query is keeping track of the cart position, if the move is greater than 10 then they both move to the new location, if the new location is within 10 of the cart then the person moves but the cart does not. This continues until the next location is further than 10 from where the cart was left.

 

The below table is example output that i would like, calculating the 2nd and 3rd columns from the 1st. 

 

For the 2nd column i would do something like..

case when y_coordinate > (sum(y_coordinate) over (rows between 1 preceeding and 1 preceeding order by event_timestamp))+10  -- if the move is 10 away from the cart then move cart to new location

         then y_coordinate

          else sum(cart_position) over (rows between 1 preceedig an 1 preceeding order by event_timestamp))  -- if the location is nearby dont move the cart with you

end as cart_position

 

The problem is that cart_position doesnt exist so i cant reference it without creating an additional column. Then in the additional column i cant reference that column either, so i would end up having to create enough additional columns to cover the realistic maximum number of moves without taking the cart.

 

 

 

y coordinateCart PositionDistance Cart Moved
15150
272712
383811
41380
42380
555517
56550
56550
58550
666611
797913
80790
85790
10010021

 

I have been able to create the data by putting in the values the cart moves and then filling the gaps with the following iterative update, but i would much rather be able to add the logic into the select query:

 

update vtj v1

set new_cart_posn = (select case when abs(v2.new_cart_posn - v1.to_y_coordinate) < 10 then v2.new_cart_posn else v1.to_y_coordinate end
from vtj v2
where v2.rn+1=v1.rn
)


Accepted Solutions
Teradata Employee

Re: moving coordinates without iterative query

Well, yeah 100 millions rows a day is a challenge for sure.

 

I'll still give you the recursive answer so you can test it.

 

Datas

 

create multiset volatile table mvt_datas, no log
( cart_id       byteint      not null
, event_ts      timestamp(0) not null
, y_coord       byteint      not null
)
primary index (cart_id)
on commit preserve rows;

insert into mvt_datas values (1, timestamp '2019-04-16 10:01:00',  15);
insert into mvt_datas values (1, timestamp '2019-04-16 10:02:00',  27);
insert into mvt_datas values (1, timestamp '2019-04-16 10:03:00',  38);
insert into mvt_datas values (1, timestamp '2019-04-16 10:04:00',  41);
insert into mvt_datas values (1, timestamp '2019-04-16 10:05:00',  42);
insert into mvt_datas values (1, timestamp '2019-04-16 10:06:00',  55);
insert into mvt_datas values (1, timestamp '2019-04-16 10:07:00',  56);
insert into mvt_datas values (1, timestamp '2019-04-16 10:08:00',  56);
insert into mvt_datas values (1, timestamp '2019-04-16 10:09:00',  58);
insert into mvt_datas values (1, timestamp '2019-04-16 10:10:00',  66);
insert into mvt_datas values (1, timestamp '2019-04-16 10:11:00',  79);
insert into mvt_datas values (1, timestamp '2019-04-16 10:12:00',  80);
insert into mvt_datas values (1, timestamp '2019-04-16 10:13:00',  85);
insert into mvt_datas values (1, timestamp '2019-04-16 10:14:00', 100);

Query

 

with recursive cte_recurs (cart_id, event_ts, y_coord, cart_coord, dist_moved, rn) as
(
select cart_id
     , event_ts
     , y_coord
     , y_coord
     , 0
     , rn
  from cte_rownum
 where rn = 1
 union all
select rec.cart_id
     , crn.event_ts
     , crn.y_coord
     , case when crn.y_coord > rec.cart_coord + 10 then crn.y_coord else rec.cart_coord end
     , case when crn.y_coord > rec.cart_coord + 10 then crn.y_coord else rec.cart_coord end - rec.cart_coord
     , crn.rn
  from cte_recurs as rec
  join cte_rownum as crn  on crn.cart_id = rec.cart_id
                         and crn.rn      = rec.rn + 1
)
  ,  cte_rownum (cart_id, event_ts, y_coord, rn) as
(
select cart_id, event_ts, y_coord
     , row_number() over(partition by cart_id order by event_ts asc)
  from mvt_datas
)
select cart_id, event_ts, y_coord, cart_coord, dist_moved
  from cte_recurs;

 cart_id event_ts            y_coord cart_coord dist_moved 
 ------- ------------------- ------- ---------- ---------- 
       1 2019-04-16 10:01:00      15         15          0
       1 2019-04-16 10:02:00      27         27         12
       1 2019-04-16 10:03:00      38         38         11
       1 2019-04-16 10:04:00      41         38          0
       1 2019-04-16 10:05:00      42         38          0
       1 2019-04-16 10:06:00      55         55         17
       1 2019-04-16 10:07:00      56         55          0
       1 2019-04-16 10:08:00      56         55          0
       1 2019-04-16 10:09:00      58         55          0
       1 2019-04-16 10:10:00      66         66         11
       1 2019-04-16 10:11:00      79         79         13
       1 2019-04-16 10:12:00      80         79          0
       1 2019-04-16 10:13:00      85         79          0
       1 2019-04-16 10:14:00     100        100         21

I couldn't find a non recursive solution to your problem.

 

1 ACCEPTED SOLUTION
5 REPLIES 5
Teradata Employee

Re: moving coordinates without iterative query

Hi jamiemack,

 

Could you provide a sample of datas of what you really have with the minimum columns set to fully implement the logic - 10 to 20 rows is good ?

And what you expect as an output, reading your topic it's not very clear to me.

 

Also, why not an iterative query ? It could be the only solution.

Iterative queries have "upgraded" SQL to a Turing complete language.

Re: moving coordinates without iterative query

Hi Waldar,

 

Thanks for your reply.

 

The logic is quite simple, a person with a cart moves from location to location (along y coordinate), picking up items. 90% of the time they will walk with the cart to the next location, but if there are several locations to visit in close proximity (<10) they will stop at the first, visit the locations without the cart then return to the cart.

 

1. In the case of the long move, the person and the cart both move to the new y-coordinate.

2. In the case the next item is nearby they retreive it but without moving the cart then return to the cart with the item at the old y-coordinate.

3. In the case the next item is near the last but out of the range of the cart, then they move the cart to the new y-coordinate location.

 

Both events are easy to distinguish from the change in y-coordinate column, however I also need to keep track of where the cart was left so we can calculate if the next item is far enough to require moving it.  To do this i would want a column of cart_location, but Teradata wont let me use that column to calculate itself, even from previous rows. 

 

This means that in order to save the value we have to put it into a column, to reference it any output would have to go into a new column, so to cover all eventualities i would probably have to create 20 additional columns to coalesce into 1.

 

I could try an iterative solution, but im likely to have 100million rows / day and imagine it would become incredibly slow.  A way of referencing the previous value of the same column like you can in an update would be much easier if it were possible.

 

Thanks for any assistance/feedback

 

 

 

 

Teradata Employee

Re: moving coordinates without iterative query

Well, yeah 100 millions rows a day is a challenge for sure.

 

I'll still give you the recursive answer so you can test it.

 

Datas

 

create multiset volatile table mvt_datas, no log
( cart_id       byteint      not null
, event_ts      timestamp(0) not null
, y_coord       byteint      not null
)
primary index (cart_id)
on commit preserve rows;

insert into mvt_datas values (1, timestamp '2019-04-16 10:01:00',  15);
insert into mvt_datas values (1, timestamp '2019-04-16 10:02:00',  27);
insert into mvt_datas values (1, timestamp '2019-04-16 10:03:00',  38);
insert into mvt_datas values (1, timestamp '2019-04-16 10:04:00',  41);
insert into mvt_datas values (1, timestamp '2019-04-16 10:05:00',  42);
insert into mvt_datas values (1, timestamp '2019-04-16 10:06:00',  55);
insert into mvt_datas values (1, timestamp '2019-04-16 10:07:00',  56);
insert into mvt_datas values (1, timestamp '2019-04-16 10:08:00',  56);
insert into mvt_datas values (1, timestamp '2019-04-16 10:09:00',  58);
insert into mvt_datas values (1, timestamp '2019-04-16 10:10:00',  66);
insert into mvt_datas values (1, timestamp '2019-04-16 10:11:00',  79);
insert into mvt_datas values (1, timestamp '2019-04-16 10:12:00',  80);
insert into mvt_datas values (1, timestamp '2019-04-16 10:13:00',  85);
insert into mvt_datas values (1, timestamp '2019-04-16 10:14:00', 100);

Query

 

with recursive cte_recurs (cart_id, event_ts, y_coord, cart_coord, dist_moved, rn) as
(
select cart_id
     , event_ts
     , y_coord
     , y_coord
     , 0
     , rn
  from cte_rownum
 where rn = 1
 union all
select rec.cart_id
     , crn.event_ts
     , crn.y_coord
     , case when crn.y_coord > rec.cart_coord + 10 then crn.y_coord else rec.cart_coord end
     , case when crn.y_coord > rec.cart_coord + 10 then crn.y_coord else rec.cart_coord end - rec.cart_coord
     , crn.rn
  from cte_recurs as rec
  join cte_rownum as crn  on crn.cart_id = rec.cart_id
                         and crn.rn      = rec.rn + 1
)
  ,  cte_rownum (cart_id, event_ts, y_coord, rn) as
(
select cart_id, event_ts, y_coord
     , row_number() over(partition by cart_id order by event_ts asc)
  from mvt_datas
)
select cart_id, event_ts, y_coord, cart_coord, dist_moved
  from cte_recurs;

 cart_id event_ts            y_coord cart_coord dist_moved 
 ------- ------------------- ------- ---------- ---------- 
       1 2019-04-16 10:01:00      15         15          0
       1 2019-04-16 10:02:00      27         27         12
       1 2019-04-16 10:03:00      38         38         11
       1 2019-04-16 10:04:00      41         38          0
       1 2019-04-16 10:05:00      42         38          0
       1 2019-04-16 10:06:00      55         55         17
       1 2019-04-16 10:07:00      56         55          0
       1 2019-04-16 10:08:00      56         55          0
       1 2019-04-16 10:09:00      58         55          0
       1 2019-04-16 10:10:00      66         66         11
       1 2019-04-16 10:11:00      79         79         13
       1 2019-04-16 10:12:00      80         79          0
       1 2019-04-16 10:13:00      85         79          0
       1 2019-04-16 10:14:00     100        100         21

I couldn't find a non recursive solution to your problem.

 

Re: moving coordinates without iterative query

Thanks ill give that a go and see how i get on and if it scales :)

Teradata Employee

Re: moving coordinates without iterative query

Hi jamiemack,

 

Did you had the chance to try if the recursive query scales ok ?

I've been hinted by @ToddAWalter to try to solve this using nPath

I think I did it, the SQL may look cumbersome.

 

If you can try how this one scales - and if it's accurate regarding your real datas :

with cte_npath (cart_id, event_ts, y_coord, y_coord_max, y_acc) as
(
select cart_id, event_ts, y_coord, y_coord_max
     , otranslate(y_acc (varchar(100)), '[] ', '')
  from npath(on mvt_datas
             partition by cart_id
                 order by event_ts asc
             using
             mode    ( overlapping )
             symbols ( True as LINE )
             pattern ( 'LINE*' )
             result  ( First(cart_id      of LINE) as cart_id
                     , First(event_ts     of LINE) as event_ts
                     , First(y_coord      of LINE) as y_coord
                     , Last (y_coord      of LINE) as y_coord_max
                     , accumulate(to_char(event_ts, 'yyyy-mm-ddhh24:mi:ss') || '$' || to_char(y_coord) of LINE) as y_acc
                     )
             filter  ( First(y_coord + 10 of LINE) >= Last(y_coord of LINE) )
            ) as dt
) 
  ,  cte_filter as
(
-- filtering rows where a coord is previously used
select t1.*
  from cte_npath as t1
 where not exists (select null
                     from cte_npath as t2 
                    where t2.cart_id      = t1.cart_id
                      and t2.event_ts    <  t1.event_ts
                      and t2.y_coord_max >= t1.y_coord)
)
  ,  cte_str_split as
(
-- expanding the accumulate stuff and restore datatypes
select strtok(t.row_id , '$', 1) (integer) as cart_id
     , to_timestamp(strtok(t.y_coord, '$', 1), 'yyyy-mm-ddhh24:mi:ss') as event_ts
     , strtok(t.y_coord, '$', 2) (integer) as y_coord     
     , strtok(t.row_id , '$', 2) (integer) as cart_y
  from table(strtok_split_to_table(to_char(cte_filter.cart_id) || '$' || to_char(cte_filter.y_coord), cte_filter.y_acc, ',')
             returns (row_id varchar(30), tokennumber integer, y_coord varchar(50))
             ) as t
)
-- adding missing rows
    select mvt.cart_id
         , mvt.event_ts
         , mvt.y_coord
         , coalesce(spt.cart_y, mvt.y_coord) as cart_coord
         , cart_coord - lag(cart_coord, 1, cart_coord) over(partition by mvt.cart_id order by mvt.event_ts asc) as dist_moved
      from mvt_datas     as mvt
 left join cte_str_split as spt on spt.cart_id  = mvt.cart_id
                               and spt.event_ts = mvt.event_ts
   order by 1, 2;