In the book Database Fundamentals, Silberschatz. It is explained that aggregate functions can be calculated on the march.
This make sense. What it means is that for calculating the maximun, average or count the items in a set, you don't need to pass a copy of the set to the aggregate procedures, you only process each record meanwhile you transverse the set.
One naive implementation could be to keep a variable for each aggregate desired. For example, a
SELECT sum(a_field), count(a_field), max(a_field) FROM a_set could be implemented as:
sum_ = 0 count_ = 0 max_ = -INF for record in a_set: sum_ = sum_ + record.a_field count_ = count_ + 1 max_ = max(max_, record.a_field) return (sum_, count_, max_)
Of course, this is unthinkable as the loop over the set should not be so tied to the aggregate computation. I suppose the loop delegates the aggregation to a kind of coroutine.
Supposing a coroutine is a kind of object with two methods:
- feed: where you can pass a value to the coroutine
- get: which gives you the result of a computation
The loop would be something like:
# Given a set C of aggregation coroutines for record in a_set: for c in C: c.feed(record.a_field) return (c.get() for c in C)
In this case, I imagine a coroutine like
max_ = -INF while item = consume(): max_ = max(max_, item) yield max_
Here, I'm supposing that when the coroutine invokes
consume it waits until somebody calls it's
feed method. And when it calls
yield, that value is collected later by the one who invokes it's
Just for fun, let's implement the
sum_ = 0 while item = consume(): sum_ = sum_ + item yield sum_
So, this is broadly what I imagine is happening behind the scenes, but I can't be sure, so:
- How is this process actually implemented in the most of SQL engines?.
- What would happen with an aggregation which requires two or more transverses on the dataset, as the standard deviation?.
Note: The pseudo is a kind of pseudo Python.