カスタム計算とは?

カスタム計算を使用すると、データセットクエリを変更せずに動的なメトリクスと変換を定義できます。この記事では、AI/BI ダッシュボードでカスタム計算を使用する方法について説明します。

カスタム計算を使用する理由

カスタム計算を使用すると、ソース SQL を変更することなく、既存のダッシュボードデータセットから新しいフィールドを作成して視覚化できます。ダッシュボードごとに最大 200 個のカスタム計算を定義できます。

カスタム計算は、次のいずれかの種類です。

• 計算メジャー : 総売上高や平均コストなどの集計値。計算メジャーでは、 AGGREGATE OVER コマンドを使用して、時間範囲全体の値をコンピュートできます。
• 計算ディメンション : 年齢範囲の分類や文字列の書式設定など、集計されていない値または変換。

カスタム計算はメトリクスビューと同様に動作しますが、定義されているデータセットとダッシュボードにスコープが設定されます。他のデータ資産で使用できるカスタムメトリクスを定義するには、 Unity Catalog メトリクスビューを参照してください。

計算メジャーを使用した動的メトリクスの作成

次のデータセットがあるとします。

項目	リージョン	料金	コスト	Date
リンゴ	USA	30	15	2024-01-01
リンゴ	Canada	20	10	2024-01-01
オレンジ	USA	20	15	2024-01-02
オレンジ	Canada	15	10	2024-01-02

地域ごとの利益率を視覚化します。カスタム計算を使用しない場合は、 margin 列を含む新しいデータセットを作成する必要があります。

リージョン	マージン
USA	0.40
Canada	0.43

このアプローチは機能しますが、新しいデータセットは静的であり、1 つの視覚化しかサポートしない場合があります。 元のデータセットに適用されたフィルターは、追加の手動調整を行わない限り、新しいデータセットに影響を与えません。

カスタム計算では、次の式を使用して利益率を集計として表すことができます。

SQL

(SUM(Price) - SUM(Cost)) / SUM(Price)

このメジャーは動的です。ビジュアリゼーションで使用すると、データセットに適用されたフィルターを反映するように自動的に更新されます。

範囲にわたる計算メジャーの定義

ダッシュボードの視覚化で一般的なタスクは、過去 7 日間などの範囲にわたる SUM(sales) などのメジャーをコンピュートすることです。 ダッシュボード データセットの場合は、 AGGREGATE OVER コマンドを使用して、これらのタイプの範囲ベースのメジャー計算を定義します。

AGGREGATE OVER コマンドは、現在のグループまたは区画の外部のデータを組み込むことができます。たとえば、ビジュアライゼーション グループ データを日ごとに表示する場合、末尾の 7 日間の範囲では、各日のデータポイントに過去 6 日間のデータを含めることができます。 月ごとにグループ化されたビジュアライゼーションで同じメジャーが使用されている場合、7 日間の範囲では、各月の過去 7 日間のみが使用されます。

前の例と同じデータセットを使用して、次の式は conピュート the trailing 7 日間の平均利益率です。

SQL

(
  (SUM(Price) - SUM(Cost)) / SUM(Price)
) AGGREGATE OVER (
  ORDER BY Date
  TRAILING 7 DAY
)

作成後、このメジャーは任意の視覚化に適用できます。

AGGREGATE OVER 構文

AGGREGATE OVER コマンドには、次の構文が必要です。

{expr} AGGREGATE OVER (ORDER BY {field} {frame})

引数

• expr

  評価する有効な計算メジャー式

• フィールド (必須)

  有効な列名

• フレーム (必須) 次のいずれかになります。

  • CURRENT
  • CUMULATIVE
  • ALL
  • (TRAILING|LEADING) 番号 単位
    • number は整数です
    • unit が DAY、 MONTH、または YEAR
    • 例: TRAILING 7 DAY または LEADING 1 MONTH

次の表は、集計オーバーのフレーム仕様が同等の SQL ウィンドウ フレーム 句とどのように比較されるかを示しています。

