Excel 標準偏差、相関係数、線形回帰

標準偏差、相関係数、線形回帰の求め方。
手動計算とエクセル関数を使った場合。

分析基礎

・基本

結論は金額換算して分かりやすく。継続観察していくならKPIを設定する。

・サンプルの中で変数に属性を追加。

例)
来店率が下がっている顧客、上がっている顧客、特定の時期に上がる顧客等。
購入金額が高い顧客(Aランク)、低い顧客(Bランク)など。

これにより、顧客のニーズの仮説や、分析から排除の必要性等を検討する。

・次に他のサンプルと比較。

サンプル１(会員等)での特徴(標準偏差や中央値等)がサンプル２(非会員等)と比較してどう違うか。そうすることで、サンプル１はサンプル２と比べてはどのような特徴があるのか分かる。

・複数のサンプルでの比較

会員と商品の関係での特徴は、非会員と商品の関係と比較してどう違うか。
会員は特定商品のリピート率が高い等。

SQLで相関係数(傾き)

標準偏差や分散などは標準の関数であるが、相関係数はない。
集計用に傾きだけ欲しかったので、とりあえずSQLで求めた

テーブル名:t
結果:17.2482570475901

select t4.cor * ( t4.y_dev / t4.x_dev ) from (

	select avg(t3.zz) / ( avg(t3.x_dev) * avg(t3.y_dev) ) as cor , avg(t3.x_dev) as x_dev , avg(t3.y_dev) as y_dev
	from (

		select ( t2.xx * t2.yy ) as zz , t2.x_dev , t2.y_dev
		from (

			select
				t.x_m , 
				t.y_m , 
				t.x_m - ( select avg(x_m) from t ) as xx , 
				t.y_m - ( select avg(y_m) from t ) as yy , 
				( select stdevp(x_m) from t ) as x_dev , 
				( select stdevp(y_m) from t ) as y_dev
			from t 

		) as t2

	) as t3

) as t4

select t4.cor * ( t4.y_dev / t4.x_dev ) from (

select avg(t3.zz) / ( avg(t3.x_dev) * avg(t3.y_dev) ) as cor , avg(t3.x_dev) as x_dev , avg(t3.y_dev) as y_dev

from (

select ( t2.xx * t2.yy ) as zz , t2.x_dev , t2.y_dev

from (

select

t.x_m ,

t.y_m ,

t.x_m - ( select avg(x_m) from t ) as xx ,

t.y_m - ( select avg(y_m) from t ) as yy ,

( select stdevp(x_m) from t ) as x_dev ,

( select stdevp(y_m) from t ) as y_dev

from t

) as t2

) as t3

) as t4

傾きで利用したストアド

declare cur cursor for
select distinct カード番号_s from tbl

open cur

declare @id bigint
fetch next from cur into @id

while @@fetch_status = 0
begin

if 7 <> (select count(*) from tbl where カード番号_s = @id)
goto brk

select
@id,
t4.cor * ( t4.y_dev / t4.x_dev )
from
(
	select
	avg(t3.zz) / ( avg(t3.x_dev) * avg(t3.y_dev) ) as cor , 
	avg(t3.x_dev) as x_dev , 
	avg(t3.y_dev) as y_dev
	from
	(
		select
		(t2.xx * t2.yy) as zz , 
		t2.x_dev , 
		t2.y_dev
		from
		(
			select
			集計期間月_m - ( select avg(集計期間月_m) from tbl where カード番号_s = @id ) as xx , 
			買上金額_m - ( select avg(買上金額_m) from tbl where カード番号_s = @id ) as yy , 
			( select stdevp(集計期間月_m) from tbl where カード番号_s = @id ) as x_dev , 
			( select stdevp(買上金額_m) from tbl where カード番号_s = @id ) as y_dev
			from tbl
			where カード番号_s = @id 
		) 
		as t2
	) 
	as t3
) 
as t4

brk:
fetch next from cur into @id

end

close cur
deallocate cur

declare cur cursor for

select distinct カード番号_s from tbl

open cur

declare @id bigint

fetch next from cur into @id

while @@fetch_status = 0

begin

if 7 <> (select count(*) from tbl where カード番号_s = @id)

goto brk

select

@id,

t4.cor * ( t4.y_dev / t4.x_dev )

from

(

select

avg(t3.zz) / ( avg(t3.x_dev) * avg(t3.y_dev) ) as cor ,

avg(t3.x_dev) as x_dev ,

avg(t3.y_dev) as y_dev

from

(

select

(t2.xx * t2.yy) as zz ,

t2.x_dev ,

t2.y_dev

from

(

select

集計期間月_m - ( select avg(集計期間月_m) from tbl where カード番号_s = @id ) as xx ,

買上金額_m - ( select avg(買上金額_m) from tbl where カード番号_s = @id ) as yy ,

( select stdevp(集計期間月_m) from tbl where カード番号_s = @id ) as x_dev ,

( select stdevp(買上金額_m) from tbl where カード番号_s = @id ) as y_dev

from tbl

where カード番号_s = @id

)

as t2

)

as t3

)

as t4

brk:

fetch next from cur into @id

end

close cur

deallocate cur

SQl Server Management Studioでの結果をそのままエクセルに貼り付けたかったので、
以下のような書き方で解決した。

declare @mydate nchar(10)
set @mydate = '2017/1/1'

select 
(select sum(買上金額_m) from tb4 where 集計期間月_d = @mydate),
(select sum(買上金額_m) from tb4 where 集計期間月_d = @mydate and 買上回数_i <= 1),
(select sum(買上金額_m) from tb4 where 集計期間月_d = @mydate and 買上回数_i >= 2 and 買上回数_i <= 4),
(select sum(買上金額_m) from tb4 where 集計期間月_d = @mydate and 買上回数_i >= 5 and 買上回数_i <= 7),
(select sum(買上金額_m) from tb4 where 集計期間月_d = @mydate and 買上回数_i >= 8 and 買上回数_i <= 10),
(select sum(買上金額_m) from tb4 where 集計期間月_d = @mydate and 買上回数_i >= 11 and 買上回数_i <= 15),
(select sum(買上金額_m) from tb4 where 集計期間月_d = @mydate and 買上回数_i >= 16 and 買上回数_i <= 20),
(select sum(買上金額_m) from tb4 where 集計期間月_d = @mydate and 買上回数_i >= 21)

declare @mydate nchar(10)

set @mydate = '2017/1/1'

select

(select sum(買上金額_m) from tb4 where 集計期間月_d = @mydate),

(select sum(買上金額_m) from tb4 where 集計期間月_d = @mydate and 買上回数_i <= 1),

(select sum(買上金額_m) from tb4 where 集計期間月_d = @mydate and 買上回数_i >= 2 and 買上回数_i <= 4),

(select sum(買上金額_m) from tb4 where 集計期間月_d = @mydate and 買上回数_i >= 5 and 買上回数_i <= 7),

(select sum(買上金額_m) from tb4 where 集計期間月_d = @mydate and 買上回数_i >= 8 and 買上回数_i <= 10),

(select sum(買上金額_m) from tb4 where 集計期間月_d = @mydate and 買上回数_i >= 11 and 買上回数_i <= 15),

(select sum(買上金額_m) from tb4 where 集計期間月_d = @mydate and 買上回数_i >= 16 and 買上回数_i <= 20),

(select sum(買上金額_m) from tb4 where 集計期間月_d = @mydate and 買上回数_i >= 21)