根据上一行的值获取百分比 [英] Get percentage based on value in previous row

问题描述

对于给定的数据:

+---+--------+------+
|CID| number | rum  |
+---+--------+------+
| 1 | 1.0000 | NULL |
| 3 | 2.0000 | NULL |
| 5 | 2.0000 | NULL |
| 6 | 4.0000 | NULL |
+---+--------+------+

我想用当前数字和前一个数字的百分比变化来计算朗姆酒.

I want to calculate rum with percentage change of current and previous number.

rum = (currNumber - prevNumber) / prevNumber * 100

预期结果:

+---+--------+------+
|CID| number | rum  |
+---+--------+------+
| 1 | 1.0000 | NULL |
| 3 | 2.0000 |100.0 |
| 5 | 2.0000 |  0.0 |
| 6 | 4.0000 |100.0 |
+---+--------+------+

LAG 函数在 MySQL 中不起作用.

LAG function doesn't work here in MySQL.

推荐答案

假设行是根据 CID 排序的，您可以使用相关子查询找到前一行:

Assuming the rows are ordered based on CID you can find previous row using correlated subquery:

SELECT CID, number, (
    SELECT (c.number - p.number) / p.number * 100
    FROM t AS p
    WHERE p.CID < c.CID
    ORDER BY p.CID DESC
    LIMIT 1
) AS rum
FROM t AS c

SQL 小提琴

这篇关于根据上一行的值获取百分比的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！