src/ports/postgres/modules/stats/test/ks_test.sql_in

/* -----------------------------------------------------------------------------
 * Test Kolmogorov-Smirnov test.
 *
 * Example taken from:
 * http://www.physics.csbsju.edu/stats/KS-test.html
 * -------------------------------------------------------------------------- */

m4_include(`SQLCommon.m4')
m4_changequote(<!,!>)
m4_ifdef(<!__HAS_ORDERED_AGGREGATES__!>,<!

CREATE TABLE ks_sample_1 AS
SELECT
    TRUE AS first,
    unnest(ARRAY[0.22, -0.87, -2.39, -1.79, 0.37, -1.54, 1.28, -0.31, -0.74, 1.72, 0.38, -0.17, -0.62, -1.10, 0.30, 0.15, 2.30, 0.19, -0.50, -0.09]) AS value
UNION ALL
SELECT
    FALSE,
    unnest(ARRAY[-5.13, -2.19, -2.43, -3.83, 0.50, -3.25, 4.32, 1.63, 5.18, -0.43, 7.11, 4.87, -3.10, -5.81, 3.76, 6.31, 2.58, 0.07, 5.76, 3.50]);

CREATE TABLE ks_test_1 AS
SELECT (ks_test(first, value,
    (SELECT count(value) FROM ks_sample_1 WHERE first),
    (SELECT count(value) FROM ks_sample_1 WHERE NOT first)
    ORDER BY value)).*
FROM ks_sample_1;

SELECT * FROM ks_test_1;
SELECT assert(
    relative_error(statistic, 0.45) < 0.001,
    'Kolmogorov-Smirnov: Wrong results'
) FROM ks_test_1;


CREATE TABLE ks_sample_2 AS
SELECT
    TRUE AS first,
    unnest(ARRAY[1.26, 0.34, 0.70, 1.75, 50.57, 1.55, 0.08, 0.42, 0.50, 3.20, 0.15, 0.49, 0.95, 0.24, 1.37, 0.17, 6.98, 0.10, 0.94, 0.38]) AS value
UNION ALL
SELECT
    FALSE,
    unnest(ARRAY[2.37, 2.16, 14.82, 1.73, 41.04, 0.23, 1.32, 2.91, 39.41, 0.11, 27.44, 4.51, 0.51, 4.50, 0.18, 14.68, 4.66, 1.30, 2.06, 1.19]);

CREATE TABLE ks_test_2 AS
SELECT (ks_test(first, value,
    (SELECT count(value) FROM ks_sample_2 WHERE first),
    (SELECT count(value) FROM ks_sample_2 WHERE NOT first)
    ORDER BY value)).*
FROM ks_sample_2;

SELECT * FROM ks_test_2;
SELECT assert(
    relative_error(statistic, 0.45) < 0.001,
    'Kolmogorov-Smirnov: Wrong results'
) FROM ks_test_2;

!>)
m4_changequote(<!`!>,<!'!>)