SQLで縦横を変換したい

Question

下記の変更前テーブルから変更後テーブルへ変換できるSQLを書きたいのですが、
書き方が分からず、止まってしまっています。

おわかりの方、お知見を拝借させていだだけませんでしょうか。
よろしくお願い致します。

変更前テーブル

term	team	sales	budget	ratio
1Q	teamA	100	200	0.5
2Q	teamA	100	300	0.3333
3Q	teamA	100	300	0.3333
4Q	teamA	100	200	0.5

変更後テーブル

|item|team|1Q|2Q|3Q|4Q|
|:--|:--:|:--:|:--:|--:|
|sales|teamA|100|100|100|100|
|budget|teamA|200|300|300|200|
|ratio|teamA|0.5|0.3333|0.3333|0.5|

Comments

[ahodana]

https://teratail.com/questions/17179

Answer 1

Treasure DataですとUPDATEは使えません。
一般的なSQLであれば、変更後テーブルのうちitem,teamだけをガワで登録しておいて
後からその２カラムを指定してUPDATE、するという手が使えますが、TDは使えません。

こういう場合はQuaterごとの中間テーブルを作ります。
__ //ちなみに、TDだと数字から始まるカラム名は指定できないので1Q⇒Q1と直しています。 __

--中間テーブルのCREATE
create table after_q1(
item VARCHAR
,team VARCHAR
,value DOUBLE --SALESとBUDGETは整数ですが、Ratioは浮動小数型。型は適宜かえて。
)
--（中略）
create table after_q4(
item VARCHAR
,team VARCHAR
,value DOUBLE
)

--中間テーブルにQuaterごとのレコードを登録
insert into after_q1(item,team,value)
select 'sales', team, sales from before where term = '1Q' union
select 'budget', team, budget from before where term = '1Q'  union 
select 'ratio', team, ratio from before where term = '1Q' 
--(中略)
insert into after_q4(item,team,value)
select 'sales', team, sales from before where term = '4Q' union 
select 'budget', team, budget from before where term = '4Q'  union 
select 'ratio', team, ratio from before where term = '4Q' 

--最後にJOINして、TDにエクスポート
select 
   q1.item
  ,q1.team
  ,q1.value as Q1
  ,q2.value as Q2
  ,q3.value as Q3
  ,q4.value as Q4
from 
  after_q1 as q1
  left join after_q2 as q2 on q1.item = q2.item and q1.team = q2.team
  left join after_q3 as q3 on q1.item = q3.item and q1.team = q3.team
  left join after_q4 as q4 on q1.item = q4.item and q1.team = q4.team

Answer 2

テーブルのアイテム（項目名）をデータにするのですか？
データは上記の4件のみですか？
まさかcreate tableから作るんでしょうか？

Comments

[mo102u]

変更前データを作成する別途テーブルが存在します。
実データは4件のみではありません。
create_dataのカラムデータを元に四半期ごとに合算した変更前データを作成しています。

Answer 3

ピボットテーブルを別途用意して無理やり縦横変換することは可能ですが
基本的に1Q~4Qのように型が違うもの（おそらくintとdouble）を
一緒に表示するのはデータの持ち方としておかしいと思います

また命題のデータはteamが縦横変換されておらずピボットになっていません。

pivotを使ったsample

• テーブル作成

/* 元データ */
create table tbl(term varchar(10),team varchar(10),sales int, budget int,ratio double);
insert into tbl values
('1Q','teamA',100,200,0.5),
('2Q','teamA',100,300,0.3333),
('3Q','teamA',100,300,0.3333),
('4Q','teamA',100,200,0.5);

/* pivot作成 */
create table pivot(term varchar(10),1Q int,2Q int,3Q int,4Q int);
insert into pivot select term,term='1Q' as 1q,term='2Q' as 2q,term='3Q' as 3q,term='4Q' as 4q from tbl;

/* 横方向のデータ保持 */
create table cols(col varchar(10));
insert into cols values('sales'),('budget'),('ratio');

• とりあえず結合

select 
t1.*,
case t1.col 
when 'sales' then t2.sales
when 'budget' then t2.budget
when 'ratio' then t2.ratio
end as val
from (select * from pivot,cols) as t1 inner join 
tbl as t2 on t1.term =t2.term ;

• 上記をもとに集計

select col
,sum(1Q*val) as 1Q
,sum(2Q*val) as 2Q
,sum(3Q*val) as 3Q
,sum(4Q*val) as 4Q
from(
select 
t1.*,
case t1.col 
when 'sales' then t2.sales
when 'budget' then t2.budget
when 'ratio' then t2.ratio
end as val
from (select * from pivot,cols) as t1 inner join 
tbl as t2 on t1.term =t2.term 
) sub
group by col;

※前回も下記来ましたが結果としてteamを縦に書くのはロジックとしておかしいので
やっていません。

Comments

[mo102u]

実データはTreasureData内にあるため、Prestoを使用しています。
その内容を元にご回答いただけそうでしたら、何卒よろしくお願い致します。

[yambejp]

追記してあるとおり、pivotテーブル自体はありきたりの考え方なので
その実装は容易だとおもいます

Answer 4

PIVOT を利用してはいかがでしょう？

以下の記事は SQL Server の例ですが MySQL でも同様なことはできるのではないかと思います。

PIVOT の使用
http://surferonwww.info/BlogEngine/post/2010/08/07/Use-of-PIVOT.aspx

ハズレでしたらすみませんでした。

Comments

[SVC34]

pivotはSQL Server独自の機能です

[退会済みユーザー]

それは失礼しました