【SQL】集約とカット
前回の続きで第四章の主要点をまとめていきたいと思います。 (具体的なテーブル表など割愛させてもらうことが多いです)
今回は「集合指向」についておってみます。
SQLの考え方として、行単位でなく、行の集合単位でまとめて記述するというものがあります。
集約
集約関数は以下の5つが挙げられます。
- COUNT
- SUM
- AVG
- MAX
- MIN
これらは主に複数行を1つ行にまとめる機能を持っています。
どういう場合に活用できるかというと、以下に例をあげてみます。
非集約テーブルという、ある対象になるデータに対して情報が複数行に分散されている場合があります。
この場合、その対象のデータをSELECTをすると当然ながら複数行出力されます。 ただ今回は、1件(1行)のみ出力させたいとします。
安易にUNIONで複数行のクエリをマージすることはアンチパターンになってしまいます。
この場合は、MAX関数を使用することをおすすめします。
SELECTで、MAX (CASE WHEN
さてこのSQLを実行すると実行計画として得られるのは、'ハッシュ'というアルゴリズムが使用されていることに気付きます。
この時(ソートでも同様だが)、メモリを多く使用する演算であるため、十分のハッシュ用のワーキングメモリが必要です。 スワップが発生するとストレージ上のファイルを使用するので遅延が想定されます。(= TEMP落ち )
カット
いきなりSQLを見てみましょう。
SELECT CASE WHEN age < 20 THEN '子供' WHEN age BETWEEN 20 AND 69 THEN '成人' WHEN age >= 70 THEN '老人' ELSE NULL END AS age_class, COUNT(*) FROM Persons GROUP BY WHEN age < 20 THEN '子供' WHEN age BETWEEN 20 AND 69 THEN '成人' WHEN age >= 70 THEN '老人' ELSE NULL END;
なんとなくどういうものを抽象したのかわかるでしょう。
このSQLの実行計画もハッシュが使われます。 カットの基準となるキーをGROUP BYとSELECT句の両方に記述するのがポイントです。
これはお互いに重複する要素を持たない部分集合のことでパーティションといいます。
PARTITION BY
PARTITION BY句にもGROUP BYと同様に単純な列名でなく、CASE式や計算式などを用いて複雑な記述ができます。
先程のSQLにPARTITION BYをこのようにいれてみると、 年齢階層内で年齢のランクを設けて出力ができます。
RANK() OVER ( PARTITION BY CASE WHEN age < 20 THEN '子供' WHEN age BETWEEN 20 AND 69 THEN '成人' WHEN age >= 70 THEN '老人' ELSE NULL END ORDER BY age) AS age_rank_in_class
条件として扱えば、入力に情報えお付け加えるだけでなく、オリジナルのテーブルとして情報を保存できています。 もとのPersonsテーブルの行が全て出力されました。
まとめ
PARTITION BY句は集合のカットをしていること。 GROUP BY句やウィンドウ関数は、内部的にハッシュやソートを行っていることを覚えておきましょう。 GROUP BY、CASE式、ウィンドウ関数をうまく利用できるようになれたらいいですね。
- 前回の記事