I've got this table:
prod | customer | city | num | time | isextra
-----+----------+---------+------+--------------------+-------
1 | Jim | Venice | 5 |2015-08-27 1:10:00 | 0
1 | Jim | Venice | 5 |2015-08-27 1:10:15 | 0
1 | Jim | Venice | 5 |2015-08-27 1:10:28 | 0
4 | Jane | Vienna | 8 |2018-06-04 2:20:43 | 0
4 | Jane | Vienna | 8 |2018-06-04 2:20:43 | 0
4 | Jane | Vienna | 8 |2018-06-04 2:20:49 | 0
4 | Jane | Vienna | 8 |2018-06-04 2:30:55 | 0
7 | Jack | Vilnius | 4 |2015-09-15 2:20:55 | 0
7 | Jake | Vigo | 9 |2018-01-01 10:20:05 | 0
7 | Jake | Vigo | 2 |2018-01-01 10:20:25 | 0
Now take all rows that are similar by prod, customer, city, num then any row whose time is within 30 seconds of the first in the group, its 'isextra' field is UPDATED to 1
I'd like to use a CTE query with an UPDATE and have this be the result:
prod | customer | city | num | time | isextra
-----+----------+---------+------+--------------------+-------
1 | Jim | Venice | 5 |2015-08-27 1:10:00 | 0
1 | Jim | Venice | 5 |2015-08-27 1:10:15 | 1
1 | Jim | Venice | 5 |2015-08-27 1:10:28 | 1
4 | Jane | Vienna | 8 |2018-06-04 2:20:43 | 0
4 | Jane | Vienna | 8 |2018-06-04 2:20:43 | 1
4 | Jane | Vienna | 8 |2018-06-04 2:20:49 | 1
4 | Jane | Vienna | 8 |2018-06-04 2:30:55 | 0
7 | Jack | Vilnius | 4 |2015-09-15 2:20:55 | 0
7 | Jake | Vigo | 9 |2018-01-01 10:20:05 | 0
7 | Jake | Vigo | 2 |2018-01-01 10:20:25 | 0
Ok, I have this working, but how to convert to CTE? Or is it even worth it, would it offer any performance benefits over millions of rows?
update public.mytable
set isextra = 1
from
(
select prod, customer, city, num, min(time) as mintime
from mytable
group by prod, customer, city, num
) b
where public.mytable.prod = b.prod
and public.mytable.customer = b.customer
and public.mytable.city = b.city
and public.mytable.num = b.num
and public.mytable.time <= b.mintime + interval '30 seconds'
and public.mytable.time <> b.mintime;