PostgreSQL column value depending on previous rows

Question

I have a table with five columns: id, user_code, created_at, A, and B, and start. id is just a key, user_code identifies a user associated with that line, created_at is a timestamp, A, B, and start can independently take the value 0 or 1.

What I was is to run a query that adds the column As and Bs, which orders the original table in ascending order according to created_at and then the As value of a given row equals the number of previous rows with the same user_code that had A = 1 since the last time start = 1 (and same thing for Bs and B).

Sample input:

 id | user_code | created_at | A | B | start
---------------------------------------------
 00 |     1     |     t0     | 1 | 0 |   1
 01 |     1     |     t1     | 0 | 0 |   1
 02 |     2     |     t1     | 0 | 0 |   1
 03 |     1     |     t2     | 1 | 0 |   0
 04 |     2     |     t2     | 0 | 1 |   0
 05 |     2     |     t3     | 0 | 1 |   0
 06 |     1     |     t4     | 1 | 1 |   0
 07 |     1     |     t5     | 0 | 1 |   1

Sample output:

 id | user_code | created_at | A | B | start | As | Bs
-------------------------------------------------------
 00 |     1     |     t0     | 1 | 0 |   1   | 01 | 00
 01 |     1     |     t1     | 0 | 0 |   1   | 00 | 00
 02 |     2     |     t1     | 0 | 0 |   1   | 00 | 00
 03 |     1     |     t2     | 1 | 0 |   0   | 01 | 00
 04 |     2     |     t2     | 0 | 1 |   0   | 00 | 01
 05 |     2     |     t3     | 0 | 1 |   0   | 00 | 02
 06 |     1     |     t4     | 1 | 1 |   0   | 02 | 01
 07 |     1     |     t5     | 0 | 1 |   1   | 00 | 01
 08 |     2     |     t6     | 1 | 0 |   1   | 01 | 00

Giorgos Betsos · Accepted Answer · 2016-03-16 20:39:55Z

3

You can use COUNT with OVER clause:

SELECT "id", "user_code", "created_at", "A", "B",
       COUNT(CASE WHEN "A" <> 0 THEN 1 END) 
          OVER (PARTITION BY "user_code" ORDER BY "id") AS As,
       COUNT(CASE WHEN "B" <> 0 THEN 1 END) 
          OVER (PARTITION BY "user_code" ORDER BY "id") AS Bs
FROM mytable

Demo here

answered Mar 16, 2016 at 20:39

Giorgos Betsos

72.3k10 gold badges69 silver badges103 bronze badges

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

1 Comment

Red Over a year ago

That's a great solution to the problem I explained! But I forgot to add a detail that was important and that make this solution not quite work :x I edited to add it.

Collectives™ on Stack Overflow

PostgreSQL column value depending on previous rows

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related