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 sets of values i.e.,
Upper_limit.How? every three elements of array make the set as 1 thus 18 elements / 3 = 6 sets (count). Now, I need to conditionally modify my array i.e., when upper limit and lower limits are coinciding AND if the difference among values is less than or equal to 1 then merge those sets and 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?
The array is formed from a series of three members/elements i.e., lower limit, value and upper limit. Whenever, these three values appear in array elements it means we have 1 set (count = 1). In the question, there are 18 elements in array >> 18 / 3 = 6 sets (or count = 6). It can be considered as counting the number of elements in array (3 elements make one set i.e., foo, value, bar >> 1 set). I hope things are clear and I may replace "count" by "set" in question if it creates confusion.
Now my requirement was:
- Starting from the first set, check if the upper limit of first set coincides with the next AND
- if the difference among values (19.3 and 19.3) is less than or equal to 1
- If both or any of above condition fulfills, then merge them and get limits of merged ones
- Do the same for all sets and get the lower and upper limit of finally merged set. Count the elements of merged set and update "counter/set"
In the given array, the difference among values is less than or equal to 1 and also starting from 1st set
(112, 19.3, 142)to second set
the upper limit of first set coincides with the lower limit of second set means there are next to each other and also the difference between their values is 0 thus they should be merged. In this manner, I needed to have my desired output. Hope things are clear and make sense now.