oto-tanblog Written by oto-tan

【PostgreSQL】一つのカラムを横に並べて表示する

PostgreSQL

こんにちは!

おとーたんです。

同テーブル内にある一つのカラムを、別のカラムとして横並びに表示する方法を学んだのでご紹介いたします。

イメージはこんな感じ。

value(同一カラム) value(同一カラム)
101 101
102 102
103 103
104 104
105 105
106 106
107 107
108
109
110

[box01 title=”この記事を読んでわかること”]

  • 同テーブル内の一つのカラムを別カラムとして横並びにする方法

[/box01]

1回目と2回目の計測値を横に並べる

[box01 title=”想定”]

  • 検査機器を使用して計測
  • 1度に最低で4回、最大で10回計測
  • 2度計測することもある
  • 1度目と2度目の検査回数が同じ回数だとは限らない

[/box01]

上記のような想定で値を取得する場面が出てきました。

「1度目と2度目のカラムを分ければいいじゃん」

って思いましたが、どうもそうはいかない仕様みたい(笑)

1度目と2度目を交互に計測するわけではなく、1度に

ピッピッピーーーーーっ

と数回を計測して、その後に2度目の計測を

ピッピッピーーーーーっ

と計測するので、1つのカラムで1度目と2度目を分けるしかなかったみたいです。

データのイメージとしてはこんな感じ。

count_id test_id count value
1001 10001 1 101
1002 10001 1 102
1003 10001 1 103
1004 10001 1 104
1005 10001 1 105
1006 10001 1 106
1007 10001 1 107
1008 10001 1 108
1009 10001 1 109
1010 10001 1 110
1011 10001 2 101
1012 10001 2 102
1013 10001 2 103
1014 10001 2 104
1015 10001 2 105
1016 10001 2 106
1017 10001 2 107
1018 10002 1 101
1019 10002 1 102
1020 10002 1 103
1021 10002 1 104
1022 10002 1 105
1023 10002 1 106
1024 10002 2 101
1025 10002 2 102
1026 10002 2 103
1027 10002 2 104
1028 10002 2 105
1029 10002 2 106
1030 10002 2 107
1031 10002 2 108
1032 10002 2 109
カラム内容
count_id 計測回数ごとに採番されるID
test_id 計測対象のID
count 4〜10回までを1セットとする計測回数(1度目 or 2度目)
value 計測値

横に並べるときのSQL

「test_id」に対する計測値「value」を、「count」ごとに取得して横並びにします。

SQLを組むときのロジックは

  • 1度目と2度目の計測値をサブクエリで取得
  • 1度目と2度目の計測値取得時にそれぞれ連番を採番
  • 採番した連番で結合

といったイメージです。


SELECT
 count_1st.value AS "1度目"
,count_2nd.value AS "2度目"
FROM
/* サブクエリを使って1度目と2度目の計測値を取得 */
(
  SELECT
  /* row_numberを使用してcount_idを基準にして連番を採番 */
   row_number() over(ORDER BY testvalue.count_id ASC)  AS row_num
  ,testvalue.count_id
  ,testvalue.value
  FROM testvalue
  WHERE testvalue.count = 1 /* 1度目 */
  AND testvalue.test_id = 10001
) AS count_1st
/* いずれかがnullでも大丈夫なように「FULL JOIN」で結合 */
FULL JOIN
(
  SELECT
   row_number() over(ORDER BY testvalue.count_id ASC) AS row_num
  ,testvalue.value
  FROM testvalue
  WHERE testvalue.count = 2 /* 2度目 */
  AND testvalue.test_id = 10001
) AS count_2nd
/* 1度目と2度目で採番した連番で結合 */
ON count_1st.row_num = count_2nd.row_num

このSQL文を流すと、同一カラムを横並びにしてデータを取得できました!

上記SQLは2度目の計測回数が1度目よりも少ない場合です。

【PostgreSQL】横に並べる

1度目の計測回数が2度目よりも少ない場合はこのようになります。


SELECT
 count_1st.value AS "1度目"
,count_2nd.value AS "2度目"
FROM
/* サブクエリを使って1度目と2度目の計測値を取得 */
(
  SELECT
  /* row_numberを使用してcount_idを基準にして連番を採番 */
   row_number() over(ORDER BY testvalue.count_id ASC)  AS row_num
  ,testvalue.count_id
  ,testvalue.value
  FROM testvalue
  WHERE testvalue.count = 1 /* 1度目 */
  AND testvalue.test_id = 10002
) AS count_1st
/* いずれかがnullでも大丈夫なように「FULL JOIN」で結合 */
FULL JOIN
(
  SELECT
   row_number() over(ORDER BY testvalue.count_id ASC) AS row_num
  ,testvalue.value
  FROM testvalue
  WHERE testvalue.count = 2 /* 2度目 */
  AND testvalue.test_id = 10002
) AS count_2nd
/* 1度目と2度目で採番した連番で結合 */
ON count_1st.row_num = count_2nd.row_num
【PostgreSQL】横に並べる

ボーリングのスコアで試してみた

なんか似たようなデータを作って、他にも試せないかなぁっと思って思いついたのでやってみました。

