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

查看:129
本文介绍了计算上一行值的增加/减少百分比的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

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

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屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