SQL按照数值列从小到大排序并判断是否在前60%

背景

工作中遇到这样一个需求：在明细表中按分组字段分组对数值字段求和并从大到小排序，判断每一组是否站在总数的前60%。

思路

先分组求和并按聚合结果降序排序，对结果使用子查询和窗口函数进行累加求和，前60%的记录累加值<记录总和*0.6。

代码

select 
	tmp.<group_field>,
	num '数量',
	office_num '涉及公司',
		case when cum_sum<=(select count(*)*0.6
							from 
								tableName
							where <where_contation>) then '是'
			else '否'
		end as '是否前60%',
from
(select
	*,sum(num) over(order by num desc) cum_sum
from
(select
	<group_field>,
	count(*) num,
	count(distinct office_id) office_num
from 
	tableName
where <where_contation>
group by <group_field>
order by num desc)t
)tmp