PostgreSQL: counting elements per hour and position

Question

I have a PostgreSQL table with a structure like this

id    elementid    timestamp                providerid    x    y
1     1            2014-10-01T00:00:00Z     12           12   18
2     1            2014-10-01T00:10:00Z     12           14   18
3     1            2014-10-01T00:15:00Z     12           16   20
4     2            2014-10-01T00:50:00Z     12           12   18
5     2            2014-10-01T01:10:00Z     12           14   18
6     2            2014-10-01T01:15:00Z     12           16   20
7     7            2014-10-01T00:00:00Z     14           12   18
8     7            2014-10-01T00:10:00Z     14           14   18
9     7            2014-10-01T00:15:00Z     14           16   20

And I want to count the number of different elementid belonging to the same providerid per hour and position.

An example

Between 00:00 and 01:00, in the box defined by xmin=12, ymin=18, xmax=16, ymax=20, there are 2 elements belonging to providerid 12 (4 first records: element with elementid = 1 and element with elementid = 2). So, the count is 2 elements per hour, for providerid = 12.

As you can see, I'm really dealing with moving elements. So, in that time frame (1 hour), I get the element 1 in 3 different positions and the element 2 in one position. So, a total of 2 elements, belonging to the same provider (providerid = 12).

Then, my resultset should look like this:

providerid              start_time                end_time    num_elements
        12    2014-10-01T00:00:00Z    2014-10-01T01:00:00Z               2

And I want the same for each provider.

I guess the response is similar to this one, this one or this one, but still didn't find the solution. Any help is really appreciated.

Many thanks in advance!

score 1 · Accepted Answer · answered Nov 13 '14 at 20:22

Have you tried COUNT( DISTINCT ... ) ?

SELECT t1."providerid",
       s.start_time,
       s.end_time,
       COUNT( DISTINCT "elementid" ) As num_elements
FROM table1 t1
JOIN start_end_time s
ON t1."timestamp" BETWEEN s.start_time AND s.end_time
GROUP BY 
       t1."providerid",
       s.start_time,
       s.end_time

Demo: http://sqlfiddle.com/#!12/b55a8/2

PostgreSQL: counting elements per hour and position

1 Answers1