Re: How to join table to itself N times? - Mailing list pgsql-general

Okay, how about this (table names shortened):

create table m (d varchar(255) not null, v varchar(255) not null);
insert into m (d, v) values ('geography', 'north'), ('geography',
'south'), ('industry type', 'retail'), ('industry type',
'manufacturing'), ('industry type', 'wholesale');

WITH RECURSIVE t(combo, n) AS (
  WITH dims AS (SELECT DISTINCT d, row_number() OVER () AS n FROM m GROUP BY d)
  SELECT '{}'::text[], 1
  UNION ALL
  SELECT array_append(t2.combo::text[], m.v::text), t2.n+1
  FROM  t t2, dims
  CROSS JOIN m
  WHERE m.d = dims.d AND dims.n = t2.n
)
SELECT *
FROM t
WHERE n = (SELECT COUNT(DISTINCT d) + 1 FROM m);

Gives these results:

         combo         | n
-----------------------+---
 {retail,north}        | 3
 {manufacturing,north} | 3
 {wholesale,north}     | 3
 {retail,south}        | 3
 {manufacturing,south} | 3
 {wholesale,south}     | 3
(6 rows)

Paul


On Wed, Mar 20, 2013 at 8:40 PM, Paul Jungwirth
<pj@illuminatedcomputing.com> wrote:
> Wow, this is a fun puzzle. I'd love to be the first to solve it with
> just SQL, but I don't have a solution yet. Here are some elements that
> might be useful:
>
> SELECT market_segment_dimension, array_agg(value)
> FROM market_segment_dimension_values
> GROUP BY market_segment_dimension;
>
> the UNNEST function
> the ROW function
> window functions like row_number and nth_value
> the crosstab function (requires installing an extension; this seems
> like cheating if you ask me)
>
> Good luck!
> Paul
>
>
> On Wed, Mar 20, 2013 at 7:14 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
>> On Wed, Mar 20, 2013 at 5:38 PM, W. Matthew Wilson <matt@tplus1.com> wrote:
>>> 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.
>>
>> If the number of dimensions is not fixed, then you'll probably have to
>> write a plpgsql function to first interrogate the data set for how
>> many dimensions there are and then to build an n-dimension query.
>> While joining a variable number of tables may be problematic as you
>> won't have a fixed number of columns, using a union might give you
>> what you want with a fixed number of columns.
>>
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>
>
>
> --
> _________________________________
> Pulchritudo splendor veritatis.



--
_________________________________
Pulchritudo splendor veritatis.