SQL を記述するためのヒント

はじめに

このページでは、SQL 変換を記述するためのヒントとコツを紹介します。

全般

• 和集合では、名前と、フィールドの順序が完全に一致している必要があります。和集合の部分で空のフィールドを作成し、すべてのフィールドを、select ステートメントの null as "Field_X" を使用して揃えなければならない場合があります。
• SQL Server の dbt を記述する場合、Jinja ステートメントは、SQL のコメント内にあるかどうかに関係なく、すべて SQL コードにコンパイルされます。たとえば、以下の -- {{ ref('Table1') }} では、Jinja は SQL コードにコンパイルされます。
• 数値を丸めないでください。不整合が発生する可能性があります。プラットフォームの表示値に丸めた値が必要な場合は、常に自動処理が実行されます。

変換構造

SQL では、すべての変換を同じテーブルで計算できるわけではありません。その理由に挙げられるのが、1 つの選択ステートメントで表せない集計やプロパティです。このためにサポート テーブルを作成できます。データベース内にサポート テーブルを作成すると、そのモデルを複数の変換で使用できます。モデルを再利用する必要がない場合は、サポート変換を前処理クエリとして既存のテーブルに追加することもできます。

サポート対象の変換とその他の変換を区別するには、それらを別のサブディレクトリにグループ化します。

パフォーマンス

クエリの実行を高速化するには、以下の手順を実行します。

• 集計を構築して where 句でレコードを 1 つだけ取得することもできる場合は、select distinct の使用を避けます。
• union の代わりに union all を使用します。union all を使用するとテーブルのレコードが連結されますが、union を使用すると重複が削除されます。
• サイズの大きいデータセットで作業する場合、開発時に扱うデータを制限できます。
• すべてのモデル (1_input ディレクトリ内のモデルを除く) は、dbt によってテーブルとして具体化されます。これは、Process Mining のすべてのコネクタで既定の設定です。詳細については、dbt のドキュメントの「Materializations」をご覧ください。
• id フィールドを生成する際は、pm-utils で利用可能なid() マクロを使用します。例は devkit-connector で確認できます。

SQL スタイル ガイド

Process Mining のアプリ テンプレートの開発には、以下のスタイル ガイドを使用しました。一貫性と読みやすさのために、これらのガイドラインに従うことをお勧めします。

• SQL のコマンドと関数は小文字で記述します。
• select、from、where、join などには同じレベルのインデントを使用し、モデルの構造を容易に理解できるようにします。
  • 読みやすさが向上する場合は、関数の使用時にもインデントを使用します。たとえば、case 関数の各ステートメントに同じインデントを使用します。

• テーブルとフィールドには一貫性のある命名規則を使用し、テーブルやフィールドがデータベースに存在しないことを示す SQL エラーが発生しないようにします。次のガイドラインに従ってください。

  • テーブルとフィールドは大文字で始めます。
  • テーブルとフィールドの別個の単語の間にアンダースコア _ を使用します。
  • フィールドはすべて引用符で囲みます。
  • テーブルは引用符で囲みません。これにより、フィールドには引用符が付くことと併せて、読みやすさが向上します。
• すべてのフィールドに元のテーブルをプレフィックスとして付け、モデルを容易に理解および拡張できるようにします。
• フィールドを区切る際に使用するコンマは行の末尾に配置します。
• 特定の構造を説明する必要がある場合は、インライン コメントを使用します。その場合、コメントを付けることで価値が加わるようにしてください。
• 読みやすさと保守性を確保するため、クエリの変換部分では明確な select ステートメントを使用し、select * は使用しないでください。特に和集合の場合は、select * を使用するとエラーが発生する可能性があります。