Using PL/R for quantiles

I was trying to reproduce a plot from a book today. The plot has quantiles (10th, 25th, 50th, 75th, and 90th percentiles) for market-to-book for US companies from 1963 through 2010.

I wanted to pull the data together in PostgreSQL, then ship it over to R for plotting. I couldn’t work out how to do quantiles elegantly using PostgreSQL or using PL/R. So I reached out to the PL/R community and got two replies. Here is one solution

The following adds a function to aggregate arrays before passing them over to R.

DROP AGGREGATE IF EXISTS array_accum(anyelement) ;

The following passes an array over to R for calculation of the requested quantile.

CREATE OR REPLACE FUNCTION r_quantile(double precision[], double precision) RETURNS double precision AS 'quantile(arg1, arg2, na.rm=TRUE)' LANGUAGE plr STRICT;

Then to put this into action:

drv <- dbDriver("PostgreSQL")
pg <- dbConnect(drv, dbname="crsp")

mb <- dbGetQuery(pg, "
     SELECT fyear, 
         r_quantile(array_accum(mb), 0.10) AS p10,
         r_quantile(array_accum(mb), 0.25) AS p25,
         r_quantile(array_accum(mb), 0.50) AS p50,
         r_quantile(array_accum(mb), 0.75) AS p75,
         r_quantile(array_accum(mb), 0.90) AS p90
     FROM (
         SELECT gvkey, fyear, prcc_f*csho AS mktcap,
         CASE WHEN ceq >0 THEN prcc_f*csho/ceq ELSE NULL END AS mb 
         FROM comp.funda 
         WHERE indfmt='INDL' AND datafmt='STD' AND popsrc='D' AND consol='C') AS a
     WHERE mktcap > 200 AND fyear>=1963
     GROUP BY fyear
     ORDER BY fyear")

rs <- dbDisconnect(pg)

xyplot(p10 + p25 + p50 + p75 + p90 ~ fyear,
       data=mb,  auto.key = list(points = FALSE, lines = TRUE), 

The resulting plot looks like this:

This entry was posted in PostgreSQL and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s