Oracle STATS_ Test Functions

Is the difference between two groups real, or could it be chance? Statistical tests answer that, and Oracle runs the common ones in SQL: t-tests, F-tests, analysis of variance, chi-squared, and non-parametric tests. Each returns a test statistic or a significance level, without exporting the data to a statistics package.

Code for This Guide

The main examples are in the examples/aggregate-functions folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.

They come from Oracle Database 26ai SQL and PL/SQL Book.

The Tests

Syntax:

stats_t_test_indepu(group_column, value_column [, return_value [, group1]])
stats_one_way_anova(group_column, value_column [, return_value])
stats_crosstab(column1, column2 [, return_value])

The return_value argument chooses what to return, such as 'STATISTIC', 'TWO_SIDED_SIG', 'F_RATIO', 'SIG', or 'CHISQ_SIG'. A significance below 0.05 is the usual sign that a difference is not chance.

Fares and Ratings

The first query compares business and economy fares with Welch's t-test; the second tests whether review ratings differ between routes with one-way analysis of variance.

Example:

-- is the fare in business class different from economy? (Welch's t-test)
select round(stats_t_test_indepu(cabin, fare, 'STATISTIC', 'BUSINESS'), 3) as t_value,
       round(stats_t_test_indepu(cabin, fare, 'TWO_SIDED_SIG', 'BUSINESS'), 6) as p_value
from   tickets;

-- do ratings differ between flights of different routes? (one-way ANOVA)
select round(stats_one_way_anova(f.route_id, r.rating, 'F_RATIO'), 3) as f_ratio,
       round(stats_one_way_anova(f.route_id, r.rating, 'SIG'), 4)     as significance
from   reviews r join flights f on f.flight_id = r.flight_id;

Output:

   T_VALUE    P_VALUE
__________ __________
    18.192          0

   F_RATIO    SIGNIFICANCE
__________ _______________
     1.057          0.4228

The fares differ beyond doubt: a t value of 18.2 and a significance of practically 0. The ratings do not differ significantly between routes: a significance of 0.42 is far above 0.05.

Are Cabin and Status Related?

STATS_CROSSTAB runs a chi-squared test of two categorical columns, here whether business and economy bookings are cancelled or completed in different proportions.

Example:

-- is the cabin related to the booking status? (chi-squared test)
select round(stats_crosstab(b.status, t.cabin, 'CHISQ_OBS'), 3) as chi_squared,
       round(stats_crosstab(b.status, t.cabin, 'CHISQ_SIG'), 4) as significance,
       stats_crosstab(b.status, t.cabin, 'CHISQ_DF')             as degrees_of_freedom
from   bookings b join tickets t on t.booking_id = b.booking_id;

Output:

   CHI_SQUARED    SIGNIFICANCE    DEGREES_OF_FREEDOM
______________ _______________ _____________________
         2.726          0.2559                     2

A significance of 0.26 means no evidence that cancellation depends on the cabin.

Things to Know

• A significant result says a difference is unlikely to be chance, not that it is large or important.
• Check the assumptions of each test; use the non-parametric tests when data is far from normal.
• Combine the tests with GROUP BY to test each segment separately.

Related Guides

• Oracle VARIANCE Function
• Oracle CORR Function

Conclusion

Oracle's STATS_ test functions run t-tests, F-tests, ANOVA, chi-squared, and non-parametric tests as SQL aggregates, returning statistics or significance levels. Use them to check whether differences between groups in your data are real before acting on them.