Início / dba / Perguntas / 129483
Accepted
Kokizzu
Asked: 2016-02-17 21:05:11 +0800 CST

Proporção por grupo no PostgreSQL

  • 772

Se eu tiver linhas assim:

lec1 major1 class1 10
lec1 major2 class1 40
lec1 major1 class2 30
lec1 major3 class3 35
lec2 major1 class3 15
lec2 major3 class3 10

E eu preciso disso para combinar a proporção em:

lec1 major1 0.2 -- 10/(10+40)
lec1 major2 0.8 -- 40/(10+40)
lec1 major1 1 -- 30/30
lec1 major3 1 -- 35/35
lec2 major1 0.6 -- 15/(10+15)
lec2 major3 0.4 -- 10/(10+15)

Ou algo parecido com isto:

lec1 major1 1.2 -- 10/(10+40) + 30/30
lec1 major2 0.8 -- 40/(10+40)
lec1 major3 1 -- 35/35
lec2 major1 0.6 -- 15/(10+15)
lec2 major3 0.4 -- 10/(10+15)

Que consulta deve ser feita?

postgresql-9.4 group-by

1 respostas

  1. Best Answer

    Deixa pra lá, descobri eu mesmo :3

    WITH merged_stat (lec, maj, cla, cou) AS (
    values 
      ('lec1', 'major1', 'class1', 10.0),
      ('lec1', 'major2', 'class1', 40),
      ('lec1', 'major1', 'class2', 30),
      ('lec1', 'major3', 'class3', 35),
      ('lec2', 'major1', 'class3', 15),
      ('lec2', 'major3', 'class3', 10)
)
SELECT lec
    , maj
    , cou/SUM(cou) OVER (PARTITION BY lec, cla)
FROM merged_stat
;

    resultado:

     lec  |  maj   |        ?column?        
------+--------+------------------------
 lec1 | major1 | 0.20000000000000000000
 lec1 | major2 | 0.80000000000000000000
 lec1 | major1 | 1.00000000000000000000
 lec1 | major3 | 1.00000000000000000000
 lec2 | major1 | 0.60000000000000000000
 lec2 | major3 | 0.40000000000000000000
    • 0

relate perguntas