计算上一行值的增加/减少百分比 [英] Calculate percent increase/decrease from previous row value

问题描述

我有一个看起来像这样的表:

I have a table that looks something like this:

|date_start  | date_end    |amount | 
+------------+-------------+-------+
|2015-02-23  | 2015-03-01  |50     |
|2015-03-02  | 2015-03-08  |50     |
|2015-03-09  | 2015-03-15  |100    |
|2015-03-16  | 2015-03-22  |800    |
|2015-03-23  | 2015-03-29  |50     |

，我想算出前一天以来列amount的增加/减少百分比.例如，结果将是这样，

and I'd like to work out the percent increase/decrease for column amount, from the previous date. For example the result would be something like this,

|date_start  | date_end    |amount | perc_change | 
+------------+-------------+-------+-------------+
|2015-02-23  | 2015-03-01  |50     | 
|2015-03-02  | 2015-03-08  |50     | 0
|2015-03-09  | 2015-03-15  |100    | 50
|2015-03-16  | 2015-03-22  |800    | 700
|2015-03-23  | 2015-03-29  |50     | -750

我已经搜寻并绞尽脑汁了几天.通常，我只是使用服务器端代码来完成此操作，但是现在我需要将其全部包含在查询中.

I've searched and racked my brain for a couple of days now. Usually, I simply do this using server side code but now I need to contain it all within the query.

推荐答案

尝试一下:

 SELECT t.*,
 amount - (SELECT amount FROM transactions prev WHERE prev.date_end     < t.date_start ORDER BY date_start DESC LIMIT 1) AS changes
 FROM   transactions t

这篇关于计算上一行值的增加/减少百分比的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！