我有一张叫“ sales”的桌子。
create table sales
(
cust varchar(20),prod varchar(20),day integer,month integer,year integer,state char(2),quant integer
);
insert into sales values ('Bloom','Pepsi',2,12,2001,'NY',4232);
insert into sales values ('Knuth','Bread',23,5,2005,'PA',4167);
insert into sales values ('Emily',22,1,2006,'CT',4404);
insert into sales values ('Emily','Fruits',11,2000,'NJ',4369);
insert into sales values ('Helen','Milk',7,210);
insert into sales values ('Emily','Soap',4,2002,2549);
insert into sales values ('Bloom','Eggs',30,559);
..共有498行。这是此表的概述:
现在,我想获取每种产品的中位数。结果表应如下所示:
我已经尝试过这些代码,并且可以正常工作:
CREATE OR REPLACE FUNCTION _final_median(NUMERIC[])
RETURNS NUMERIC AS
$$
SELECT AVG(val)
FROM (
SELECT val
FROM unnest($1) val
ORDER BY 1
LIMIT 2 - MOD(array_upper($1,1),2)
OFFSET CEIL(array_upper($1,1) / 2.0) - 1
) sub;
$$
LANGUAGE 'sql' IMMUTABLE;
CREATE AGGREGATE median(NUMERIC) (
SFUNC=array_append,STYPE=NUMERIC[],FINALFUNC=_final_median,INITCOND='{}'
);
SELECT prod,round(median(quant)) AS median_quant FROM sales
group by prod
order by prod;
但是我想使用“聚合”功能获得相同的结果,如果有的话我可以在没有特殊功能的情况下做到这一点?