SQL Server SQL查询中的Excel四分位数函数

Question

我有一组这样的值：

40
50
50
66
83
100
100
100
100
100
100
100
100
100
100
100
100

当我在excel中做四分位数函数时，我得到了第一个四分位数(25(、第二个四分位(50(、第三个四分位组(75(和最大(100(的这四个值

四分位数=83.33100100100

因此，当我比较一个获得80%的销售代表时，根据excel计算，他们将落在最后四分之一。

我需要在sql中重做相同的功能，并在下面给出了我的代码。

declare @sales table(
salesRepId int,
percentageSales int)
insert into @sales(salesRepId, percentageSales)
values(1,40)
,(2,50)
,(3,50)
,(4,66.7)
,(5,83.33)
,(6,100)
,(7,100)
,(8,100)
,(9,100)
,(10,100)
,(11,100)
,(12,100)
,(13,100)
,(14,100)
,(15,100)
,(16,100)
,(17,100);
with quintile as(
select percentagesales, ntile(4) over(order by percentagesales) 
as quintile
from (select distinct percentagesales from @sales) as s
)
select salesrepid, r.percentagesales, q.quintile
from @sales r
join quintile q on r.percentagesales = q.percentagesales
order by q.quintile, percentagesales

当我运行这个程序时，我会得到以下结果集：查询结果

salesrepid  percentagesales quintile
1   40  1
2   50  1
3   50  1
4   66  2
5   83  3
6   100 4
7   100 4
8   100 4
9   100 4
10  100 4
11  100 4
12  100 4
13  100 4
14  100 4
15  100 4
16  100 4
17  100 4

根据sql，这80%将属于中等四分之一。

如何在SQL查询中获得类似excel的四个百分点值

Answer 1

在percentageSales:中将int更改为decimal

declare @sales table(
salesRepId int,
percentageSales decimal(8,2))

insert into @sales(salesRepId, percentageSales)
values(1,40)
,(2,50)
,(3,50)
,(4,66.7)
,(5,83.33)
,(6,100)
,(7,100)
,(8,100)
,(9,100)
,(10,100)
,(11,100)
,(12,100)
,(13,100)
,(14,100)
,(15,100)
,(16,100)
,(17,100);

with quintile as(
select percentagesales, ntile(4) over(order by percentagesales) 
as quintile
from (select distinct percentagesales from @sales) as s
)
select salesrepid, r.percentagesales, q.quintile
from @sales r
join quintile q on r.percentagesales = q.percentagesales
order by q.quintile, percentagesales

Answer 2

我尝试了percentile_cont建议Larnu，我得到了四个四分位数作为

83100100100

但从技术上讲，它应该是83.33100100100

这是我到目前为止的查询

declare @sales table(
salesRepId int,
percentageSales int)
insert into @sales(salesRepId, percentageSales)
values(1,40.00)
,(2,50.00)
,(3,50.00)
,(4,66.77)
,(5,83.33)
,(6,100.00)
,(7,100.00)
,(8,100.00)
,(9,100.00)
,(10,100.00)
,(11,100.00)
,(12,100.00)
,(13,100.00)
,(14,100.00)
,(15,100.00)
,(16,100.00)
,(17,100.00);
with quintile as(
select percentagesales, ntile(100) over(order by percentagesales) as quintile
from (select distinct percentagesales from @sales) as s
)
select salesrepid, r.percentagesales, q.quintile
from @sales r
join quintile q on r.percentagesales = q.percentagesales
order by q.quintile, percentagesales;
declare @p Decimal(2,2) = 0.25;
with quartile as(
select 
salesRepId,
percentageSales 
,cast(percentile_cont(.25)  within group(order by percentagesales) over() as decimal(36,2)) as quartile25th
,cast(percentile_cont(.5)  within group(order by percentagesales) over() as decimal(36,2)) as quartile50th
,cast(percentile_cont(.75)  within group(order by percentagesales) over() as decimal(36,2)) as quartile75th
,cast(percentile_cont(1)  within group(order by percentagesales) over() as decimal(36,2)) as quartile100th
from @sales
)
select 
s.salesRepId
,s.percentageSales
, case 
when s.percentageSales < q.quartile25th then 'red'
when s.percentageSales >=q.quartile25th and s.percentageSales <q.quartile75th then 'yellow'
when s.percentageSales >= q.quartile75th then 'green'
end as color
From @sales s
join quartile q on q.percentageSales = s.percentageSales and q.salesRepId = s.salesRepId

我如何才能像在excel中一样获得四分位数。

谢谢