CODEWITHSUNDEEP
sqlstatisticstrinofunction-approximation
Walter Tross
Walter Tross

Reputation: 12627

How to calculate the Gauss error function erf/erfc in Trino (or SQL)

I need to calculate the Gauss error function erf or its complement erfc = 1−erf in Trino. I need it for the estimation of the p (probability) value of a deviation from a binomial distribution expressed in terms of the standard deviation (sigma), so I don't need a great precision. Trino does not provide erf/erfc among its functions.

I could use a table of values and interpolate between them, but this seems a bit cumbersome, and I am not even sure what values to put into the table in order not to introduce a systematic error when doing a linear interpolation. Or maybe I should interpolate differently? But I would prefer to avoid interpolating in the first place. What do you suggest?

Upvotes: 0

Views: 57

Answers (1)

Walter Tross
Walter Tross

Reputation: 12627

It turns out I could use a UDF (user-defined function) if only the version of Trino I have access to was the latest. At the time of writing the latest version is 468, and UDFs are documented here (see in particular the examples). The documentation of versions before 468 is completely missing the udf chapter.

I would even have an untested implementation of the approximation within a maximum error of 3E-7 reported in the "Numerical approximations" section of the Wikipedia article (here as an inline UDF):

-- complementary Gauss error function within 3E-7
-- source: https://en.wikipedia.org/wiki/Error_function#Numerical_approximations
WITH FUNCTION erfc(x double)
RETURNS double
BEGIN
  DECLARE y double;
  DECLARE z double;
  DECLARE z4 double;
  DECLARE z16 double;
  SET y = abs(x);
  SET z = (((((0.0000430638 * y + 0.0002765672) * y + 0.0001520143) * y + 0.0092705272) * y + 0.0422820123) * y + 0.0705230784) * y + 1.0;
  SET z4 = (z * z) * (z * z);
  SET z16 = (z4 * z4) * (z4 * z4);
  IF x >= 0.0 THEN
    RETURN 1.0 / z16;
  END IF;
  RETURN 2.0 - 1.0 / z16;
END
SELECT erfc(1.0)

Upvotes: 0

Related Questions