※当サイトの記事には、広告・プロモーションが含まれます。

PostgreSQLでカンマ区切りの値を分割後、SELECTして別テーブルにINSERTする

japan.zdnet.com

⇧ amazing...

PostgreSQLでカンマ区切りの値を分割後、SELECTして別テーブルにINSERTする

例えば、

⇧ 上記のohenro_idsカラムのようなカンマ区切りの値を分割後にSELECTして別テーブルにINSERTしたいってことがあったので、調べてみたところ、

www.system-exe.co.jp

dev.classmethod.jp

⇧ 上記サイト様を参考にさせていただきました。

まずは、テーブルの作成とデータをINSERTしておきます。

■shikoku_ohenro_logテーブル

create table public.shikoku_ohenro_log (
  id serial not null
  , user_id bigint not null
  , ohenro_id bigint not null
  , created timestamp(6) with time zone default CURRENT_TIMESTAMP
  , updated timestamp(6) with time zone
  , is_deleted boolean default false
  , primary key (id)
);    

■shikoku_ohenro_userテーブル

create table public.shikoku_ohenro_user (
  id serial not null
  , last_name character varying(50) not null
  , first_name character varying(50) not null
  , rome_last_name character varying(50) not null
  , rome_first_name character varying(50) not null
  , ohenro_ids text
  , created timestamp(6) with time zone default CURRENT_TIMESTAMP
  , updated timestamp(6) with time zone
  , is_deleted boolean default false
  , primary key (id)
);    

■shikoku_ohenro_userテーブルへデータをINSERT

-- お遍路ユーザーのデータ
INSERT INTO shikoku_ohenro_user (id, last_name, first_name, rome_last_name, rome_first_name, ohenro_ids)
 VALUES(1, '佐藤', '一', 'sato', 'hajime', '1,2,3,4,5')
 ,(2, '鈴木', '一郎', 'suzuki', 'ichiro', '81,82,83,84,85,86,87,88')
 ,(3, '高橋', '一登', 'takahashi', 'kazuto', '24,25,26')
 ,(4, '田中', '一成', 'tanaka', 'hitonari', '40,41,42,43,44,45')
 ,(5, '伊藤', '一二三', 'ito', 'hifumi', '1,2,3,4')
 ,(6, '渡辺', '一夫', 'watanabe', 'kazuo', '81,82,83,84,85')
 ,(7, '山本', '一宜', 'yamamoto', 'hitonori', '24,25,26,27,28')
 ,(8, '中村', '一代', 'nakamura', 'kazuyo', '40,41,42,43')
 ,(9, '小林', '一平', 'kobayashi', 'kazuhira', '1,2,3,4,5,6')
 ,(10, '加藤', '一樹', 'kato', 'kazuki', '81,82,83,84,86')
 ,(11, '吉田', '一紀', 'yoshida', 'ikki', '24,25,26,27')
 ,(12, '山田', '一朗太', 'yamada', 'ichirota', '40,41,42,43,44')
 ,(13, '佐々木', '一乃条', 'sasaki', 'ichinojyo', '1,2,3,4')
 ,(14, '山口', '一太', 'yamaguti', 'ichita', '81,82,83,84,85')
 ,(15, '松本', '一乃', 'matsumoto', 'ichino', '24,25,26,27,28')
 ,(16, '井上', '一風', 'inoue', 'ichikaze', '40,41,42,43,44')
 ,(17, '木村', '一瀬', 'kimura', 'ichise', '1,2,3,4,5,6')
 ,(18, '林', '太一', 'hayashi', 'taichi', '81,82,83,84,85,86,87')
 ,(19, '斎藤', '一', 'saito', 'hajime', '24,25,26,27,28,29,30')
 ,(20, '清水', '喜一', 'shimizu', 'kiichi', '40,41,42,43,44,45,46')
 ,(21, '山崎', '孝一', 'yamazaki', 'kouichi', '1,2,3,4,5,6')
 ,(22, '森', '文一', 'mori', 'fumikazu', '81,82,83,84,85,86')
 ,(23, '池田', '寅一', 'ikeda', 'toraichi', '24,25,26,27,28,29')
 ,(24, '橋本', '一男', 'hashimoto', 'kazuo', '40,41,42,43,44,45')
 ,(25, '阿部', '一弥', 'abe', 'ichiya', '1,2,3,4,5,6,7')
 ,(26, '石川', '一虎', 'ishikawa', 'kazuotora', '81,82,83,84,85,86,87')
 ,(27, '山下', '一子', 'yamashita', 'kazuko', '24,25,26,27,28,29,30')
 ,(28, '中島', '一美', 'nakajima', 'kazumi', '40,41,42,43,44,45,46')
 ,(29, '石井', '誠一', 'ishii', 'seiichi', '1,2,3,4,5,6')
 ,(30, '小川', '一星', 'ogawa', 'issei', '81,82,83,84,85,86')
 ,(31, '前田', '一比良', 'maeda', 'kazuhira', '24,25,26,27,28,29,30')
 ,(32, '岡田', '健一郎', 'okada', 'kenichiro', '40,41,42,43,44,45,46,47')
 ,(33, '長谷川', '斗一', 'hasegawa', 'toichi', '1,2,3,4,5')
 ,(34, '藤田', '一清', 'fujita', 'kazukiyo', '81,82,83,84,85,86,87')
 ,(35, '後藤', '輝一', 'goto', 'teruichi', '24,25,26,27,28,29')
 ,(36, '近藤', '海一', 'kondo', 'kaiichi', '40,41,42,43,44,45,46')
 ,(37, '村上', '芳一', 'murakami', 'houichi', '1,2,3,4,5,6,7')
 ,(38, '遠藤', '隼一', 'endo', 'hayaichi', '81,82,83,84,85')
 ,(39, '青木', '顕一', 'aoki', 'kenichi', '24,25,26,27,28,29,30')
 ,(40, '坂本', '一太郎', 'sakamoto', 'ichitaro', '40,41,42,43,44,45,46,47') 
 ,(41, '斉藤', '裕一', 'saito', 'yuuichi', '1,2,3,4,5,6,7,8')
 ,(42, '福田', '正一', 'fukuda', 'syouichi', '81,82,83,84,85,86')
 ,(43, '太田', '寛一', 'oota', 'kanichi', '24,25,26,27,28,29')
 ,(44, '西村', '光一', 'nishimura', 'kouichi', '40,41,42,43,44,45')
 ,(45, '藤井', '源一郎', 'fujii', 'genichiro', '1,2,3,4,5,6')
 ,(46, '金子', '仁一', 'kaneko', 'jinichi', '81,82,83,84,85')
 ,(47, '岡本', '宗一郎', 'okamoto', 'souichiro', '24,25,26,27,28')
 ,(48, '藤原', '真一', 'fujiwara', 'shinichi', '40,41,42,43,44,45,46')
 ,(49, '中野', '一豊', 'nakano', 'kazutoyo', '1,2,3,4,5,6')
 ,(50, '三浦', '一馬', 'miura', 'kazuma', '81,82,83,84,85')
 ,(51, '原田', '一清', 'harada', 'kazukiyo', '24,25,26,27,28,29')
 ,(52, '中川', '賢一', 'nakagawa', 'kenichi', '40,41,42,43,44,45')
 ,(53, '松田', '兼一', 'matsuda', 'kaneichi', '1,2,3,4,5,6')
 ,(54, '竹内', '一翔', 'takeuchi', 'kazuto', '81,82,83,84,85,86')
 ,(55, '小野', '才一', 'ono', 'saiichi', '24,25,26,27,28')
 ,(56, '田村', '一朗', 'tamura', 'ichiro', '40,41,42,43,44,45')
 ,(57, '中山', '隆一', 'nakayama', 'ryuuichi', '1,2,3,4,5,6')
 ,(58, '和田', '一貴', 'wada', 'kazuki', '81,82,83,84,85,86')
 ,(59, '石田', '憲一', 'ishida', 'kenichi', '24,25,26,27,28,29,30')
 ,(60, '森田', '与一', 'morita', 'yoichi', '40,41,42,43,44,45');    

