効率的なSQLの書き方とは

単純にデータベース設計に関わってきたのでメモ程度に書きます。随時更新していく予定です。

==== 08/27更新 ====

ORDER BY

指定したカラムを基準にして昇順・降順でソートします

• ASC・・・昇順（小さい順）
• DESC・・降順（大きい順）

サンプルデータ

• user_tbl

ut_user_id	ut_user_name
1	A
2	B
3	C
4	A

数値で並び替えするパターン

#--- 数値並び替え（昇順）
SELECT
       *
FROM
       user_tbl
ORDER BY
       ut_user_id ASC;

ut_user_id	ut_user_name
1	A
2	B
3	C
4	A

#--- 数値並び替え（降順）
SELECT
       *
FROM
       user_tbl
ORDER BY
       ut_user_id DESC;

ut_user_id	ut_user_name
4	A
3	C
2	B
1	A

文字列で並び替えするパターン

#--- 文字列並び替え（昇順）
SELECT
      *
FROM
      user_tbl
ORDER BY ut_user_name ASC;

ut_user_id	ut_user_name
1	A
4	A
2	B
3	C

#--- 文字列並び替え（降順）
SELECT
      *
FROM
      user_tbl
ORDER BY ut_user_name DESC;

ut_user_id	ut_user_name
3	C
2	B
4	A
1	A

数値、文字列（複数カラム）で並び替えするパターン

#--- 数値（昇順）、文字列並び替え（昇順）
SELECT
      *
FROM
      user_tbl
ORDER BY
      ut_user_id,
      ut_user_name ASC;

ut_user_id	ut_user_name
1	A
4	A
2	B
3	C

#--- 数値（降順）、文字列並び替え（昇順）
SELECT
      *
FROM
      user_tbl
ORDER BY
      ut_user_name DESC,
      ut_user_id ASC;

ut_user_id	ut_user_name
4	A
1	A
2	B
3	C

GROUP BY

主にデータを集約するときに利用します

サンプルデータ

• user_tbl

ut_user_id	ut_user_name
1	A
2	B
3	C
4	A

• product_tbl

pt_product_id	pt_product_name
1001	JZA80
1002	AE86
1003	FT86
1004	BNR34

• user_product_tbl

upt_user_id	upt_product_id
1	1001
1	1003
2	1001
3	1002
3	1004
4	1001

とりあえず全件表示

SELECT
      upt_user_id
      , ut_user_name
      , upt_product_id
FROM
      user_product_tbl

INNER JOIN
      user_tbl ON ut_user_id = upt_user_id;

upt_user_id	ut_user_name	upt_product_id
1	A	1001
1	A	1003
2	B	1001
3	C	1002
3	C	1004
4	D	1001

upt_user_idでグルーピング

SELECT
      upt_user_id
      , ut_user_name
      , upt_product_id
FROM
      user_product_tbl

INNER JOIN
      user_tbl ON ut_user_id = upt_user_id
      
GROUP BY upt_user_id;

upt_user_id	ut_user_name	upt_product_id
1	A	1003
2	B	1001
3	C	1004
4	D	1001

upt_user_idが１つにまとまってデータ的には見やすくなりました。しかし、upt_product_idまでもが自動的にまとまってしまって、実際のデータとは異なる表示がされています。

GROUP_CONCAT

この命令を利用することでGROUP BY句によって集約されたデータを復元することができます。

SELECT
      upt_user_id
      , ut_user_name
      , GROUP_CONCAT(upt_product_id) as 'upt_product_id '
FROM
      user_product_tbl

INNER JOIN
      user_tbl ON ut_user_id = upt_user_id
      
GROUP BY upt_user_id;

upt_user_id	ut_user_name	upt_product_id
1	A	1001,1003
2	B	1001
3	C	1002,1004
4	D	1001

また区切り文字を変更することもできます

SELECT
      upt_user_id
      , ut_user_name
      , GROUP_CONCAT(upt_product_id SEPARATOR '／') as upt_product_id
FROM
      user_product_tbl

INNER JOIN
      user_tbl ON ut_user_id = upt_user_id
GROUP BY upt_user_id;

upt_user_id	ut_user_name	upt_product_id
1	A	1001／1003
2	B	1001
3	C	1002／1004
4	D	1001

さらにはGROUP_CONCATのなかでORDER BYを使うこともできます。何も指定していない場合にはASCがデフォルトになります。

SELECT
      upt_user_id
      , ut_user_name
      , GROUP_CONCAT(
            upt_product_id ORDER BY upt_product_id ASC
            SEPARATOR '／'
    ) as upt_product_id 
FROM
      user_product_tbl

INNER JOIN
      user_tbl ON ut_user_id = upt_user_id
GROUP BY upt_user_id;

upt_user_id	ut_user_name	upt_product_id
1	A	1001／1003
2	B	1001
3	C	1002／1004
4	D	1001

HAVING

HAVING句はWHERE句と異なりGROUP BY句によってグループ化が行われたデータに対して条件を指定してデータを絞り込む場合に使用します。

SELECT
      upt_user_id
      , ut_user_name
      , GROUP_CONCAT(
            upt_product_id ORDER BY upt_product_id ASC
            SEPARATOR '／'
     ) as products 
FROM
      user_product_tbl
INNER JOIN
      user_tbl ON ut_user_id = upt_user_id
GROUP BY upt_user_id
HAVING COUNT(products) > 1;

upt_user_id	ut_user_name	products
1	A	1001／1003
3	C	1002／1004

このように記述することで、GROUP_BYによってまとめられたカラムの個数に応じて取得できるデータを編集することができます。

CASE

SQL文中でも条件分岐を表現することができます。以下の書式で書かれた文法をCASE式といいます。CASE式には単純CASE式と検索CASE式がある。

#--単純CASE式
CASE gender
    WHEN 'man'   THEN 1
    WHEN 'woman' THEN 2
    ELSE 99
END

#--検索CASE式
CASE
    WHEN gender = 'man'   THEN 1
    WHEN gender = 'woman' THEN 2
    ELSE 99
END

CASE式を使うときには「条件は排他的に書くこと」「ELSEも必ず書くこと」

また、CASE式は便利ですが、下手をすると混乱を招くので以下のような書き方はやめた方がいいです。

#-- 評価されない条件が存在する
CASE
    WHEN gender = 'man' OR gender = 'woman' THEN 0
    WHEN gender = 'man'   THEN 1
    WHEN gender = 'woman' THEN 2
END

#-- どの条件にも当てはまらなかった場合NULLになる
#-- NULLを取得したい場合も、明示的に書くべきである
CASE
    WHEN gender = 'man'   THEN 1
    WHEN gender = 'woman' THEN 2
END

実例）フラグ

SELECT
    user_id
    , user_name
    , product_point
    , (
      CASE
          WHEN product_point > 80 THEN 1
          ELSE 0 
      END
    ) AS high_point
FROM
    user_data
ORDER BY user_id ASC

実例）階級分け

SELECT
    user_id
    , user_name
    , product_point
    , (
      CASE
          WHEN product_point >= 80 THEN 'S'
          WHEN product_point >= 60 THEN 'A'
          WHEN product_point >= 40 THEN 'B'
          ELSE 'C'
      END
    ) AS grade
FROM
    user_data
ORDER BY user_id ASC

その他実例

qiita.com