EXPERT RESPONSE
Great question!
The following solution involves a theta self-join.
If that sounds strange, we will break it down, take it slowly,
and see how it works from the ground up.
Before getting to the SQL, let's do a quick review of joins.
Consider the numbers 1 through 9. Join these numbers to themselves, and
you get 9 * 9 = 81 different combinations. That's the typical
cross join we're all familiar with. It's also a
self-join. In fact, a self-join can be a cross join,
inner join, or any other type of join. In this case, we're interested
primarily in a certain type of inner join.
In the following diagram, there's an X
where the value down the left is equal to the value across the top.
This illustrates an equi-join:
1 2 3 4 5 6 7 8 9
1 X . . . . . . . .
2 . X . . . . . . .
3 . . X . . . . . .
4 . . . X . . . . .
5 . . . . X . . . .
6 . . . . . X . . .
7 . . . . . . X . .
8 . . . . . . . X .
9 . . . . . . . . x
An equi-join is a special type of inner join. It uses
equality of values as the join condition. The cross join,
by comparison, is also a type of inner join, but it has no
join condition, so every value is joined with every value, and there would be
an X in every spot in that diagram.
A theta join uses some other condition, not equality,
in order to join rows. In the following diagram, there's an X where the
value down the left is equal to or greater than the value across the top.
This "triangle" appearance illustrates a theta join:
1 2 3 4 5 6 7 8 9
1 X . . . . . . . .
2 X X . . . . . . .
3 X X X . . . . . .
4 X X X X . . . . .
5 X X X X X . . . .
6 X X X X X X . . .
7 X X X X X X X . .
8 X X X X X X X X .
9 X X X X X X X X X
In the following diagram, there's an X where the value down the left is
equal to or greater than the value across the top, but not greater than 2 greater.
The triangle has been "cut off" and so this illustrates a
different theta join:
1 2 3 4 5 6 7 8 9
1 X . . . . . . . .
2 X X . . . . . . .
3 X X X . . . . . .
4 . X X X . . . . .
5 . . X X X . . . .
6 . . . X X X . . .
7 . . . . X X X . .
8 . . . . . X X X .
9 . . . . . . X X X
In general, there are a zillion ways you can construct a theta join.
We'll be using a version of the theta join in the last diagram.
Please proceed to
N consecutive rows, Part 2 of 4.
|
