| set hive.cbo.enable=false; |
| |
| drop table t_n6; |
| create table t_n6 (id int,px int,y decimal,x decimal); |
| |
| insert into t_n6 values (101,1,1,1); |
| insert into t_n6 values (201,2,1,1); |
| insert into t_n6 values (301,3,1,1); |
| insert into t_n6 values (401,4,1,11); |
| insert into t_n6 values (501,5,1,null); |
| insert into t_n6 values (601,6,null,1); |
| insert into t_n6 values (701,6,null,null); |
| insert into t_n6 values (102,1,2,2); |
| insert into t_n6 values (202,2,1,2); |
| insert into t_n6 values (302,3,2,1); |
| insert into t_n6 values (402,4,2,12); |
| insert into t_n6 values (502,5,2,null); |
| insert into t_n6 values (602,6,null,2); |
| insert into t_n6 values (702,6,null,null); |
| insert into t_n6 values (103,1,3,3); |
| insert into t_n6 values (203,2,1,3); |
| insert into t_n6 values (303,3,3,1); |
| insert into t_n6 values (403,4,3,13); |
| insert into t_n6 values (503,5,3,null); |
| insert into t_n6 values (603,6,null,3); |
| insert into t_n6 values (703,6,null,null); |
| insert into t_n6 values (104,1,4,4); |
| insert into t_n6 values (204,2,1,4); |
| insert into t_n6 values (304,3,4,1); |
| insert into t_n6 values (404,4,4,14); |
| insert into t_n6 values (504,5,4,null); |
| insert into t_n6 values (604,6,null,4); |
| insert into t_n6 values (704,6,null,null); |
| insert into t_n6 values (800,7,1,1); |
| |
| |
| explain select px,var_pop(x),var_pop(y),corr(y,x),covar_samp(y,x),covar_pop(y,x),regr_count(y,x),regr_slope(y,x), |
| regr_intercept(y,x), regr_r2(y,x), regr_sxx(y,x), regr_syy(y,x), regr_sxy(y,x), regr_avgx(y,x), regr_avgy(y,x), regr_count(y,x) |
| from t_n6 group by px order by px; |
| |
| select px, |
| round( var_pop(x),5), |
| round( var_pop(y),5), |
| round( corr(y,x),5), |
| round( covar_samp(y,x),5), |
| round( covar_pop(y,x),5), |
| regr_count(y,x), |
| round( regr_slope(y,x),5), |
| round( regr_intercept(y,x),5), |
| round( regr_r2(y,x),5), |
| round( regr_sxx(y,x),5), |
| round( regr_syy(y,x),5), |
| round( regr_sxy(y,x),5), |
| round( regr_avgx(y,x),5), |
| round( regr_avgy(y,x),5), |
| round( regr_count(y,x),5) |
| from t_n6 group by px order by px; |
| |
| |
| select id,regr_count(y,x) over (partition by px) from t_n6 order by id; |