CODEWITHSUNDEEP
postgresqlgroup-bycount
balkon16
balkon16

Reputation: 1448

PostgreSQL count other values of ID that have the same value of other column

Let's say we have the following table that stores id of an observation and its address_id. You can create the table with the following code:

drop table if exists schema.pl_address_cnt;
create table schema.pl_address_cnt (
    id serial,
    address_id int);

insert into schema.pl_address_cnt(address_id) values 
(100), (101), (100), (101), (100), (125), (128), (200), (200), (100);

My task is to count for each id how many other ids (thus -1) have the same address_id. I've come up with a solution that turns out to be quite expensive (explain) on the original dataset. I wonder whether my solution can be somehow optimised.

with tmp_table as (select address_id
                        , count(distinct id) as id_count
                    from schema.pl_address_cnt
                    group by address_id
)
select id
    , id_count - 1
from schema.pl_address_cnt as pac
left join tmp_table as tt on tt.address_id=pac.address_id;

Upvotes: 0

Views: 574

Answers (1)

sticky bit
sticky bit

Reputation: 37472

You can try to omit the CTE and do a self left join on common address but different ID and then aggregate this.

SELECT pac1.id,
       count(pac2.id)
       FROM pl_address_cnt pac1
            LEFT JOIN pl_address_cnt pac2
                      ON pac1.address_id = pac2.address_id
                         AND pac1.id <> pac2.id
       GROUP BY pac1.id
       ORDER BY pac1.id;

For performance you can try indexes on (address_id, id) and (id).

Upvotes: 1

Related Questions