46120088-update-table-in-postgresql-by-grouping-rows, PostgreSQL

Language: Layout:

--PostgreSQL 9.6
--'\\' is a delimiter

select version() as postgresql_version;

create table foo (
      id        integer primary key,
      id_number integer,
      date      integer,
      count     integer
    );

insert into foo values
      (1,    1      ,   2001      ,    1   ),
      (2,    1      ,   2001      ,    2   ),
      (3,    1      ,   2002      ,    1   ),
      (4,    2      ,   2001      ,    6   ),
      (5,    2      ,   2003      ,    12  ),
      (6,    2      ,   2003      ,    2   );

select * from foo;
    
    update foo 
      set count = count_sum
      from (
          select id, id_number, date, 
                 sum(count) over (partition by id_number, date) as count_sum
            from foo
            ) foo_added
      where foo.id_number = foo_added.id_number 
        and foo.date      = foo_added.date;

delete from foo 
      using (
          select id, id_number, date, 
                 row_number() over (partition by id_number, date order by id) as inner_order
            from foo
            ) foo_ranked
      where foo.id = foo_ranked.id
        and foo_ranked.inner_order <> 1;
        
    select * from foo;


absolute service time: 0,38 sec