请选择 进入手机版 | 继续访问电脑版
查看: 863|回复: 0

[Mysql数据库] mysql行列转置并计算均值和方差

3万

主题

3万

帖子

10万

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
100167
发表于 2015-12-2 18:44:47

mysql行列转置并计算均值和方差,有需要的朋友可以参考下。


select aa.*, round(sum((bb.data_-aa.平均值)* (bb.data_-aa.平均值))/count(bb.month_),4) 平方差 from
(select kpi,kpi2,
sum(case when month_=201501 then data_ else 0 end) '201501',
sum(case when month_=201502 then data_ else 0 end) '201502',
sum(case when month_=201503 then data_ else 0 end) '201503',
avg(data_) 平均值
from (
select 201501 month_,1 data_,'上海' kpi,'杭州' kpi2
union all
select 201501 month_,2 data_,'上海' kpi,'武汉' kpi2
union all
select 201502 month_,5 data_,'上海' kpi,'武汉' kpi2
union all
select 201501 month_,3 data_,'上海' kpi,'合肥' kpi2
union all
select 201501 month_,4 data_,'合肥' kpi,'上海' kpi2
union all
select 201502 month_,3 data_,'合肥' kpi,'上海' kpi2
union all
select 201503 month_,2 data_,'合肥' kpi,'上海' kpi2) a
group by kpi,kpi2 ) aa,
(
select 201501 month_,1 data_,'上海' kpi,'杭州' kpi2
union all
select 201501 month_,2 data_,'上海' kpi,'武汉' kpi2
union all
select 201502 month_,5 data_,'上海' kpi,'武汉' kpi2
union all
select 201501 month_,3 data_,'上海' kpi,'合肥' kpi2
union all
select 201501 month_,4 data_,'合肥' kpi,'上海' kpi2
union all
select 201502 month_,3 data_,'合肥' kpi,'上海' kpi2
union all
select 201503 month_,2 data_,'合肥' kpi,'上海' kpi2
) bb
where aa.kpi=bb.kpi and aa.kpi2=bb.kpi2

group by aa.kpi,aa.kpi2


回复

使用道具 举报