Archive for February 18th, 2011
Recently I had to deal with several scenarios which required processing and aggregating continuous series of data.
I believe this could be best illustrated with an example:
The records are ordered by
id, and within this order there are continuous series of records which share the same value of
source. In the table above, the series are separated by thick lines.
We want to calculate some aggregates across each of the series:
This can be used for different things. I used that for:
- Reading sensors from a moving elevator (thus tracking its position)
- Recording user’s activity on a site
- Tracking the primary node in a server cluster
, but almost any seasoned database developer can recall a need for such a query.
As you can see, the values of
source are repeating so a mere
GROUP BY won’t work here.
In the systems supporting window functions there is a workaround for that: