CODEWITHSUNDEEP
pythondatabasepostgresql
thealchemist
thealchemist

Reputation: 407

Conditional row counter in postgres

I have the following data in my postgres table: 

user id     time_stamp                action

1      '2014-12-04 17:44:46+00'     sign up
1      '2015-03-26 15:06:27+00'      work out
3      '2015-02-09 11:40:25+00'      sign up

what i want is to create a 4th column

that counts the actions done by the user based on time stamp which would look like this: 

user id   time_stamp             action    event id

1    '2014-12-04 17:44:46+00'    sign up    1-1 ( 1st action of user id 1
1    '2015-03-26 15:06:27+00'    work out   1-2 (2nd action of user id 1
3    '2015-02-09 11:40:25+00'    sign up    2-1 ( 1st actoin of user id 1

this problem has been bothering me for a few months now.. would really really appreciate if anyone can help me solve it or guide me in the right direction..

Upvotes: 2

Views: 56

Answers (1)

klin
klin

Reputation: 121804

You should userow_number() function. Assuming that your table name is users:

alter table users add column event_id text;

update users t
set event_id = e_id
from (
    select *, 
        format('%s-%s', user_id, row_number() over (partition by user_id order by time_stamp)) as e_id
    from users
    order by 1, 2
    ) sub
where sub.user_id = t.user_id and sub.time_stamp = t.time_stamp;

Upvotes: 1

Related Questions