CODEWITHSUNDEEP
sql-updatehavingdolphindb
Kim
Kim

Reputation: 11

How to modify the first row after context by grouping in DolphinDB?

I’m trying to update the first row of each group after using context by in DolphinDB. Here is my current code:

update tt set ret = (close - open)/open context by instrument, tradingday having rowNo(ret) == 0

However, this does not work. How should I adjust this query to correctly update the first row within each group?

Additional Context:

The goal is to modify the first row (ordered by grouping keys) of each group. I suspect the having clause or rowNo() function might be misused here.

Upvotes: 1

Views: 31

Answers (1)

Landon
Landon

Reputation: 1

The UPDATE statement now supports using csort in the context by clause to specify the order. It also supports using HAVING to update only groups that meet the conditions of an aggregate function. Additionally, you can use HAVING with non-aggregate functions, or mix non-aggregate and aggregate functions to update groups that meet the specified conditions.

you can implement the following code:

update tt set ret = (close - open)/open where contextby(rowNo,instrument,[instrument,tradingday])=0

UPDATE tt SET ret = iif(rowNo(instrument) == 0, (close - open) / open, NULL) CONTEXT BY instrument, tradingday

Upvotes: 0

Related Questions