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 * を使用するとエラーが発生する可能性があります。