Given
drop table if exists t;
create table t(dt date,cola int,colb int);
insert into t values
(str_to_date('29/10/2020','%d/%m/%Y'),147,150),
(str_to_date('30/10/2020','%d/%m/%Y'),160,170),
(str_to_date('01/11/2020','%d/%m/%Y'),170,170),
(str_to_date('02/11/2020','%d/%m/%Y'),180,170),
(str_to_date('03/11/2020','%d/%m/%Y'),190,170);
You could use correlated sub queries to get the totals from the current row to the end of column
select t.*,
(select sum(cola) from t t1 where t1.dt >=t.dt) sumcola,
(select sum(colb) from t t1 where t1.dt >=t.dt) sumcolb,
(select sum(colb) from t t1 where t1.dt >=t.dt) /
(select sum(cola) from t t1 where t1.dt >=t.dt) kpi
from t;
Or if you have version 8 or above
select t.*,
sum(cola) over (order by dt rows between current row and unbounded following) sumcola,
sum(colb) over (order by dt rows between current row and unbounded following) sumcolb,
sum(colb) over (order by dt rows between current row and unbounded following) /
sum(cola) over (order by dt rows between current row and unbounded following) kpi;
from t;
+------------+------+------+---------+---------+--------+
| dt | cola | colb | sumcola | sumcolb | kpi |
+------------+------+------+---------+---------+--------+
| 2020-10-29 | 147 | 150 | 847 | 830 | 0.9799 |
| 2020-10-30 | 160 | 170 | 700 | 680 | 0.9714 |
| 2020-11-01 | 170 | 170 | 540 | 510 | 0.9444 |
| 2020-11-02 | 180 | 170 | 370 | 340 | 0.9189 |
| 2020-11-03 | 190 | 170 | 190 | 170 | 0.8947 |
+------------+------+------+---------+---------+--------+
5 rows in set (0.001 sec)
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…