英文:
DolphinDB: How to calculate the difference between two rows in a table?
问题
我有一个包含四列“order_book_id”、“date”、“Q”和“revenue”的表格。我想执行以下操作：
-
按“order_book_id”和“date”对表格进行分组。
-
验证每个分组中“Q”列的值是否都等于1（每个分组内的值相同）。
-
对于满足条件的分组，返回最新的行。
-
对于不满足条件的分组，计算最新两行之间的差值。
以下是我的脚本：
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;
目前，我正在使用iif(Q == 1, revenue, deltas(revenue))来实现所需的逻辑。为了分别计算多列的差异，必须为每列单独确定iif条件。
在DolphinDB中是否有更高效的函数或方法？提前感谢。
英文:
I have a table containing the four columns “order_book_id”, “date”, “Q”, and “revenue”. I want to perform the following operations:
-
Group the table by “order_book_id” and “date”.
-
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.
答案1
得分: 1
你可以参考以下脚本：
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))}
// 或者 defg f(revenue){ return last(deltas(revenue))}
// 或者 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
要处理多列，你可以尝试元编程：
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()
英文:
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()
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论