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

/* -----------------------------------------------------------------------------
 * Test one-sample t-test.
 *
 * Example ("ZARR13.DAT") taken from:
 * http://www.itl.nist.gov/div898/handbook/eda/section3/eda352.htm
 * -------------------------------------------------------------------------- */

CREATE TABLE ZARR13 (
	id SERIAL,
	value FLOAT8
);

COPY ZARR13(value) FROM stdin;
9.206343
9.299992
9.277895
9.305795
9.275351
9.288729
9.287239
9.260973
9.303111
9.275674
9.272561
9.288454
9.255672
9.252141
9.297670
9.266534
9.256689
9.277542
9.248205
9.252107
9.276345
9.278694
9.267144
9.246132
9.238479
9.269058
9.248239
9.257439
9.268481
9.288454
9.258452
9.286130
9.251479
9.257405
9.268343
9.291302
9.219460
9.270386
9.218808
9.241185
9.269989
9.226585
9.258556
9.286184
9.320067
9.327973
9.262963
9.248181
9.238644
9.225073
9.220878
9.271318
9.252072
9.281186
9.270624
9.294771
9.301821
9.278849
9.236680
9.233988
9.244687
9.221601
9.207325
9.258776
9.275708
9.268955
9.257269
9.264979
9.295500
9.292883
9.264188
9.280731
9.267336
9.300566
9.253089
9.261376
9.238409
9.225073
9.235526
9.239510
9.264487
9.244242
9.277542
9.310506
9.261594
9.259791
9.253089
9.245735
9.284058
9.251122
9.275385
9.254619
9.279526
9.275065
9.261952
9.275351
9.252433
9.230263
9.255150
9.268780
9.290389
9.274161
9.255707
9.261663
9.250455
9.261952
9.264041
9.264509
9.242114
9.239674
9.221553
9.241935
9.215265
9.285930
9.271559
9.266046
9.285299
9.268989
9.267987
9.246166
9.231304
9.240768
9.260506
9.274355
9.292376
9.271170
9.267018
9.308838
9.264153
9.278822
9.255244
9.229221
9.253158
9.256292
9.262602
9.219793
9.258452
9.267987
9.267987
9.248903
9.235153
9.242933
9.253453
9.262671
9.242536
9.260803
9.259825
9.253123
9.240803
9.238712
9.263676
9.243002
9.246826
9.252107
9.261663
9.247311
9.306055
9.237646
9.248937
9.256689
9.265777
9.299047
9.244814
9.287205
9.300566
9.256621
9.271318
9.275154
9.281834
9.253158
9.269024
9.282077
9.277507
9.284910
9.239840
9.268344
9.247778
9.225039
9.230750
9.270024
9.265095
9.284308
9.280697
9.263032
9.291851
9.252072
9.244031
9.283269
9.196848
9.231372
9.232963
9.234956
9.216746
9.274107
9.273776
\.

CREATE TABLE t_test_one AS
SELECT (t_test_one(value - 5.0)).* FROM zarr13;

SELECT * FROM t_test_one;
SELECT assert(
    relative_error(statistic, 2611.284) < 0.001 AND
    df = 194,
    'One-sample t-test: Wrong results'
) FROM t_test_one;

/* -----------------------------------------------------------------------------
 * Test one-sample t-test.
 *
 * Example ("AUTO83B.DAT") taken from:
 * http://www.itl.nist.gov/div898/handbook/eda/section3/eda353.htm
 * -------------------------------------------------------------------------- */

CREATE TABLE auto83b (
    id SERIAL,
    mpg_us DOUBLE PRECISION,
    mpg_j DOUBLE PRECISION
);

