SQL中,根据数值字段动态分组查询

在SQL中,分组查询的字段一般都是离散的属性字段,比如组织,日期,客户,但如果要按照数值字段来分组,分段,很多同学就不知道如何写了
比如有下面的一张表,我需要 按照 cost 进行5等分,统计每个组的数量.

现在要统计按照收入来分段统计汇总,一把的写法是用case when ,这种写法可以实现需求,但必须事先写死分组的数量
比如:
with a as
(
select (max(total_cost)-min(total_cost))/5 cost_per, min(total_cost) cost_min , max(total_cost) cost_max from T_SR_TANGENT_POINT_COST
)
select
case
when (b.total_cost < a.cost_min+cost_per) then
1
when b.total_cost >= a.cost_min+cost_per and (b.total_cost < a.cost_min+2cost_per) then
2
when b.total_cost >= a.cost_min+2cost_per and (b.total_cost < a.cost_min+3cost_per) then
3
when b.total_cost >= a.cost_min+3cost_per and (b.total_cost < a.cost_min+4cost_per) then
4
when b.total_cost >= a.cost_min+4cost_per then
5
end
cost_level,
b.*
from T_SR_TANGENT_POINT_COST b,a

上网百度了一下,基本都是上面这种写法, 但我们的需求是需要动态分组, 这时候,用case when 就无法解决了. 可以采用下面的写法

with lev as
(
select id,a.cost_min+a.cost_per*(b.id-1) as cost_min,a.cost_min+a.cost_perb.id as cost_max from
(
select row_number() over(partition by ‘1’ order by total_cost) id from T_SR_TANGENT_POINT_COST
)b,(select (max(total_cost)-min(total_cost))/20 cost_per, min(total_cost) cost_min from T_SR_TANGENT_POINT_COST) a
where b.id<=20
)
select
lev.id as cost_level,
d.
from T_SR_TANGENT_POINT_COST d,lev where d.total_cost>=lev.cost_min and d.total_cost<=lev.cost_max

如果大家有什么更好的方法,欢迎留言