PostgreSQL aggregate function for each row across multiple unknown number of columns

Question

I looked through similar questions like this one, but they seem to have a definite number of columns. I would like to input a table that I do not know the number of columns.

Question:

How to calculate aggregate functions (e.g. avg() or sum() ) for each row across several columns if number of columns is not known in advance?

I have put the input table panel_stats_rnd csv and a DLL to create it here.

I would like to calculate for each row the rnd_avg_parcelcount as average of all columns c_1_avg_parcelcount, c_2_avg_parcelcount, ... where I can have input tables with any number (say 100) columns of _avg_parcelcount. And for columns rnd_sum_parcelcount I would like to calculate sum() of all columns that start with c_ and end with _sum_parcelcount.

The table looks like this:

SELECT * FROM panel_stats_rnd;
 gid | d  | dist_from | dist_to | distlabel | rnd_avg_parcelcount | rnd_sum_parcelcount | rnd_avg_callcount | rnd_sum_callcount | rnd_avg_perccalled | called_avg_parcelcount | called_sum_parcelcount | called_avg_callcount | called_sum_callcount | called_avg_perccalled | c_1_avg_parcelcount | c_1_sum_parcelcount | c_1_avg_callcount | c_1_sum_callcount |  c_1_avg_perccalled  | c_2_avg_parcelcount | c_2_sum_parcelcount | c_2_avg_callcount | c_2_sum_callcount |  c_2_avg_perccalled  
-----+----+-----------+---------+-----------+---------------------+---------------------+-------------------+-------------------+--------------------+------------------------+------------------------+----------------------+----------------------+-----------------------+---------------------+---------------------+-------------------+-------------------+----------------------+---------------------+---------------------+-------------------+-------------------+----------------------
   1 |  0 |         0 |     100 | 0-100     |                     |                     |                   |                   |                    |                 119045 |                 119045 |               119045 |                   23 |  0.000193204250493511 |              119045 |              119045 |            119045 |                16 | 0.000134402956865051 |              119045 |              119045 |            119045 |                16 | 0.000134402956865051
   2 |  1 |       100 |     200 | 100-200   |                     |                     |                   |                   |                    |                 163140 |                 163140 |               163140 |                   22 |  0.000134853500061297 |              163140 |              163140 |            163140 |                17 | 0.000104204977320093 |              163140 |              163140 |            163140 |                18 | 0.000110334681868334
   3 |  2 |       200 |     300 | 200-300   |                     |                     |                   |                   |                    |                 135934 |                 135934 |               135934 |                   10 |   7.3565112481057e-05 |              135934 |              135934 |            135934 |                18 | 0.000132417202465903 |              135934 |              135934 |            135934 |                15 | 0.000110347668721585
   4 |  3 |       300 |     400 | 300-400   |                     |                     |                   |                   |                    |                 116874 |                 116874 |               116874 |                   13 |  0.000111230898232284 |              116874 |              116874 |            116874 |                11 | 9.41184523503944e-05 |              116874 |              116874 |            116874 |                18 | 0.000154012012937009
   5 |  4 |       400 |     500 | 400-500   |                     |                     |                   |                   |                    |                  93216 |                  93216 |                93216 |                   12 |  0.000128733264675592 |               93216 |               93216 |             93216 |                10 | 0.000107277720562993 |               93216 |               93216 |             93216 |                12 | 0.000128733264675592
   6 |  5 |       500 |     600 | 500-600   |                     |                     |                   |                   |                    |                  69992 |                  69992 |                69992 |                    7 |    0.0001000114298777 |               69992 |               69992 |             69992 |                10 | 0.000142873471253858 |               69992 |               69992 |             69992 |                 7 |   0.0001000114298777
   7 |  6 |       600 |     700 | 600-700   |                     |                     |                   |                   |                    |                  50816 |                  50816 |                50816 |                   10 |  0.000196788413098237 |               50816 |               50816 |             50816 |                 6 | 0.000118073047858942 |               50816 |               50816 |             50816 |                 0 |                    0
   8 |  7 |       700 |     800 | 700-800   |                     |                     |                   |                   |                    |                  34814 |                  34814 |                34814 |                    0 |                     0 |               34814 |               34814 |             34814 |                 6 | 0.000172344459125639 |               34814 |               34814 |             34814 |                 4 | 0.000114896306083759
   9 |  8 |       800 |     900 | 800-900   |                     |                     |                   |                   |                    |                  23023 |                  23023 |                23023 |                    1 |  4.34348260435217e-05 |               23023 |               23023 |             23023 |                 4 | 0.000173739304174087 |               23023 |               23023 |             23023 |                 1 | 4.34348260435217e-05
  10 |  9 |       900 |    1000 | 900-1000  |                     |                     |                   |                   |                    |                  14215 |                  14215 |                14215 |                    1 |  7.03482237073514e-05 |               14215 |               14215 |             14215 |                 1 | 7.03482237073514e-05 |               14215 |               14215 |             14215 |                 5 | 0.000351741118536757
  11 | 10 |      1000 |    5000 | 1000-5000 |                     |                     |                   |                   |                    |                  23527 |                  23527 |                23527 |                    0 |                     0 |               23527 |               23527 |             23527 |                 0 |                    0 |               23527 |               23527 |             23527 |                 3 | 0.000127513070089684
(11 rows)

I tried the following for 2 columns (works but I'd rather not write it 5 times for 100 columns, besides the number of columns has to be a parameter):

SELECT d,c_1_avg_parcelcount,c_2_avg_parcelcount,
  (SELECT avg(c) FROM (VALUES (c_1_avg_parcelcount) , (c_2_avg_parcelcount) ) T (c)) AS Avg_,
  (SELECT sum(c) FROM (VALUES (c_1_avg_parcelcount) , (c_2_avg_parcelcount) ) T (c)) AS sum_
FROM   panel_stats_rnd;

I also tried the following but doesn't work.

WITH cols AS (
    select value(column_name) from information_schema.columns
    where table_name = 'panel_stats_rnd'
          AND column_name SIMILAR TO 'c_%avg_parcelcount'
          AND column_name != 'called_avg_parcelcount'
)
SELECT *, (SELECT avg(Col) FROM cols V(Col) ) AS col_average
FROM   panel_stats_rnd;

I am almost there but something is missing...

Abelisto · Accepted Answer · 2017-04-10 08:32:41Z

2

select
  *,
  (select avg(v::numeric)
     from json_each_text(row_to_json(panel_stats_rnd.*)) as j(k,v)
    where k like 'c\_%\_avg\_parcelcount') as rnd_avg_parcelcount,
  (select sum(v::numeric)
     from json_each_text(row_to_json(panel_stats_rnd.*)) as j(k,v)
    where k like 'c\_%\_sum\_parcelcount') as rnd_sum_parcelcount
from
  panel_stats_rnd;

Look at the documentation about functions involved.

There are escapes for underlying characters (\_) because for like operator it is meaning any single character, for example select 'a' like '_'; is true.

edited Apr 10, 2017 at 8:32

answered Apr 6, 2017 at 20:25

Abelisto

15.8k3 gold badges38 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL aggregate function for each row across multiple unknown number of columns

Question:

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Question:

1 Answer 1

Comments

Linked

Related