COPY auto83b (mpg_us, mpg_j) FROM stdin;
18	24
15	27
18	27
16	25
17	31
15	35
14	24
14	19
14	28
15	23
15	27
14	20
15	22
14	18
22	20
18	31
21	32
21	31
10	32
10	24
11	26
9	29
28	24
25	24
19	33
16	33
17	32
19	28
18	19
14	32
14	34
14	26
14	30
12	22
13	22
13	33
18	39
22	36
19	28
18	27
23	21
26	24
25	30
20	34
21	32
13	38
14	37
15	30
14	31
17	37
11	32
13	47
12	41
13	45
15	34
13	33
13	24
14	32
22	39
28	35
13	32
14	37
13	38
14	34
15	34
12	32
13	33
13	32
14	25
13	24
12	37
13	31
18	36
16	36
18	34
18	38
23	32
11	38
12	32
13	-999
12	-999
18	-999
21	-999
19	-999
21	-999
15	-999
16	-999
15	-999
11	-999
20	-999
21	-999
19	-999
15	-999
26	-999
25	-999
16	-999
16	-999
18	-999
16	-999
13	-999
14	-999
14	-999
14	-999
28	-999
19	-999
18	-999
15	-999
15	-999
16	-999
15	-999
16	-999
14	-999
17	-999
16	-999
15	-999
18	-999
21	-999
20	-999
13	-999
23	-999
20	-999
23	-999
18	-999
19	-999
25	-999
26	-999
18	-999
16	-999
16	-999
15	-999
22	-999
22	-999
24	-999
23	-999
29	-999
25	-999
20	-999
18	-999
19	-999
18	-999
27	-999
13	-999
17	-999
13	-999
13	-999
13	-999
30	-999
26	-999
18	-999
17	-999
16	-999
15	-999
18	-999
21	-999
19	-999
19	-999
16	-999
16	-999
16	-999
16	-999
25	-999
26	-999
31	-999
34	-999
36	-999
20	-999
19	-999
20	-999
19	-999
21	-999
20	-999
25	-999
21	-999
19	-999
21	-999
21	-999
19	-999
18	-999
19	-999
18	-999
18	-999
18	-999
30	-999
31	-999
23	-999
24	-999
22	-999
20	-999
22	-999
20	-999
21	-999
17	-999
18	-999
17	-999
18	-999
17	-999
16	-999
19	-999
19	-999
36	-999
27	-999
23	-999
24	-999
34	-999
35	-999
28	-999
29	-999
27	-999
34	-999
32	-999
28	-999
26	-999
24	-999
19	-999
28	-999
24	-999
27	-999
27	-999
26	-999
24	-999
30	-999
39	-999
35	-999
34	-999
30	-999
22	-999
27	-999
20	-999
18	-999
28	-999
27	-999
34	-999
31	-999
29	-999
27	-999
24	-999
23	-999
38	-999
36	-999
25	-999
38	-999
26	-999
22	-999
36	-999
27	-999
27	-999
32	-999
28	-999
31	-999
\.

CREATE TABLE t_test_two_pooled AS
SELECT (t_test_two_pooled(is_us, mpg)).* FROM (
    SELECT TRUE AS is_us, mpg_us AS mpg
    FROM auto83b
    WHERE mpg_us != -999
    UNION ALL
    SELECT FALSE, mpg_j
    FROM auto83b
    WHERE mpg_j != -999
) q;

CREATE TABLE t_test_two_unpooled AS
SELECT (t_test_two_unpooled(is_us, mpg)).* FROM (
    SELECT TRUE AS is_us, mpg_us AS mpg
    FROM auto83b
    WHERE mpg_us != -999
    UNION ALL
    SELECT FALSE, mpg_j
    FROM auto83b
    WHERE mpg_j != -999
) q;

SELECT * FROM t_test_two_pooled;
SELECT assert(
    relative_error(statistic, -12.62059) < 0.001 AND
    df = 326,
    'Pooled two-sample t-test: Wrong results'
) FROM t_test_two_pooled;

SELECT * FROM t_test_two_unpooled;
SELECT assert(
    relative_error(statistic, -12.94627) < 0.001 AND
    relative_error(df, 136.875) < 0.001,
    'Unpooled two-sample t-test: Wrong results'
) FROM t_test_two_unpooled;