Обсуждение: How to join table to itself N times?

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@tplus1.com
http://tplus1.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@tplus1.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@tplus1.com
http://tplus1.com

correction:

2013/3/22 Misa Simic <misa.simic@gmail.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@tplus1.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@tplus1.com
http://tplus1.com