SQL関数
シナリオ
SQL関数に適用されるクラスは、他のRule-Alias-Functionインスタンスとは異なるEmbed-UserFunctionです。 App Explorerでは、Embed-UserFunctionクラスでフィルター処理して、SQL関数のみを表示することができます。
あなたは、FSGのBookingアプリケーションの強化をサポートするリードシステムアーキテクト(LSA)だとします。 あなたは、ある場所で行われたFSG主催のイベントの出来栄えについて、お客様の意見を把握するタスクを任されました。 お客様には、FSGのパフォーマンスを0から10までの数字で評価していただきます。 0と10という数字は、ネットプロモータースコア(NPS)の最小値と最大値を表します。 この値の範囲は、以下の3つのカテゴリーで構成されます。
- 0~6:批判者
- 7~8:中立者
- 9~10:推奨者
ネットプロモータースコアの取得後、FSGは以下の例のような出力を生成するレポートを定義することを希望します。 レポートは、場所ごとに分類されます
場所 | 合計イベント | 推奨者 | 中立者 | 批判者 |
ABCスタジアム | 20 | 13 | 4 | 3 |
DEFコンサートホール | 11 | 3 | 6 | 2 |
ソリューション設計
デシジョンルールを使用したカテゴリーの事前計算は、複数の理由からあまり良い選択肢ではありません。
- データ冗長性:元の値が更新されたと仮定
- 「WHAT IF」分析の抑制
デシジョンルールを使用せずにこの出力を生成する方法は、以下のように他にもあります。
- カスタムSQL関数を使用して、NPSが特定の範囲内にあるかどうかを示すフラグを設定し、SUM関数を使用します。 SQL関数を3回使用します。
- パラメーター化されたサブレポートを使用して、NPSが特定の範囲内にあるかどうかをカウントします。 サブレポートを3回使用します。
推奨されるアプローチ、v1
最適なアプローチは、3つの整数パラメーター(値、範囲の開始、範囲の終了など)を持つSQL関数を定義することです。 SQL関数は、以下のコードを使用しています。
CASE WHEN {1} >= {2} AND {1} <= {3} THEN 1
ELSE 0
END
レポートでは、各列が表すカテゴリー(推奨者、中立者、批判者など)に応じてラベル付けされています。 推奨者列の2番目と3番目のパラメーターは、それぞれ9と10です。
手順
- FSG COEは、FSGルールセットに抽象的なFSG-Data-Feedbackクラスを定義しました。 Bookingチームはパターン継承を使用して、抽象的なFSG-Data-FeedbackクラスをFSG-Data-Feedback-Eventという具体的なクラスに拡張しました。 Bookingチームは、Bookingアプリケーションが所有するBookEventルールセット内にFSG-Data-Feedback-Eventクラスを作成します。
- FSG-Data-Feedback-EventクラスにはEventRefプロパティが含まれ、
FSG-Booking-Work-BookEvent
ケースのpyIDと等しい値が設定されます。 - 再利用の可能性を最大化するために、カスタムSQL関数を利用したレポートディフィニッションは、FではなくFSG-Data-Feedback-Eventで定義されますSG-Booking-Work-BookEvent.
- FSG-Booking-Work-BookEvent
WHERE .EventRef = EVENT.pyID
へのレポートディフィニッションINNER JOINFSG-Data-Feedback-Event では、「EVENT」がJOINエイリアスになります。 - また、
.EVENT.VenueGUID = VENUE.pyGUID
を使用するFSG-Booking-Work-BookEventからFSG-Data-VenueへのレポートディフィニッションINNER JOINでは、「VENUE」がJOINエイリアスになります。 - レポートディフィニッション
GROUP BY VENUE.Name
では、レポートに残った列は集約関数を使用し、最初の列はCOUNT EVENT.pyID
です。 - 右側の3つの集計列では、SUM()関数を使用しています。
- 各SUM()関数に入力するのは、上述のカスタムSQL関数です。 そのカスタムSQL関数の第一引数は、
FSG-Data-Feedback
.NetPromoterScoreプロパティです。 カスタムSQL関数の第2引数と第3引数には、9や10などのリテラル値、または前述の参照テーブルへのJOINで得られる値を指定します。
推奨されるアプローチ、v2
上記のv1の代わりに、SELECTサブクエリーを使用して、以下に示すようにライブデータ行のグループ化から開始と終了の範囲の値を取得できます。
CASE WHEN {1} >= (select RANGE_START from RANGE_VALUES where RANGE_NAME = {2} AND RANGE_GROUP = {3})
AND {1} <= (select RANGE_END from RANGE_VALUES where RANGE_NAME = {2} AND RANGE_GROUP = {3})
THEN 1
ELSE 0
END
上記のサブクエリーでは、RANGE_GROUP、RANGE_NAME、START、およびENDの列名で参照テーブルが定義されているとします。
RANGE_GROUP | RANGE_NAME | RANGE_START | RANGE_END |
NPS | 推奨者 | 9 | 10 |
NPS | 中立者 | 7 | 8 |
NPS | 批判者 | 0 | 6 |
別のアプローチ
この方法は推奨されません。
オルタネートソリューションでは、各サブレポートを適切に機能させるためにOUTER JOINを使用する必要があります。 明らかに、OUTER JOINが行われた3つのサブクエリーが必要なレポートは、SQL関数アプローチで生成されたSQLよりもパフォーマンスが低くなります。 その理由は、照会した各行の検査が行われ、3つの各カテゴリーで単純な比較ロジックが使用され、0または1が出力されるため、SQL関数が単純だからです。 同時に、SUM集約関数は、GROUP BYの一意の組み合わせ(ここでは場所の名前のみ)ごとに、ケースの出力を実行中のその合計値に追加します。
一方、サブレポートのアプローチでは、イベントの合計数をカウントするメインレポートのGROUP BYに、OUTER JOINが行われた3つのサブクエリーからのカウントも「追加」する必要があります。 これは、データベースで実行するには複雑さが増すため、パフォーマンスも低くなります。
このトピックは、下記のモジュールにも含まれています。
- 高度なクエリー設計 v3