フレーム仕様	同等の SQL ウィンドウフレーム句
CURRENT	RANGE BETWEEN CURRENT ROW AND CURRENT ROW
ALL	RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
CUMULATIVE	RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
TRAILING <NUMBER> <UNIT>	RANGE BETWEEN <NUMBER> <UNIT> PRECEDING and 1 <UNIT> PRECEDING
LEADING <NUMBER> <UNIT>	RANGE BETWEEN 1 <UNIT> FOLLOWING and <NUMBER> <UNIT> FOLLOWING

AGGREGATE OVERとウィンドウ関数との比較

AGGREGATE OVERコマンドは、メトリクスを計算するときによく使われるSQLのウィンドウ関数に似ています。ただし、ウィンドウ関数とは異なり、式にパーティションフィールドを指定せずに AGGREGATE OVER を使用できます。代わりに、視覚化クエリのグループ化からパーティションを継承します。つまり、これらのメジャーをより動的に使用できます。たとえば、ビジュアライゼーションの Y 軸にメジャーを加算し、製品ラインを X 軸に追加することで、製品ライン別の 7 日間移動平均を計算できます。

ORDER BY フィールドがビジュアライゼーションでグループ化されていない場合、AGGREGATE OVER は最後の行の集計値を各グループに表示する値として受け取ります。半加法メジャーに精通している場合は、これを半加法動作と同等 last 認識するかもしれません。

カスタムディメンションで集計されていない値を定義する

計算ディメンションを使用すると、ソースデータセットを変更せずに、集計を行わない値や軽量の変換処理を定義できます。これは、視覚化のためにデータを整理または再フォーマットする場合に便利です。

たとえば、個々の年齢ではなく年齢グループ別に年齢の傾向を分析するには、次の式を使用してカスタム age_group ディメンションを定義できます。

SQL

CASE
   WHEN age < 18 THEN '<18'
   WHEN age >= 18 AND age < 25 THEN '18–24'
   WHEN age >= 25 AND age < 35 THEN '25–34'
   WHEN age >= 35 AND age < 45 THEN '35–44'
   WHEN age >= 45 AND age < 55 THEN '45–54'
   WHEN age >= 55 AND age < 65 THEN '55–64'
   WHEN age >= 65 THEN '65+'
END

パフォーマンス上の利点

カスタム計算はパフォーマンスのために最適化されています。小さなデータセット (≤100,000 行と ≤100 MB) の場合、計算はブラウザーで実行され、応答性が向上します。より大きなデータセットは、ウェアハウス SQLで処理されます。 詳細については、「 データセットの最適化とキャッシュ」 を参照してください。

カスタム計算を作成する

この例では、 samples.nyctaxi.trips データセットに基づいて計算メジャーを作成します。AI/BI ダッシュボードの操作方法に関する一般的な知識を前提としています。AI/BI ダッシュボードの作成に慣れていない場合は、「 ダッシュボードの作成 」を参照して開始してください。

1. 既存のデータセットを開くか、新しいデータセットを作成します。

2. [カスタム計算] をクリックします。

   

3. 画面の右側に「 計算の作成 」パネルが開きます。 名前 テキストフィールドに Cost per mile と入力します。

4. (オプション) 説明 テキスト フィールドに「運賃額と移動距離を使用してマイルあたりのコストを計算します」と入力します。

5. 式 フィールドに、次のように入力します。

   SQL

   try_divide(SUM(fare_amount), SUM(trip_distance))

6. 作成 をクリックします。

カスタム計算をメトリクスビューに追加する

備考

プレビュー

この機能は パブリック プレビュー段階です。

カスタム計算は、メトリクス ビューによって作成されたデータセットの上に定義できます。 データセットを開くと、 結果テーブル と スキーマ のみが表示されます。[ カスタム計算] をクリックして、新しいカスタム計算を定義します。他のデータ資産が使用できる追加のカスタム メトリクスを定義するには、ビュー定義を変更します。 メトリクス ビューUnity Catalogを参照してください。

ダッシュボードのデータセットエディターから新しいメトリクスビューを定義するには、「 メトリクスビューの作成」を参照してください。

スキーマの表示

