A unique pairs version of UNNEST() ?
| От | Wells Oliver |
|---|---|
| Тема | A unique pairs version of UNNEST() ? |
| Дата | |
| Msg-id | CAOC+FBWoZS1DBCTVwzEVobwSxWNyV4ozt7OPXvTV8kg=fDDKng@mail.gmail.com обсуждение исходный текст |
| Ответы |
Re: A unique pairs version of UNNEST() ?
Re: A unique pairs version of UNNEST() ? |
| Список | pgsql-general |
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}
В списке pgsql-general по дате отправления: