Re: Produce set of sets using SQL

Years ago I worked for a trucking company.

We'd have loads regularly going such that in some cities we had a lot of empty trucks (more deliveries there than pick ups), and other cities had to be sent empty trucks (more pickups than deliveries), so the challenge was to design our data to handle analysis so that the business we did have could be better balanced, and so that our sales force would know where to focus efforts ... seek business where we need to send deliveries from the cities, where we now have empty trucks, to the cities where we now have to send empty trucks. This be dynamic, as the business evolves, we need to continue this effort iteratively.

So a new data base was developed ...
location involved for pickup or drop off
is it pickup or drop off?
volume (dimensions) & weight
where is it going to or coming from
is this a regular thing ... what schedule, like pickup every Wednesday, need to deliver before the weekend

This data was poputated from the other data in our system.

We are then able to subtotal the mass by location, total it by city, see which cities have more going in (volume, weight) than coming out & vica versa, identify those with the greatest disparity.

There were whole other applications related to most efficient routes to drive from perspective of saving gasoline, avoiding traffic jams, giving drivers good opportunities for rest ... factor into the load balancing where the driver's homes are, so if a load has to go to Nashville TN & the truck layover a day, then use a driver who has family in Nashville, who can stay with them, instead of the company paying a motel nite.

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?
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.