sql计算占比_bitcarmanlee的博客

link之家
链接快照平台
输入网页链接，自动生成快照
标签化管理网页链接
1.数据准备

现在有一张表，表里有两个字段，op_name与state，都为离散型可枚举数据，除此以外表里还有其他字段。数据形式如下
op_name	state	c1	c2
n1	s1	...
n2 	s2	...
n1	s3	...
n3	s2	...
2.计算某个字段各枚举值占比
 
以上面的数据表为，我们想计算state字段各状态占比，可以有如下写法 
SELECT state, count(*) as state_num, round(
count(*) / SELECT count(*) from xxx , 5) as percentage_of_state
from xxx
group by state
with rollup
order by percentage_of_state DESC
limit 200;
最终实际业务表上返回结果为 
null	67264089	1
s1	37465212	0.55699
s2	22699606	0.33747
s3	6953280	0.10337
s4	124627	0.00185
s5	15030	0.00022
s6	6334	0.00009
因为要计算占比，肯定需要计算总数。在上面的代码中，我们使用了一个子查询，先计算出数据总量，然后根据state字段再分组，计算各state状态值的占比。 
3.更复杂的分组占比
 
上面求的占比比较简单，只跟state字段有关。
 如果我们提升一下难度，想计算每个op_name中state占比该如何处理？
 更具像一点就是：
 假设op_name字段有一个值为n1，n1对应的state有三种状态s1, s2, s3，现在想计算出在所有n1中，s1, s2, s3的占比。 
可以按照如下思路实现 
首先，要计算op_name中state占比，肯定需要先对op_name进行分组，计算各op_name的总量。
 其次，还需要对op_name,state进行联合分组，这样才能得到占比中的分子。
 最后，分子分母都计算出来了，根据op_name进行join，就可以计算出占比。 
SELECT a.op_name, a.state, a.state_count, a.state_count/op_count from
(SELECT op_name, state, count(*) as state_count  from xxx
group by op_name, state)a
(SELECT op_name， count(*) as op_count from xxx
group by op_name)b
on a.op_name = b.op_name
order by op_name desc
limit 20;
将上述代码在实际业务表中运行，计算得出的结果如下。 
n1	s1	5738	0.94468225
n1	s2	308	0.05070794
n1	s3	28	0.00460981
n2	s1	208	0.28108108
n2	s2	170	0.22972973
n2	s3	362	0.48918919
4.使用分组函数计算占比
 
上面join的方式可以计算出结果，但是代码显得稍微麻烦了点。有没有更简洁的方式？
 答案是有的，我们可以使用窗口函数达到同样的效果。 
首先我们先简单看下窗口函数的定义与语法 
OVER用于为行定义一个窗口，它对一组值进行操作，不需要使用GROUP BY子句对数据进行分组，能够在同一行中同时返回基础行的列和聚合列。 
OVER ( [ PARTITION BY column ] [ ORDER BY culumn ] )
其中，PARTITION BY 用来对子句进行分组，而order by则是对子句进行排序使用。 
OVER()指定了分组的行，不使用group by就可以达到对数据分组的目的，还可以返回基础列与聚合列。 
OVER窗口函数必须与聚合函数或者排序函数一起使用。聚合函数比如SUM, MAX, MIN, AVG,COUNT等，而排序函数有RANK, ROW_NUMBER，NTILE等。 
回到上面的需求，我们可以有如下解法 
SELECT op_name, state, state_count, state_count / sum(state_count) over(PARTITION BY op_name) as ratio from
(SELECT op_name, state, count(*) as state_count  from xxx
group by op_name, state)a
order by op_name desc
limit 20;
上面用一个子查询就解决了前面的问题。子查询对op_name, state进行了联合分组，然后窗口函数中对op_name进行分组并对state_count进行求和操作，就得到了op_name的总数，即计算占比的分母。 
最后的结果，与前面的join结果一样。 
n1	s1	5738	0.94468225
n1	s2	308	0.05070794
n1	s3	28	0.00460981
n2	s1	208	0.28108108
n2	s2	170	0.22972973
n2	s3	362	0.48918919
				在日常的工作项目中，会需要获取占比的计算。
我们往往会选择嵌套再一次查询去计算占比值，但是如果我们需要配置起来，实现通用性，对于我们项目加大了难度。
为此，我考虑不以嵌套的形式，一条sql直接输出我们想要的占比，只需改变字段名称，就可直接获取占比值，一并返回。
  --只针对mysql8.0以上版本，其余数据库正常使用
  select id ,
       count(1)                   as count,
     concat(round(cast(count(1) /
				可以用下面的SQL语句来统计一列特定值的百分比：
SELECT 列名, ROUND(COUNT() * 100.0 / SUM(COUNT()) OVER (), 2) AS 百分比
FROM 表名
GROUP BY 列名;
	DROP FUNCTION IF EXISTS calculateWorkingTime;
	set @workStartTime='09:30:00';
	set @workEndTime='18:30:00';
	— 创建函数
	CREATE FUNCTION calculateWorkingTime(startDate datetime，endDate datetime)
	RETURNS decimal(32，4)
	BEGIN
	DECLARE intnum int(255) DEFAULT 0;