データを出してと頼まれた時のSQLコードの書くスピードについて

Question

SQLコードの記述スピードについての質問です。

PostgreSQLにてデータ出しをすることが多いのですが、自分ではかなり時間が掛かってしまうなと思うことが多いです。
記述のスピード感としては、中間テーブルなど様々なテーブルを用いて200行前後のコードで10時間ほど掛かってしまいます。
使用するテーブルは大体5個ほど。

触っているテーブルは10個ほどのテーブルを使い回しているというのが多いですが、データを出す状況に応じてかなり条件文が変わります。

######自身で感じている原因
遅くなってしまっている原因としては、出したデータが間違えていることが多く（集計漏れや重複集計など）、サブクエリを作成する度に本当に意図しているデータなのか逐次確認しているからだと考えています。

#####自身のSQLレベル感

• 本格的にSQLを触り始めたのは半年前から
• 集約関数・ウィンドウ関数などは問題なく使えている（と思います）
• SQL身につけたのは実務でググりながら身につけて他者からのコードレビューは受ける機会がなかった
• まだまだ知らない記法や関数などあるかもしれないです
• サブクエリ多用してしまっている

そこで以下の質問です。

1. データを出す速度を早めるために改善すべき点や気をつけるべき点はどこか（環境・エディターなど含め）
2. 間違えているデータを出さないために気をつけるべき点・または間違えていることにいち早く気付ける方法など

回答者様の思考や過去の経験から上達するのに役立つ情報などが差し支えなければ教えていただきたいです。
よろしくお願い致します。

Answer 1

イマイチ質問したいところがわからないけど…。

単純に

• 個々のテーブルから必要なデータを取得するクエリを書く
• 結合が必要なテーブルを１つずつ結合して、過不足なく取得できているか確認する
• 上記作業を繰り返して複数の結果セットを最終的に欲しい形に結合する

基本的にはこの手順をやってりゃ良いだけでは。

複数のテーブルの結果セットをいきなり組み合わせたり、複数の条件を満たすクエリをいきなり書こうとするから間違えるんじゃないですか？

テーブル結合が必要な場合、１個ずつ分割して出来た結果セットを「一つのテーブル」だと考えればそんなに難しい話じゃないはず。

シンプルな問題に分割していくのは、どんなプログラミングでも共通の基礎だと思うけども。

Answer 2

1.データを出す速度を早めるために改善すべき点や気をつけるべき点はどこか（環境・エディターなど含め）

・データの抽出と条件を分ける事
テーブルの結合条件は決まっているのですから、用途に応じて条件を変えれば良いだけにする。
抽出項目は削除したり追加すれば良いだけ。
条件の無いビューに対して、キーを条件にした条件用のクエリーを準備するイメージです。
ビューと条件用のクエリーではテーブルが重複しますが、分離させた方が効率的です。

サブクエリ多用してしまっている

分離する事で、使う場面が絞り込まれると思います。

・記録する事
そのシステムで使用したSQLは保存しておき、流用する。

・簡潔な記述になるようにする。
複雑になるとネストが深くなったりしますが、withを使用したり、テーブル構造を型として利用する事ができるpostgresの特徴(*)を利用すると簡潔になります。

*
リレーションの関係で同じ項目名があった場合のネストで、項目にエイリアスを付けないと駄目な場面で活躍します。
例えば、(key, value)の構造を持つtblA,tblB,tblCがあったとして以下の様に記述できます。

select (AB.recA).*, (AB.recB).value, c.value
from (
  select (A.*)::tblA recA, (B.*)::tblB recB
  from tblA a left join tblB b
       on a.key=b.key
) AB left join tblC c
on (AB.recb).Key=c.key

・一括のコピー＆ペーストが出来る場面を探す。
項目の列挙や条件の列挙など、外部の文字情報を１項目ではなく纏めてコピーできると効率的です。
矩形貼付けなどが出来るテキストエディタの活用と、インデントを揃える習慣付けで効率化が図れます。

また、処理用のテーブルを準備して貼付けして利用するような事も考えられます。

・ツールの活用
分析結果の報告する場合excelが多いと思いますが、報告の為の整形の手間も見落としがちです。
SQLの結果をエクセルにコピー＆ペーストするだけで済むように工夫すると良いでしょう。

postgresの場合pivotは面倒ですから、Accessでクロス集計を行うなど他のツールを使うとか、array_aggで纏めたものをエクセルの区切り位置指定で分割するとか、SQLだけではなく他にも効率化できるところがあったりします。

同じような事をした場合に使ったツールは（サクラエディタ,pgadmin,A5-SQL,Access,Excel）辺りですね。
特にサクラエディタは、SQLソースのメンテや、大量データの確認など非常に有用です。

2.間違えているデータを出さないために気をつけるべき点。または間違えていることにいち早く気付ける方法など

・条件に関係するものもselect項目にして確認
大量データで条件だけにしか記述が無いものだと確認が取り辛いですから、select項目として、区分などに編集する。

Answer 3

ひとつのSELECT文にJOINを使うことで複数のテーブルやビューを書けることをご存知でしょうか？ひとつのSELECT文でテーブル5個くらい使うことって、ごく普通にあります。

昔、大きな銀行で動いているであろうシステムのパフォーマンス・チューニングでひとつのSELECT文でテーブルを1つしか書いていないSQLがほとんどだったことがあります。SQL入門レベルの技術もない人だけを使ってシステムを作ったのは明らかでした。JOINを使ってSQLのパフォーマンスを改善することを提案したのですが、SQLは変更しない、ということでなんら改善するところなく契約期間は終了しました。