で、シーケンスとかがズレるっぽいので、

select setval('shikoku_ohenro_user_id_seq',(select max(id) from shikoku_ohenro_user));

⇧ シーケンスを最新に更新しておきます。

で、準備が整ったら、カンマ区切りの値を分割後、SELECTして別テーブルにINSERTする。

-- SELECTして別テーブルにINSERT
INSERT INTO shikoku_ohenro_log (
  user_id
  ,ohenro_id
)
SELECT id AS user_id
       ,cast(regexp_split_to_table(ohenro_ids, ',') as integer) AS ohenro_id
  FROM shikoku_ohenro_user sou
  WHERE NOT EXISTS(
    SELECT *
      FROM shikoku_ohenro_log sol
      WHERE sou.id = sol.user_id
  )
ON CONFLICT
DO NOTHING;    

A5:SQL Mk-2でSQL文を選択した状態で、実行ボタンを押下。

INSERTされました。

自分の環境では、事前にshikoku_ohenro_logにデータがある程度INSERTされてたので、

ON CONFLICT
DO NOTHING;      

⇧ 上記がないと、一意制約違反のエラーが出てしまってデータがINSERTされなかった。

本日の各寺の訪問客数を集計してみる。

SELECT ohenro_id, COUNT(*)
  FROM shikoku_ohenro_log
  WHERE CAST(created as DATE) = CURRENT_DATE 
  GROUP BY ohenro_id
  ORDER BY ohenro_id ASC;    

⇧ お寺毎の訪問客数が集計できました、shikoku_ohenro_logテーブルに存在しないohenro_idは無視されてしまってますが...。

kntmr.hatenablog.com

⇧ 上記サイト様を参考に、UNIONで全部のお寺を集計できたかと。

SELECT * FROM (
  SELECT ohenro_id AS os_id, COUNT(ohenro_id) 
    FROM shikoku_ohenro_log
    WHERE CAST(created as DATE) = CURRENT_DATE
    GROUP BY ohenro_id
  UNION
  SELECT id, 0 
    FROM shikoku_ohenro so
    WHERE NOT EXISTS (
      SELECT * FROM shikoku_ohenro_log sol
      WHERE so.id = sol.ohenro_id
    )
) AS temp
ORDER BY temp.os_id;    

ついでに、お寺の訪問客数を全て合算。

SELECT SUM(subtotal) FROM (
  SELECT ohenro_id AS os_id, COUNT(ohenro_id) AS subtotal
    FROM shikoku_ohenro_log
    WHERE CAST(created as DATE) = CURRENT_DATE
    GROUP BY ohenro_id
  UNION
  SELECT id, 0 
    FROM shikoku_ohenro so
    WHERE NOT EXISTS (
      SELECT * FROM shikoku_ohenro_log sol
      WHERE so.id = sol.ohenro_id
    )
) AS temp    

カンマ区切りの値の扱いのベストプラクティスを知りたいですかね...というかテーブル設計のベストプラクティスを知りたい...

毎度モヤモヤ感が半端ない...

今回はこのへんで。