Compute trace of matrix in database

user_2137314 0
Viewed 29

I have a table in a Postgres that stores 10x10 matrices, where each row has it's own entry, defined as:

id, matrix_id, row_id, col1, col2, col3...

I'd like to compute the trace (sum of main diagonal) for every matrix identified by its matrix_id, that is, for every matrix_id, I would like to get (col1 where row_id=1) + (col2 where row_id=2) + (col3 where row_id=3)...

I've tried grouping it by matrix_id but then I cannot use subqueries, something like:

select matrix_id, (select col1 where row_id=1) + (col2 where row_id=2) +
(col3 where row_id=3) ... from matrix group by matrix_id;

but it doesn't work this way.

How could I do that?

postgresql sql
1 Answers

So long as they are all 10x10 matrices, use a case statement like so:

select matrix_id,
       sum(
         case row_id
           when 1 then col1
           when 2 then col2
           when 3 then col3
           when 4 then col4
           when 5 then col5
           when 6 then col6
           when 7 then col7
           when 8 then col8
           when 9 then col9
           when 10 then col10
         end
       ) as trace
  from matrix
 group by matrix_id;

Had variable-sized matrices been allowed, you could transpose columns to rows via to_jsonb() and then sum where row_id = <column suffix>.

EDIT TO ADD

Based on your comment, you really should update your version of PostgreSQL. That said, try a CTE to filter on the new trace column:

with traces as (
  select matrix_id,
         sum(
           case row_id
             when 1 then col1
             when 2 then col2
             when 3 then col3
             when 4 then col4
             when 5 then col5
             when 6 then col6
             when 7 then col7
             when 8 then col8
             when 9 then col9
             when 10 then col10
           end
         ) as trace
    from matrix
   group by matrix_id
)
select *
  from traces
 where trace > 100;
user_138083190
Related