(新卒で役にたつ！)SQL小技集 | フューチャー技術ブログ

はじめに

Photo taken by @kaedemalu

春の入門連載2021の3日目です。

今回は業界歴の比較的浅い方々をターゲットに、覚えておくと現場で役立ちそうなSQLの小技をいくつか紹介したいと思います。

知らなくても困らないけど、知っているとちょっと仕事を効率化できそうなもの、考え方に触れれば比較的簡単に応用ができそうなものをピックアップしてみました。

紹介しているサンプルはPostgreSQLを前提とした記述しています。多少構文が異なるケースはあるものの、どのプロダクトでも同様の事ができます。

メタデータの利用

一般的にデータベースは、内部でオブジェクト(テーブル、インデックス、ストアドプログラムなど)をはじめとしたデータベース構成要素の定義情報を持っており、それらは「メタデータ」や「ディクショナリ」と呼ばれています。条件付きではありますが、データベース内で発生した「出来事」の情報も保持しています。

メタデータを上手くを活用すると、例えば簡単なケースでは、後述するような環境のクリーニング(全テーブルのデータを一括削除)であったり、テーブルのレコード件数の確認だったりといった、日常のちょっとした機械的な作業の手数を減らす事ができます。

その他にも、「〇〇カラムってどのテーブルで持ってたんだっけ」といった、設計ドキュメントを見れば分かるけれど、それを探して開くのが面倒くさいような、ちょっとした調べ事をSQLの発行で代替できます。

応用性が非常に高い領域ですので、まずは簡単な事から初めつつ、引き出しを増やしていってみてください。

Sample

特定環境の全テーブルを削除するSQLをSQLで作成

SELECT
    'TRUNCATE TABLE ' || table_schema || '.' || table_name || ';'
FROM
    information_schema.tables   /* Oracleの場合はDBA_TABLES、USER_TABLESなど、プロダクトに応じて変えてください */
WHERE
    table_schema = 'sample' /* 対象スキーマを限定するなど、状況に応じて指定してください */
;

特定文字を含むカラム名を持つテーブルの調査

SELECT
    table_name,
    column_name
FROM
    information_schema.columns
WHERE
    column_name LIKE '%uriage%' /* 「uriage(売上)」という文字が含まれる */
;

SELECT句のみSQL

一般的にSELECT文はFROM句とセットで記述すると習うのではないかと思います。
しかしながら、PostgreSQLなどのDBプロダクトではFROM句のないSELECT文が構文として許容されています(OracleなどはNGです)。

「FROM句のないSELECT文を書くケースなんてあるのか？」という疑問を抱かれるでしょうが、初めて利用する関数の挙動確認などを行う場合など、デバッグやカジュアルな検証を行う際に便利です。わざわざテストテーブルを作成し、テスト用ダミーデータを入れてといった手間を省く事ができます。

Sample

SQL版Hello World!

SELECT 'Hello World!';

/* Oracleの場合はFROM句省略は不可ですが、DUAL表(1カラムだけ定義され、1レコードだけ入っているビルドインのテーブル)
 を利用すれば同様の事が実施可能 */
SELECT 'Hello World!' FROM dual;

組み込み関数の挙動確認

/* 「 UPPER(小文字を大文字に変換して返す))」なる関数が便利そうだが、想定する結果を返してくれるか？ */
SELECT UPPER('AbCdE');

/* 文字列型 → 日付型への型変換で指定するフォーマットは合っているだろうか？ */
SELECT TO_DATE('2021/04/16', 'YYYY/MM/DD');

JOINのコーディング量削減 ※限定利用推奨

新人研修などでも「内部結合と外部結合の理解は必須」などと言われるかと思いますが、データベース(RDBMS)を利用する場合、JOINは日常的に書く事になります。

コピー & ペーストである程度楽できるとはいえ、結合する両テーブルのカラムを指定したり、「=」や「AND」、半角スペースを入れたりと、キーボードを叩く手数は少なくありません。

システム内でのコーディングスタイルの一貫性が損なわれる可能性がある(ONとUSINGが混在すると)といったような理由のため、アプリケーションに組み込むSQLで利用する事は推奨しませんが、結合するテーブル間で結合キーの物理名が同じ場合には(条件が整っていれば)「USING」句を利用する記述方法で、コーディング量を1/2以下に減らす事ができます。

Sample

USINGとONの比較

/* USING */
SELECT
    *
FROM
    table_1
        JOIN table_2    /* 内部結合の場合は「INNER」も省略してしましょう */
            USING (join_key1, join_key2)
;

/* ON */
SELECT
    *
FROM
    table_1
        JOIN table_2
            ON      table_1.join_key1 = table_2.join_key1
                AND table_1.join_key2 = table_2.join_key2
;

コメント化/解除ショートカットキー

SQLに限った話ではないですが、普段利用するツールのショートカットキーを把握しておく事は作業の効率化に繋がります。SQLの場合、ショートカットキーの中でも特に利用頻度が多いのが行単位でコメント化/解除(行の先頭に「– 」を挿入する/消す)を行うものです。

ツールによって異なりますが、私の知る限りは「Ctrl + /」が割り当てられているケースが多いです。
本当によく利用するので知っていると便利です。

Sample

WHERE句の条件指定を色々と変えながらデータを探す

SELECT
    *
FROM
    table_1
WHERE
        xxx_month = '202104'
 --   AND yyyy_code = '0001'

まとめ

研修や入門書では触れられなさそうな、地味な「小技」を紹介してみました。

SQLに限らず実施頻度が多い作業はなるべく効率化を検討し、生産性を上げていきましょう。