Merge/combine values within a group - Databases

khajlk

Maybe I am wrong, but the solution doesn't seem to work as expected when there is a significant gap in ids (non-adjacent ids). I have explained the issue in the follow-up question.

khajlk

Hi Jack Douglas, I have posted a follow up question https://topanswers.xyz/databases?q=1654.

khajlk

Sure, I will do. Thanks.

Jack Douglas replying to khajlk

If you wouldn't mind posting the followup as a sererate question, I'll post an answer — you can add a link back to this question to indicate it is a follup but it should be answerable without reading this question too

khajlk

In the above, I have only written the expected output with no merging. Please consider to see the sample data.

khajlk

182,212,0.0, 212, 242, 0.0, 242, 272, 0.0, 272, 302, 21.4, 302, 332, 21.4, 332, 2, 0.0, 2, 32, 0,0

khajlk

´292,322,18.8,322,352,18.8,352,22,18.8, 22,52,18.8, 52, 82, 18.8, 82, 112, 18.8´

khajlk replying to Jack Douglas

Hi Jack Douglas, hope you are well, just following up on this question. What if no merging was perfomed. For example, for each group and id, I wish to have an output like this:

khajlk

Thank you very much..

Jack Douglas

there you go

Jack Douglas replying to khajlk

ok don't worry, we won't do a full merge then, but I'll switch the attribution on this question so you see it on your profile.

khajlk

I have noted the login id key of this account. If you are asking about the other "anonymous" account, I am not sure if I can access that account anymore since I couldn't manage to keep the key.

Jack Douglas

(or first 8 chars from your old account token if you noted it down)

Jack Douglas replying to khajlk

can you post the first 8 chars of your login id cookie for the other account if you still have access?

khajlk

Yes, I think, it's Ok to merge the accounts. I didn't like to be called "anonymous" :P

Jack Douglas replying to khajlk

ah ok you have a second account now — do you still have access to your other account? If you do we can merge the accounts.

khajlk

Yes, I am :)

Jack Douglas replying to khajlk

are you the same person who posted the question?

khajlk

Thank you very much for the tips and suggestions, very pleased to be here to improve my SQL knowledge.

Jack Douglas

@Anonymous a couple of style issues:  
1. you should never `insert` without explicitly specifying the columns (you can get away with it immediately after the `create table` statement but it is almost always bad otherwise) 
1. it is generally unwise to use reserved words as column names (so `avg` should be called something else)

Anonymous 1097

It helped surely :) I shall have a look at the code, and post my question if I have one. Thanks a lot!

Jack Douglas replying to Anonymous 1097

I'm not sure I follow that — surely the ordering is already based on `id`? See this for example, which shows the min and max ids in each subgroup:  
  
<>https://dbfiddle.uk/?rdbms=postgres_9.5&fiddle=6366833c9a7bab46413a62971ccf78b9&hide=3

Jack Douglas replying to Anonymous 1097

glad it helped :)

Anonymous 1097 replying to Jack Douglas

Thank you very much for all your help, really appreciate that! It was the first time I came at this platform and I am glad I chose to come here.

Anonymous 1097 replying to Jack Douglas

I just asked out of my curiosity, the ordering of `grp=7` should also be based on `id`.

Jack Douglas replying to Anonymous 1097

I've updated the question and answer to use the supplied `id` for ordering — you can find the originals in the question and answer history if you need them for reference

Jack Douglas replying to Anonymous 1097

what exactly is that ordering of `grp=7` based on?

Anonymous 1097

I am puzzled that this could be done without `id` ordering.. interesting! keeping the same approach.. is it also possible to have `array_agg` of `grp=7` like this: `{335,35,0.0,35,155,21.2,275,335,0.0}`? If yes, could you please  show me how? Regarding `id` ordering, that would be great if you could also suggest alternate solution using `id` ordering.

Jack Douglas

if you'd like me to tweak the answer to rely on the `id` ordering let me know…

Jack Douglas replying to Anonymous 1097

the solution I've posted can be simplified if the ordering can be done on `id` as you say — can you see how that would be done from my fiddle (if you look at the hidden batches, you'll see each step broken down and the initial steps won't need the recursive ordering to add an `ordinal` column, you can just do it with `row_number() over (partition by grp order by id)`

Anonymous 1097

Low and high values within a group (for each row are unique), so, yes they are not repeated..

Anonymous 1097

I added the "id" column for ordering within each group and updated the expected output in case of ordering with "id".

Anonymous 1097 replying to Jack Douglas

I think for ordering "id" could be used (please have a look at same data my_tbl1), I would update the new sample data.. In case of multiple merged rows (example, grp 6 and 7), the low and high need to be arranged in such a way that the high of the last merged row will be the low of the next merged_row unless there is a gap in next low/high..

Anonymous 1097 replying to Jack Douglas

Yes. I try to explain the logic with an example. In the sample data "my_tbl2", grp = 6,  the first three rows have avg values = 0.0, in principle, all these three rows could be merged and output could have been 182,272,0.0. But, it is not allowed. It is because in the first row (grp=6), there is axis_2=212 so this row is merged until high=212 and should not be merged to the next row. Afterwards, Merging starts again until value = 0.0 is same. In other words, merging needs to be stopped/split at axis_1 and axis_2 values. Hope it makes sense.

Jack Douglas

> rows should not be merged crossing over the axes values@Anonymous   
  
by that you mean if `low` or `high` is actually equal to the axis value?

Anonymous 1097

True, it's old an version and for some strange reason I found pgadmin3 over pgadmin4. Fortunately, Pg9.5 is still supported by Enterprise Db.

Jack Douglas

@Anonymous is the only way of ordering the rows within a `grp`to iterate through chaining the previous `high`with the next `low`? Can we assume there will never be repeated values of `low` and `high` within each `grp` (as there aren't in your sample data)?

Jack Douglas replying to Anonymous 1097

probably won't make a difference, it's just quite an unusual old version to have in production as it doesn't match the supported RHEL or Debian versions — just curious really!

Jack Douglas replying to Anonymous 1097

ah sorry I understand now!

Anonymous 1097

@Jack Douglas does it make a  difference with  Pg9.5 or 11?

Anonymous 1097

I have updated the sample data. Yes, I am using Pg9.5

Anonymous 1097

Okay that's weird, I posted a new sample data which was related to the dbfiddle I mentioned. I am going to edit the question to update axes values.

Jack Douglas

@Anonymous are you really using postgres 9.5?

Jack Douglas

@Anonymous `axis_1` and `axis_2` aren't in your sample data — are they generated somehow?

1 Answer