在postgresql中使用sql平衡值

mklgxw1f 于 2021-06-24 发布在 Mysql

关注(0)|答案(1)|浏览(301)

我的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 姓名合计 power 应该是平等的 grp 名字。
我们可以从上面的查询中获取sum不相等的记录。然后我们就可以找出 sum(power) 值并将此差值添加到 power 任何 subgroup 哪里 power 在那种特殊的情况下更小 grp .
mysql解决方案也将被接受。
上面的查询将返回此数据，因为对于相同的 subgroup 总计 power 不等于跨越 grp 是的，

(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和组b之间的总功率差为（9-（-1-1））=11，因此我们将在组b下的任何sub_a功率值中添加11，假设我们修改了此记录， (3, 'Sub_A', -1, 'Group_B') 转换为 (3, 'Sub_A', 10, 'Group_B') 我们也会为其他人做同样的事情，只要有不平衡的地方。

sql mysql postgresql

来源：https://stackoverflow.com/questions/51058285/balance-the-values-using-sql-in-postgresql

1条答案

按热度按时间

ccrfmcuu1#

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

with foo(man_id, subgroup, power, grp) as (
    select * from
    (
    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')
    ) as x(man_id, subgroup, power, grp)
), sub_per_group as (
  select
    subgroup,
    grp,
    sum(power) tot_per_grp
  from foo
  group by subgroup,grp
), sub_calc as (
select
  subgroup,
  max(tot_per_grp) as max,
  json_agg(
    json_build_object(
        'grp',grp,
        'tot_per_grp',tot_per_grp
    )
  ) as grps_tot
from sub_per_group
group by subgroup
having count(distinct tot_per_grp)!=1
)
select f.man_id,f.subgroup,
  case
      when rn=1 then
      (
        power+
        (
         coalesce(max,0)-
         coalesce((
           select (v->>'tot_per_grp')::int
            from
          json_array_elements(grps_tot) as v where (v->>'grp')::text =f.grp),0)
        )
      )
    else
     power
  end,
  f.grp
from sub_calc sc
right join (
  select
    row_number() over(partition by subgroup,grp) as rn,
    foo.*
  from foo
) f on f.subgroup=sc.subgroup and f.rn=1
order by subgroup,grp

赞(0）回复(0）举报 2021-06-24

我来回答

在postgresql中使用sql平衡值

1条答案

相关问题

热门标签

最新问答