Re: How to join table to itself N times?

correction:

2013/3/22 Misa Simic <misa(dot)simic(at)gmail(dot)com>

> Hi,
>
> Not clear what is expected result - if you add new dimension...
>
> a) three columns? - well not possible to write SQL query which returns
> undefined number of columns... unfortunatelly - though I am not clear why :)
>
> b) But you can get the similar result as from python... my guess is you
> expect:
>
>
>
> ('north', 'retail', small),
> ('north', 'retail', big),
> ('north', 'manufacturing', small),
> ('north', 'manufacturing', big),
> ('north', 'wholesale', small),
> ('north', 'wholesale', big),
> ('south', 'retail', small),
> ('south', 'retail', big),
> ('south', 'manufacturing', small),
> ('south', 'manufacturing', big)
> ('south', 'wholesale', small)
> ('south', 'wholesale', big)
>
>
>
> In your dimensions table (called: market_dimensions) you would need one
> more column to define desired result order
>
> i.e.
>
> market_segment_dimensions
> market_segment_dimension , ord
> geography, 1
> industry type, 2
> customer size, 3
>
>
> WITH RECURSIVE t (
>
> SELECT array_agg(value) AS values, ord + 1 AS next_dim_ord, ord AS
> agg_dims
> FROM market_segment_dimension_values
> INNER JOIN market_segment_dimensions USING (market_segment_dimension)
> WHERE ord = 1
> UNION ALL
> SELECT array_agg(value) AS values, ord + 1 AS next_dim_ord, ord AS
> agg_dims
> FROM t
> INNER JOIN market_segment_dimensions ON (ord = t.next_dim_ord)
> INNER JOIN market_segment_dimension_values USING (
> market_segment_dimension)
> )
>
> SELECT values FROM t WHERE t.agg_dims = (SELECT MAX(ord) FROM
> market_segment_dimensions)
>
>
>
>
>
>
> 2013/3/21 W. Matthew Wilson <matt(at)tplus1(dot)com>
>
> I got this table right now:
>>
>> select * from market_segment_dimension_values ;
>> +--------------------------+---------------+
>> | market_segment_dimension | value |
>> +--------------------------+---------------+
>> | geography | north |
>> | geography | south |
>> | industry type | retail |
>> | industry type | manufacturing |
>> | industry type | wholesale |
>> +--------------------------+---------------+
>> (5 rows)
>>
>> The PK is (market_segment_dimension, value).
>>
>> The dimension column refers to another table called
>> market_segment_dimensions.
>>
>> So, "north" and "south" are to values for the "geography" dimension.
>>
>> In that data above, there are two dimensions. But sometimes there could
>> be just one dimension, or maybe three, ... up to ten.
>>
>> Now here's the part where I'm stumped.
>>
>> I need to create a cartesian product of the dimensions.
>>
>> I came up with this approach by hard-coding the different dimensions:
>>
>> with geog as (
>> select value
>> from market_segment_dimension_values
>> where market_segment_dimension = 'geography'),
>>
>> industry_type as (
>> select value
>> from market_segment_dimension_values
>> where market_segment_dimension = 'industry type')
>>
>> select geog.value as g,
>> industry_type.value as ind_type
>> from geog
>> cross join industry_type
>> ;
>> +-------+---------------+
>> | g | ind_type |
>> +-------+---------------+
>> | north | retail |
>> | north | manufacturing |
>> | north | wholesale |
>> | south | retail |
>> | south | manufacturing |
>> | south | wholesale |
>> +-------+---------------+
>> (6 rows)
>>
>> But that won't work if I add a new dimension (unless I update the query).
>> For example, maybe I need to add a new dimension called, say, customer
>> size, which has values "big" and "small". A
>>
>> I've got some nasty plan B solutions, but I want to know if there's some
>> solution.
>>
>> There's a really elegant solution in python using itertools.product, like
>> this:
>>
>> >>> list(itertools.product(*[['north', 'south'], ['retail',
>> 'manufacturing', 'wholesale']]))
>>
>> [('north', 'retail'),
>> ('north', 'manufacturing'),
>> ('north', 'wholesale'),
>> ('south', 'retail'),
>> ('south', 'manufacturing'),
>> ('south', 'wholesale')]
>>
>> All advice is welcome. Thanks in advance!
>>
>> Matt
>>
>>
>>
>> --
>> W. Matthew Wilson
>> matt(at)tplus1(dot)com
>> http://tplus1.com
>>
>>
>