Skip to content

Instantly share code, notes, and snippets.

@olooney

olooney/pnorm.sql

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.
Download ZIP
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.
Raw
pnorm.sql
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;
@olooney
Copy link
Author

olooney commented Jun 13, 2018

Note: the coefficients used appears in many text books, although I'm not sure where they are originally from; for example:
https://books.google.com/books?id=yXO-CgAAQBAJ&pg=PA310&lpg=PA310&dq=0.000005383+0.0000488906&source=bl&ots=3t_nbmhwZA&sig=t2XbXVPvyPN6Z-lEC7blYGZCmTQ&hl=en&sa=X&ved=0ahUKEwiQivHw3dHbAhXl7IMKHWuMBBMQ6AEINTAD#v=onepage&q=0.000005383%200.0000488906&f=false

https://books.google.com/books?id=4842MXNn5o4C&pg=PA45&lpg=PA45&dq=0.000005383+0.0000488906&source=bl&ots=mgPkVP6aVs&sig=ktK-A7jJJhekRg3V5aQV2ReGW08&hl=en&sa=X&ved=0ahUKEwiQivHw3dHbAhXl7IMKHWuMBBMQ6AEIPjAF#v=onepage&q=0.000005383%200.0000488906&f=false

@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