CODEWITHSUNDEEP
sqlpostgresqlwindow-functions
Ellis Valentiner
Ellis Valentiner

Reputation: 2206

Count string values across moving window

I'm trying to count the occurrence of string values across a moving window. Specifically I want to count the occurrences of each string value for the previous 3 rows -- excluding the row

My data looks something like this:

id |  color
---+---------
 0 |  'blue'
 : |      :
 6 | 'green'
 7 |  'blue'
 8 | 'green'
 9 |   'red'
10 |   'red'

and I'm trying to get something like this:

id | n_red | n_blue | n_green 
---+-------+--------+---------
 : |     : |      : |       :
 9 |     0 |      1 |       2
10 |     1 |      1 |       1

where the data is number of occurrences of each 'red', 'blue', and 'green' across the previous 3 rows (e.g. in the 3 rows before id 10 there is 1 red, 1 blue, and 1 green).

I think I should be able to do this using a window function but haven't been able to quite work it out.

select
    sum(red) over(order by id rows between 3 preceding and 1 preceding) end as n_red,
    sum(blue) over(order by id rows between 3 preceding and 1 preceding) end as n_blue,
    sum(green) over(order by id rows between 3 preceding and 1 preceding) end as n_green
from (select id,
        case when color = 'red' then 1 else 0 end as red,
        case when color = 'blue' then 1 else 0 end as blue,
        case when color = 'green' then 1 else 0 end as green
    from color_table) as dummy_color_table

This seems to work but is not very parsimonious. Does anyone have experience writing these types of queries who can perhaps improve it?

Upvotes: 1

Views: 58

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270523

You can take advantage of :: in Postgres:

select sum((color = 'red')::int) over (order by id rows between 3 preceding and 1 preceding) end as n_red,
       sum((color = 'blue')::int) over (order by id rows between 3 preceding and 1 preceding) end as n_blue,
       sum((color = 'green')::int) over (order by id rows between 3 preceding and 1 preceding) end as n_green
from color_table;

Upvotes: 4

Related Questions