对于给定的子组，所有组的总功率是否相等？ [英] Do all groups have equal total power for given subgroup?

问题描述

我有一个这样的PostgreSQL表：

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', -1, 'Group_B' ),
    ( 4, 'Sub_B',  1, 'Group_B' ),
    ( 5, 'Sub_A', -1, 'Group_A' ),
    ( 6, 'Sub_B',  1, 'Group_A' ),
    ( 7, 'Sub_A', -1, 'Group_B' ),
    ( 8, 'Sub_B',  1, 'Group_B' );

功率计算如下：

grp Group_A中的子组Sub_A的总功率为（1 +（-1））= 0

grp Group_A中的子组Sub_B的总功率为（（-1）+ 1）= 0

grp Group_B中的子组Sub_A的总功率为（（-1）+（-1））= -2

grp Group_B中的子组Sub_B的总功率为（ 1 + 1）= 2

因此，Group_A中Sub_A的幂不等于Group_B中Sub_A的幂

因此，Group_A中Sub_B的功效不等于Group_B中Sub_B的功效

我想用<$来查询数据库c $ c> subgroup 名称。如果相同的子组的名称权力在所有其他 grp 名称中均相等，则它将返回 True ，否则 False 。

I want to query the database with a subgroup name. If for a same subgroup name power is equal across all the other grp names, then it will return True, else False.

例如， sub_A 和 sub_B 都将返回 False 。推荐的方法是什么？

As an example, sub_A and sub_B both will return False. What would be the recommended way to do this?

我想要类似的东西：

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

它返回 False 。

推荐答案

阅读问题

Read the question carefully

我要用子组名称查询数据库。

I want to query the database with a subgroup name.

并且：

我想要类似的东西

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);

以下每个查询均返回 FALSE 至少两个组的给定子组的总和不同，在 all 其他情况下， TRUE （查询5的次要例外，请参见下文） ）。

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).

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;

查询2

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;

查询3

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;

查询4

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:

• 如果找不到记录，则返回一个值


• 如何尝试多个SELECT直到结果可用？

• Return a value if no record is found
• Way to try multiple SELECTs till a result is available?

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;

如果 NULL 中的值最后一个可能失败允许电源。 （但是无论如何，您都必须在这种情况下定义预期的结果。）

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<>小提琴此处

查询5往往要比其他查询快一点。

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

这篇关于对于给定的子组，所有组的总功率是否相等？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！