[解決済み] SQLクエリのテストに最適な方法 [終了しました]。

質問

複雑な SQL クエリーがエラーで出力され続けるという問題に遭遇しました。基本的に、これは間違った顧客にメールを送ったり、そのような他の「問題」のような結果になります。

このような SQL クエリを作成することについて、みなさんはどのような経験をお持ちでしょうか。私たちは 1 週間おきに新しいコホート データを作成しています。

そこで、私の考えとそれに対する制限をいくつか紹介します。

• テストデータの作成 これは、私たちがすべての正しいデータを持っていることを証明しますが、実稼働時の異常の除外を強制するものではありません。 これは、今日では間違っていると考えられるが、10 年前には正しかったかもしれないデータのことで、文書化されていなかったため、データが抽出された後に初めて知ることになります。

• ベン図やデータマップを作成する これはクエリの設計をテストする堅実な方法のように見えますが、しかし実装が正しいことを保証するものではありません。 これは、開発者が前もって計画を立て、書くときに何が起こっているかを考えるようにします。

私の問題に与えてくれるどんな意見にも感謝します。

どのように解決するのですか？

あなたは200行の長い関数でアプリケーションを書くことはないでしょう。これらの長い関数を、それぞれ明確に定義された1つの責任を持つ、より小さな関数に分解するのです。

なぜそのようなSQLを書くのでしょうか？

クエリを分解してください。 関数を分解するのと同じように。これにより、クエリはより短く、よりシンプルに、より理解しやすくなります。 テストしやすくなる そしてリファクタリングが容易になります。そして、手続き型コードで行うのと同じように、それらの間に「シム」を追加したり、それらの周りに「ラッパー」を追加したりすることができるようになります。

どのようにこれを行うのでしょうか？クエリが行う重要なことをそれぞれビューにするのです。そして を構成します。 ちょうど、より原始的な関数からより複雑な関数を構成するように、これらの単純なビューから、より複雑なクエリを構成します。

そして、素晴らしいことに ほとんどの の構成では、RDBMS から全く同じパフォーマンスを得ることができます。(そうでないものもありますが、だから何ですか? 早すぎる最適化は諸悪の根源です。まずは正しくコーディングしましょう。 その後 必要であれば最適化する)

複雑なクエリを分解するためにいくつかのビューを使用する例です。

この例では、各ビューが1つの変換を追加するだけなので、エラーを見つけるためにそれぞれを独立してテストすることができ、テストは簡単です。

ここに例のベースとなるテーブルがあります。

create table month_value( 
    eid int not null, month int, year int,  value int );

この表は欠陥があります。なぜなら、月と年という2つの列を使って、1つのデータである絶対月を表現しているからです。以下は、新しい計算された列の仕様です。

私たちは、(年、月) と同じソート、および (年、月) のタプルに対して 1 つだけの値、およびすべての値が連続するような線形変換としてそれを実行することにします。

create view cm_absolute_month as 
select *, year * 12 + month as absolute_month from month_value;

つまり、どんなタプル(年、月)でも、(absolute_month)は一つしかなく、(absolute_month)は連続であるということです。いくつかのテストを書きましょう。

私たちのテストはSQLの select クエリで、テスト名と case 文が一緒になった構造になっています。テスト名は単なる任意の文字列です。case 文は単に case when テスト文 then 'passed' else 'failed' end .

テスト文は、テストに合格するために真でなければならないSQLのselect（サブクエリ）だけである。

これが最初のテストです。

--a select statement that catenates the test name and the case statement
select concat( 
-- the test name
'For every (year, month) there is one and only one (absolute_month): ', 
-- the case statement
   case when 
-- one or more subqueries
-- in this case, an expected value and an actual value 
-- that must be equal for the test to pass
  ( select count(distinct year, month) from month_value) 
  --expected value,
  = ( select count(distinct absolute_month) from cm_absolute_month)  
  -- actual value
  -- the then and else branches of the case statement
  then 'passed' else 'failed' end
  -- close the concat function and terminate the query 
  ); 
  -- test result.

そのクエリを実行すると、このような結果が得られます。 For every (year, month) there is one and only one (absolute_month): passed

month_valueに十分なテストデータがある限り、このテストは動作します。

十分なテストデータがある場合のテストも追加しておきましょう。

select concat( 'Sufficient and sufficiently varied month_value test data: ',
   case when 
      ( select count(distinct year, month) from month_value) > 10
  and ( select count(distinct year) from month_value) > 3
  and ... more tests 
  then 'passed' else 'failed' end );

では、連続しているかどうかテストしてみましょう。

select concat( '(absolute_month)s are consecutive: ',
case when ( select count(*) from cm_absolute_month a join cm_absolute_month b 
on (     (a.month + 1 = b.month and a.year = b.year) 
      or (a.month = 12 and b.month = 1 and a.year + 1 = b.year) )  
where a.absolute_month + 1 <> b.absolute_month ) = 0 
then 'passed' else 'failed' end );

では、テスト(単なるクエリ)をファイルにまとめ、そのスクリプトをデータベースに対して実行してみましょう。実際、データベースに対して実行されるスクリプト (あるいはスクリプト。関連するビューごとにひとつのファイルを推奨します) にビューの定義を格納すれば、各ビューのためのテストを と同じです。 スクリプトに追加することで、ビューを (再) 作成する際にそのビューのテストも実行することができます。この方法では、ビューを再作成するときにリグレッション テストを取得し、ビューの作成が本番環境で実行されるとき、ビューも本番環境でテストされます。