From: | Andreas Kretschmer <andreas(at)a-kretschmer(dot)de> |
---|---|
To: | pgsql-sql(at)lists(dot)postgresql(dot)org |
Subject: | Re: window function ? |
Date: | 2017-12-10 18:48:58 |
Message-ID: | d3a908bd-cc65-719d-7cb8-19a36cbefd5f@a-kretschmer.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Olivier Leprêtre | 2017-12-11 17:09:25 | Windowing ? |
Previous Message | Olivier Leprêtre | 2017-12-10 18:33:54 | window function ? |