id player_id player_nm flame throw_cnt score
10001 1001 おとーたん 1 1 5
10002 1001 おとーたん 1 2 4
10003 1001 おとーたん 1 3
10004 1001 おとーたん 2 1 10
10005 1001 おとーたん 2 2
10006 1001 おとーたん 2 3
10007 1001 おとーたん 3 1 7
10008 1001 おとーたん 3 2 3
10009 1001 おとーたん 3 3
10010 1001 おとーたん 4 1 8
10011 1001 おとーたん 4 2 1
10012 1001 おとーたん 4 3
10013 1001 おとーたん 5 1 6
10014 1001 おとーたん 5 2 2
10015 1001 おとーたん 5 3
10016 1001 おとーたん 6 1 6
10017 1001 おとーたん 6 2 4
10018 1001 おとーたん 6 3
10019 1001 おとーたん 7 1 10
10020 1001 おとーたん 7 2
10021 1001 おとーたん 7 3
10022 1001 おとーたん 8 1 9
10023 1001 おとーたん 8 2 1
10024 1001 おとーたん 8 3
10025 1001 おとーたん 9 1 7
10026 1001 おとーたん 9 2 2
10027 1001 おとーたん 9 3
10028 1001 おとーたん 10 1 6
10029 1001 おとーたん 10 2 3
10030 1001 おとーたん 10 3
10031 1002 おかーたん 1 1 7
10032 1002 おかーたん 1 2 3
10033 1002 おかーたん 1 3
10034 1002 おかーたん 2 1 8
10035 1002 おかーたん 2 2 1
10036 1002 おかーたん 2 3
10037 1002 おかーたん 3 1 6
10038 1002 おかーたん 3 2 2
10039 1002 おかーたん 3 3
10040 1002 おかーたん 4 1 6
10041 1002 おかーたん 4 2 4
10042 1002 おかーたん 4 3
10043 1002 おかーたん 5 1 10
10044 1002 おかーたん 5 2
10045 1002 おかーたん 5 3
10046 1002 おかーたん 6 1 9
10047 1002 おかーたん 6 2 1
10048 1002 おかーたん 6 3
10049 1002 おかーたん 7 1 7
10050 1002 おかーたん 7 2 2
10051 1002 おかーたん 7 3
10052 1002 おかーたん 8 1
10053 1002 おかーたん 8 2 3
10054 1002 おかーたん 8 3
10055 1002 おかーたん 9 1 4
10056 1002 おかーたん 9 2 4
10057 1002 おかーたん 9 3
10058 1002 おかーたん 10 1 10
10059 1002 おかーたん 10 2 10
10060 1002 おかーたん 10 3 10
カラム内容
player_id プレイヤーID
player_nm プレイヤー名
flame 1ゲームのフレーム
throw_cnt 1フレーム辺りの投数
(1〜9までは2、10のみ3)
score 計算がめんどくさいので倒れた本数

フレームで並べると横に長くなる、かつSQL文が長くなるので、投数で繋げました。

ボーリングのスコア表と違い、縦並びでスコアが表示されます。


SELECT
 throw_cnt_1st.player_nm AS "プレイヤー名"
,throw_cnt_1st.flame AS "フレーム"
,throw_cnt_1st.score AS "1投目"
,throw_cnt_2nd.score AS "2投目"
,throw_cnt_3rd.score AS "3投目"
FROM
/* サブクエリを使って1投目と2投目、3投目の計測値を取得 */
(
  SELECT
   row_number() over(ORDER BY bowling.id ASC)  AS row_num
  ,bowling.player_nm
  ,bowling.flame
  ,bowling.score
  ,bowling.player_id
  FROM bowling
  WHERE bowling.throw_cnt = 1 /* 1投目 */
) AS throw_cnt_1st
/* 1投目と2投目、1投目と3投目を「FULL JOIN」で結合 */
FULL JOIN
(
  SELECT
   row_number() over(ORDER BY bowling.id ASC) AS row_num
  ,bowling.id
  ,bowling.player_id
  ,bowling.score
  FROM bowling
  WHERE bowling.throw_cnt = 2 /* 2投目 */
  --
) AS throw_cnt_2nd
ON throw_cnt_1st.row_num = throw_cnt_2nd.row_num
FULL JOIN
(
  SELECT
  /* 3投目は10フレーム時にしか投げないので、採番した連番の後ろに「0」を繋げて2桁にする */
   row_number() over(ORDER BY bowling.id ASC) || '0' AS row_num
  ,bowling.id
  ,bowling.player_id
  ,bowling.score
  FROM bowling
  WHERE bowling.throw_cnt = 3 /* 3投目 */
) AS throw_cnt_3rd
/* 3投目で採番した連番を数値に変換 */
/* ※ 3投目で採番した連番は後ろに「0」を繋げているため文字列扱いになっている */
ON throw_cnt_1st.row_num = to_number(throw_cnt_3rd.row_num, '999999')
ORDER BY
 throw_cnt_1st.player_id
,throw_cnt_1st.flame;
【PostgreSQL】横に並べる

まとめ

2回か3回くらいのデータなら良さそうですが、4・5・6・7…と増えていくとだらだらと長いSQLになるので、あまりおすすめできないです(笑)

結合回数が少ない場合には良いですね!

他の方法があれば是非ご教授お願いいたしますm(_ _)m