実務で使うSQL (Redshift版)

この記事は約9分で読めます。
sponsored link

Redshift向け SQLコマンド

本エントリは私が実務で使うことが多いSQLのコマンドをまとめたものです。
それぞれの例ごとに異なる関数を利用しているので、SQLの復習などにご活用ください。

例1(テーブル作成)
DROP TABLE IF EXISTS schema_0.table_0 CASCADE;  --既存のテーブルを削除
--CASCADEは親元の変更に従うという記述(親元が消えれば消える)
CREATE TABLE schema_0.table_0 AS (
  column1  timestamp     not null,  --NULLでない数値型のカラム
  column2  varchar(256)  not null,
  column3  integer       not null,
);

GRANT SELECT, UPDATE, DELETE ON schema_0.table_0 TO tom;  --参照(SELECT), 更新(UPDATE), 削除(DELETE)の権限をtomに付与
COMMIT;  --今回の更新を永続的なものにする
例2(WITH句でビューを作成)
CREATE OR REPLACE VIEW schema_1.sample AS  --スキーマ1にサンプルという名前のビューを作成
WITH temp_1 AS (  --仮のテーブルを作成
  SELECT
    school, student, subject, score,
    CASE score  --点数を判別して成績カラムを作成
      WHEN 1 THEN 'bad' 
      WHEN 2 THEN 'ok'
      WHEN 3 THEN 'good'
      ELSE NULL END AS grade,  --ASは省略可能
    SUM(score) total_score,    --ASを省略
    MAX(score) max_score,
    score/COUNT(subject) avg_score  --点数を科目数で割り平均点を算出
  FROM 
    schema_1.test_score
  WHERE 
    (school='開成' OR school='灘')
    AND student LIKE '鈴木%'  --名字が鈴木の生徒のみを取得
    AND subject IS NOT NULL  --教科名がNULLでないものを取得
  GROUP BY 
    school, student  --ここでデータをグループ化しないと上の集計関数(SUM, MAX, COUNT)がエラーとなる
),
temp_2 AS (   --2つ目の仮テーブルを作成
  SELECT DISTINCT  --重複行を除外
    school, subject, test_date, 
    CAST(class AS integer) AS period,  --CASTで型変換
    CASE
      WHEN town::text='西日暮里' THEN '東京'::character varying --「::」を使った型変換
      WHEN town::text='魚崎北町' THEN '兵庫'::character varying          
      ELSE town END AS region,
    LEAD(test_date) 
      OVER(PARTITION BY subject ORDER BY test_date DESC) AS advance_notice_date,  --日付を降順に並べその1行前のデータを科目単位で取得
    DATE_DIFF('minutes'::character varying::text,  --2つの日付の差分を計算 
      TO_TIMESTAMP(start_time::character, 'HHMI'::character varying::text)::timestamp without time zone,  --開始時間が文字列で入っているので日付型に変換して使う
      TO_TIMESTAMP(end_time::character,'HHMI'::character varying::text)::timestamp without time zone) AS test_time,
    ROW_NUMBER() OVER(PARTITION BY subject ORDER BY test_date) AS number_of_execution  --科目毎の実施回数
  FROM 
    schema_1.test_schedule
  WHERE 
    TO_DATE('2021/01/01','yyyy/mm/dd') <= test_date AND test_date <= GETDATE() --2021の年始から今日まで
),
temp_3 AS (
  SELECT DISTINCT school, course, teacher 
    FROM schema_1.teacher_men
  UNION  --UNIONには重複レコードが含まれないがUNION ALLには含まれる
  SELECT DISTINCT school, course, teacher 
    FROM schema_1.teacher_women
),
temp_4 AS (
  SELECT 
    t2.school, t2.subject, --カラム名が重複する場合はどのテーブルを参照しているかを明記する
    test_date, period, region, advance_notice_date, test_time, number_of_execution, teacher
  FROM 
    temp_2 AS t2
  JOIN 
    temp_3 AS t3 
    ON t2.school=t3.school AND t2.subject=t3.course  --結合キーを指定
)

SELECT
  t1.school, t1.subject, student, score, grade, total_score, max_score, avg_score, test_date, region, test_time
FROM
  temp_1 AS t1
  LEFT JOIN temp_4 AS t4 USING(school, subject)  --キーが共通する場合はUSINGが使える(ONでなくても良い)
WHERE 
  DATE_DIFF('week', test_date, advance_totice_date) < 3  --2週間前に連絡された試験のみを指定
  AND period BETWEEN 1 and 5    --1限目から5限目までが対象
  AND number_of_execution < 10  --テストは過去に10回以上実施されてないものとする
  AND teacher <> 'John'  --John先生を除外
ORDER BY test_date DESC

WITH NO SCHEMA BINDING;  --今回作成するビューと参照元オブジェクトを紐付けない
GRANT SELECT ON schema_1.sample TO tom;
COMMIT;
例3(サブクエリでビューを作成)
CREATE OR REPLACE VIEW schema_2.sample AS (
SELECT *
FROM
  (SELECT shop_name, foods, drinks, price, discount_flag, '明石家さんま' AS owner  --オーナーレコードを新規追加
     FROM schema_2.restaurant 
     WHERE region = '大阪'
   UNION
   SELECT shop_name, foods, drinks, price, discount_flag, 'タモリ' AS owner
     FROM schema_2.restaurant
     WHERE region = '東京') AS a
LEFT JOIN
  (SELECT 
     shop_name, 
     TO_CHAR(business_start_time, 'HH24:MI') AS start_time,    --TIMESTAMPから「hh:mm」部分を取得
     DATEADD('day', -1, business_day) AS business_day_before,  --営業日の前日を計算
     NULLIF(sales, 0) AS sales,  --売上が0であればNULLを返す
     LAG(ISNULL(sales), 7)       --1週間前の売上をノルマとする計算(日付でソートして7つ前のレコードを取得)
       OVER(PARTITION BY shop_name ORDER BY business_day) AS sales_quota,
     LISTAGG(business_partner ,',')  --複数行に格納されたパートナーの名前をカンマ区切りで1つのレコードにまとめた
       WITHIN GROUP (ORDER BY shop_name) 
       OVER(PARTITION BY shop_name) AS partner_all,
     REPLACE(town, '宮古島', '沖縄') AS town_2,  --値の置換
     CAST(LEFT(stuff_memo, 5) AS integer) AS stuff_id,   --スタッフメモの先頭5文字をスタッフID
     SUBSTRING(stuff_memo, 6, 100) AS stuff_memo_short,  --スタッフメモ短縮版
     SPLIT_PART(lost_item_info, '|', 1) AS lost_item     --「|」という文字で区切ったときの1パート目
   FROM
     (SELECT DISTINCT *,
        SUM(amount) AS sales,
        CASE DATEPART('weekday', business_day)  --REDSHIFTのweekdayは0から7
          WHEN 0 THEN '日'
          WHEN 1 THEN '月'
          WHEN 2 THEN '火' 
          WHEN 3 THEN '水'
          WHEN 4 THEN '木'
          WHEN 5 THEN '金'
          WHEN 6 THEN '土' END AS weekday,
      FROM schema_2.restaurant_details  --FROMの中にFROMを入れた(サブクエリ)
      GROUP BY shop_name
      HAVING amount > 1000  --HAVINGはWHEREと同じ役割だが実行順序が異なる
      --SQLの実行順序は「FROM→WHERE→GROUPBY→HAVING→SELECT→ORDERBY」
    ) AS b 
USING(shop_name)

WITH NO SCHEMA BINDING;
GRANT SELECT ON schema_2.sample TO tom;
COMMIT;