結果パネルの スキーマ タブをクリックして、カスタム計算とそれに関連するコメントを表示します。

計算されたメジャーは メジャー セクションに一覧表示され、 fx でマークされます。計算メジャーに関連付けられた値は、ビジュアライゼーションで GROUP BY を設定するときに動的に計算されます。結果テーブルに値は表示されません。計算されたディメンションは、 ディメンション セクションに表示されます。

ビジュアライゼーションでのカスタム計算の使用

以前に作成した Cost per mile 計算メジャーを視覚化で使用できます。

1. キャンバス をクリックします。次に、新しい視覚化ウィジェットをキャンバスに配置します。
2. 視覚化構成パネルを使用して、次のように設定を編集します。

   • データセット： Taxicab data

   • 視覚化： 棒

   • X軸:

     • フィールド: dropoff_zip
     • スケールタイプ: Categorical
     • 変換： None

   • Y軸:

     • マイルあたりのコスト

注記

テーブルのビジュアライゼーションは、計算ディメンションをサポートしますが、計算メジャーはサポートしません。

次の図はグラフを示しています。

カスタム計算を使用したビジュアライゼーションは、フィルターが適用されると自動的に更新されます。たとえば、 pickup_zip フィルターを追加すると、選択した値に一致するデータのみが表示されるように視覚化が更新されます。

カスタム計算の編集

計算を編集するには:

1. [ データ ] タブをクリックし、編集する計算に関連付けられているデータセットをクリックします。
2. 結果パネルの スキーマ タブをクリックします。
3. メジャー と ディメンション は、データセット フィールドの一覧の下に表示されます。編集する計算の右側にある ケバブメニューをクリックします。次に、[ 編集 ] をクリックします。
4. カスタム計算の編集 パネルで、編集するテキスト フィールドを更新します。次に、[ 更新 ] をクリックします。

カスタム計算の削除

計算を削除するには:

1. [ データ ] タブをクリックし、編集するメジャーに関連付けられているデータセットをクリックします。
2. 結果パネルの スキーマ タブをクリックします。
3. [ メジャー ] セクションは、フィールドの一覧の下に表示されます。編集する計算の右側にある ケバブメニューをクリックします。次に、[ 削除 ] をクリックします。
4. 表示される 削除 ダイアログで 削除 をクリックします。

制限

カスタム計算を使用するには、次の条件を満たす必要があります。

• 式で使用される列は、同じデータセットに属している必要があります。
• 外部テーブルまたはデータソースを参照する式はサポートされていないため、失敗したり、予期しない結果が返されたりする可能性があります。

サポートされている関数

次の表に、サポートされている関数を示します。サポートされていない関数を使用しようとすると、エラーが発生します。

集計関数

すべての計算済みメジャーは集計する必要があります。 次の集計操作がサポートされています。

集計	説明
avg(expr) または mean(expr)	列または式で計算された平均を返します。
count(*)	グループ内の行数を返します。
count(DISTINCT expr)	グループ内の一意の行の数を返します。
sum(expr)	列または式の値の合計を返します。
max(expr)	列または式の最大値を返します。
min(expr)	列または式の最小値を返します。
percentile(expr, percentage [,frequency])	グループ内の指定されたパーセンテージで expr の正確なパーセンタイル値を返します。
first(expr[,ignoreNull])	グループの最初の expr の値を返します。
last(expr[,ignoreNull])	グループの最後の expr の値を返します。
count_if	指定された条件を満たす行の数を返します。
median	一連の値の中央値を返します。
stddev	一連の値の標準偏差を返します。
variance	値のセットの分散を返します。

算術演算

式は、次の算術演算と組み合わせることができます。

