Postgres Pro
Downloads
Home   >   mailing lists

Thread: window function ?

window function ?

From
Olivier Leprêtre
Date:

Hi,

 

I have a table containing sort of boxes in different categories described by three columns categorie/box/count

In each categorie, I want to associate each box with the count of the others (sum of counts of this categorie but not the current one).

 

As an example :

 

cat     box     count

cat1    box21 2

cat1    box23 6

cat1    box34 1

cat1    box37 3

cat3    box45 12

cat3    box62 2

cat3    box89 7

cat3    box12 9

cat3    box28 10

cat8    box02 10

cat8    box87 2

cat8    box46 3

 

will return

 

cat1    box21 2        10 (6+1+3) => 2 not added

cat1    box23 6        6 (2+1+3) => 6 not added

cat1    box34 1        11 (2+6+3) => 1 not added

cat1    box37 3        9 (2+6+1) => 3 not added

 

cat3    box45 12       28 (2+7+9+10)

cat3    box62 2        38 (12+7+9+10)

cat3    box89 7        33 (12+2+9+10)

cat3    box12 9        31 (12+2+7+10)

cat3    box28 10       30 (12+2+7+9)      

 

cat8    box02 10       5 (2+3)

cat8    box87 2        13 (10+3)

cat8    box46 3        12 (10+2)

 

I searched thru lateral and window functions but didn't manage to do that.

 

thanks for any help.

 

Olivier


Garanti sans virus. www.avast.com

Re: window function ?

From
Andreas Kretschmer
Date:

Am 10.12.2017 um 19:33 schrieb Olivier Leprêtre:
>
> Hi,
>
> I have a table containing sort of boxes in different categories 
> described by three columns categorie/box/count
>
> In each categorie, I want to associate each box with the count of the 
> others (sum of counts of this categorie but not the current one).
>
> As an example :
>
> cat     box     count
>
> cat1    box21 2
>
> cat1    box23 6
>
> cat1    box34 1
>
> cat1    box37 3
>
> cat3    box45 12
>
> cat3    box62 2
>
> cat3    box89 7
>
> cat3    box12 9
>
> cat3    box28 10
>
> cat8    box02 10
>
> cat8    box87 2
>
> cat8    box46 3
>
> will return
>
> cat1    box21 2        10 (6+1+3) => 2 not added
>
> cat1    box23 6        6 (2+1+3) => 6 not added
>
> cat1    box34 1        11 (2+6+3) => 1 not added
>
> cat1    box37 3        9 (2+6+1) => 3 not added
>
> cat3    box45 12       28 (2+7+9+10)
>
> cat3    box62 2        38 (12+7+9+10)
>
> cat3    box89 7        33 (12+2+9+10)
>
> cat3    box12 9        31 (12+2+7+10)
>
> cat3    box28 10       30 (12+2+7+9)
>
> cat8    box02 10       5 (2+3)
>
> cat8    box87 2        13 (10+3)
>
> cat8    box46 3        12 (10+2)
>
> I searched thru lateral and window functions but didn't manage to do that.
>
>
>
>     
>


test=*# select * from boxes ;
  cat  |  box  | count
------+-------+-------
  cat1 | box21 |     2
  cat1 | box23 |     6
  cat1 | box34 |     1
  cat1 | box37 |     3
  cat3 | box45 |    12
  cat3 | box62 |     2
  cat3 | box89 |     7
  cat3 | box12 |     9
  cat3 | box28 |    10
  cat8 | box02 |    10
  cat8 | box87 |     2
  cat8 | box46 |     3
(12 Zeilen)

test=*# select cat, box, sum(count) over (partition by cat) - count from 
boxes;
  cat  |  box  | ?column?
------+-------+----------
  cat1 | box21 |       10
  cat1 | box23 |        6
  cat1 | box34 |       11
  cat1 | box37 |        9
  cat3 | box45 |       28
  cat3 | box62 |       38
  cat3 | box89 |       33
  cat3 | box12 |       31
  cat3 | box28 |       30
  cat8 | box02 |        5
  cat8 | box87 |       13
  cat8 | box46 |       12
(12 Zeilen)

test=*#


helps that?


Regards, Andreas

-- 
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com