一、前言
这篇博客的标题不大好起,所以下面先具体说下需求吧。
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
不是博主需要的,这里贴给大家看看。
二、具体实现
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