Postresql DB 9. *. I want to get from:

from

time | value | -----+---------+ 1 | A | 2 | A | 3 | A | 4 | B | 5 | B |

this

 time | value | length | buf_1 | buf_2 | -----+---------+--------+---------+-------+ 1 | A | 1 | A | A | 2 | A | 1 | A,A | A | 3 | A | 1 | A,A,A | A | 4 | B | 2 |A,A,A,B | A,B | 5 | B | 2 |A,A,A,B,B| A,B |

where the column length, it seems to me, is approximately like this:

 array_length(array_agg(distinct(values)), 1)

but this will give me a finite length for all values, and I want the length to be recorded dynamically for time-ordered values, or at least get the time when value changes from A to B, i.e. time = 4 here thanks!

  • write russian please - cache
  • I do not understand, you just need a column with an array of values ​​accumulated in this line or just a number that increases when the value changes - Mike
  • you need a column length, and in fact the value of time when the length each time becomes more by one - Marcel Mars
  • But in your source data I see only single characters, there are no values. I understand you are trying to gather them into an array to get the length of this array. But the same will give you the rank () function - Mike
  • Well, maybe I don’t really know sql) so I thought to do it through 'select rank () over (partition by smth)' but I didn’t know what to divide, so that 'rank ()' A, B was like in the column length. And, accordingly, could pull out the value of time , where length (rank ()) == 2 - Marcel Mars

1 answer 1

If I understood the question correctly, then something like this:

 with TableX as( select * from ( values (1,'A'),(2,'A'),(3,'A'),(4,'B'),(5,'B') ) X(time, value) ) select time, value, sum(Change) over(order by time)+1 from ( select *, case when value <> lag(value) over(order by time) then 1 else 0 end as Change from TableX ) A

In an internal query, the lag() function retrieves the value of the field from the previous entry in the specified sort. And based on this, case returns 1 for strings where the value has changed. If you only need these strings in the end, you can add the condition where Change=1 in the external query. Just in case, I got a field like your length by summing up the signs of change.

  • Got it. Thank you!) - Marcel Mars