Postgres Window function

Question

Please provide some suggestions to solve the problem below with window functions, in postgres.

Input data:
111;"2016-01-01 10:00:00";"park"
111;"2016-01-01 10:01:00";"guide"
111;"2016-01-01 10:02:00";"guide"
111;"2016-01-01 10:03:00";"operate"
111;"2016-01-01 10:04:00";"guide"
111;"2016-01-01 10:05:00";"guide"
222;"2016-01-01 10:00:00";"park"
222;"2016-01-01 10:01:00";"guide"
222;"2016-01-01 10:02:00";"guide"
222;"2016-01-01 10:03:00";"operate"
222;"2016-01-01 10:04:00";"guide"
222;"2016-01-01 10:05:00";"guide"

Output Required:

111;"2016-01-01 10:00:00";"park";1
111;"2016-01-01 10:01:00";"guide";2
111;"2016-01-01 10:02:00";"guide";2
111;"2016-01-01 10:03:00";"operate";3
111;"2016-01-01 10:04:00";"guide";4
111;"2016-01-01 10:05:00";"guide";4
222;"2016-01-01 10:00:00";"park";1
222;"2016-01-01 10:01:00";"guide";2
222;"2016-01-01 10:02:00";"guide";2
222;"2016-01-01 10:03:00";"operate";3
222;"2016-01-01 10:04:00";"guide";4
222;"2016-01-01 10:05:00";"guide";4

code for Input data:
create temp table input_tb (id int, date timestamp, category text);
insert into input_tb values (111, '2016-01-01:10:00:00', 'park');
insert into input_tb values (111, '2016-01-01:10:01:00', 'guide');
insert into input_tb values (111, '2016-01-01:10:02:00', 'guide');
insert into input_tb values (111, '2016-01-01:10:03:00', 'operate');
insert into input_tb values (111, '2016-01-01:10:04:00', 'guide');
insert into input_tb values (111, '2016-01-01:10:05:00', 'guide');
insert into input_tb values (222, '2016-01-01:10:00:00', 'park');
insert into input_tb values (222, '2016-01-01:10:01:00', 'guide');
insert into input_tb values (222, '2016-01-01:10:02:00', 'guide');
insert into input_tb values (222, '2016-01-01:10:03:00', 'operate');
insert into input_tb values (222, '2016-01-01:10:04:00', 'guide');
insert into input_tb values (222, '2016-01-01:10:05:00', 'guide');

Thanks for your help.

Suresh

Quassnoi · Accepted Answer · 2016-04-27 21:49:04Z

2

SELECT  id, date, category,
        SUM(swap)  OVER (PARTITION BY id ORDER BY date)
FROM    (
        SELECT  *,
                (LAG(category) OVER (PARTITION BY id ORDER BY date) IS DISTINCT FROM category)::INTEGER swap
        FROM    input_tb
        ) q

See the fiddle

answered Apr 27, 2016 at 21:49

Quassnoi

427k94 gold badges628 silver badges623 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres Window function

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related