1

I have a table containing the four columns “order_book_id”, “date”, “Q”, and “revenue”. I want to perform the following operations:

  1. Group the table by “order_book_id” and “date”.

  2. Verify if the values of column "Q" are equal to 1 within each group (the values are same within each group)

  • For groups where the condition is met, return the latest row.

  • For groups where the condition is not met, calculate the difference between the two latest rows.

Here is my script:

result = select date, order_book_id, iif(Q == 1, revenue, deltas(revenue)) as revenue from tmp context by date, order_book_id order by order_book_id asc limit -1;

Currently, I'm using iif(Q == 1, revenue, deltas(revenue)) to achieve the desired logic. To calculate the differences for multiple columns separately, the iff condition must be determined for each column individually.

Is there a more efficient function or method in DolphinDB? Thanks in advance.

Smalllpotato
  • 241
  • 4

1 Answers1

1

You can refer to the following scripts:

order_book_id =  `a`b`a`b`c`a`c`a`b
date = take(2022.01.01, 9)
Q = 1 0 1 0 1 1 1 1 0
revenue = 1 3 2 5 9 2 1 6 2
t = table(order_book_id, date, Q, revenue)
defg f(revenue){ return last(revenue) - last(prev(revenue))}
// or defg f(revenue){ return last(deltas(revenue))} 
// or defg f(revenue){ return revenue[revenue.size()-1] - revenue[revenue.size()-2] }

select iif(last(Q)==1, last(revenue), f(revenue)) from t group by date, order_book_id

To deal with multiple columns, you can try metaprogramming:

order_book_id =  `a`b`a`b`c`a`c`a`b
date = take(2022.01.01, 9)
Q = 1 0 1 0 1 1 1 1 0
revenue = 1 3 2 5 9 2 1 6 2
revenue1 = 1 3 2 5 9 2 1 6 2
revenue2 = 1 3 2 5 9 2 1 6 2
t = table(order_book_id, date, Q, revenue, revenue1, revenue2)

defg f(Q,x): iif(last(Q)==1, last(x), last(x)-last(prev(x)))
col_list = [`revenue, `revenue1, `revenue2]
sql(select=sqlColAlias(eachRight(makeCall{f}, sqlCol(`Q), sqlCol(col_list)), `a`b`c), from=t, groupBy=sqlCol([`date, `order_book_id]), orderBy=sqlCol(`order_book_id)).eval()
Shena
  • 341
  • 1
  • 5