mysql累加某字段到达临界值,获取另一个字段的值 ゝ一纸荒年。 2022-12-15 14:16 164阅读 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
还没有评论,来说两句吧...