mysql累加某字段到达临界值,获取另一个字段的值

一、前言

这篇博客的标题不大好起，所以下面先具体说下需求吧。

1、表结构

+----+-----------+-------------------+
| id | cost_rate | conversions_value |
+----+-----------+-------------------+
|  1 |      3.58 |             84.83 |
|  2 |      0.55 |             69.86 |
|  3 |     18.29 |             69.86 |
|  4 |      0.32 |             59.88 |
|  5 |      0.15 |             44.91 |
|  6 |      0.44 |             39.92 |
|  7 |      0.01 |             54.89 |
|  8 |      0.12 |             39.92 |
|  9 |      0.16 |             39.92 |
| 10 |      6.35 |             34.93 |
+----+-----------+-------------------+

      如图所示，表中有3个字段。我们的需求是：获取按照cost_rate倒序排列，然后累加conversion_value的值，直到某个临界点，比如累加的值达到所有conversion_value值的80%，然后获取此时的cost_rate的值。

2、需求分析

      按照需求，应该查拆分为两部分，一部分是获取累加值，另一部分是获取累加临界点的值对应的cost_rate。

      这里的难点是要累加conversions_value和上一行的conversions_value的值，并且要保证cost_rate不能乱，因此不能使用sum()这种。

3、类似的实现

      博主这边是需要一条sql搞定的，在不断的搜索过程中，发现了很符合需求的一篇文章，奈何里面的sql不是博主需要的，这里贴给大家看看。

[数据库] 取指定表中某字段的累加和不超过总和80%的行

二、具体实现

1、先累加conversions_value的值

$sql_1 = "
SELECT
    cost_rate,
    conversions_value,
    @total := @total + conversions_value AS 'total_value'
FROM
    (
       select id,cost_rate,conversions_value from google_uac_placement order by cost_rate desc
    ) AS temp,
    (SELECT @total := 0) AS T1
ORDER BY cost_rate DESC limit 10
";

//先按照cost_rate字段倒序排列
select id,cost_rate,conversions_value from google_uac_placement order by cost_rate desc
//定义变量，并给初始值
 SELECT @total := 0
//获取倒序排列的字段，并且此时定义变量@total，并给变量赋值。此处效果类似于a = a+1
 @total := @total + conversions_value AS 'total_value'

解释下这个sql:

（1）@x 是 用户自定义的变量 ，@@x 是 global或session变量。对应这里的定义变量@total ，用于存储计算的结果。

（2）:= 是变量赋值的作用 ，单独的 =号是判断等于的意思，@num=@num+1,此时=是等于的作用，@num不等于@num+1，所以始终返回0，如果改为@num=@num,始终返回1了。mysql数据库中，用1表示真，0表示假。

参考：

https://www.cnblogs.com/jpfss/p/9140622.html
https://www.cnblogs.com/qlqwjy/p/8470722.html
https://blog.csdn.net/SunFlowerXT/article/details/89470843

2、累加结果

+-----------+-------------------+--------------------+
| cost_rate | conversions_value | total_value        |
+-----------+-------------------+--------------------+
|   7944.72 |          18106.52 |           18106.52 |
|   4962.98 |           9671.83 |           27778.35 |
|   1888.20 |          12709.29 |           40487.64 |
|   1046.47 |           2860.04 |           43347.68 |
|   1043.52 |           4492.83 |           47840.51 |
|   1040.67 |           1145.31 |           48985.82 |
|    998.80 |           3544.98 |            52530.8 |
|    954.51 |           1270.47 | 53801.270000000004 |
|    925.95 |           2360.39 |           56161.66 |
|    748.98 |           1444.66 |  57606.32000000001 |
+-----------+-------------------+--------------------+

      这里可以看到我们这里total_value 就是累加后的值了，非常符合需求。

3、取累加临界点的值

      按照累加的结果再做一次筛选，此次筛选可以从累加的结果去查，就是from的子查询了，具体的sql如下：

SELECT cost_rate,total_value FROM
(
SELECT
    cost_rate,
    conversions_value,
    @total := @total + conversions_value AS 'total_value'
FROM
    (
       select id,cost_rate,conversions_value from google_uac_placement order by cost_rate desc
    ) AS temp,
    (SELECT @total := 0) AS T1
ORDER BY cost_rate DESC) AS b
WHERE total_value > 20000 limit 10;

";

解释：

（1）此处比着上面，只是把上面的结果作为一个子查询
（2）最后通过where条件来限制临界点，加入临界点是20000，那么我们就获取大于临界点20000的cost_rate的值

4、最终结果

+-----------+--------------------+
| cost_rate | total_value        |
+-----------+--------------------+
|   4962.98 |           27778.35 |
|   1888.20 |           40487.64 |
|   1046.47 |           43347.68 |
|   1043.52 |           47840.51 |
|   1040.67 |           48985.82 |
|    998.80 |            52530.8 |
|    954.51 | 53801.270000000004 |
|    925.95 |           56161.66 |
|    748.98 |  57606.32000000001 |
|    657.36 |  58576.43000000001 |
+-----------+--------------------+

      确实是获取到了累加值>20000的结果集，此时limit 1即可获取对应的cost_rate的值。

5、效率如何

294719 rows in set (0.77 sec)

      博主这里是一共将近30w的数据，查询效率挺快的，毕竟我们只是要取临界点的值。当然，此处的sql还可以继续加条件进行优化的，效率这块应该没什么问题。

三、总结

      通过这个需求，博主是第一次在sql中使用变量，以前都是一条普通的sql就搞定需求的，没想到竟然在sql中还能使用变量，这种写法可太新颖了。果然是学无止境，奥利给！

end