df-drills pandas · Polars · DuckDB SQL · DuckDB Relational 题目列表 登录

日收益率

简单 15-window-offset 窗口:位移 pct-changelagnull-propagation

计算每只股票的日收益率:ret = close / 前一交易日的 close - 1。

  • “前一交易日”指同一 ticker 按 date 排序后的上一行。
  • 每只股票的第一行 ret 为 null。
  • 不能跳过 null:某一天 close 为 null 时,这一天的 ret 为 null,下一天的 ret 也为 null(它的前一交易日收盘价是 null);不要用更早的收盘价代替,也不要把 null 当作 0。
  • 表里的行不保证有序。

输入

  • prices(ticker, date, open, high, low, close, volume)

输出

列为 ticker, date, ret,每行输入对应一行输出,行顺序不限。

输入表 (case base,共 2 个 case:base、edge)

prices stocks/prices
#tickerstringdatedateopenfloat64highfloat64lowfloat64closefloat64volumeint64
1 AAPL 2026-06-01 189.58 190.97 188.93 190.66 7374313
2 AAPL 2026-06-02 191.71 195.6 191.42 194.12 1753601
3 AAPL 2026-06-03 194.95 196.13 193.04 193.67 2817746
4 AAPL 2026-06-04 194.03 194.88 187.39 189.08 2783341
5 AAPL 2026-06-05 189.72 193.66 189.02 193.14 8942829
6 AAPL 2026-06-08 192.9 193.62 188.21 188.64 1357786
7 AAPL 2026-06-09 188.63 189.8 185.9 188.98 5482117
8 AAPL 2026-06-10 189.78 191.79 188.78 191.45 1430484
9 AAPL 2026-06-11 191.12 193.18 190.85 192.68 7642427
10 AAPL 2026-06-12 191.04 193.87 190.02 193.37 2476587
11 AAPL 2026-06-15 193.53 195.45 193.24 193.68 1680820
12 AAPL 2026-06-16 194.69 195.53 192.04 193.09 4196575
13 AAPL 2026-06-17 192.64 196.25 191.03 195.73 1464728
14 AAPL 2026-06-18 195.19 196.94 192.23 192.74 2059908
15 AAPL 2026-06-19 192.02 196.01 191.78 195.55 8247165
16 AAPL 2026-06-22 195.28 196.64 188.45 null 2043265
17 AAPL 2026-06-23 190.64 191.82 190.11 191.55 2220638
18 AAPL 2026-06-24 192.95 193.43 189.2 190.21 2962146
19 AAPL 2026-06-25 190.43 190.7 188.71 189.26 1607246
20 AAPL 2026-06-26 189.15 189.58 188.4 189.15 4162289
显示前 20 行,共 296 行

期望输出 (case base,行顺序不限)

#tickerStringdateDateretFloat64
1 BABA 2026-06-01 null
2 BABA 2026-06-02 0.01290928295
3 BABA 2026-06-03 0.007530992932
4 BABA 2026-06-04 -0.01207451702
5 BABA 2026-06-05 -0.01676172739
6 BABA 2026-06-08 -0.004735409021
7 BABA 2026-06-09 -0.003687403354
8 BABA 2026-06-10 -0.01599808978
9 BABA 2026-06-11 0.003275903907
10 BABA 2026-06-12 0.01281896239
11 BABA 2026-06-15 0.01779104478
12 BABA 2026-06-16 0.01630689817
13 BABA 2026-06-17 0.004501904652
14 BABA 2026-06-18 -0.007469547231
15 BABA 2026-06-19 -0.001505152252
16 BABA 2026-06-22 -0.008928571429
17 BABA 2026-06-23 -0.006201006201
18 BABA 2026-06-24 0.003649635036
19 BABA 2026-06-25 0.01219941349
20 BABA 2026-06-26 -0.01958511994
21 BABA 2026-06-29 0.009574468085
22 BABA 2026-06-30 0.02599227257
23 BABA 2026-07-01 0.0009129293621
24 BABA 2026-07-02 0.01117318436
25 BABA 2026-07-06 -0.009471191792
26 BABA 2026-07-09 0.01798520205
27 BABA 2026-07-10 0.0149837862
28 BABA 2026-07-13 0.0083728104
29 BABA 2026-07-14 0.006118212608
30 BABA 2026-07-15 0.004343576936
31 BABA 2026-07-16 0.005838469024
32 BABA 2026-07-17 0.03224766204
33 BABA 2026-07-20 0.002707487244
34 BABA 2026-07-21 0.005815764877
35 BABA 2026-07-22 0.02219927723
36 BABA 2026-07-23 -0.00595959596
37 BABA 2026-07-24 -0.01361650239
38 BABA 2026-07-27 0.02091274338
39 BABA 2026-07-28 0.02209889001
40 BABA 2026-07-29 -0.003652877875
41 BABA 2026-07-30 0.007134363853
42 BABA 2026-07-31 -0.01879181425
43 BABA 2026-08-03 -0.003509475584
44 BABA 2026-08-04 0.005433688871
45 BABA 2026-08-05 0.02081665332
46 BABA 2026-08-06 0.02598039216
47 BABA 2026-08-07 0.01634018156
48 BABA 2026-08-10 -0.001316284317
49 BABA 2026-08-11 -0.01016757673
50 BABA 2026-08-12 -0.008369792657
显示前 50 行,共 296 行