计算mysql表中的值的百分比，并将百分比值写入另一个表中

debugcn 发表于 Dev

维维克·兰耶卡（Vivek Lanjekar）

嗨，我的SQL查询中的iam noob。以下是我的数据集

+------------+---------+-----------+--+------+--------+
| day_id     | hour_id | user_type |id|Charge| mobile |
+------------+---------+-----------+--+------+--------+
| 2015-07-17 |      20 | Overall   |1 |   30 |     60 |
| 2015-07-17 |      20 | Overall   |2 |  100 |    100 |
| 2015-07-17 |      20 | new       |1 |   20 |     30 |
| 2015-07-17 |      20 | new       |2 |  100 |     60 |
| 2015-07-17 |      21 | Overall   |1 |   10 |     20 |
| 2015-07-17 |      21 | Overall   |2 |    1 |      1 |
| 2015-07-17 |      21 | New       |1 |   10 |     50 |
| 2015-07-17 |      21 | New       |2 |    1 |      2 |
| 2015-07-16 |      20 | Overall   |1 |   60 |     30 |
| 2015-07-16 |      20 | Overall   |2 |   50 |    200 |
| 2015-07-16 |      20 | New       |1 |   40 |     60 |
| 2015-07-16 |      20 | New       |2 |  140 |     30 |
| 2015-07-16 |      21 | Overall   |1 |   14 |     40 |
| 2015-07-16 |      21 | Overall   |2 |    2 |      1 |
| 2015-07-16 |      21 | New       |1 |   14 |    100 |
| 2015-07-16 |      21 | New       |2 |    1 |      4 |
| 2015-07-15 |      20 | Overall   |1 |   60 |     60 |
| 2015-07-15 |      20 | Overall   |2 |   40 |     70 |
+------------+---------+-----------+--+------+--------+

现在我需要在shell中编写一个查询，或者可能只是一个sql查询，以便我可以计算出值（列收费和移动）前2天相比值（列收费和移动）的百分比变化

即“费用”和“移动”列中值的变化百分比，其中day_id =今天，即2015-07-17，user_type =总体，id = 0到1天的旧值，即“费用”列中的2015-07-17 “和” mobile“，其中user_type =总体，id = 0，百分比变化应这样写在不同的表中

+------------+---------+-----------+--+------+--------+
| day_id     | hour_id | user_type |id|Charge| mobile |
| Today      |         |           |  |Change|Change  |
+------------+---------+-----------+--+------+--------+
| 2015-07-17 |      20 | Overall   |1 | -100 |    100 |
| 2015-07-17 |      20 | Overall   |2 |  100 |   -100 |
| 2015-07-17 |      20 | new       |1 | -100 |      0 |
| 2015-07-17 |      20 | new       |2 |   40 |    100 |
| 2015-07-17 |      21 | Overall   |1 |  -40 |   -100 |
| 2015-07-17 |      21 | Overall   |2 | -100 |      0 |
| 2015-07-17 |      21 | New       |1 |  -40 |   -100 |
| 2015-07-17 |      21 | New       |2 |    0 |   -100 |

巴尔玛

您可以通过自我加入来执行此操作：

CREATE TABLE newTable
SELECT t1.day_id, t1.hour_id, t1.user_type, t1.id, 
        (t1.Charge-t2.Charge)/t2.Charge*100 AS Charge_Change,
        (t1.mobile-t2.mobile)/t2.mobile*100 AS mobile_Change
FROM yourTable AS t1
JOIN yourTable AS t2 ON t1.day_id = DATE_ADD(t2.day_id, INTERVAL 2 DAY)
    AND t1.hour_id = t2.hour_id AND t1.user_type = t2.user_type
    AND t1.id = t2.id

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。