0

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., Lower_limit, height and 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?

UPDATE1:

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"

illustration of required scenario

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 (142,19.3, 172), 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.

closed as unclear what you're asking by ypercubeᵀᴹ, Colin 't Hart, McNets, Marco, LowlyDBA Jun 12 '17 at 14:37

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • "The array represents six (thus count = 6) set of values i.e., Lower_limit, Value and Upper_limit".. That's three values, am I confused here? – Evan Carroll Jun 10 '17 at 0:39
  • The array contained 18 elements. Three elements make count as 1 (we can also assume this as one set) thus, 18 elements / 3 = 6 sets (count). Each set must contain three values. So probably we can count() the array elements and update "count/set" in the last. Just an idea :) Hope things are clear. – khajlk Jun 10 '17 at 9:58
3

I figured out your question by brute force. Please give more details next time. I still have no idea what count() is for. You want the min(), max() and mode() from an array. We can do that by splitting out the text value with string_to_array. Then we can unnest the array to get something we can run our aggregate functions on.

Note, mode is a ordered set aggregate. That's why it looks different.

From there the job is simple.

CREATE TABLE foo(v) AS 
VALUES ('112,19.3,142,142,19.3,172,172,20.3,202,202,20.3,232,232,19.3,262,262,19.3,292');

SELECT
  min(x),
  mode() WITHIN GROUP (ORDER BY x),
  max(x)
FROM foo
CROSS JOIN LATERAL unnest(string_to_array(v, ',')) AS x;

 min | mode | max 
-----+------+-----
 112 | 19.3 | 292
(1 row)

Or, if you want that in an array..

SELECT ARRAY[
  min(x),
  mode() WITHIN GROUP (ORDER BY x),
  max(x)
]
FROM foo
CROSS JOIN LATERAL unnest(string_to_array(v, ',')) AS x;

     array      
----------------
 {112,19.3,292}
(1 row)
  • 1
    I think mode() is an Ordered-Set Aggregate Function and not a Hypothetical-Set Aggregate Function – joanolo Jun 10 '17 at 6:34
  • @Evan: First, thank you and my bad for providing less details. I have edited the question and added more details to make things clear. Please have a look and let me know if still confusion persists. I want to select the most repeated value but based on some conditions which I have added. – khajlk Jun 10 '17 at 9:54
  • a little twist if you dont mind answering. In the same question, for the same array how do i find all the values less than 140 – PirateApp Mar 13 '18 at 11:50

Not the answer you're looking for? Browse other questions tagged or ask your own question.