查看: 1904|回復: 0

[Mysql數據庫] mysql行列轉置并計算均值和方差

3萬

主題

3萬

帖子

10萬

積分

管理員

Rank: 9Rank: 9Rank: 9

積分
100197
發表于 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


回復

使用道具 舉報