Search code examples
postgresqlwindow-functions

Postgres 9.3 count rows matching a column relative to row's timestamp

I've used WINDOW functions before but only when working with data that has a fixed cadence/interval. I am likely missing something simple in aggregation but I've never had a scenario where I'm not working with fixed intervals.

I have a table the records samples at arbitrary timestamps. A sample is only recorded when it is a delta from the previous sample and the sample rate is completely irregular due to a large number of conditions. The table is very simple:

id (int) 
happened_at (timestamp)
sensor_id (int)
new_value (float)

I'm trying to construct a query that will include a count of all of the samples before the happened_at of a given result row. So given an ultra simple 2 row sample data set:

id|happened_at     |sensor_id| new_value
1 |2019-06-07:21:41|134679   | 123.331
2 |2019-06-07:19:00|134679   | 100.009

I'd like the result set to look like this:

happened_at     |sensor_id | new_value | sample_count
2019-06-07:21:41|134679    |123.331    |2
2019-06-07:19:00|134679    |123.331    |1

I've tried:

SELECT *,
       (SELECT count(sample_history.id) OVER (PARTITION BY score_history.sensor_id 
        ORDER BY sample_history.happened_at DESC))
FROM sensor_history
ORDER by happened_at DESC

and the duh not going to work.

(SELECT count(*) 
FROM sample_history
WHERE sample_history.happened_at <= sample_timestamp)

Insights greatly appreciated.

Solution

  • Get rid of the SELECT (sub-query) when using the window function. 

    SELECT *,
       count(*) OVER (PARTITION BY sensor_id ORDER BY happened_at DESC)
FROM sensor_history
ORDER BY happened_at DESC