我有一些sql如下所示:
SELECT
stageName,
count(*) as `count`
FROM x2production.contact_stages
WHERE FROM_UNIXTIME(createDate) between '2016-05-01' AND DATE_ADD('2016-08-31', INTERVAL 1 DAY)
AND (stageName = 'DI-Whatever' OR stageName = 'DI-Quote' or stageName = 'DI-Meeting')
Group by stageName
Order by field(stageName, 'DI-Quote', 'DI-Meeting', 'DI-Whatever')
这将生成一个看起来像:
+-------------+-------+
| stageName | count |
+-------------+-------+
| DI-quote | 1230 |
| DI-Meeting | 985 |
| DI-Whatever | 325 |
+-------------+-------+
问题:
我想要从一行到下一行的百分比。例如,DI会议对DI报价的百分比。计算结果是100*985/1230=80.0%
最后这张桌子看起来是这样的:
+-------------+-------+------+
| stageName | count | perc |
+-------------+-------+------+
| DI-quote | 1230 | 0 |
| DI-Meeting | 985 | 80.0 |
| DI-Whatever | 325 | 32.9 |
+-------------+-------+------+
在mysql中有什么方法可以做到这一点吗?
这里有一个SQL fiddle来处理数据:http://sqlfiddle.com/#!9/61398/1
最佳答案
询问
select stageName,count,if(rownum=1,0,round(count/toDivideBy*100,3)) as percent
from
( select stageName,count,greatest(@rn:=@rn+1,0) as rownum,
coalesce(if(@rn=1,count,@prev),null) as toDivideBy,
@prev:=count as dummy2
from
( SELECT
stageName,
count(*) as `count`
FROM Table1
WHERE FROM_UNIXTIME(createDate) between '2016-05-01' AND DATE_ADD('2016-08-31', INTERVAL 1 DAY)
AND (stageName = 'DI-Underwriting' OR stageName = 'DI-Quote' or stageName = 'DI-Meeting')
Group by stageName
Order by field(stageName, 'DI-Quote', 'DI-Meeting', 'DI-Underwriting')
) xDerived1
cross join (select @rn:=0,@prev:=-1) as xParams1
) xDerived2;
结果
+-----------------+-------+---------+
| stageName | count | percent |
+-----------------+-------+---------+
| DI-Quote | 16 | 0 |
| DI-Meeting | 13 | 81.250 |
| DI-Underwriting | 4 | 30.769 |
+-----------------+-------+---------+
注意,您希望第一行的百分比为0。很容易改成100。
cross join
引入要使用的变量并初始化它们。greatest
和coalesce
用于变量使用的安全性,正如本文article中所述,以及MySQL手册页Operator Precedence中的提示。派生表的名称就是:每个派生表都需要一个名称。如果不遵守这些参考文章中的原则,那么使用变量是不安全的。我不是说我搞定了,但安全始终是我的重点。
变量的赋值需要遵循一种安全的形式,比如在函数内部设置的
@rn
变量,比如greatest
或least
。我们知道@rn
总是大于0。所以我们使用greatest
函数来强制查询。与coalesce
相同的技巧,空永远不会发生,:=
在它后面的列中的优先级更低。也就是说,最后一个:@prev:=
在coalesce
之后。这样,在选择行中的其他列尝试使用其值之前设置变量。
所以,仅仅得到预期的结果并不意味着你做的安全,它将与你的真实数据。