Oracle PL/SQL Tutorial/Linear Regression Functions/STATS CROSSTAB
Содержание
- 1 For chi-square degrees of freedom: STATS_CROSSTAB(expr1, expr2, "CHISQ_DF")
- 2 For chi-square significance level: STATS_CROSSTAB(expr1, expr2, "CHISQ_SIG")
- 3 For chi-square value: STATS_CROSSTAB(expr1, expr2, "CHISQ_OBS")
- 4 For other related test statistics: STATS_CROSSTAB(expr1, expr2, "PHI_COEFFICIENT")
- 5 STATS_CROSSTAB(expr1, expr2, "COHENS_K")
- 6 STATS_CROSSTAB(expr1, expr2, "CONT_COEFFICIENT")
- 7 STATS_CROSSTAB(expr1, expr2, "CRAMERS_V")
For chi-square degrees of freedom: STATS_CROSSTAB(expr1, expr2, "CHISQ_DF")
<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_CROSSTAB(value, id, "CHISQ_DF") FROM myTable; STATS_CROSSTAB(VALUE,ID,"CHISQ_DF")
256
SQL> SQL> SQL> -- clean the table SQL> drop table myTable
2 /
Table dropped.</source>
For chi-square significance level: STATS_CROSSTAB(expr1, expr2, "CHISQ_SIG")
<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_CROSSTAB(value, id, "CHISQ_SIG") FROM myTable; STATS_CROSSTAB(VALUE,ID,"CHISQ_SIG")
.239713886
SQL> SQL> SQL> -- clean the table SQL> drop table myTable
2 /
Table dropped. SQL></source>
For chi-square value: STATS_CROSSTAB(expr1, expr2, "CHISQ_OBS")
This function takes in two nominal values and returns a value based on the third argument.
<source lang="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_CROSSTAB(value, id, "CHISQ_OBS") FROM myTable; STATS_CROSSTAB(VALUE,ID,"CHISQ_OBS")
271.647059
SQL> SQL> SQL> -- clean the table SQL> drop table myTable
2 /
Table dropped. SQL></source>
<source lang="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_CROSSTAB(value, id, "PHI_COEFFICIENT") FROM myTable; STATS_CROSSTAB(VALUE,ID,"PHI_COEFFICIENT")
3.997404
SQL> SQL> SQL> -- clean the table SQL> drop table myTable
2 /
Table dropped.</source>
STATS_CROSSTAB(expr1, expr2, "COHENS_K")
<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_CROSSTAB(value, id, "COHENS_K") FROM myTable; STATS_CROSSTAB(VALUE,ID,"COHENS_K")
-.01048951
SQL> SQL> SQL> -- clean the table SQL> drop table myTable
2 /
Table dropped.</source>
STATS_CROSSTAB(expr1, expr2, "CONT_COEFFICIENT")
<source lang="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_CROSSTAB(value, id, "CONT_COEFFICIENT") FROM myTable; STATS_CROSSTAB(VALUE,ID,"CONT_COEFFICIENT")
.97010543
SQL> SQL> SQL> -- clean the table SQL> drop table myTable
2 /
Table dropped.</source>
STATS_CROSSTAB(expr1, expr2, "CRAMERS_V")
<source lang="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_CROSSTAB(value, id, "CRAMERS_V") FROM myTable; STATS_CROSSTAB(VALUE,ID,"CRAMERS_V")
.999351
SQL> SQL> SQL> -- clean the table SQL> drop table myTable
2 /
Table dropped.</source>