オペレーション	説明
expr1 + expr2	expr1 と expr2 の合計を返します。
expr1 - expr2	expr1から expr2 を減算したときの差を返します。
multiplier * multiplicand	2 つの式の積を返します。
dividend/divisor	dividendをdivisorで除算した結果を返します。
- expr	式の負の値を返します。
+ expr	式の値を返します。
try_add(expr1, expr2)	2 つの値を加算します。エラーが発生した場合は、 NULLを返します。
try_subtract(expr1, expr2)	expr1から expr2 を減算します。エラーが発生した場合は、 NULLを返します。
try_multiply(multiplier, multiplicand)	2 つの数値を乗算します。エラーが発生した場合は、 NULLを返します。
try_divide(dividend, divisor)	dividendをdivisorで割ります。エラーが発生した場合は、 NULLを返します。
ln(expr)	式の自然対数を返します。
ログ(ベース、エクスプラー)	指定したbaseを持つexprの対数を返します。
log10(expr)	式の 10 進法の対数を返します。
powまたはpower	expr1を expr2の累乗にした結果を返します。
ラウンド(expr [, d])	丸めモードを使用して小数点以下 d 桁に丸められたexprHALF_UP返します。

Boolean 関数と演算子

カスタム計算では、=、 ==、 <=、 >=、 <、 >、 is null、 AND、 OR、 NOT、 !などの基本的な比較演算子とBoolean演算子がサポートされています。次の関数を使用して式を評価することもできます。

関数	説明
ISNULL(expr)	exprがNULLの場合はtrueを返します。
isnotnull(expr)	expr が NULLでない場合は true を返します。

キャスト関数

次の関数を使用して、指定した型に値をキャストします。

関数	説明
cast(expr AS type)	値 expr をターゲット データ型 typeにキャストします。
try_cast(expr AS type)	expr値をターゲットデータ型に安全にキャストtype。

日付、タイムスタンプ、および間隔関数

次の関数を使用して、日付、タイムスタンプ、および間隔を操作します。

関数	説明
datediff(endDate, startDate)	startDate から endDateまでの日数を返します。
timestampdiff(unit, start, stop)	単位で測定された 2 つのタイムスタンプの差を返します。
date_format(expr, fmt)	タイムスタンプを fmt形式の文字列に変換します。
timediff(unit, start, stop)	単位で測定された 2 つのタイムスタンプの差を返します。
date_part	日付またはタイムスタンプから、年、月、日などの特定の部分を抽出します。
date_trunc	日付またはタイムスタンプを、年や月などの指定した単位に切り捨てます。

文字列関数

次の関数を使用して、文字列を変換します。

関数	説明
concat(expr1, expr2[, …])	引数の連結を返します。
concat_ws(sep[, expr1[, …]])	sepで区切られた文字列の連結を返します。

その他の機能

次の機能もサポートされています。

関数	説明
AGGREGATE OVER	メジャーの時間範囲またはその他の順序付きセットにわたる値を計算します。
CASE expr { WHEN opt1 THEN res1 } […] [ELSE def] END	最初のoptNが expr に等しいか、一致しない場合は def の resN を返します。
CASE { WHEN cond1 THEN res1 } […] [ELSE def] END	true と評価される最初のcondNの resN を返すか、見つからない場合は def を返します。
coalesce(expr1, expr2 [, …])	最初の null 以外の引数を返します。
nvl(expr1, expr2)	expr1 が NULLの場合は expr2 を返し、そうでない場合は expr1 を返します。

例

次の例は、カスタム計算の一般的な使用方法を示しています。各カスタム計算は、データセットのスキーマの [データ] タブに表示されます。キャンバスでは、カスタム計算をフィールドとして選択できます。

条件付きでデータをフィルタリングして集計する

CASE ステートメントを使用して、条件付きでデータを集計します。次の例では、 samples.nyctaxi.trips データセットを使用し、郵便番号 10103 で始まるすべての乗車料金の合計を計算します。

SQL

SUM(CASE
  WHEN pickup_zip=10103 THEN fare_amount
  WHEN pickup_zip!=10103 THEN 0
END)

文字列の構築

CONCAT 関数を使用して、新しい文字列値を作成します。concat関数とconcat_ws関数を参照してください。

SQL

CONCAT(first_name, ' ', last_name)

日付のフォーマット

DATE_FORMAT を使用して、視覚化に表示される日付文字列を書式設定します。

SQL

DATE_FORMAT(tpep_pickup_datetime, 'YYYY-MM-dd')