关于表两行作差的问题
请教大家！
需求是：有一张表，分组后（分组后的结果可能有多行），判断特定值是否满足某个条件（每组中特定值都相等），如果满足就返回最新一行，不满足就最新两行作差。请问有效率比较高的写法吗？
现在用的是 `iif` 函数，但是如果要对所有字段进行操作的话，就需要每个字段都比较一次，请问有行作差的函数吗？
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;
最佳答案 2023-07-26 17:04
我尝试用 group by 实现，行做差的逻辑我定义了一个自定义函数：
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()
