PostgreSQLで別テーブルに変換するInsert文

Question

前提・実現したいこと

PostgreSQLでaテーブルにあるデータを変換してbテーブルにINSERTを行いたいです。

aテーブル

CREATE	TABLE	a
(
	ID			INTEGER		  	NOT NULL	,
	DATETIME	  TIMESTAMP			NOT NULL	,
	NUM		   INTEGER							
)

id,datetime,num

10,2019-02-05 12:00:00,10
10,2019-02-06 12:00:00,5
10,2019-02-05 10:00:00,3
50,2019-02-07 03:00:00,30
50,2019-02-05 14:00:00,15

変換先bテーブル

CREATE	TABLE	b
(
	ID			    INTEGER			NOT NULL	    ,
	DATE			  DATE		       NOT NULL        ,
	NUM_00			INTEGER							,
	NUM_01			INTEGER							,
	NUM_02			INTEGER							,
	NUM_03			INTEGER							,
	NUM_04			INTEGER							,
	NUM_05			INTEGER							,
	NUM_06			INTEGER							,
	NUM_07			INTEGER							,
	NUM_08			INTEGER							,
	NUM_09			INTEGER							,
	NUM_10			INTEGER							,
	NUM_11			INTEGER							,
	NUM_12			INTEGER							,
	NUM_13			INTEGER							,
	NUM_14			INTEGER							,
	NUM_15			INTEGER							,
	NUM_16			INTEGER							,
	NUM_17			INTEGER							,
	NUM_18			INTEGER							,
	NUM_19			INTEGER							,
	NUM_20			INTEGER							,
	NUM_21			INTEGER							,
	NUM_22			INTEGER							,
	NUM_23			INTEGER
)

id,date,num_00,num_01,num_02,num_03,num_04,num_05,num_06,num_07,num_08,num_09,num_10,num_11
,num_12,num_13,num_14,num_15,num_16,num_17,num_18,num_19,num_20,num_21,num_22,num_23

10,2019-02-05,0,0,0,0,0,0,0,0,0,0,3,0,10,0,0,0,0,0,0,0,0,0,0,0
10,2019-02-06,0,0,0,0,0,0,0,0,0,0,0,0,5,0,0,0,0,0,0,0,0,0,0,0
50,2019-02-05,0,0,0,0,0,0,0,0,0,0,0,0,0,0,15,0,0,0,0,0,0,0,0,0
50,2019-02-07,0,0,30,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0

このようにしてidとdatetimeの日付が一致しているものを
一つのレコードにし、datetimeの時刻とnumを
時間別のカラムに変換するSQLを作成しています。

INSERTする際に上記のように変換することは可能なのでしょうか。

どうか、ご教授頂けないでしょうか。

試したこと

INSERTを行ってから別SQLでUPDATEを行って一つずつ入れて行こうと作業していたら
一つのSQLでINSERTを行う際に変換するようにとの指示が出てそちらで対応しようとしています。

補足情報（FW/ツールのバージョンなど）

Windows10
Postgresql-11
pgAdmin 4 v3

Comments

[m.ts10806]

各テーブルのCREATE TABLE文、サンプルデータのINSERT文をご提示いただいた方が再現確認ができますし、的確な回答につながります。

[m.ts10806]

datetimeのtime部分はどうするつもりなのでしょうか。

[m.ts10806]

↑ごめんなさい読み違えました。bのnum_XXがそれぞれ時間になってるわけですね。

[m.ts10806]

>変換するようにとの指示が出て
これはどういう状況なのでしょうか。
あまり作業依頼、宿題代行のような内容だとそもそも質問になってませんので、そのあたりが誤解されないように、まず自身がやってみたこととか調べたことを記載して、何が問題か課題かを明確に記載されるよう強くすすめます。
今のところ丸投げですし。

[sazi]

aテーブルの状況が変わったら、updateも必要だと思いますが、insertのみってことはdeleteしてinsertなんですか？

[durasama]

>これはどういう状況なのでしょうか。
INSERT文とUPDATE文を分けて二つのSQLにすれば
変換の方法はわかるのですが
INSERT文のみで変換を行いbテーブルに追加するという流れが
私の方では知識が足りなかったため質問させていただきました。
丸投げの様になってしまい申し訳ございません。

[durasama]

>aテーブルの状況が変わったら、updateも必要だと思いますが、insertのみってことはdeleteしてinsertなんですか？
説明不足でした。
aテーブルの中身は変わらないので固定のaテーブルからbテーブルにinsertのみになります。

[sazi]

> aテーブルの中身は変わらないので固定のaテーブルからbテーブルにinsertのみになります。
変わらないものを、再度insertすると重複になるので、insertは一度きりのデータ移行みたいなものってことですか？
一度しか実行しないのならinsertのみでっていうハードルは何なのでしょうね

[m.ts10806]

質問は編集できますので適宜加筆修正願います

[durasama]

>insertは一度きりのデータ移行みたいなものってことですか？
そうなります。

Answer 1

insertはinsert into テーブル selectという構文が使えますので、selectで成形すれば可能です。
case で分岐させた列を作ります。

insert into b
select id, datetime ::date
     , case  when date_part('hour',datetime)=0 then num else 0 end -- num_00
-- ～略～
     , case  when date_part('hour',datetime)=23 then num else 0 end -- num_23
from a
order by id, datetime

Comments

[durasama]

sazi様
ありがとうございます。
上記SQLを実行することで望んでた処理が行えました。
SQLにはまだあまり慣れていなくて勉強になりました。
お忙しい中回答して頂いてありがとうございます。

Answer 2

ヒントはSTRING_AGG

Comments

[sazi]

いや、それは。
STRING_AGGで纏めても時間帯ごとにどうやって分離します？

[Orlofsky]

そうか、時間帯毎に分けるのね。
SQLだけじゃ無理だから、LOOPが使えるPL/pgSQLなどを使わないと実現できませんね。

[sazi]

caseで分ければ大丈夫ですよ。

[Orlofsky]

CASEを24回も書きたくないですね。

[sazi]

ほぼコピペで大丈夫です。
pl/pgsqlでif文の分岐は数変わらないし、ループする処理を書く方が面倒です。

Answer 3

sazi様にご回答頂いた

insert into b
select id, datetime ::date
     , case  when date_part('hour',datetime)=0 then num else 0 end -- num_00
-- ～略～
     , case  when date_part('hour',datetime)=23 then num else 0 end -- num_23
from a
order by id, datetime

上記を24通り時間ごとに行うことで処理できました。
皆様、お忙しい中ありがとうございました。