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

历史最高与回撤

中等 16-window-cumulative 窗口:累计与滑动 cummaxdrawdownNone

计算每只股票截至每个交易日的历史最高收盘价 peak,以及当天相对历史最高的回撤 dd = close / peak - 1(为 0 或负数)。

  • 每只股票按 date 排序;peak 是从第一天到当天(含)所有非 null close 的最大值。
  • 当天 close 为 null 时:peak 仍然是之前的最大值(不是 null),dd 为 null。
  • 截至当天还没有出现过任何非 null 收盘价时,peak 和 dd 都为 null。
  • 表里的行不保证有序。

输入

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

输出

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

输入表 (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,行顺序不限)

#tickerStringdateDatecloseFloat64peakFloat64ddFloat64
1 BABA 2026-06-01 85.21 85.21 0.0
2 BABA 2026-06-02 86.31 86.31 0.0
3 BABA 2026-06-03 86.96 86.96 0.0
4 BABA 2026-06-04 85.91 86.96 -0.01207451702
5 BABA 2026-06-05 84.47 86.96 -0.02863385465
6 BABA 2026-06-08 84.07 86.96 -0.03323367065
7 BABA 2026-06-09 83.76 86.96 -0.03679852806
8 BABA 2026-06-10 82.42 86.96 -0.05220791168
9 BABA 2026-06-11 82.69 86.96 -0.04910303588
10 BABA 2026-06-12 83.75 86.96 -0.03691352346
11 BABA 2026-06-15 85.24 86.96 -0.01977920883
12 BABA 2026-06-16 86.63 86.96 -0.003794848206
13 BABA 2026-06-17 87.02 87.02 0.0
14 BABA 2026-06-18 86.37 87.02 -0.007469547231
15 BABA 2026-06-19 86.24 87.02 -0.008963456677
16 BABA 2026-06-22 85.47 87.02 -0.01781199724
17 BABA 2026-06-23 84.94 87.02 -0.02390255114
18 BABA 2026-06-24 85.25 87.02 -0.02034015169
19 BABA 2026-06-25 86.29 87.02 -0.00838887612
20 BABA 2026-06-26 84.6 87.02 -0.02780969892
21 BABA 2026-06-29 85.41 87.02 -0.01850149391
22 BABA 2026-06-30 87.63 87.63 0.0
23 BABA 2026-07-01 87.71 87.71 0.0
24 BABA 2026-07-02 88.69 88.69 0.0
25 BABA 2026-07-06 87.85 88.69 -0.009471191792
26 BABA 2026-07-09 89.43 89.43 0.0
27 BABA 2026-07-10 90.77 90.77 0.0
28 BABA 2026-07-13 91.53 91.53 0.0
29 BABA 2026-07-14 92.09 92.09 0.0
30 BABA 2026-07-15 92.49 92.49 0.0
31 BABA 2026-07-16 93.03 93.03 0.0
32 BABA 2026-07-17 96.03 96.03 0.0
33 BABA 2026-07-20 96.29 96.29 0.0
34 BABA 2026-07-21 96.85 96.85 0.0
35 BABA 2026-07-22 99.0 99.0 0.0
36 BABA 2026-07-23 98.41 99.0 -0.00595959596
37 BABA 2026-07-24 97.07 99.0 -0.01949494949
38 BABA 2026-07-27 99.1 99.1 0.0
39 BABA 2026-07-28 101.29 101.29 0.0
40 BABA 2026-07-29 100.92 101.29 -0.003652877875
41 BABA 2026-07-30 101.64 101.64 0.0
42 BABA 2026-07-31 99.73 101.64 -0.01879181425
43 BABA 2026-08-03 99.38 101.64 -0.02223534042
44 BABA 2026-08-04 99.92 101.64 -0.01692247147
45 BABA 2026-08-05 102.0 102.0 0.0
46 BABA 2026-08-06 104.65 104.65 0.0
47 BABA 2026-08-07 106.36 106.36 0.0
48 BABA 2026-08-10 106.22 106.36 -0.001316284317
49 BABA 2026-08-11 105.14 106.36 -0.01147047762
50 BABA 2026-08-12 104.26 106.36 -0.01974426476
显示前 50 行,共 296 行