付け焼き刃でSQLやってた人が見落としてた色々な事

達人に学ぶ SQL徹底指南書が難しめだったので、同著者の簡単そうな本SQL 第2版 ゼロからはじめるデータベース操作を1から読んでみました。意外とSQLわかった気になっていて見落としていた(知らなかった)点が多かったのでまとめてみます

DISTINCTはCOUNT関数の引数として使用可能

DISTINCTを使用したCOUNT

SELECT COUNT(DISTINCT shohin_bunrui)
  FROM Shohin;

上記はShohinテーブルに存在するレコードのshohin_bunruiを重複なく表示するSQL。
COUNT()の中にDISTINCT使えるとか知らなかったので下みたいな頭悪いサブクエリ書いてました(笑)

サブクエリを使った悪い例

SELECT COUNT(*) FROM(
  SELECT DISTINCT shohin_bunrui
    FROM Shohin
  );

TRUNCATE

TRUNCATEの使い方

TRUNCATE TABLE <テーブル名>;

WHERE句は使えず、テーブルの全レコードを削除する。
DELETE文より高速。そもそもDELETE文はDMLの中でも処理速度が遅いため、全レコードを消す場合はTRUNCATEのほうが良いらしい。
ただしOracleではDMLではなくDDLとして定義されているためROLLBACKができない

本書ではSQL:2003で書いているので特に明言されていないが
SQL:2008から標準機能となった。¹

ウインドウ関数

OLAP関数(OnLine Analytical Processing)とも呼ばれる
累計、移動平均、ランキングなどを簡単に計算できる。

移動平均の例

SELECT Avg(hanbai_tanka) 
         OVER ( 
           ORDER BY sohin_id ROWS 2 preceding --前の2行まで集計
         ) AS moving_avg 
FROM   shohin; 

上記SQLで直近3行の移動平均を求めることができる。
例えば月ごとの売上に直近3ヶ月の売上平均を並べたSELECT文とか書ける

GROUPING演算子

ROLLUP,CUBEなど。
ROLLUPでは個別のレコードと集計した合計をUNIONした形のようなものを短く書ける。

ROLLUP演算子の例

SELECT sales_date,SUM(sales_amount) AS 売上
  FROM sales
GROUP BY ROLLUP(sales_date)

sales_date	売上
	37000
2012-12-25	32500
2012-12-26	500
2012-12-27	2500
2012-12-28	1500

上記の例では一番上に合計、下に続いて書く日の売上が出力されている。
ぱっと思いついた使い所は、スーパーやファミレスのレシート形式のデータ。
ああいう形式のものは一本のSQLでつくれそう。

SELECTで付けた列の別名はGROUP BYでは使用できないが、ORDER BYでは使用できる

別名

SELECT
    item_name AS name, --別名その1
    item_code AS code, --別名その2
    count(amount) AS total_amount
FROM
    t_sales
WHERE
    sales_date > 20161225
GROUP BY
    --☓別名使用不可。item_name、item_codeと正しいテーブル名を書く必要がある。
    name,
    code
ORDER BY
    --◯こっちでは別名使用可能
    name;

1. FROM
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. ORDER BY

の順で実行されるので上記のような現象がおこってしまう。
ただしPostgreSQLではGROUP BYの中でSELECT中の別名が使用可能なので、上記のSQLが実行できる。
ちなみ上記の順から察するとおり、WHEREの中でもSELECTで付けた列別名は使えない。

ソート処理はコストが大きい(処理が重い)

本書によると、ソートはマシンに負荷を「重い」処理であるらしい(3章-3のCOLUMより)
なので以下の点に気をつける必要がある。

UNION ALLのほうがソートが発生しない分UNIONより速い

UNIONでは重複結果を除くためのソートが発生するため、UNION ALLのほうが速い。
重複業が発生しないとわかっている場合はUNION ALLと書いたほうが良い。

HAVINGよりWHEREに条件を記述するほうが処理速度がはやい

HAVINGには集約関数の他にGROUP BYで指定した集約キーも書くことができるが、
ソートする行数をWHEREで絞り込めるので
帰ってくる結果が同じでもWHEREに書いたほうがはやい。
そもそもグループに対する条件をHAVINGに、行に対する条件をWHEREに書くことを勧めている。

LIKEで%以外に使える記号"_"

'-'を使ったLIKEの例

SELECT
    *
FROM
    t_word
WHERE
    word_name LIKE '___able'; --3文字+ableに一致する単語を表示する

上記の例だと「enable」は一致するが「stable」は一致しない。(もちろん'__able'と書くことで2文字 + ableを検索可。)
LIKE '%中間一致%'な書き方はよく使うが、'_'で文字数を固定できるのは知らなかった。

BETWEEN と <>

BETWEENは以上、以下の範囲で検索する。
より大きい、未満の範囲で条件を書きたい場合、<と>を使わなければならない。
ちなみにBETWEEN使わなずに<=と>=でなんとかやっていたので、特に意識したことはなかった。

JOINの結合条件にBETWEENなどの述語が使える

構文的には問題ない。
1=1を書くとCROSS JOINのような出力結果になる

内部結合にJOIN ONを使わない構文は古い書き方らしい

古い結合の構文

SELECT
    *
FROM
    TenpoShohin TS,
    Shohin S
WHERE
    TS.shohin_id = S.shohin_id
AND TS.tenpo_id = '000A';

SQLとしては正しいが

• 結合が内部結合なのか外部結合なのか一目でわからない
• 結合条件とレコードの制限条件なのか一目でわからない
• 構文が古いので使えなくなる日が来る

本書では上記3点の理由で使用は非推奨。
ただし、この書き方が残っているので読めるようになっておく必要はあるとのこと。

脚注

1. https://en.wikipedia.org/wiki/Truncate_(SQL) ↩