具有多个条件的Oracle SQL Group和sum

Question

​

​

我附上了两张表的截图：

  - the left table is a result of others "Select" query
  - the right table is the result I want from the left table

可以按照下面的条件创建正确的表：

1. 当相同的Unit具有全正或所有负能量值时，当相同的Unit有E 112正和负E 213E 213能量值时，结果保持相同的
2. 值：H 214G 215

- Make a sum of all Energy for that `Unit`(-50+15+20 = -15) and then take the maximum of absolut value for the Energy.`e.g. max(abs(energy))=50` and take the price for that value. 

​

我使用SQL ORACLE。

我真的很感激你在这件事上的帮助！

http://sqlfiddle.com/#!4/eb85a/12

Answer 1

这将返回所需的结果：

• signs CTE查找是否存在正负值，以及ABS的最大能量值
• ，然后，有两个选择的结合:一个返回“原始”行(如果不同符号的计数为1)，另一个返回“计算”值，正如您所描述的

SQL> with
  2  signs as
  3    (select unit,
  4            count(distinct sign(energy)) cnt,
  5            max(abs(energy)) max_abs_ene
  6     from tab
  7     group by unit
  8    )
  9  select t.unit, t.price, t.energy
 10    from tab t join signs s on t.unit = s.unit
 11    where s.cnt = 1
 12  union all
 13  select t.unit, t2.price, sum(t.energy)
 14    from tab t join signs s on t.unit = s.unit
 15    join tab t2 on t2.unit = s.unit and abs(t2.energy) = s.max_abs_ene
 16    where s.cnt = 2
 17    group by t.unit, t2.price
 18  order by unit;

UNIT                      PRICE     ENERGY
-------------------- ---------- ----------
A                            20        -50
A                            50        -80
B                            13        -15

SQL>

但是，如果energy = +50中还有另一个"B“单元行，您会期待什么呢？那么两行将具有相同的MAX(ABS(ENERGY))值。

Answer 2

union all可能是最简单的解决方案：

with t as (
      select t.*,
             max(energy) over (partition by unit) as max_energy,
             min(energy) over (partition by unit) as min_energy
      from t
     ) 
select unit, price, energy
from t
where max_energy > 0 and min_energy > 0 or
      max_energy < 0 and min_enery < 0
union all
select unit,
       max(price) keep (dense_rank first order by abs(energy)),
       sum(energy)
from t
where max_energy > 0 and min_energy < 0
group by unit;