SQLの縦持ち(unpivot)と横持ち(pivot)関数

2022年8月3日 22:57

表記件、Teradataで横持ちテーブルを縦持ちにする際の関数、反対に縦持ちテーブルを横持ちにする際の関数を使ってみました。まずは以下のような横持ちテーブルを用意します。

作成SQLは以下の通りです。

 /*サンプルデータのテーブル*/ 
 -- drop table jumbo.pivo; 
create table jumbo.pivo ( 
顧客番号 integer, 
地域 varchar(2) character set unicode, 
商品a integer, 
商品b integer, 
商品c integer 
) primary index(顧客番号) 
; 

insert into jumbo.pivo values ('1','関東','8','8','3'); 
insert into jumbo.pivo values ('2','関東','9','1','1'); 
insert into jumbo.pivo values ('3','関西','6','2','6'); 
insert into jumbo.pivo values ('4','関西','7','0','8'); 
insert into jumbo.pivo values ('5','関東','8','0','8'); 
insert into jumbo.pivo values ('6','関西','8','6','9'); 
insert into jumbo.pivo values ('7','関西','3','0','1'); 
insert into jumbo.pivo values ('8','関西','0','9','8'); 
insert into jumbo.pivo values ('9','関東','5','3','9'); 
insert into jumbo.pivo values ('10','九州','5','6','4');

縦持ち(unpivot)

ご覧の通り横持ちされたデータで、商品の3列を縦持ちしたいとしましょう。3つ程度の列であればunionしても良いですが、数が多くなると大変です。まずはシステムテーブルから対象テーブルの列情報を取得します。

 /*unpivotSQL用の文字列取得*/ 
select
columnname||', ' as aa 
from dbc.columnsv
where databasename='jumbo'
and tablename='pivo'
;

aaという列で取得したリストから、「縦に溶解したい列のみ」を、以下のSQLに貼り付けます。要はこの列が縦持ちの対象ですよということをここで指定します。また[売上数量]は、実際の数値データが縦持ちされて入る列であり、[商品]が元々は列名だった商品区分が入る列です。ご自身で利用される際は読み替えください。

 /*unpivot(縦持ち)処理*/ 			
 -- drop table jumbo.unpivo; 			
create table jumbo.unpivo as ( 			
	select * from jumbo.pivo unpivot ( 		
		売上数量 for 商品 in ( 	
			 /*aaの結果をここに、最後のカンマはとる*/ 
			商品a, 
			商品b, 
			商品c 
		) 	
	) a1 		
) with data 			
;

結果は以下のように縦持ちされます。保持する必要がなければcreate table とwith dataの行は不要です。

横持ち(pivot)

続いて、縦持ちされたデータを横持ちに戻します。以下がそのSQLです。

 /*pivot(横持ち)処理*/ 		
select 		
顧客番号, 		
地域, 		
 /*bbの結果をここに、最後のカンマはとる*/ 		
商品a_x as 商品a, 		
商品b_x as 商品b, 		
商品c_x as 商品c 		
from jumbo.unpivo pivot ( 		
	max(売上数量) as x for (	
	商品 	
	) in ( 	
		 /*ccの結果をここに、最後のカンマはとる*/ 
		 '商品a' as 商品a, 
		 '商品b' as 商品b, 
		 '商品c' as 商品c 
	)	
) a1 		
;

[max(売上数量) as x]と記載がある部分は集約関数を指定します。またカンマ区切りで集約関数を複数指定できます。そしてfor句の中にある[商品]は、横に展開したい列を指定します。in句の中には、[商品]の中の値に応じてどのように横展開するかを記述します。この結果が一番外側のSQL(a1の外側)に出力されるのですが、ここで気にしなければならない点が2つです。
1点目は、元テーブルにある売上数量と商品以外の入力列でgroup byされるということです。ここでは顧客番号と地域でgroup byされます。このデータでは発生してないですが、仮に顧客番号に対して複数の地域が存在していた場合、顧客番号と地域の組み合わせごとにデータは集約され、顧客番号から見た際には泣き別れになります。
そして2点目は、in句の中で指定した列名に、アンダースコア(_)、そしてmax(売上数量)のエイリアスとして指定したxがついて列名として出力されるということです。仮に集約関数を2つ指定していたら、_xと_yの列が3列ずつ作成されることになります。複数の集約関数を使う際には便利ですが、集約関数が1つの場合には_xが余計です。全部に対して列名を変更するのも、3つならいいですがたくさんあると手間です。

そのため、横持ちSQLを書く前に、まず元テーブルに対して以下のようなSQLを投げます。要は値のバリエーションに対して、ひと手間加えたリストを取得し、それを上述のSQLに貼り付けてあげています。このSQLで得られたbb列、cc列を上述のSQLに貼り付けることにより、割と手間をかけずにpivotのSQLを作成できます。

 /*pivotSQL用の文字列取得*/ 
select 
商品, 
商品||'_x as '||商品||', ' as bb,  
 ' '''||商品||''' as '||商品||', ' as cc 
from jumbo.unpivo 
group by 1,2,3 
order by 1 
;

実行結果は冒頭の表と同じになるため、割愛します。より詳細な使い方はマニュアルを参照ください。

unpivot関数: 縦持ちにする

pivot関数: 横持ちにする

以上です。

#sql #teradata #pivot #unpivot

///

SQLの縦持ち(unpivot)と横持ち(pivot)関数

縦持ち(unpivot)

横持ち(pivot)

いいなと思ったら応援しよう！