Suppose I have a table with simulated data as follows:
time = [09:00:00, 09:00:01, 09:00:02, 09:00:03, 09:00:04, 09:00:05, 09:00:06, 09:00:07, 09:00:08]
b = [101, 103, 105, 110, 129, 134, 123, 111, 120]
a = [100, 100, 100, 200, 200, 300, 200, 200, 100]
t = table(time, b, a)
// t:
time b a
-------- --- ---
09:00:00 101 100
09:00:01 103 100
09:00:02 105 100
09:00:03 110 200
09:00:04 129 200
09:00:05 134 300
09:00:06 123 200
09:00:07 111 200
09:00:08 120 100
I want to use the changes in the a column as grouping markers.
For example, 100 100 100 as the first group, 200 200 as the second group, 300 as the third group, 200 200 as the fourth group, and 100 as the fifth group.
Within each group, I want to calculate the cumulative minimum of the b column.
What I expect:
time b a cummin_b
-------- --- --- ---------
09:00:00 101 100 101
09:00:01 103 100 101
09:00:02 105 100 101
09:00:03 110 200 110
09:00:04 129 200 110
09:00:05 134 300 134
09:00:06 123 200 123
09:00:07 111 200 111
09:00:08 120 100 120
How should I write the DolphinDB SQL script to achieve this function?
It looks like a perfect case for a segment window:
select time, b, a, cummin(b) as cummin_b from t context by segment(a);