function to sum all first value of Results SQL

user_15172345 0
Viewed 280

I have a table with "Number", "Name" and "Result" Column. Result is a 2D text Array and I need to create a Column with the name "Average" that sum all first values of Result Array and divide by 2, can somebody help me Pls, I must use the create function for this. Its look like this:

Table1

Number Name Result Average
01 Kevin {{2.0,10},{3.0,50}} 2.5
02 Max {{1.0,10},{4.0,30},{5.0,20}} 5.0

Average = ((2.0+3.0)/2) = 2.5

= ((1.0+4.0+5.0)/2) = 5.0

postgresql function sql
2 Answers

First of all: You should always avoid storing arrays in the table (or generate them in a subquery if not extremely necessary). Normalize it, it makes life much easier in nearly every single use case.

Second: You should avoid more-dimensional arrays. The are very hard to handle. See Unnest array by one level

However, in your special case you could do something like this:

demo:db<>fiddle

SELECT
    number,
    name,
    SUM(value) FILTER (WHERE idx % 2 = 1) / 2                   -- 2
FROM mytable,
    unnest(avg_result) WITH ORDINALITY as elements(value, idx)  -- 1
GROUP BY number, name
  1. unnest() expands the array elements into one element per record. But this is not an one-level expand: It expand ALL elements in depth. To keep track of your elements, you could add an index using WITH ORDINALITY.
  2. Because you have nested two-elemented arrays, the unnested data can be used as follows: You want to sum all first of two elements, which is every second (the odd ones) element. Using the FILTER clause in the aggregation helps you to aggregate only exact these elements.

However: If that's was a result of a subquery, you should think about doing the operation BEFORE array aggregation (if this is really necessary). This makes things easier.

user_39842210

Assumptions:

  • number column is Primary key.
  • result column is text or varchar type

Here are the steps for your requirements:

  1. Add the column in your table using following query (you can skip this step if column is already added)
alter table table1 add column average decimal;
  1. Update the calculated value by using below query:
update table1 t1
set average = t2.value_
from 
(
select
number, 
sum(t::decimal)/2 as value_ 
from table1 
cross join lateral unnest((result::text[][])[1:999][1]) as t 
group by 1
) t2 
where t1.number=t2.number

Explanation: Here unnest((result::text[][])[1:999][1]) will return the first value of each child array (considering you can have up to 999 child arrays in your 2D array. You can increase or decrease it as per your requirement)

DEMO

Now you can create your function as per your requirement with above query.

user_77724830
Related