Обсуждение: 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