SQL

SQL の書き方で調べたことのメモ

• MySQL 8.0 Reference Manual
• Presto Documentation

構文の違い

SQL は標準的な仕様が ANSI (American National Standards Institute) によって定めれらている。
しかし、中には SQL-like なだけで実は standard SQL には従っていない構文を使うデータベースもある。
そのため、データベースを触り始める前にはそこで使う構文が standard SQL に従っているかどうか必ず確認する必要がある。

• Hive → HiveQL
• Presto → standard SQL + alpha
• Teradata → TeradataSQL (difference)
• MySQL → standard SQL + alpha

見てみたところ MySQL はほとんど standard SQL に従っているようなので、MySQL がほぼ standard SQL だと思って学習を進めるのが良さそう。

JOIN の動作

テーブルの横方向の結合に用いる JOIN の使い方

• (INNER) JOIN
  2 つのテーブルを比較して結合条件に一致したレコードだけを返す
• LEFT/RIGHT/FULL (OUTER) JOIN
  結合条件に一致しなくても LEFT/RIGHT/FULL で指定したテーブルの全てのレコードを返す
  (NATURAL LEFT/RIGHT JOIN も同じ)
• CROSS JOIN
  2 つのテーブルの全ての組み合わせを返す

ついでに、テーブルの縦方向の結合は UNION ALL/DISTINCT を用いる。
ALL を用いると全てのデータが、DISTINCT (省略可) を用いると重複を除いたデータのみが結合される。

条件分岐

MySQL の条件分岐文 statement と operator の 2 種類がある。

• CASE Statement, IF Statement
• CASE Operator, IF Function

基本的には operator, function の CASE, IF が Presto で使えるよう。

-- CASE Operator
CASE value
    WHEN compare_value THEN result
    [WHEN compare_value THEN result ...]
    [ELSE result]
END

CASE 
    WHEN condition THEN result
    [WHEN condition THEN result ...]
    [ELSE result]
END

-- IF Function
IF(expr1, expr2, expr3)

-- CASE Statement
CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list] ...
    [ELSE statement_list]
END CASE

CASE
    WHEN search_condition THEN statement_list
    [WHEN search_condition THEN statement_list] ...
    [ELSE statement_list]
END CASE

-- IF Statement
IF search_condition THEN statement_list
    [ELSEIF search_condition THEN statement_list] ...
    [ELSE statement_list]
END IF

表示桁数の指定

SQL で表示される桁数を指定したい場合、関数 FORMAT(N, D, local) を用いる。

• N: フォーマットしたい数
• D: 小数点以下の表示したい桁数を指定
• locale (optional):

四捨五入したいときは ROUND() を用いる。

正規表現

関数 REGEXP_LIKE(expr, pattern[, match_type]) を用いることで正規表現が使える。

expr が指定した pattern にマッチした場合 1 を、そうでなければ 0 を返す。
expr or pattern が NULL の場合は NULL を返す。

大文字と小文字の区別

MySQL ではデータベース名とテーブル名は大文字と小文字の区別をすることができる。
(コマンドはどっちでも良い)
Default の設定がどうなっているかは OS に依っており、

• Unix: 区別する
• Windows, macOS: 区別しない

となっている。

現在の状態の確認には lower_case_table_names の値を参照すれば良い。

> SHOW VARIABLES LIKE 'lower_case_%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_file_system | OFF   |
| lower_case_table_names | 1     |
+------------------------+-------+

値の意味は

• 0: 大文字・小文字を区別して保存され、名前比較では大文字と小文字が区別される
• 1: 全て小文字で格納され、名前比較では大文字と小文字は区別されない
• 2: 大文字・小文字を区別して保存されるが MySQL ではルックアップ時に小文字に変換され、名前比較では大文字と小文字が区別されない

MySQL の version の確認方法

やり方は以下の3通り

--
select VERSION();
--
show VARIABLES like '%version%';
--
status;

SQL の実行順序

SQL の標準的な実行順序は以下の順になっている。

以上を踏まえた注意点

• WHERE, GROUP BY, LIMIT などは普通に書いた場合 JOIN の前に実行させることができない (やりたかったら副問合せなどが必要)

しかし、(公式の資料がないことから想像できるように) 細かい実際の実行順序は書かれた SQL query に応じて異なってくるので注意。

Refs.

• SQL Order of Operations – In Which Order MySQL Executes Queries?
• 【SQL】実行の順序

複数条件に対する LIKE

LIKE で複数条件を指定したい場合、

SELECT * FROM some_table WHERE col LIKE IN ('%hoo%', '%bar%');

としたくなるがこれはできない。

この場合、LIKE を OR で繋ぐか、正規表現 REGEXP_INSTR を使う。

SELECT * FROM some_table WHERE col LIKE  '%hoo%' OR col LIKE '%bar%';

SELECT * FROM some_table WHERE REGEXP_INSTR(col, 'hoo|bar');

Naming Convention

SQL では特に命名規則は決まっていないが、How to がありそうなのでまとめておく。

• Use the singular (単数系) for the table name and column names and multiple words are no problem
• Case はなんでも良い (Camel-, Pascal-case が多そう)
• Primary key は (table name) x id の組み合わせにする

GROUP BY と WINDOW 関数の併用

併用できる
group by した後にその total を求めるとかやりたい時に有用