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

ゝ一纸荒年。 2022-12-15 14:16 253阅读 0赞

## 一、前言 ##

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

### 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%的行][_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**

[_80]: https://www.cnblogs.com/yangyxd/p/5146748.html