MySQL查询以获取总百分比变化 [英] MySQL query to get total percentage change

问题描述

如何在MySQL中添加百分比变化列(不是百分比点)?/p>

有一个表格，其中列出了百分比变化列:

  + --------- +|百分比+ --------- +|-0.50 ||0.50 ||1.00 ||-0.20 ||0.50 ||-1.00 ||-2.00 ||0.75 ||1.00 ||0.50 |+ --------- + 

如何编写查询来计算每一行的值的总百分比变化，以便计算出的行表示其百分比变化以及所有以前的百分比变化行?

预期结果:

  + --------- + --------------- + --------------- +|百分比标称值|total_percent |+ --------- + --------------- + --------------- +|-0.50 |0.50 |-0.50 ||0.50 |0.75 |-0.25 ||1.00 |1.50 |0.50 ||-0.20 |1.20 |0.20 ||0.50 |1.80 |0.80 ||-1.00 |0.00 |-1.00 ||-2.00 |-2.00 |-3.00 ||0.75 |-0.50 |-1.50 ||1.00 |0.00 |-1.00 ||0.50 |0.50 |-0.50 |+ --------- + --------------- + --------------- + 

其中 nominal_value 是一个任意值，已被 percent 更改，因此对于第一行，如果标称值为1.0(100％)但已被 -0.50 ( -50％)，导致标称值为 0.5 .

然后在第二行 percent 更改为 +0.50 ( +50％)，因此标称值增加了一半<代码> 0.5 =>0.75 ，但也可以说，从 1.0 -0.25 ( -25％)>到 0.75 是 1.0 的 -0.25 ( -25％).

这正是我改变 total_percent 之后的结果， nominal_value 仅用于说明目的，不需要.

我正在使用MySQL 8，因此查询可能使用窗口函数/范围等.

这是要复制的测试表:

 如果不存在则创建表测试(百分比DECIMAL(5,2)NOT NULL)引擎= InnoDB;插入测试值(百分比)(-0.50)，(0.50)，(1.00)，(-0.20)，(0.50)，(-1.0)，(-2.0)，(0.75)，(1.0)，(0.50); 

解决方案

  DROP TABLE IF EXISTS测试；创建表测试(id串行主键，百分比DECIMAL(5,2)NOT NULL);插入测试值(百分比)(-0.5)，(0.5)，(1)，(-0.2)，(0.5)，(-1);SELECT ROUND(@i:=(@ i +(@ i * percent))，2)n从测试，(SELECT @i:= 1)vars命令按编号；+ ------ +|n |+ ------ +|0.50 ||0.75 ||1.50 ||1.20 ||1.80 ||0.00 |+ ------ +设置6行(0.00秒)mysql> 

How to add a column of percent change (not percentage points) in MySQL?

there is a table with column of changes in percents:

+---------+
| percent |
+---------+
|   -0.50 |
|    0.50 |
|    1.00 |
|   -0.20 |
|    0.50 |
|   -1.00 |
|   -2.00 |
|    0.75 |
|    1.00 |
|    0.50 |
+---------+

How to write a query that calculates a total percent change of a value for each row so the calculated row expresses its percentage change and all previous rows of percentage change?.

expected result:

+---------+---------------+---------------+
| percent | nominal_value | total_percent |
+---------+---------------+---------------+
|   -0.50 |          0.50 |         -0.50 |
|    0.50 |          0.75 |         -0.25 |
|    1.00 |          1.50 |          0.50 |
|   -0.20 |          1.20 |          0.20 |
|    0.50 |          1.80 |          0.80 |
|   -1.00 |          0.00 |         -1.00 |
|   -2.00 |         -2.00 |         -3.00 |
|    0.75 |         -0.50 |         -1.50 |
|    1.00 |          0.00 |         -1.00 |
|    0.50 |          0.50 |         -0.50 |
+---------+---------------+---------------+

Where the nominal_value is an arbitrary value that was changed by percent so for the first row if the nominal value was 1.0 (100%) but was changed by -0.50 (-50%) it resulted in nominal value 0.5 .

Then at the second row percent change was +0.50 (+50%) so the nominal value was increased by half of it 0.5 => 0.75 but one can also say that it was just lowered by -0.25 (-25%) from its original value since from 1.0 to 0.75 is a -0.25 (-25%) of 1.0.

That's exactly what I'm after a total_percent change, the nominal_value was just for the explanatory purpose and is not needed.

I'm using MySQL 8 so the query may use window functions / ranges etc.

here is the test table to replicate:

CREATE TABLE IF NOT EXISTS test
(
    percent DECIMAL(5,2) NOT NULL
)
ENGINE = InnoDB
;

INSERT INTO test (percent) VALUES 
(-0.50)
,(0.50)
,(1.00)
,(-0.20)
,(0.50)
,(-1.0)
,(-2.0)
,(0.75)
,(1.0)
,(0.50)
;

解决方案

DROP TABLE IF EXISTS test;

CREATE TABLE test
( id SERIAL PRIMARY KEY
, percent DECIMAL(5,2) NOT NULL
);

INSERT INTO test (percent) VALUES 
(-0.5)
,(0.5)
,(1)
,(-0.2)
,(0.5)
,(-1)
;

SELECT ROUND(@i:=(@i+(@i*percent)),2)n 
  FROM test
     , (SELECT @i:=1) vars 
 ORDER 
    BY id;
+------+
| n    |
+------+
| 0.50 |
| 0.75 |
| 1.50 |
| 1.20 |
| 1.80 |
| 0.00 |
+------+
6 rows in set (0.00 sec)

mysql>

这篇关于MySQL查询以获取总百分比变化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！