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

#データベース

takahara

2016.12.01

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

はじめに

データベースの中で何かの理由で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句の条件に含まれていないためです。