mysql - 如何使用窗口函数计算 Mysql 中的回撤？

Question

我有这个架构：

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `amount` int(11) DEFAULT NULL,
  `group_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

我已经用以下数据填充了该表：

insert into test (amount, group_id) values
(1,1), (3,1), (-4,1), (-2,1), (5,1), (10,1), (18,1), (-3,1), 
(-5,1), (-7,1), (12,1), (-9,1), (6,1), (0,1), (185,2), (-150,2) 

表格是：

# id, amount, group_id
1, 1, 1
2, 3, 1
3, -4, 1
4, -2, 1
5, 5, 1
6, 10, 1
7, 18, 1
8, -3, 1
9, -5, 1
10, -7, 1
11, 12, 1
12, -9, 1
13, 6, 1
14, 0, 1
15, 185, 2
16, -150, 2

这是我现在使用的查询：

SELECT 
    t1.id, 
    t1.amount, 
    t1.cumsum, 
    (MAX(t1.cumsum) OVER (ORDER BY id RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) - t1.cumsum) as drawdown    
FROM 
(
    SELECT 
        id,
        amount,
        group_id,
        SUM(amount) OVER (ORDER BY id RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as cumsum        
    FROM 
        test
) as t1
order by drawdown desc

查询返回此输出：

id, amount, cumsum, drawdown
10  -7  16  15
12  -9  19  12
9   -5  23  8
4   -2  -2  6
14  0   25  6
13  6   25  6
3   -4  0   4
8   -3  28  3
11  12  28  3
5   5   3   1
16  150 360 0
2   3   4   0
7   18  31  0
15  185 210 0
1   1   1   0
6   10  13  0

快速澄清一下：“回撤”一词（在这种情况下）是指值（字段）的最大累积和与简单累积和（累积和）的差异。

我不是指任何特定的交易定义，我只需要区分最大选择和最低选择。（显然，最高选择必须出现在最低 = 下降趋势之前）。

好的，我的问题是我需要按字段分组group_id，如果我在查询中添加 group by 子句，所有的 windows 功能都会搞砸。

预期结果：

我需要得到一个按 group_id 字段分组的列表，显示每个组的最大回撤。

编辑：（@Akina）

group_id, drawdown
1    15
2    150

标签： mysqldatabasemariadbrdbms