helloworlds

not a noun, it's a verb

【SQL】集約とカット

前回の続きで第四章の主要点をまとめていきたいと思います。 (具体的なテーブル表など割愛させてもらうことが多いです)

o21o21.hatenablog.jp

今回は「集合指向」についておってみます。

SQLの考え方として、行単位でなく、行の集合単位でまとめて記述するというものがあります。

集約

集約関数は以下の5つが挙げられます。

  • COUNT
  • SUM
  • AVG
  • MAX
  • MIN

これらは主に複数行を1つ行にまとめる機能を持っています。

どういう場合に活用できるかというと、以下に例をあげてみます。

非集約テーブルという、ある対象になるデータに対して情報が複数行に分散されている場合があります。

この場合、その対象のデータをSELECTをすると当然ながら複数行出力されます。 ただ今回は、1件(1行)のみ出力させたいとします。

安易にUNIONで複数行のクエリをマージすることはアンチパターンになってしまいます。

この場合は、MAX関数を使用することをおすすめします。 SELECTで、MAX (CASE WHEN = 'A' THEN ELSE NULL END) AS こんな形で使用します。

さてこの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式、ウィンドウ関数をうまく利用できるようになれたらいいですね。

  • 前回の記事

o21o21.hatenablog.jp