Re: How to count pairs?

Dear all,

I need to count all pairs of instances which occure under given ID
number, e.g.:

ID word
-------------
1 car
1 fruit
2 car
2 fruit
2 vegetable
3 car
3 vegetable

And the output should be simillar to:

car fruit 2
car vegetable 2
fruit vegetable 1

Any suggestion how to do that in PgSQL? Thanks in advance for any pointers.

Cheers, Andrej

am Sun, dem 10.06.2007, um 13:41:27 +0200 mailte Andrej Kastrin folgendes:
> Dear all,
>
> I need to count all pairs of instances which occure under given ID
> number, e.g.:
>
> ID word
> -------------
> 1 car
> 1 fruit
> 2 car
> 2 fruit
> 2 vegetable
> 3 car
> 3 vegetable
>
> And the output should be simillar to:
>
> car fruit 2
> car vegetable 2
> fruit vegetable 1
>

test=*# select * from foo;
id | word
----+-----------
1 | car
1 | fruit
2 | car
2 | fruit
3 | car
3 | vegetable
2 | vegetable
(7 rows)

test=*# select word_a || ',' || word_b as pair,
count(1)
from ( select a.word as word_a, b.word as word_b from foo a join foo b on a.id=b.id where a.word != b.word and a.word > b.word) as bla
group by pair;
pair | count
-----------------+-------
vegetable,car | 2
vegetable,fruit | 1
fruit,car | 2
(3 rows)

Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net

A. Kretschmer wrote:
> am Sun, dem 10.06.2007, um 13:41:27 +0200 mailte Andrej Kastrin folgendes:
>
>> Dear all,
>>
>> I need to count all pairs of instances which occure under given ID
>> number, e.g.:
>>
>> ID word
>> -------------
>> 1 car
>> 1 fruit
>> 2 car
>> 2 fruit
>> 2 vegetable
>> 3 car
>> 3 vegetable
>>
>> And the output should be simillar to:
>>
>> car fruit 2
>> car vegetable 2
>> fruit vegetable 1
>>
>>
>
> test=*# select * from foo;
> id | word
> ----+-----------
> 1 | car
> 1 | fruit
> 2 | car
> 2 | fruit
> 3 | car
> 3 | vegetable
> 2 | vegetable
> (7 rows)
>
> test=*# select word_a || ',' || word_b as pair,
> count(1)
> from ( select a.word as word_a, b.word as word_b from foo a join foo b on a.id=b.id where a.word != b.word and a.word > b.word) as bla
> group by pair;
> pair | count
> -----------------+-------
> vegetable,car | 2
> vegetable,fruit | 1
> fruit,car | 2
> (3 rows)
>
>
> Andreas
>
Thanks Andreas.

Is there a simple way to modify the proposed querry to count also the
equal pairs under ID number; e.g; I add new entry (ID=1; value=car), so:

select * from foo;
id | word
----+-----------
1 | car
1 | car
1 | fruit
2 | car
2 | fruit
2 | vegetable
3 | car
3 | vegetable
(8 rows)

The result according Andreas querry is as follows:
pair | count
-----------------+-------
vegetable,car | 2
vegetable,fruit | 1
fruit,car | 3
(3 rows)

How to evaluate pair car,car under ID=1 too?

With my best regards, Andrej

On Sun, Jun 10, 2007 at 06:40:19PM +0200, Andrej Kastrin wrote:
> A. Kretschmer wrote:
> >am Sun, dem 10.06.2007, um 13:41:27 +0200 mailte Andrej Kastrin folgendes:
> >
> >>Dear all,
> >>
> >>I need to count all pairs of instances which occure under given ID
> >>number, e.g.:
> >>
> >>ID word
> >>-------------
> >>1 car
> >>1 fruit
> >>2 car
> >>2 fruit
> >>2 vegetable
> >>3 car
> >>3 vegetable
> >>
> >>And the output should be simillar to:
> >>
> >>car fruit 2
> >>car vegetable 2
> >>fruit vegetable 1
> >>
> >>
> >
> >test=*# select * from foo;
> > id | word
> >----+-----------
> > 1 | car
> > 1 | fruit
> > 2 | car
> > 2 | fruit
> > 3 | car
> > 3 | vegetable
> > 2 | vegetable
> >(7 rows)
> >
> >test=*# select word_a || ',' || word_b as pair,
> > count(1)
> >from ( select a.word as word_a, b.word as word_b from foo a join foo b on
> >a.id=b.id where a.word != b.word and a.word > b.word) as bla group by pair;
> > pair | count
> >-----------------+-------
> > vegetable,car | 2
> > vegetable,fruit | 1
> > fruit,car | 2
> >(3 rows)
> >
> >
> >Andreas
> >
> Thanks Andreas.
>
> Is there a simple way to modify the proposed querry to count also the
> equal pairs under ID number; e.g; I add new entry (ID=1; value=car), so:
>
> select * from foo;
> id | word
> ----+-----------
> 1 | car
> 1 | car
> 1 | fruit
> 2 | car
> 2 | fruit
> 2 | vegetable
> 3 | car
> 3 | vegetable
> (8 rows)
>
> The result according Andreas querry is as follows:
> pair | count
> -----------------+-------
> vegetable,car | 2
> vegetable,fruit | 1
> fruit,car | 3
> (3 rows)
>
> How to evaluate pair car,car under ID=1 too?
>
> With my best regards, Andrej

Starting from Andreas' code with a teensy change, it's fairly
straight-forward.

The original:

SELECT word_a || ',' || word_b as pair, count(*)
FROM
(
SELECT a.word AS word_a, b.word AS word_b
FROM
foo a
JOIN
foo b
ON (
a.id=b.id
AND a.word != b.word
AND a.word > b.word
) AS bla
GROUP BY pair;

Now with duplicates allowed. Note that I've just commented out one
line and changed > to >=.

SELECT word_a || ',' || word_b as pair, count(*)
FROM
(
SELECT a.word AS word_a, b.word AS word_b
FROM
foo a
JOIN
foo b
ON (
a.id=b.id
/* AND a.word != b.word */
AND a.word >= b.word
) AS bla
GROUP BY pair;

Hope this helps :)

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter

Remember to vote!
Consider donating to PostgreSQL: http://www.postgresql.org/about/donate