Do All Groups Have Equal Total Power For Given Subgroup?

October 14, 2022 Post a Comment

I have a PostgreSQL table like this: CREATE TABLE foo (man_id, subgroup, power, grp) AS VALUES ( 1, 'Sub_A', 1, 'Group_A' ), ( 2, 'Sub_B', -1, 'Group_A' ), ( 3, 'Sub_A

Solution 1:

Read the question carefully

I want to query the database with a subgroup name.

And:

I want something like

SELECT * FROM foo (solution query will be added)
WHERE subgroup = 'Sub_A'

The important point for performance is to exclude irrelevant rows early and only compute aggregates for the given subgroup. Then (assuming more than a few distinct subgroups), an index on (subgroup) can help:

CREATE INDEX ON foo (subgroup);

Each of the following queries returns FALSE if at least two groups have different total sums for the given subgroup, and TRUE in all other cases (with a minor exception for query 5, see below).

Query 1

SELECT count(DISTINCT total_power) = 1
FROM  (
   SELECT sum(power) AS total_power
   FROM   foo
   WHERE  subgroup = 'Sub_B'  -- exclude irrelevant rows early!
   GROUP  BY grp
   ) sub;

Query 2

SELECT count(*) = 1
FROM  (
   SELECT true
   FROM  (
      SELECT sum(power) AS total_power
      FROM   foo
      WHERE  subgroup = 'Sub_C'
      GROUP  BY grp
      ) sub2
   GROUP  BY total_power
   ) sub2;

Query 3

SELECT count(*) OVER () = 1
FROM  (
   SELECT sum(power) AS total_power
   FROM   foo
   WHERE  subgroup = 'Sub_A'
   GROUP  BY grp
   ) sub
GROUP  BY total_power
LIMIT  1;

Query 4

(
SELECT FALSE
FROM  (
   SELECT sum(power) AS total_power
   FROM   foo
   WHERE  subgroup = 'Sub_A'
   GROUP  BY grp
   ) sub
GROUP  BY total_power
OFFSET 1
LIMIT  1
)
UNION ALL
SELECT TRUE
LIMIT 1;

This one is special. Related answers with explanation:

Query 5

SELECT min(total_power) = max(total_power)  -- can fail for NULL values
FROM  (
   SELECT sum(power) AS total_power
   FROM   foo
   WHERE  subgroup = 'Sub_A'
   GROUP  BY grp
   ) sub;

The last can fail if NULL values in power are allowed. (But you would have to define expected results in this case anyway.)

I ran an extensive test and found all queries to perform about the same under ideal conditions:

db<>fiddle here

Query 5 tended to be a tad bit faster than the rest.

Solution 2:

Assuming that in your CREATE TABLE statement, 'sub_A' is meant to be 'Sub_A' (because Postgres is case-sensitive), and that your power values are actually integers (if they aren't, just add casts; the code below is simpler without them), then you can calculate the power for each subgroup within a group as follows:

select
    subgroup,
    grp,
    sum(power) as sum_power
from
    foo
group by
    subgroup,
    grp

To determine whether all of the total power values for a subgroup are the same, just check that the minimum and maximum values are the same. Convert the previous query into a subquery, where the main query does that comparison, as follows:

select
    subgroup
from (
    select
        subgroup,
        grp,
        sum(power) as sum_power
    from
        foo
    group by
        subgroup,
        grp
    ) as subpwr
group by
    subgroup
having
    min(sum_power) = max(sum_power);

Solution 3:

Here's 1 way not yet mentioned by the other answers

SELECT SUM(power) = FIRST_VALUE(SUM(power)) OVER () powpow 
FROM foo
WHERE subgroup = 'Sub_A'
GROUP BY grp
ORDER BY powpow
LIMIT 1

-- returns:
-- false if some values differ
-- true if all values are the same
-- no rows if the where condition fails to match any rows.

Lab Sql