Thread: 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@fetter.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