时间戳

首页 > 解决方案 > 在 PostgreSQL 中使用 SQL 平衡值

mysql - 在 PostgreSQL 中使用 SQL 平衡值

问题描述

我的 Postgresql 表看起来像，

CREATE TABLE foo(man_id, subgroup, power, grp)
AS VALUES
    (1, 'Sub_A',  4, 'Group_A'),
    (2, 'Sub_B', -1, 'Group_A'),
    (3, 'Sub_A', -1, 'Group_B'),
    (4, 'Sub_B',  6, 'Group_B'),
    (5, 'Sub_A',  5, 'Group_A'),
    (6, 'Sub_B',  1, 'Group_A'),
    (7, 'Sub_A', -1, 'Group_B'),
    (8, 'Sub_B',  2, 'Group_B'),
    (9, 'Sub_C',  2, 'Group_B');

功率计算如下：

Total Power of Subgroup Sub_A in the grp Group_A is (4 + 5 ) = 9
Total Power of Subgroup Sub_B in the grp Group_A is ((-1) + 1 ) = 0
Total Power of Subgroup Sub_A in the grp Group_B is ((-1) + (-1) ) = -2
Total Power of Subgroup Sub_B in the grp Group_B is (6 + 2 ) = 8
So the power of Sub_A in the Group_A is not equal to power of Sub_A in the Group_B

So the power of Sub_B in the Group_A is not equal to power of Sub_B in the Group_B

我可以查询数据库并获取数据，其中相同subgroup名称的总数power在所有其他grp名称中不相等。

SELECT f.*
FROM  (
   SELECT subgroup
   FROM  (
      SELECT subgroup, grp, sum(power) AS total_power
      FROM   foo
      GROUP  BY subgroup, grp
      ) sub
   GROUP  BY 1
   HAVING min(total_power) <> max(total_power)
   ) sg
JOIN foo f USING (subgroup);

我也想让总和值相同。对于相同的名称，所有其他名称的subgroup总数应该相等。powergrp

我们可以从上面的查询中获取 sum 不相等的记录。然后我们可以找到值的差异，sum(power)并将这个差异值添加到该特定值下较小power的任何subgroup地方。powergrp

MySQL 解决方案也将被接受。

上述查询将返回此数据，因为对于相同的subgroup总数power不等于跨grps，

(1, 'Sub_A',  4, 'Group_A')
(5, 'Sub_A',  5, 'Group_A')
(3, 'Sub_A', -1, 'Group_B')
(7, 'Sub_A', -1, 'Group_B')
(2, 'Sub_B', -1, 'Group_A')
(6, 'Sub_B',  1, 'Group_A')
(4, 'Sub_B',  6, 'Group_B')
(8, 'Sub_B',  2, 'Group_B')

现在，我想修改幂的值以使总和相同，

例如，对于 Sub_A，Group_A 和 Group_B 之间的总功率差为 (9-(-1-1)) = 11 ，因此我们将在 Group_B 下的任何 Sub_A 功率值中添加 11，假设我们修改此记录，

(3, 'Sub_A', -1, 'Group_B')转换成(3, 'Sub_A', 10, 'Group_B')

我们也会为其他人做同样的事情，只要有不平衡的地方。

标签： mysqlsqlpostgresql

解决方案

下面的查询将产生所需的结果

  用 foo(man_id, subgroup, power, grp) 作为 (
    选择 * 从
    (
    价值观
    (1, 'Sub_A', 4, 'Group_A'),
    （2，'Sub_B'，-1，'Group_A'），
    (3, 'Sub_A', -1, 'Group_B'),
    (4, 'Sub_B', 6, 'Group_B'),
    (5, 'Sub_A', 5, 'Group_A'),
    (6, 'Sub_B', 1, 'Group_A'),
    (7, 'Sub_A', -1, 'Group_B'),
    (8, 'Sub_B', 2, 'Group_B'),
    (9, 'Sub_C', 2, 'Group_B')
    ) 作为 x(man_id, subgroup, power, grp)
), sub_per_group 为 (
  选择
    子群，
    grp,
    总和（功率）tot_per_grp
  来自 foo
  按子组分组，grp
), sub_calc 为 (
选择
  子群，
  最大值（tot_per_grp）为最大值，
  json_agg(
    json_build_object(
        'grp',grp,
        'tot_per_grp',tot_per_grp
    )
  ) 作为 grps_tot
来自 sub_per_group
逐个分组
有计数（不同的 tot_per_grp）！= 1
)
选择 f.man_id,f.subgroup,
  案子
      当 rn=1 时
      (
        电源+
        (
         合并（最大，0）-
         合并（（
           选择 (v->>'tot_per_grp')::int
            从
          json_array_elements(grps_tot) as v 其中 (v->>'grp')::text =f.grp),0)
        )
      )
    别的
     力量
  结尾，
  f.grp
来自 sub_calc sc
右连接 (
  选择
    row_number() over(partition by subgroup,grp) as rn,
    富。*
  来自 foo
) f.subgroup=sc.subgroup 和 f.rn=1 上的 f
按子组排序，grp

推荐阅读