摘要:对结果按整值和0.5分段计算数据分布
业务中，需要对某个结果按整值和0.5分段计算数据分布。
示例如下：
declare @TAB table(score float null);
insert into @TAB(score) values
(4.55749),(3.97298),(5.061),(5.23483),(4.9859),(4.77588),(4.77948),(4.81817),(4.47336),
(3.30898),(4.46726),(4.51435),(5.16969),(3.15595),(5.11572),(4.60834),(4.88941),
(3.13779),(3.11459),(4.1895),(4.63827),(4.79419),(4.86259),(4.86318),(5.21334),
(5.75969),(4.80027),(5.09955),(4.90393),(4.80337),(5.08852),(5.03955),(5.40235),
(4.16609),(4.98767),(6.56728),(6.47172),(3.78699),(4.77609),(4.87557),(4.72873),
(5.61807),(5.25014),(6.12711),(4.62214),(5.73764),(4.8397),(4.79726),(4.81298),
(5.01072),(5.00441),(8.02305),(4.93154),(4.73005),(5.04054),(5.07489),(5.23969),
(4.30552),(4.86349),(4.84106),(5.01844),(4.71113),(4.61199),(8.41617),(5.87958),
(4.75808),(3.92746),(4.42495),(4.9191),(4.26017),(3.91111),(4.06557),(5.21136),
(4.11484),(4.70865),(4.60692),(5.45771),(6.16208),(5.54537),(5.35907),(5.07367),
(4.59573),(4.96666),(4.8851),(5.00444),(3.78278),(4.86767),(5.72804),(4.79991),
(5.04434),(3.82248),(4.54309),(5.58708),(5.16396),(5.26609),(3.84406),(4.36991)
,(5.29609),(3.55906),(4.20596)
--按0.5分段汇总
select block_value,count(*) as block_count,sum(count(*)) over() as total_count
,count(*)*100.0/sum(count(*) ) over() as percent_block
from
(
select
case when score>=(floor(score)+0.50) then ceiling(score) else (floor(score)+0.50) end as block_value
,* from @TAB
) as a group by block_value
order by block_value
----------------------------------------
--按整分段汇总
select block_value,count(*) as block_count,sum(count(*)) over() as total_count
,count(*)*100.0/sum(count(*) ) over() as percent_block
from
(
select
ceiling(score) as block_value
,* from @TAB
) as a group by block_value
order by block_value
---------------------------------------------------
