I’m working on grouping data by whether the value in a column is greater than the given threshold. Here, a group contains continuous data no smaller than a given threshold. I would like to retrieve the first row containing the max value in a group, and if there is only one value in a group, it should be saved directly. As shown in the below picture, suppose the threshold is 0.3, and I want to obtain the records pointed by arrows:
Can it only be achieved by writing a for .. loop?
The segment function can be used to group the data which need to be ordered one by one in a group.
t=table(2021.09.29+0..15 as date,0 0 0.3 0.3 0 0.5 0.3 0.5 0 0 0.3 0 0.4 0.6 0.6 0 as v)
select * from t context by segment(v >= 0.3) having v >=0.3 and v=max(v) limit 1
Note: context by can be used together with the limit clause to get the first n or last n records. If the number after limit is a positive number, the first n records will be selected. On the contrary, if it’s negative, the last n records will be selected. As shown in the picture, the value of v of row 2021.10.04 and 2021.10.06 in the same group are all 0.5. If I write 1 after limit, the record of row 2021.10.14 will be obtained. If I write -1 after limit, the record of row 2021.10.16 will be obtained.
