tag:blogger.com,1999:blog-8839574367290288724.post3008755570877508300..comments2023-12-11T13:13:11.330-08:00Comments on Pavel Stehule's blog: Aggregate function MEDIAN in PostgreSQLPavel Stěhulehttp://www.blogger.com/profile/01996484227228696817noreply@blogger.comBlogger4125tag:blogger.com,1999:blog-8839574367290288724.post-70749649606463832522011-11-18T13:20:14.838-08:002011-11-18T13:20:14.838-08:00I'm not sure why this is faster, but here'...I'm not sure why this is faster, but here's a 4th analytic method that runs a tad faster than the other three:<br /><br /><br /><br />select <br /> avg(ordered.value)<br />from <br /> (<br /> select<br /> row_number() over(order by a) as position,<br /> a as value<br /> from<br /> milrows<br /> ) as ordered<br />where<br /> ordered.position = (select ceil((count(a)::float8-1)/2+1) from milrows) or<br /> ordered.position = (select floor((count(a)::float8-1)/2+1) from milrows)<br />;<br /><br />Here's the timing:<br />alpha=# create table milrows(a real);<br />CREATE TABLE<br />Time: 2.410 ms<br />alpha=# insert into milrows select random()*1000000 from generate_series(1,1000000);<br />INSERT 0 1000000<br />Time: 2474.768 ms<br />alpha=#<br />alpha=# select avg(a)<br />alpha-# from ( select a, row_number() over (order by a),(select count(*) from milrows) as count from milrows ) s<br />alpha-# where row_number between floor((count::float8-1)/2+1) and ceil((count::float8-1)/2+1)<br />alpha-# ;<br /> avg<br />--------------<br /> 499884.46875<br />(1 row)<br /><br />Time: 3242.837 ms<br />alpha=# select<br />alpha-# avg(ordered.value)<br />alpha-# from<br />alpha-# (<br />alpha(# select<br />alpha(# row_number() over(order by a) as position,<br />alpha(# a as value<br />alpha(# from<br />alpha(# milrows<br />alpha(# ) as ordered<br />alpha-# where<br />alpha-# ordered.position = (select ceil((count(a)::float8-1)/2+1) from milrows) or<br />alpha-# ordered.position = (select floor((count(a)::float8-1)/2+1) from milrows)<br />alpha-# ;<br /> avg<br />--------------<br /> 499884.46875<br />(1 row)<br /><br />Time: 2802.536 ms<br />alpha=#Nick Fankhauserhttps://www.blogger.com/profile/04062103139184449817noreply@blogger.comtag:blogger.com,1999:blog-8839574367290288724.post-60827580913974795542009-12-05T10:30:11.520-08:002009-12-05T10:30:11.520-08:00[Josh] - My implementation isn't good for very...[Josh] - My implementation isn't good for very large data sets (but is better then currently used others method). Real implementation have to be based on tuple store. And it needs different API than is current aggregate API :( - so it needs lot of work.Pavel Stěhulehttps://www.blogger.com/profile/01996484227228696817noreply@blogger.comtag:blogger.com,1999:blog-8839574367290288724.post-4381266037652949822009-12-05T10:12:42.548-08:002009-12-05T10:12:42.548-08:00Pavel,
Why not submit median() to mainstream Post...Pavel,<br /><br />Why not submit median() to mainstream PostgreSQL? It doesn't seem particularly Oracle-ish.Josh Berkushttp://www.pgexperts.comnoreply@blogger.comtag:blogger.com,1999:blog-8839574367290288724.post-3708337688893228012009-11-26T00:30:27.103-08:002009-11-26T00:30:27.103-08:00Pavel,
Sounds cool. I'll have to check otu th...Pavel,<br />Sounds cool. I'll have to check otu that package.<br /><br />Thanks,<br />ReginaReginahttp://www.postgresonline.comnoreply@blogger.com