helloworlds

色々試してみたくなっちゃうエンジニア 基礎をコツコツ (文系エンジニア) 師匠ほしい!w

【SQL】サブクエリの基礎

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

今回は「サブクエリ」についてです。

サブクエリとは、SQLの中で作成される一時的なテーブルということを抑えましょう。 業務中では、困るとよくサブクエリを使用したりする癖がわたしはありますが、本当に効率的なのかこの章では考えてみます。

先に前提をはっきり意識するために以下を覚えておきます。

  • テーブル 永続的且つデータを保持する。

  • VIEW(ビュー) 永続的だがデータは保持しない。アクセスのたびにSELECT文が実行される。

  • サブクエリ 非永続的で生存期間(スコープ)がSQL文の実行中に限られる。

サブクエリの問題

サブクエリはデータを保持しないことを意識する

実態的なデータを保持しないということで、当たり前ですがSELECT実行にかかるコストが上乗せされます。 このサブクエリが複雑なほどコストは落ちることになります。

面倒になってくるとサブクエリが多少複雑でも欲しい結果が得られれば「これでいいや!」ってよくなりますね笑

また、オプティマイザがクエリを解析するために必要な情報がサブクエリの結果からは得られません。

サブクエリが有効なときはどういうときなのか、ここからは考えてみます。

サブクエリが有効なとき

テーブル Receiptsを想定してみます。 このテーブルは顧客の購入明細を記録するテーブルで、連番(seq)列は顧客の古い購入ほど小さな値が振られています。 ここで最小の連番(seq)の金額(price)を求めることを考えてみると、ある顧客に対して一番古い購入履歴を見つけることと同義です。

このSQL文を考えるとき、テーブルへのアクセスを1回にします。I/Oを考慮します。

それと、ウィンドウ関数、ROW_NUMBERを使います。

ROW_NUMBERで行に連番を振り、常に最小値を1にすることで、seq列の最小値が不確定という問題を対処します。

SELECT cust_id, seq, price
FROM ( SELECT cust_id, seq, price,
                            ROW_NUMBER () OVER ( PARTITION BY cust_id
                                                                       ORDER BY seq) AS row_seq
              FROM Receipts ) WORK
WHERE WORK.row_seq = 1;

こうしたクエリでないとテーブルへのアクセスが2回になったり、クエリ自体が読みにくくなってしまいます。

また、テーブル Companiesテーブルとテーブル Shopsテーブルを想定します。 Companiesには、co_cd(会社コード)とdistrict(地域)があって、 Shopsには、co_cd(会社コード)、shop_id(事業所ID),emp_nbr(従業員数)、main_flg(主要事業所フラグ)があります。

1:Nの親子関係があるテーブルだと思って下さい。

ここから会社ごとの主要事業所の従業員数を含む結果(会社の地域を含めた)を得たいとします。

この結果を求めるには2通りのSQLが考えられます。

  • 結合を行ってから集約を行う
  • 集約を行ってから結合を行う

INNER JOINでShopsを指定するのか、INNER JOINでサブクエリを組んでそれを仮テーブルとして集約を先に行うかです。

この2つのクエリの結果は同値です。 そして文的にも複雑でないので問題ありません。 ですが、「どっちのほうがいいの?」と疑問におもってしまいます。

答えとしては、性能的に違いが出て来るケースがあり、パフォーマンスに影響してくるということです。

ポイントは、対象行数です。

前者は、 * 会社テーブル : 4行 * 事業所テーブル: 10行

後者は、 * 会社テーブル : 4行 * 事業所テーブル(仮テーブル名) : 4行

後者のテーブルでは、会社コードで集約されると4行になります。

先に集約してから結合対象の行数を絞ってあげるとI/Oコストを削減できるというわけです。 これはデータの量が増えるほど期待できます。

まとめ

サブクエリは欲しい結果の対して便利な道具であるが、パフォーマンスをかえって悪化させてしまうかもしれない。 I/Oを考え、困難を分割することは悪くないが、ウィンドウ関数や結合対象のテーブルを事前に絞ることをわすれずにすれば、 I/Oコスト的にも、SQL文的にも改善できる点が見つかるかもしれません。

  • 前回の記事

o21o21.hatenablog.jp