src/ports/postgres/modules/sample/test/sample.sql_in

/* -----------------------------------------------------------------------------
 * Test random sampling.
 * -------------------------------------------------------------------------- */

-- We use the chi-squared goodness-of-fit test for evidence that
-- weighted_sample() samples according to the distribution defined by the
-- weights. Under the null hypothesis that this be true, we require that the
-- probability of seeing a chi-squared statistic at least as extreme as the
-- one observed is at least 1e-5.
-- Hence, assuming weighted_sample() is correct, the following test fails with
-- probability at most 1e-5, i.e., at most once per 10,000 runs.
SELECT
    assert(
        (chi2_gof_test(observed, expected)).p_value > 1e-5,
        'Results of weighted_sample() do not match the expected distribution.'
    )
FROM (
    SELECT
        value,
        CAST(value AS DOUBLE PRECISION) / (10 * (10 + 1))/2 AS expected,
        count(*) AS observed
    FROM (
        SELECT weighted_sample(i, i) AS value
        FROM
            generate_series(1,10) i,
            generate_series(1,10000) trial
        GROUP BY trial
    ) AS ignored
    GROUP BY value
    ORDER BY value
) AS ignored;

-- Same again for the vector version
SELECT
    assert(
        (chi2_gof_test(observed, expected)).p_value > 1e-5,
        'Results of weighted_sample() do not match the expected distribution.'
    )
FROM (
        SELECT
        value,
        value[1] / (10 * (10 + 1))/2 AS expected,
        count(*) AS observed
    FROM (
        SELECT weighted_sample(ARRAY[i,i]::DOUBLE PRECISION[], i) AS value
        FROM
            generate_series(1,10) i,
            generate_series(1,10000) trial
        GROUP BY trial
    ) AS ignored
    GROUP BY value
    ORDER BY value
) AS ignored;

-- Two-column table with (id,poisson_count)

SELECT *, poisson_random(1.0)
FROM generate_series(1,5);

SELECT *, gamma_random(5)
FROM generate_series(1,5);