Conditionally select most common value in an array in PostgreSQL

Question

I have a table (only one row) in my PostgreSQL 9.5 db with two columns i.e., count (bigint) and array (text).

count     array
6         "112,19.3,142,142,19.3,172,172,20.3,202,202,20.3,232,232,19.3,262,262,19.3,292"

The array represents six (thus count = 6) set of values i.e., Lower_limit, Value and Upper_limit. Now, I need to conditionally modify my array i.e., when upper limit and lower limits are coinciding then select the first upper limit and last lower limit and return the most common value (which is 19.3) among the limits. My desired output would be like:

count    array
1        112, 19.3, 292

Could anyone help me to have some pointers towards my desired output?

Vao Tsun · Accepted Answer · 2017-06-09 11:02:00Z

1

I must admin - I dont understand how you get count =1, but below is an example of how you can build array with firsrt, last and most common values. Mind if there would be several mos common values it would unpredictably pick on of em

t=#
with a(r) as (values(array[112,19.3,142,142,19.3,172,172,20.3,202,202,20.3,232,232,19.3,262,262,19.3,292]))
, p as (select * from a,unnest(a.r) with ordinality)
, t as (
select count(1) over (partition by unnest)
, unnest u
, r[1] a
, r[array_length(r,1)] e
from p
order by unnest
limit 1
)
select array[a,u,e]
from t
;
     array
----------------
 {112,19.3,292}
(1 row)

answered Jun 9, 2017 at 11:02

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

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

5 Comments

khajlk Over a year ago

Count represents the six set of values (lower, value, upper) when we update our array based on coinciding upper and lower limits then isn't it possible to update count based on output? Like your query returned one set.

Vao Tsun Over a year ago

sorry - I don't follow - so the count would be 3?.. not one?.. I did not get why you have six in first place:)

khajlk Over a year ago

Each count must contain three values. So probably we can count the array elements and update count in the last. Just an idea :)

Vao Tsun Over a year ago

ah,ok I think I get now - then my answer is wrong :)

khajlk Over a year ago

combination of lower + value + upper makes count and how many values we have in array must be counted and here comes the update for count :) Probably your answer needs an update.

Collectives™ on Stack Overflow

Conditionally select most common value in an array in PostgreSQL

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related