Posts with tag: database

Databases are just Loops - GROUP BY

In my previous post - I introduced the idea that you can think of database queries as a series of loops. Let me take this ideas even further - introducing more complex database concepts in terms more familiar to computer programmers.

Databases can do this cool thing called "aggregation". You may think of it like GROUP BY. You will have seen queries like this:

SELECT x, SUM(v) AS agg_y
FROM a
GROUP BY x

What is going on here? And how can we think of this like a loop?

Aggregate or GROUP BY?

To establish some terminology: You will hear me refer to GROUP BY as "aggregation" in the following passages.

We will call SUM(v) above an "aggregate function" and we will call x a "grouping column". We will refer to the entire loop as the "aggregation loop"

Read More...

Databases are just Loops - Joins

I decided to write this post in response to my recent discussions with Matt Martin. Matt and I have been sparring lately over software performance. During one of these discussions, Matt said the magic words in the title of this post. I hypothesise that this quote carries of lot of explaining power for those who are not fully familiar with databases.

Let us play with this idea a bit! I'm going to use Python to show examples of "database loops". Obviously, no one in their sane mind would implement a database in Python. But Python has the great advantage of being readable to nearly everyone. Let's go...

Read More...