主要解释一下percentile_cont的计算方法:
数据准备
创建表:
create table test
(
id integer,
info text
);
alter table test
owner to postgres;
插入数据:
INSERT INTO public.test (id, info) VALUES (1, 'test1');
INSERT INTO public.test (id, info) VALUES (2, 'test2');
INSERT INTO public.test (id, info) VALUES (3, 'test2');
INSERT INTO public.test (id, info) VALUES (4, 'test2');
INSERT INTO public.test (id, info) VALUES (5, 'test2');
INSERT INTO public.test (id, info) VALUES (6, 'test2');
INSERT INTO public.test (id, info) VALUES (7, 'test2');
INSERT INTO public.test (id, info) VALUES (8, 'test3');
INSERT INTO public.test (id, info) VALUES (100, 'test3');
INSERT INTO public.test (id, info) VALUES (100, 'test4');
函数使用说明
参数必须为包含式[0,1]
函数结果说明
例如执行:
select percentile_cont(0.5) within group (order by id) from test;
计算方式:
N=当前分组的行数=10
RN = (1+传入参数*(N-1)) = (1+0.5*(10-1)) = 5.5
CRN = ceiling(RN) = 6
FRN = floor(RN) = 5
value of expression for row at FRN : 当前分组内第FRN行的值 = 5
value of expression for row at CRN : 当前分组内第CRN行的值 = 6
所以最终中位数值 :
(CRN - RN) * (value of expression for row at FRN) +(RN - FRN) * (value of expression for row at CRN)
= (6-5.5)*(5) + (5.5- 5)*(6) = 5.5;
如果是多个计算结果类似。
引用
感谢这位老铁的博客,我这里只是整理一下,方便后续自行学习查看。
https://blog.csdn.net/postgrechina/article/details/49131097