mysqlでクロス集計を取りたいが、sqlが分からない。

テーブル名は、収穫で下記のデータが入っている。
日付　id　size
160201　1　S
160201　2　S
160201　3　M
160202　4　L
160202　5　L
160203　6　L
160204　7　S


これのクロス集計を下記の様に作りたい。
テーブル名は、crossとする。
日付　S　M　L　合計
160201　2　1　　3
160202　　　2　2
160203　　　1　1
160204　1　　　1

済みません、sqlの回答をお願いします。

No.5 ベストアンサー 回答者： yambejp 回答日時： 2016/02/08 13:05

＞group byでユニークになっているのですね。

勘違いしてはいけないのは、データが単にユニークなだけで、ユニークな属性は
ついていないということです。
もしさらにそのテーブルからリレーションするのであれば、なんらかの
属性はつけた方がよいかもしれません

>もう一つ質問ですが。ここで作ったテーブルを別のテーブルを日付でjoin
>したいのですが。この時の日付はインデックスの指定が要るのでしょうか。

であれば「create table xt」するときに適正なインデックスをつくっておいた方がよいでしょう。
もちろん後付けでalter tableで追加することもできます

この回答へのお礼

有難う御座いました。
矢張り、インデックスは必要だと言う事ですね。

分かりました。これで、全体がすっきりと見えて来ました。
これで最後です。最後迄有難う御座いました。

お礼日時：2016/02/08 13:14

No.4 回答者： yambejp 回答日時： 2016/02/08 12:23

>実はテーブル作成に付いては、No.2の回答で満足しています。

>後出しで済みませんが、実はこのNo.2のテーブルの日付にunique
>の設定でインデックスを設けたいのですが。

uniqueつけなくてもgroup byのキーになっているのユニークですがどうしてもやりたい？
そういう場合はcreate tableで先にテーブルをつくっておくだけです

//元データ
create table hoge (日付 date,id int,size varchar(10));
insert into hoge values('2016-02-01',1,'S'),('2016-02-01',2,'S'),('2016-02-01',3,'M'),('2016-02-02',4,'L'),('2016-02-02',5,'L'),('2016-02-03',6,'L'),('2016-02-04',7,'S');

//テーブル作成
create table xt(日付 date not null unique,S int,M int,L int ,合計 int);

//単純なデータ投入
insert into xt
select 日付,sum(size='S'),sum(size='M'),sum(size='L'),=count(*)
from hoge
group by 日付;

//ユニークな日付が競合したときにアップデートする
insert into xt
select 日付,@s:=sum(size='S'),@m:=sum(size='M'),@l:=sum(size='L'),@sum:=count(*)
from hoge
group by 日付
on duplicate key update S=@s,M=@m,L=@l,合計=@sum

この回答へのお礼

有難う御座います。
group byでユニークになっているのですね。

分かりました。

もう一つ質問ですが。ここで作ったテーブルを別のテーブルを日付でjoin
したいのですが。この時の日付はインデックスの指定が要るのでしょうか。
そこの部分が心配だったので。

どうなんでしょうか。
済みません。回答願います。

お礼日時：2016/02/08 12:54

No.3 回答者： yambejp 回答日時： 2016/02/08 11:14

>テーブル作成クエリーでテーブルを作りたい

「テーブル作成クエリー」についてはよくわからないので
どこまで自動化できるかによりますね。

むしろピボットテーブルを先行して作っておけば比較的楽に処理はできると思いますが・・・
例えばこう

create table hoge (日付 date,id int,size varchar(10));
insert into hoge values('2016-02-01',1,'S'),('2016-02-01',2,'S'),('2016-02-01',3,'M'),('2016-02-02',4,'L'),('2016-02-02',5,'L'),('2016-02-03',6,'L'),('2016-02-04',7,'S');

//ピボット
create table pt(size varchar(10),S int,M int,L int,合計 int);
insert into pt values('S',1,0,0,1),('M',0,1,0,1),('L',0,0,1,1);

//実行
select 日付,sum(S) S,sum(M) M,sum(L) L ,sum(合計) 合計
from hoge inner join pt using(size)
group by 日付;

これをプロシージャをつかって半自動化することは可能ですが、ちょっと処理が面倒です

この回答へのお礼

有難う御座います。

実はテーブル作成に付いては、No.2の回答で満足しています。
後出しで済みませんが、実はこのNo.2のテーブルの日付にunique
の設定でインデックスを設けたいのですが。

出来るのでしょうか。これで最後になると思いますが。
宜しくお願いします。

お礼日時：2016/02/08 11:56

No.2 回答者： Ogre7077 回答日時： 2016/02/08 10:37

create table クロス集計

as select
日付
, sum(case size when 'S' then 1 else 0 end) as S
, sum(case size when 'M' then 1 else 0 end) as M
, sum(case size when 'L' then 1 else 0 end) as L
, count(*) as 合計
from 収穫
group by 日付

No.1 回答者： yambejp 回答日時： 2016/02/08 09:49

select 日付,sum(size='S') AS S,sum(size='M') AS M,sum(size='L') AS L,count(*) AS 合計

from テーブル
group by 日付;

この回答へのお礼

有難う御座います。

実はこの内容で、テーブル作成クエリーでテーブルを作りたいのですが。出来ないのでしょうか。

宜しく回答願います。

お礼日時：2016/02/08 09:56