I am trying to get an idea of how often we have trucks take a partial
load from one terminal to another, and then continue to (an)other
terminal(s) with the rest of the freight. I'm also trying to determine
if certain combinations of terminals are much more common than others.
Here is a simplified example which outlines the problem. Suppose we
have a table like this:
k1 k2 dest
a a 1
a a 2
a b 3
b c 4
b c 5
b c 6
(k1,k2) constitutes a unique identifier for a load, and each load has
one or more destinations. I would like to end up with ((1,2), (3),
(4,5,6)), that is, a set of all combinations of destinations which have
been stops on the same load. I'd then like to count how many loads have
used a particular combination, but that's probably best as a separate step.
I can use a query like this
select a.k1,a.k2,a.dest, coalesce(b.dest,0), coalesce(c.dest,0)
from testtable a
left outer join testtable b
on a.k1=b.k1 and a.k2 = b.k2
and a.dest<>b.dest
left outer join testtable c
on b.k1=c.k1 and b.k2=c.k2
and a.dest<>c.dest and b.dest<>c.dest
order by k1,k2
which results in
k1 k2 dest dest dest
a a 1 2 0
a a 2 1 0
a b 3 0 0
b c 4 5 6
b c 4 6 5
b c 5 4 6
b c 5 6 4
b c 6 4 5
b c 6 5 4
This clearly gets cumbersome as the number of possible combinations
increases.
Am I tilting at windmills, or just missing the boat?