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

/* -----------------------------------------------------------------------------
 * Test Wilcoxon signed-rank test.
 *
 * Example taken from:
 * http://www-stat.stanford.edu/~susan/courses/s141/hononpara.pdf
 * -------------------------------------------------------------------------- */

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

CREATE TABLE test_wsr (
    x DOUBLE PRECISION,
    y DOUBLE PRECISION
);

INSERT INTO test_wsr VALUES (0.32,0.39);
INSERT INTO test_wsr VALUES (0.4,0.47);
INSERT INTO test_wsr VALUES (0.11,0.11);
INSERT INTO test_wsr VALUES (0.47,0.43);
INSERT INTO test_wsr VALUES (0.32,0.42);
INSERT INTO test_wsr VALUES (0.35,0.3);
INSERT INTO test_wsr VALUES (0.32,0.43);
INSERT INTO test_wsr VALUES (0.63,0.98);
INSERT INTO test_wsr VALUES (0.5,0.86);
INSERT INTO test_wsr VALUES (0.6,0.79);
INSERT INTO test_wsr VALUES (0.38,0.33);
INSERT INTO test_wsr VALUES (0.46,0.45);
INSERT INTO test_wsr VALUES (0.2,0.22);
INSERT INTO test_wsr VALUES (0.31,0.3);
INSERT INTO test_wsr VALUES (0.62,0.6);
INSERT INTO test_wsr VALUES (0.52,0.53);
INSERT INTO test_wsr VALUES (0.77,0.85);
INSERT INTO test_wsr VALUES (0.23,0.21);
INSERT INTO test_wsr VALUES (0.3,0.33);
INSERT INTO test_wsr VALUES (0.7,0.57);
INSERT INTO test_wsr VALUES (0.41,0.43);
INSERT INTO test_wsr VALUES (0.53,0.49);
INSERT INTO test_wsr VALUES (0.19,0.2);
INSERT INTO test_wsr VALUES (0.31,0.35);
INSERT INTO test_wsr VALUES (0.48,0.4);

CREATE TABLE wsr_test AS
SELECT (wsr_test(
    x - y,
    2 * 2^(-52) * greatest(x,y)
    ORDER BY abs(x - y)
)).*
FROM test_wsr;

SELECT * FROM wsr_test;
SELECT assert(
    statistic = 105.5 AND
    rank_sum_pos = 105.5 AND
    rank_sum_neg = 194.5 AND
    num = 24 AND
    relative_error(z_statistic, -1.272) < 0.001,
    'Wilcoxon signed-rank: Wrong results'
) FROM wsr_test;

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