row_number()を使ってMySQLで同じ行ごとに結合する

MySQLで同じ行ごとに結合する

SQLで同じ行ごとに結合することはできない。INNER JOIN, LEFT JOINなら結合カラムを指定するし、指定しないでCROSS JOINになると左表 * 右表になってしまう。

しかし、テストデータを作成するときなど、行ごとに結合したくなる時がある。

テストデータの作成例

テストデータ作成の例として以下を考える。

personsテーブルにはidとageカラムが存在する。改修によってheight列を追加することになった。160, 170, 180の値を既存の行に設定したい。

現在、select * from persons;とすると以下のようになっている。

row_number() over()

MySQLではrow_number() over()でSELECTした結果の行番号が取れる。

select row_number() over() as rn, id, age from persons order by id;

結果

これを利用して結合する。

結合する際の左表と右表をそれぞれサブクエリにしてrow_number() over()を列に追加し、これを結合カラムとすれば行番号同士の結合ができる。

select *
from (select row_number() over() as rn, id, age from persons order by id) a
join (select row_number() over() as rn, id, age from persons order by id) b
    using(rn);

結果

つまり、160, 170, 180が入ったテーブルがあるのであれば、row_number() over()を利用してid: 1には160をid: 2には170をid: 3には180を設定することができる。

ただテストデータを作るためだけにそのようなテーブルを1から作るのは面倒臭い。

テストデータはJSON配列で用意し、JSON_TABLEでテーブルにする

テストデータはJSON配列で用意して、JSON_TABLEでテーブルにしてあげると、実テーブルを作成するよりも楽。

今回の例でいうと、[160, 170, 180]というJSON配列をベタ書きし、JSON_TABLEを使ってテーブルに変換する。

select
    id,
    age,
    height
from
    (select
        row_number() over() as rn,
        id,
        age
     from persons
     order by id
    ) a
join
    (select
        row_number() over() as rn,
        height
     from json_table(
        '[160, 170, 180]',
        '$[*]' columns (height int path '$')) inb
    ) b
    using(rn);

結果

JSON配列のindexをrow_number()で指定し、JSON_EXTRACTで取り出す

先ほどの方法でJSON配列を用いたが、JSON配列なら結合しない方法もある。

JSON_EXTRACTを使えばJSONからJSONのパスを指定して値を取り出せるので、以下のように$[0]とすることで、全員height: 160で取得できる。

select id, age, json_extract('[160, 170, 180]', '$[0]') height from persons order by id;

$[0]の部分が0固定ではなくrow_number() over() - 1になっていれば160, 170, 180の順で取り出せるため、$[とrow_number() over() - 1と]をconcatで結合すればいい。

select
    id,
    age,
    json_extract(
        '[160, 170, 180]',
        concat('$[',
            row_number() over() - 1,
            ']')
    ) height
from persons
order by id;

pasteコマンドでSQLを組み立てる

SQLでpersonsテーブルをSELECTするだけにして、シェルなどを使ってSQLを組み立てることも考えられる。pasteコマンドを使ってみる。

## selectした結果をファイル名selectedに貼り付け
$ cat << EOF > selected
1,20
2,30
3,35
EOF

## selectedの行数分、insert ~ values(までを出力し、ファイル名insertに書き出す
$ for i in $(seq 1 $(wc -l selected | awk '{print $1}'))
do
  echo "insert into persons(id, age, height) values("
done > insert

## ファイル名dataに設定したいデータを記載
$ cat <<EOF > data
,160
,170
,180
EOF

## selectedの行数分、);を出力し、ファイル名insertendに書き出す
$ for i in $(seq 1 $(wc -l b | awk '{print $1}'))
do
  echo ");"
done > insertend

## pasteで行ごとに連結してSQLを出力する
$ paste insert selected data insertend

以下のSQL文が生成できる。

insert into persons(id, age, height) values(	1,20	,160	);
insert into persons(id, age, height) values(	2,30	,170	);
insert into persons(id, age, height) values(	3,35	,180	);

列数が少ないなどシンプルな場合は簡単に作れる。

環境

MySQL 8.0

JSON_TABLE 関連記事

MySQLのJSONをテーブルに変換して集計等を行う