CODEWITHSUNDEEP
sqlpostgresql
Red
Red

Reputation: 425

PostgreSQL column value depending on previous rows

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

Upvotes: 0

Views: 1108

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

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

Upvotes: 3

Related Questions