単一列を複数行に、複数行を単一列に

文章1は「僕は イカの 刺身が 好き です」、文章2は「僕は 卵の 黄身が 好き」.. こんな文章があるとします。各文章がパーティション、スペースが区切り文字、文書の頭からの順序では、ともに「僕は」が1番目の単語です。この文章の中の単語を各行に、つまり縦に分解していくにはどうしたらいいでしょうか。また合わせて反対に、単一の行、単一の列にまとめたいケースを考えます。やりたいことは、このときに文書1は5行に分解する、文書2は4行に分解するといったことと、その結果、つまり複数行に分かれた各文書を1行1列に戻すということです。ここで面倒なのは、ある行は5行、ある行は4行と、個数が可変となってしまうことです。Teradataの場合、これはxmlaggという関数と、regexp_split_to_tableという関数で実施します。

---

行を列にする

まず行を単一列にまとめることを考えます。xmlaggという関数が使えます。xmlとは何も関係ない使い方なのですが、以前PostgresqlでListaggという関数を使い、Teradataでも同じような関数がないのかなと調べていたら、これを使えというのをネットで見つけました。以下がサンプルで、元データは年、月、日となっており、これをカレンダーテーブルから取得しています。2年分、約365*2で731行です(今年はうるう年なんですね)。これを年月ごとに1行にします。ポイントとしては年と月をgroup byに入れており、これがパーティションの役目をします。そして順序はxmlaggのパラメーターにあるorder by句にて指定しています。入力列は日ですが、もともとが数値型なので文字型に変換し、後々の区切り文字用にカンマをセットしています。ただ、このままはxml形式のデータなので、さらに文字型に変換し、最後のカンマをtrimしてあげれば完成です。月によって日数は異なりますが、単一列に収まって、12か月2年で24行にまとまりました。

 /*行を列にする*/
select
年,
月,
trim(trailing ',' from
cast(
xmlagg(to_char(日)||',' order by 日) --xmlaggの関数出力、ここまではxmlデータ
as varchar(10000)) --文字型に変換
) --最後のカンマを削る
as 日一覧
from (
    select
    year_of_calendar as 年,
    month_of_year as 月,
    day_of_month as 日
    from sys_calendar.calendar
    where year_of_calendar in (2024,2025)
) a1
group by 1,2
order by 1,2
; 

出力結果は以下の通り

---

列を行にする

続いてこいつをsrcの中に入れ、元のデータに戻してみましょう。使う関数はregexp_split_to_tableです。src内の問い合わせは上述のものと基本的には同じですが、regexp_split_to_tableの関数が使うパーティション列は1つなので、年月をつなげてgroup byを1つにしています。1つ目のカッコ内には、パーティション列、分解対象列、区切り文字(上記の結果ではカンマの後にスペースが追加されているのでそれも忘れずに)、大文字小文字の違いを認識するかのオプション指定をセットします。そしてreturnに続く2つ目カッコ内には、出力列を記載します。列名はoutkey, token_nbx, tokenの3つで、それぞれスペースの後にデータ型を記述します。tokenは最終の文字の長さが何であれ、元の文字の長さ以上でないと怒られます。最後の出力列部分は普通のSQLなので、必要な部分を抜き出して年、月、日を再現しています。token_nbxは出力時に不要なので落としていますが、ここには列内の左からの順序がセットされます。

 /*列を行にする*/ 
with src as (  
	select 
	to_char(年)||'-'||to_char(月) as 年月, 
	trim(trailing ',' from 
	cast(
	xmlagg(to_char(日)||',' order by 日) --xmlaggの関数結果、ここまではxmlデータ 
	as varchar(10000)) --文字型に変換 
	) --最後のカンマ以降を削る 
	as 日一覧 
	from ( 
		select 
		year_of_calendar as 年, 
		month_of_year as 月, 
		day_of_month as 日 
		from sys_calendar.calendar 
		where year_of_calendar in (2024,2025) 
	) a1 
	group by 1 
) 
	select 
	cast(substring(outkey from 1 for 4) as integer) as 年, 
	cast(substring(outkey from 6 for 2) as integer) as 月, 
	cast(token as integer) as 日 
	from table ( 
		regexp_split_to_table (src.年月, src.日一覧, ', ','c') returns (outkey varchar(7), token_nbx integer, token varchar(10000))
	) a1 
	order by 1,2,3 
	; 

以上です。

#teradata #sql #xmlagg #regexp_split_to_table