Skip to content

Instantly share code, notes, and snippets.

@olooney
Created June 13, 2018 23:28
Show Gist options
  • Save olooney/b4b93436ee37875fbcefe0a9b5106e90 to your computer and use it in GitHub Desktop.
Save olooney/b4b93436ee37875fbcefe0a9b5106e90 to your computer and use it in GitHub Desktop.
PostgreSQL pnorm() function calculated the c.d.f. of the normal Gaussian distribution. This function match's R's build in pnorm() function to within +/- 2e-7 over the entire real line. However, it's a constant 1/0 above/below z=+7/-7.
CREATE OR REPLACE FUNCTION pnorm(z double precision) RETURNS double precision AS $$
SELECT CASE
WHEN $1 >= 0 THEN 1 - POWER(((((((0.000005383*$1+0.0000488906)*$1+0.0000380036)*$1+0.0032776263)*$1+0.0211410061)*$1+0.049867347)*$1+1),-16)/2
ELSE 1 - pnorm(-$1)
END;
$$ LANGUAGE SQL IMMUTABLE STRICT;
@jamesallain
Copy link

Thank you very much for the function! Really helps converting z-scores for the CDC growth charts.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment