Oracle PL/SQL Tutorial/Linear Regression Functions/STATS ONE WAY ANOVA

Материал из SQL эксперт
Перейти к: навигация, поиск

For between degrees of freedom (DF): STATS_ONE_WAY_ANOVA(expr1, expr2, "DF_BETWEEN")

   <source lang="sql">

SQL> SQL> SQL> SQL> -- create demo table SQL> create table myTable(

 2    id           NUMBER(2),
 3    value        NUMBER(6,2)
 4  )
 5  /

Table created. SQL> SQL> -- prepare data SQL> insert into myTable(ID, value)values (1,9); 1 row created. SQL> insert into myTable(ID, value)values (2,2.11); 1 row created. SQL> insert into myTable(ID, value)values (3,3.44); 1 row created. SQL> insert into myTable(ID, value)values (4,-4.21); 1 row created. SQL> insert into myTable(ID, value)values (5,10); 1 row created. SQL> insert into myTable(ID, value)values (6,3); 1 row created. SQL> insert into myTable(ID, value)values (7,-5.88); 1 row created. SQL> insert into myTable(ID, value)values (8,123.45); 1 row created. SQL> insert into myTable(ID, value)values (9,98.23); 1 row created. SQL> insert into myTable(ID, value)values (10,938.23); 1 row created. SQL> insert into myTable(ID, value)values (11,984.23); 1 row created. SQL> insert into myTable(ID, value)values (12,198.23); 1 row created. SQL> insert into myTable(ID, value)values (13,928.87); 1 row created. SQL> insert into myTable(ID, value)values (14,25.37); 1 row created. SQL> insert into myTable(ID, value)values (15,918.3); 1 row created. SQL> insert into myTable(ID, value)values (16,9.23); 1 row created. SQL> insert into myTable(ID, value)values (17,8.23); 1 row created. SQL> SQL> select * from myTable

 2  /
       ID      VALUE

----------
        1          9
        2       2.11
        3       3.44
        4      -4.21
        5         10
        6          3
        7      -5.88
        8     123.45
        9      98.23
       10     938.23
       11     984.23
       ID      VALUE

----------
       12     198.23
       13     928.87
       14      25.37
       15      918.3
       16       9.23
       17       8.23

17 rows selected. SQL> SQL> SQL> SELECT STATS_ONE_WAY_ANOVA(id, value, "DF_BETWEEN") FROM myTable; STATS_ONE_WAY_ANOVA(ID,VALUE,"DF_BETWEEN")


                                       16

SQL> SQL> SQL> SQL> -- clean the table SQL> drop table myTable

 2  /

Table dropped.</source>


For between sum of squares (SS): STATS_ONE_WAY_ANOVA(expr1, expr2,"SUM_SQUARES_BETWEEN")

STATS_ONE_WAY_ANOVA tests the equality of several means.

The test statistics is based on F statistic.



   <source lang="sql">

SQL> -- create demo table SQL> create table myTable(

 2    id           NUMBER(2),
 3    value        NUMBER(6,2)
 4  )
 5  /

Table created. SQL> SQL> -- prepare data SQL> insert into myTable(ID, value)values (1,9); 1 row created. SQL> insert into myTable(ID, value)values (2,2.11); 1 row created. SQL> insert into myTable(ID, value)values (3,3.44); 1 row created. SQL> insert into myTable(ID, value)values (4,-4.21); 1 row created. SQL> insert into myTable(ID, value)values (5,10); 1 row created. SQL> insert into myTable(ID, value)values (6,3); 1 row created. SQL> insert into myTable(ID, value)values (7,-5.88); 1 row created. SQL> insert into myTable(ID, value)values (8,123.45); 1 row created. SQL> insert into myTable(ID, value)values (9,98.23); 1 row created. SQL> insert into myTable(ID, value)values (10,938.23); 1 row created. SQL> insert into myTable(ID, value)values (11,984.23); 1 row created. SQL> insert into myTable(ID, value)values (12,198.23); 1 row created. SQL> insert into myTable(ID, value)values (13,928.87); 1 row created. SQL> insert into myTable(ID, value)values (14,25.37); 1 row created. SQL> insert into myTable(ID, value)values (15,918.3); 1 row created. SQL> insert into myTable(ID, value)values (16,9.23); 1 row created. SQL> insert into myTable(ID, value)values (17,8.23); 1 row created. SQL> SQL> select * from myTable

 2  /
       ID      VALUE

----------
        1          9
        2       2.11
        3       3.44
        4      -4.21
        5         10
        6          3
        7      -5.88
        8     123.45
        9      98.23
       10     938.23
       11     984.23
       ID      VALUE

----------
       12     198.23
       13     928.87
       14      25.37
       15      918.3
       16       9.23
       17       8.23

17 rows selected. SQL> SQL> SQL> SELECT STATS_ONE_WAY_ANOVA(id, value, "SUM_SQUARES_BETWEEN") FROM myTable; STATS_ONE_WAY_ANOVA(ID,VALUE,"SUM_SQUARES_BETWEEN")


                                        2557882.91

SQL> SQL> SQL> SQL> -- clean the table SQL> drop table myTable

 2  /

Table dropped.</source>


For F statistic: STATS_ONE_WAY_ANOVA(expr1, expr2, "F_RATIO")

   <source lang="sql">

SQL> SQL> SQL> -- create demo table SQL> create table myTable(

 2    id           NUMBER(2),
 3    value        NUMBER(6,2)
 4  )
 5  /

Table created. SQL> SQL> -- prepare data SQL> insert into myTable(ID, value)values (1,9); 1 row created. SQL> insert into myTable(ID, value)values (2,2.11); 1 row created. SQL> insert into myTable(ID, value)values (3,3.44); 1 row created. SQL> insert into myTable(ID, value)values (4,-4.21); 1 row created. SQL> insert into myTable(ID, value)values (5,10); 1 row created. SQL> insert into myTable(ID, value)values (6,3); 1 row created. SQL> insert into myTable(ID, value)values (7,-5.88); 1 row created. SQL> insert into myTable(ID, value)values (8,123.45); 1 row created. SQL> insert into myTable(ID, value)values (9,98.23); 1 row created. SQL> insert into myTable(ID, value)values (10,938.23); 1 row created. SQL> insert into myTable(ID, value)values (11,984.23); 1 row created. SQL> insert into myTable(ID, value)values (12,198.23); 1 row created. SQL> insert into myTable(ID, value)values (13,928.87); 1 row created. SQL> insert into myTable(ID, value)values (14,25.37); 1 row created. SQL> insert into myTable(ID, value)values (15,918.3); 1 row created. SQL> insert into myTable(ID, value)values (16,9.23); 1 row created. SQL> insert into myTable(ID, value)values (17,8.23); 1 row created. SQL> SQL> select * from myTable

 2  /
       ID      VALUE

----------
        1          9
        2       2.11
        3       3.44
        4      -4.21
        5         10
        6          3
        7      -5.88
        8     123.45
        9      98.23
       10     938.23
       11     984.23
       ID      VALUE

----------
       12     198.23
       13     928.87
       14      25.37
       15      918.3
       16       9.23
       17       8.23

17 rows selected. SQL> SQL> SQL> SELECT STATS_ONE_WAY_ANOVA(id, value, "F_RATIO") FROM myTable; STATS_ONE_WAY_ANOVA(ID,VALUE,"F_RATIO")


SQL> SQL> SQL> SQL> -- clean the table SQL> drop table myTable

 2  /

Table dropped.</source>


For mean square (MS) between: STATS_ONE_WAY_ANOVA(expr1, expr2, "MEAN_SQUARES_BETWEEN")

   <source lang="sql">

SQL> SQL> SQL> -- create demo table SQL> create table myTable(

 2    id           NUMBER(2),
 3    value        NUMBER(6,2)
 4  )
 5  /

Table created. SQL> SQL> -- prepare data SQL> insert into myTable(ID, value)values (1,9); 1 row created. SQL> insert into myTable(ID, value)values (2,2.11); 1 row created. SQL> insert into myTable(ID, value)values (3,3.44); 1 row created. SQL> insert into myTable(ID, value)values (4,-4.21); 1 row created. SQL> insert into myTable(ID, value)values (5,10); 1 row created. SQL> insert into myTable(ID, value)values (6,3); 1 row created. SQL> insert into myTable(ID, value)values (7,-5.88); 1 row created. SQL> insert into myTable(ID, value)values (8,123.45); 1 row created. SQL> insert into myTable(ID, value)values (9,98.23); 1 row created. SQL> insert into myTable(ID, value)values (10,938.23); 1 row created. SQL> insert into myTable(ID, value)values (11,984.23); 1 row created. SQL> insert into myTable(ID, value)values (12,198.23); 1 row created. SQL> insert into myTable(ID, value)values (13,928.87); 1 row created. SQL> insert into myTable(ID, value)values (14,25.37); 1 row created. SQL> insert into myTable(ID, value)values (15,918.3); 1 row created. SQL> insert into myTable(ID, value)values (16,9.23); 1 row created. SQL> insert into myTable(ID, value)values (17,8.23); 1 row created. SQL> SQL> select * from myTable

 2  /
       ID      VALUE

----------
        1          9
        2       2.11
        3       3.44
        4      -4.21
        5         10
        6          3
        7      -5.88
        8     123.45
        9      98.23
       10     938.23
       11     984.23
       ID      VALUE

----------
       12     198.23
       13     928.87
       14      25.37
       15      918.3
       16       9.23
       17       8.23

17 rows selected. SQL> SQL> SQL> SELECT STATS_ONE_WAY_ANOVA(id, value, "MEAN_SQUARES_BETWEEN") FROM myTable; STATS_ONE_WAY_ANOVA(ID,VALUE,"MEAN_SQUARES_BETWEEN")


                                         159867.682

SQL> SQL> SQL> SQL> -- clean the table SQL> drop table myTable

 2  /

Table dropped.</source>


For mean square (MS) within: STATS_ONE_WAY_ANOVA(expr1, expr2, "SUM_SQUARES_WITHIN")

   <source lang="sql">

SQL> SQL> SQL> -- create demo table SQL> create table myTable(

 2    id           NUMBER(2),
 3    value        NUMBER(6,2)
 4  )
 5  /

Table created. SQL> SQL> -- prepare data SQL> insert into myTable(ID, value)values (1,9); 1 row created. SQL> insert into myTable(ID, value)values (2,2.11); 1 row created. SQL> insert into myTable(ID, value)values (3,3.44); 1 row created. SQL> insert into myTable(ID, value)values (4,-4.21); 1 row created. SQL> insert into myTable(ID, value)values (5,10); 1 row created. SQL> insert into myTable(ID, value)values (6,3); 1 row created. SQL> insert into myTable(ID, value)values (7,-5.88); 1 row created. SQL> insert into myTable(ID, value)values (8,123.45); 1 row created. SQL> insert into myTable(ID, value)values (9,98.23); 1 row created. SQL> insert into myTable(ID, value)values (10,938.23); 1 row created. SQL> insert into myTable(ID, value)values (11,984.23); 1 row created. SQL> insert into myTable(ID, value)values (12,198.23); 1 row created. SQL> insert into myTable(ID, value)values (13,928.87); 1 row created. SQL> insert into myTable(ID, value)values (14,25.37); 1 row created. SQL> insert into myTable(ID, value)values (15,918.3); 1 row created. SQL> insert into myTable(ID, value)values (16,9.23); 1 row created. SQL> insert into myTable(ID, value)values (17,8.23); 1 row created. SQL> SQL> select * from myTable

 2  /
       ID      VALUE

----------
        1          9
        2       2.11
        3       3.44
        4      -4.21
        5         10
        6          3
        7      -5.88
        8     123.45
        9      98.23
       10     938.23
       11     984.23
       ID      VALUE

----------
       12     198.23
       13     928.87
       14      25.37
       15      918.3
       16       9.23
       17       8.23

17 rows selected. SQL> SQL> SQL> SELECT STATS_ONE_WAY_ANOVA(id, value, "SUM_SQUARES_WITHIN") FROM myTable; STATS_ONE_WAY_ANOVA(ID,VALUE,"SUM_SQUARES_WITHIN")


SQL> SQL> SQL> SQL> -- clean the table SQL> drop table myTable

 2  /

Table dropped.</source>


For significance level: STATS_ONE_WAY_ANOVA(expr1, expr2, "SIG")

   <source lang="sql">

SQL> SQL> SQL> -- create demo table SQL> create table myTable(

 2    id           NUMBER(2),
 3    value        NUMBER(6,2)
 4  )
 5  /

Table created. SQL> SQL> -- prepare data SQL> insert into myTable(ID, value)values (1,9); 1 row created. SQL> insert into myTable(ID, value)values (2,2.11); 1 row created. SQL> insert into myTable(ID, value)values (3,3.44); 1 row created. SQL> insert into myTable(ID, value)values (4,-4.21); 1 row created. SQL> insert into myTable(ID, value)values (5,10); 1 row created. SQL> insert into myTable(ID, value)values (6,3); 1 row created. SQL> insert into myTable(ID, value)values (7,-5.88); 1 row created. SQL> insert into myTable(ID, value)values (8,123.45); 1 row created. SQL> insert into myTable(ID, value)values (9,98.23); 1 row created. SQL> insert into myTable(ID, value)values (10,938.23); 1 row created. SQL> insert into myTable(ID, value)values (11,984.23); 1 row created. SQL> insert into myTable(ID, value)values (12,198.23); 1 row created. SQL> insert into myTable(ID, value)values (13,928.87); 1 row created. SQL> insert into myTable(ID, value)values (14,25.37); 1 row created. SQL> insert into myTable(ID, value)values (15,918.3); 1 row created. SQL> insert into myTable(ID, value)values (16,9.23); 1 row created. SQL> insert into myTable(ID, value)values (17,8.23); 1 row created. SQL> SQL> select * from myTable

 2  /
       ID      VALUE

----------
        1          9
        2       2.11
        3       3.44
        4      -4.21
        5         10
        6          3
        7      -5.88
        8     123.45
        9      98.23
       10     938.23
       11     984.23
       ID      VALUE

----------
       12     198.23
       13     928.87
       14      25.37
       15      918.3
       16       9.23
       17       8.23

17 rows selected. SQL> SQL> SQL> SELECT STATS_ONE_WAY_ANOVA(id, value, "SIG") FROM myTable; STATS_ONE_WAY_ANOVA(ID,VALUE,"SIG")


SQL> SQL> SQL> SQL> -- clean the table SQL> drop table myTable

 2  /

Table dropped.</source>


For within degrees of freedom (DF): STATS_ONE_WAY_ANOVA(expr1, expr2, "DF_WITHIN")

   <source lang="sql">

SQL> SQL> SQL> -- create demo table SQL> create table myTable(

 2    id           NUMBER(2),
 3    value        NUMBER(6,2)
 4  )
 5  /

Table created. SQL> SQL> -- prepare data SQL> insert into myTable(ID, value)values (1,9); 1 row created. SQL> insert into myTable(ID, value)values (2,2.11); 1 row created. SQL> insert into myTable(ID, value)values (3,3.44); 1 row created. SQL> insert into myTable(ID, value)values (4,-4.21); 1 row created. SQL> insert into myTable(ID, value)values (5,10); 1 row created. SQL> insert into myTable(ID, value)values (6,3); 1 row created. SQL> insert into myTable(ID, value)values (7,-5.88); 1 row created. SQL> insert into myTable(ID, value)values (8,123.45); 1 row created. SQL> insert into myTable(ID, value)values (9,98.23); 1 row created. SQL> insert into myTable(ID, value)values (10,938.23); 1 row created. SQL> insert into myTable(ID, value)values (11,984.23); 1 row created. SQL> insert into myTable(ID, value)values (12,198.23); 1 row created. SQL> insert into myTable(ID, value)values (13,928.87); 1 row created. SQL> insert into myTable(ID, value)values (14,25.37); 1 row created. SQL> insert into myTable(ID, value)values (15,918.3); 1 row created. SQL> insert into myTable(ID, value)values (16,9.23); 1 row created. SQL> insert into myTable(ID, value)values (17,8.23); 1 row created. SQL> SQL> select * from myTable

 2  /
       ID      VALUE

----------
        1          9
        2       2.11
        3       3.44
        4      -4.21
        5         10
        6          3
        7      -5.88
        8     123.45
        9      98.23
       10     938.23
       11     984.23
       ID      VALUE

----------
       12     198.23
       13     928.87
       14      25.37
       15      918.3
       16       9.23
       17       8.23

17 rows selected. SQL> SQL> SQL> SELECT STATS_ONE_WAY_ANOVA(id, value, "DF_WITHIN") FROM myTable; STATS_ONE_WAY_ANOVA(ID,VALUE,"DF_WITHIN")


                                       0

SQL> SQL> SQL> SQL> -- clean the table SQL> drop table myTable

 2  /

Table dropped.</source>


For within sum of squares (SS): STATS_ONE_WAY_ANOVA(expr1, expr2, "SUM_SQUARES_WITHIN")

   <source lang="sql">

SQL> SQL> SQL> -- create demo table SQL> create table myTable(

 2    id           NUMBER(2),
 3    value        NUMBER(6,2)
 4  )
 5  /

Table created. SQL> SQL> -- prepare data SQL> insert into myTable(ID, value)values (1,9); 1 row created. SQL> insert into myTable(ID, value)values (2,2.11); 1 row created. SQL> insert into myTable(ID, value)values (3,3.44); 1 row created. SQL> insert into myTable(ID, value)values (4,-4.21); 1 row created. SQL> insert into myTable(ID, value)values (5,10); 1 row created. SQL> insert into myTable(ID, value)values (6,3); 1 row created. SQL> insert into myTable(ID, value)values (7,-5.88); 1 row created. SQL> insert into myTable(ID, value)values (8,123.45); 1 row created. SQL> insert into myTable(ID, value)values (9,98.23); 1 row created. SQL> insert into myTable(ID, value)values (10,938.23); 1 row created. SQL> insert into myTable(ID, value)values (11,984.23); 1 row created. SQL> insert into myTable(ID, value)values (12,198.23); 1 row created. SQL> insert into myTable(ID, value)values (13,928.87); 1 row created. SQL> insert into myTable(ID, value)values (14,25.37); 1 row created. SQL> insert into myTable(ID, value)values (15,918.3); 1 row created. SQL> insert into myTable(ID, value)values (16,9.23); 1 row created. SQL> insert into myTable(ID, value)values (17,8.23); 1 row created. SQL> SQL> select * from myTable

 2  /
       ID      VALUE

----------
        1          9
        2       2.11
        3       3.44
        4      -4.21
        5         10
        6          3
        7      -5.88
        8     123.45
        9      98.23
       10     938.23
       11     984.23
       ID      VALUE

----------
       12     198.23
       13     928.87
       14      25.37
       15      918.3
       16       9.23
       17       8.23

17 rows selected. SQL> SQL> SQL> SELECT STATS_ONE_WAY_ANOVA(id, value, "SUM_SQUARES_WITHIN") FROM myTable; STATS_ONE_WAY_ANOVA(ID,VALUE,"SUM_SQUARES_WITHIN")


SQL> SQL> SQL> SQL> -- clean the table SQL> drop table myTable

 2  /

Table dropped.</source>