I have the following table in postgresql lets call it table1

• I need to find the number of entities that have different values across different entity groups.
• In the above example the answer will be 2 (entity_id: 2,3).

I have a hacky way of doing this as follows

with entity_diff as (
    with entitya as (
        select entity_id,
               value as value_group_a
        from table1
        where entity_group = 'A'
    ),
         entityb as (
             select entity_id,
                    value as value_group_b
             from table1
             where entity_group = 'B'
         )
    select entitya.entity_id,
           entitya.value_group_a - entityb.value_group_b as value_diff
    from entitya
             inner join entityb on entitya.entity_id = entityb.entity_id
)
select count(*) from from entity_diff
where abs(entity_diff.value_diff) > 0;

Is there a simpler way of getting to this answer that can also scale say when I need to compare 3 or 4 groups.

CodePudding user response：

You may try the following approaches which uses:

1. a group by with a having clause
2. self-join on similar entity_ids but different entitygroups that have different values.

Query #1

select 
    count(1)
from (
    select
        entityid
    from
        table1
    group by
        entityid
    having
        count(distinct entitygroup) > 1 and
        min(value) <> max(value)
) t1;

Query #2

select
    entityid
from
    table1
group by
    entityid
having
    count(distinct entitygroup) > 1 and
    min(value) <> max(value);

Query #3

select
    count(distinct t1.entityid)
from
    table1 t1
inner join
    table1 t2 on t1.entityid = t2.entityid and
                 t1.entitygroup < t2.entitygroup and
                 t1.value <> t2.value;

Query #4

select
    count(distinct t1.entityid)
from
    table1 t1
inner join
    table1 t2 on t1.entityid = t2.entityid and
                 t1.entitygroup < t2.entitygroup and
                 abs(t1.value - t2.value)>0;

Query #5

select distinct
    t1.entityid
from
    table1 t1
inner join
    table1 t2 on t1.entityid = t2.entityid and
                 t1.entitygroup < t2.entitygroup and
                 abs(t1.value - t2.value) > 0;

View working demo on DB Fiddle

CodePudding user response：

You can use count(distinct) with a subquery:

select count(*) from (select t1.id, count(distinct t1.value) h from table1 t1 group by t1.id) t2 
where t2.h = (select count(*) from table1 t3 where t3.id = t2.id) and t2.h > 1;

Output:

count
-----
2

And the corresponding entity IDs are (select t2.id from ...):

id
-----
2
3