[Redshift] SUBSTRINGを使用して文字列を分割する。

はじめに

データベースの中で何かの理由でIDが複数の意味を持つ事が有りますが、データを見る場合にはバラして見易くしたい事もあると思います。今回はそんな時のSQLを作成してみました。

目的

テーブル「comp」のカラム「comp_id」を2桁づつに3分割し、テーブル「aggregate」の「store_id」「season_id」「age」に振り分ける。

環境

Mac OSX 10.10.5 Yosemite
Redshift 1.0.1125

準備

スキーマ：blog

CREATE SCHEMA blog;

店舗情報：store

CREATE TABLE blog.store (
  store_id INTEGER
  , store_name VARCHAR(32)
);

INSERT INTO blog.store VALUES
(1,'akihabara'),(2,'hokkaido'),(3,'osaka'),(4,'fukuoka');

postgres=# select * from blog.store;
 store_id | store_name 
----------+------------
        1 | akihabara
        2 | hokkaido
        3 | osaka
        4 | fukuoka
(4 rows)

商品売上情報:comp

CREATE TABLE blog.comp (
  item_id INTEGER
  , item_name VARCHAR(16)
  , quantity INTEGER
  , comp_id VARCHAR(6)
  , PRIMARY KEY(item_id)
);

INSERT INTO blog.comp VALUES
  (1,'apple',15,'011110')
  , (2,'orange',25,'022220')
  , (3,'banana',35,'033330')
  , (4,'cherry',45,'044440')
  , (5,'pineapple',55,'010230');

postgres=# select * from blog.comp;
 item_id | item_name | quantity | comp_id 
---------+-----------+----------+---------
       1 | apple     |       15 | 011110
       2 | orange    |       25 | 022220
       3 | banana    |       35 | 033330
       4 | cherry    |       45 | 044440
       5 | pineapple |       55 | 010230
(5 rows)

comp_idは6桁の数字で構成されていて、2桁ずつ、計3つのidを連結させたものとします。
・1〜2桁目：売上店舗ID
・3〜4桁目：季節ID
・5〜6桁目：購入者の年齢

実行

comp_idを分割する

最初にテーブルを作成します。

CREATE TABLE blog.aggregate (
  item_id INTEGER
  , item_name VARCHAR(16)
  , sell_store_id VARCHAR(2)
  , season_id VARCHAR(2)
  , age VARCHAR(2)
  , PRIMARY KEY(item_id)
);

次に、テーブルcompからaggregateにデータを入れます。

INSERT INTO
  blog.aggregate 
SELECT 
  item_id
  , item_name
  , SUBSTRING(comp_id,1,2)
  , SUBSTRING(comp_id,3,2)
  , SUBSTRING(comp_id,5,2)
FROM
  blog.comp;

実行結果。

postgres=# select * from blog.aggregate;
 item_id | item_name | sell_store_id | season_id | age 
---------+-----------+---------------+-----------+-----
       1 | apple     | 01            | 11        | 10
       2 | orange    | 02            | 22        | 20
       3 | banana    | 03            | 33        | 30
       4 | cherry    | 04            | 44        | 40
       5 | pineapple | 01            | 02        | 30
(5 rows)

ちゃんと振り分けられました。

見易くしてみる

今度は、分割したIDから名称を表示させたいと思います。
店舗情報のテーブル「store」があるので店舗名はそこを参照すれば良いですが、季節の名称は参照するテーブルがないのでCASEで作ってしまいます。
WITH句のstore_idの作成でINTEGERにCASTしているので頭の0が削除されますが、JOINするテーブル「store」のstore_idもINTEGERなので問題ありません。

WITH CTE AS (
 SELECT 
   item_id
   , item_name
   , quantity
   , CAST(SUBSTRING(comp_id,1,2) AS INTEGER) AS store_id
   , CAST(SUBSTRING(comp_id,3,2) AS INTEGER) AS season_id
   , CAST(SUBSTRING(comp_id,5,2) AS INTEGER) AS age
 FROM
   blog.comp
)
INSERT INTO
  blog.aggregate
SELECT 
  C.item_id
  , C.item_name
  , C.quantity
  , C.store_id
  , S.store_name
  , C.season_id
  , CASE WHEN C.season_id='11' THEN 'spring'
         WHEN C.season_id='22' THEN 'summer'
         WHEN C.season_id='33' THEN 'autumn'
         WHEN C.season_id='44' THEN 'winter'
         ELSE NULL
         END season
  , C."age"
FROM CTE AS C
  LEFT OUTER JOIN blog.store AS S on C.store_id = S.store_id 
ORDER BY item_id;

まず、WITH句でカラム「comp_id」の値を分割したサブクエリを作成。
それを元に、店舗情報テーブル「store」を結合して名称を設定。
季節の名称は、WITH句でカラム「comp_id」から作成したカラム「season_id」を元に名称に置き換えます。

実行結果。

postgres=# select * from blog.aggregate ;
 item_id | item_name | quantity | store_id | store_name | season_id | season | age 
---------+-----------+----------+----------+------------+-----------+--------+-----
       1 | apple     |       15 |        1 | akihabara  |        11 | spring |  18
       2 | orange    |       25 |        2 | hokkaido   |        22 | summer |  20
       3 | banana    |       35 |        3 | osaka      |        33 | autumn |  30
       4 | cherry    |       45 |        4 | fukuoka    |        44 | winter |  40
       5 | pineapple |       55 |        1 | akihabara  |         2 |        |  30
(5 rows)

カラム「season」の一番下はNULLになっていますが、これは「season_id」が2なので、CASE句の条件に含まれていないためです。

PostgreSQLでは...

PostgreSQL 9.5.1で試しましたが、同じSQLで実行可能でした。
PostgreSQL 9.5.4文書

さいごに

今回の様にWITH句を使用しなくても書けますが、場合によっては処理が遅くなったりするので状況に応じて使い分けが必要だと思います。