Thread: A unique pairs version of UNNEST() ?

On 1/4/2016 2:08 PM, Wells Oliver wrote:
> Hey all, happy new year.
>
> I am trying to get unique pairs from an array of N numbered items,
> usually 5, but possibly 4 or 6.
>
> If I just wanted unique values, I could do SELECT UNNEST(list_of_ids) AS
> id, COUNT(*) FROM table GROUP BY id but in this situation I want all
> unique pairs and a COUNT.
>
> For those familiar with python, this is the functionality found in
> itertools.combinations. I'm leaning towards just doing this in python,
> but I really like keeping as much in SQL as possible.
>
> So in an example where list_of_ids is {1,2,3,4,5} I would essentially get:
>
> {1, 2}
> {1, 3}
> {1, 4}
> {1, 5}
> {2, 3}
> {2, 4}
> {2, 5}
> {3, 4}
> {3, 5}
> {4, 5}
>
>
> Any tips? Thanks!
>
> --
> Wells Oliver
> wells.oliver@gmail.com <mailto:wellsoliver@gmail.com>

if you could convert the array to a table then cross join it.  Something
like:

select a.*, b.*
from unnest( {1,2,3,4,5} ) a
cross join unnest( {1,2,3,4,5} ) b

-Andy

Andy Colson <andy@squeakycode.net> wrote:

>> So in an example where list_of_ids is {1,2,3,4,5} I would essentially get:
>>
>> {1, 2}
>> {1, 3}
>> {1, 4}
>> {1, 5}
>> {2, 3}
>> {2, 4}
>> {2, 5}
>> {3, 4}
>> {3, 5}
>> {4, 5}
>>
>>
>> Any tips? Thanks!
>>
>> --
>> Wells Oliver
>> wells.oliver@gmail.com <mailto:wellsoliver@gmail.com>
>
> if you could convert the array to a table then cross join it.  Something
> like:
>
> select a.*, b.*
> from unnest( {1,2,3,4,5} ) a
> cross join unnest( {1,2,3,4,5} ) b
>
> -Andy

not exactly the expectet result (and syntactically wrong), better
solution:


test=*# select (a.*, b.*) from unnest( array[1,2,3,4,5] ) a cross join unnest( array[1,2,3,4,5] ) b where a < b;
  row
-------
 (1,2)
 (1,3)
 (1,4)
 (1,5)
 (2,3)
 (2,4)
 (2,5)
 (3,4)
 (3,5)
 (4,5)
(10 rows)

or

test=*# select array[a.*, b.*] from unnest( array[1,2,3,4,5] ) a cross join unnest( array[1,2,3,4,5] ) b where a < b;
 array
-------
 {1,2}
 {1,3}
 {1,4}
 {1,5}
 {2,3}
 {2,4}
 {2,5}
 {3,4}
 {3,5}
 {4,5}
(10 rows)


(matches the excpected result)


Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

> On 04 Jan 2016, at 21:08, Wells Oliver <wells.oliver@gmail.com> wrote:
>
> Hey all, happy new year.
>
> I am trying to get unique pairs from an array of N numbered items, usually 5, but possibly 4 or 6.
>
> If I just wanted unique values, I could do SELECT UNNEST(list_of_ids) AS id, COUNT(*) FROM table GROUP BY id but in
thissituation I want all unique pairs and a COUNT. 
>
> For those familiar with python, this is the functionality found in itertools.combinations. I'm leaning towards just
doingthis in python, but I really like keeping as much in SQL as possible. 
>
> So in an example where list_of_ids is {1,2,3,4,5} I would essentially get:
>
> {1, 2}
> {1, 3}
> {1, 4}
> {1, 5}
> {2, 3}
> {2, 4}
> {2, 5}
> {3, 4}
> {3, 5}
> {4, 5}

Your example reminds me of combinatory theory. The result you seem to be looking for is the list of possible unique
combinations,as sets of elements of the total set (sets are orderless). 

with list_of_ids as (
  select unnest(list_of_ids) as id from table
)
select a.id, b.id
  from list_of_ids a, list_of_ids b
 where b.id > a.id;

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.

On Tue, Jan 5, 2016 at 1:59 AM, Alban Hertroys <haramrae@gmail.com> wrote:

> with list_of_ids as (
>   select unnest(list_of_ids) as id from table
> )
> select a.id, b.id
>   from list_of_ids a, list_of_ids b
>  where b.id > a.id;

Or, to morph this to array output (which the OP seemed to want):

test=# with list_of_ids as (
test(#   select unnest('{1,2,3,4,5}'::int[]) as id
test(# )
test-# select array [a.id, b.id]
test-#   from list_of_ids a, list_of_ids b
test-#  where b.id > a.id;
 array
-------
 {1,2}
 {1,3}
 {1,4}
 {1,5}
 {2,3}
 {2,4}
 {2,5}
 {3,4}
 {3,5}
 {4,5}
(10 rows)

Nothing in that not already mentioned; just putting it all
together.

The OP mentioned wanting a count, but that wasn't too clear to me;
using a window function to number the rows, changing the comparison
from > to >= while excluding self-matches should make that pretty
easy.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company