ThoughtSpotの集計式について

 

本記事では、ThoughtSpotの集計式について解説していきたいと思います。集計式を用いなくてもキーワードで対応できるケースもありますが、高度な可視化を行う場合に集計式は非常に有用です。

 

集計式とは？

ThoughtSpotにおける数式は、標準数式と集計式の二種類に分類することができます。大きな違いは以下の通りです。

 

• 標準数式：個々の行レベルで実行される
• 集計式：行のグループに対して単一の結果を得る

 

集計式は、数式アシスタントで見たときに「集計」というカテゴリの中に格納されている数式が該当します。

 

標準数式の例：

• 四則演算の演算子（×÷+－）
• 文字列結合のconcat
• 日付差分のdiff_days

など

 

集計式の例：

• 平均を取る average
• カウントを取る count
• 累計を取る cumulative_sum
• 移動平均を取る moving_average
• グループ内のカウントを取る group_count

など

 

利用可能な集計式（高度な集計式を除く）：

• average：平均
• average_if：条件付き平均
• count：カウント
• count_if：条件付きカウント
• max：最大
• max_if：条件付き最大
• median：中央値
• min：最小
• min_if：条件付き最小
• percentile：パーセンタイル
• rank：ランク
• rank_percentile：パーセンタイルランク
• stddev：標準偏差
• stddev_if：条件付き標準偏差
• sum：合計
• sum_if：条件付き合計
• unique_count：一意の数のカウント
• unique_count_if：条件付き一意の数のカウント
• variance：分散
• variance_if：条件付き分散

※median、rank、rank_percentileは、ファントラップ、キャズムトラップなどの複雑なクエリでクエリ生成エラーが発生します

 

高度な集計式

BIツールでは高度な集計を行うために「高度な集計式」が利用できます。例えばTableauではLOD表現や表計算、PowerBIであればDAX関数なども高度な集計を行うためのものです。ThoughtSpotの高度な集計式は３種類存在します。

• グループ集計式（Group Aggregate）
• 累積式（Cumulative）
• 移動式（Moving）

 

グループ集計式（Group Aggregate）

グループ集計式（Group Aggregate）は、指定した属性ごとにグループ化を行い、集計を行います。

 

利用可能な関数：

• group_aggregate：柔軟なグループ集計式
• group_average：平均
• group_count：レコード数をカウント
• group_max：最大値
• group_min：最小値
• group_stddev：標準偏差
• group_sum：合計
• group_unique_count：一意のレコードをカウント
• group_variance：分散

 このうち、group_aggregateのみ特殊な数式となっています。他のgroup式と異なり、使用する計算式を指定し、さらに受け入れる属性・フィルターの指定を行う必要があります。

 

また、グループ集計式同士はネストすることができません（ネスト：グループ集計式の中にグループ集計式を含めること）。ネストしたい場合はグループ集計式を使ったビューを作成し、そのビューをデータソースとして再度グループ集計式を使う必要があります。例えば、明細レベルの売上がある時に、月単位で集計を行い、月単位での平均をグループ集計式で出すことはできません。

 

ここからは、group_aggregateを除いたグループ集計式と、group_aggregateを別々に解説します。

書式：group_XXXX（メジャー, 属性 ）

• メジャー：グループ集計式を適用したいメジャーを指定。
• 属性：受け入れる属性。カンマ区切りで複数指定可能。指定がなければ、属性は受け入れない（常に全データに対して集計するということになる）。

 

group_aggregateを除いたグループ集計式は比較的単純に使うことができます。第二変数の属性に何も指定しなければ、検索バーにどのような属性が入っていたとしても、常にすべての属性を無視した計算結果を返します。第二変数に何かの属性を指定した場合は、常にその属性のみを考慮した結果を返します。

つまり、指定した属性が検索バーにない場合は、常に全体に対しての結果が返ってくることになります。指定した属性が検索バーにある場合は、その属性に対しての計算結果が常に帰ってきます。

 

指定した属性が検索バーにない場合：

以下のように、group_sum(販売価格, カテゴリ)で作成した「カテゴリごとの合計」は全体の合計（group_sum( 販売価格 )）と一致します。

 

カテゴリごとの合計：group_sum(販売価格, カテゴリ)

上のスクショでは、曜日がディメンションとしてスライスしていますが、「カテゴリごとの合計」はそれに影響されず、全体の合計になっています。

 

指定した属性が検索バーにある場合：

以下のように、group_sum( 販売価格 , カテゴリ )で作成した「カテゴリごとの合計」は、他の属性があっても無視し、カテゴリごとの合計となっています。

 

カテゴリごとの合計：group_sum( 販売価格 , カテゴリ )

上のスクショでは、カテゴリごとの合計は、関数内で指定した「カテゴリ」の影響を受けて、カテゴリでスライスされ、それぞれのカテゴリ値の合計が計算されています。

 

サンプル：

メジャーは「Sales」とします。属性は「カテゴリ」とします。

• 全体に対する割合：sum( Sales ) / group_sum ( Sales ) 
• カテゴリ内の割合：sum( Sales ) / group_sum ( Sales, カテゴリ) 

 

「全体に対する割合」を表形式で可視化すると、以下のようになります。

これは、属性に何も指定していないため、group_sumは全体に対して合計を行っています。そのため、各カテゴリ、サブカテゴリのアイテムごとに合計した売上を全体の合計で割っているため、全体に対する割合が計算されています。

一方、「カテゴリ内の割合」を表形式で可視化すると、以下のようになります。

ここでは、group_sumの属性にカテゴリを指定しており、赤枠、青枠で囲った部分に対してそれぞれのカテゴリごとの売上の合計が計算され、各カテゴリ、サブカテゴリのアイテムごとに合計した売上をカテゴリ合計で割っている、ということになり、つまりカテゴリ内のサブカテゴリの売上の割合が計算されています。

 

書式：group_aggregate（集計式(メジャー), {クエリグループ}, {フィルタ} ）

• 集計式：sum、averageなどの集計式。他のグループ集計式は使えない。
• メジャー：グループ集計式を適用したいメジャーを指定（必ず集計式で囲う必要がある）。
• クエリグループ：受け入れる属性。中括弧「{}」で囲い、リストとして指定する。リスト内はカンマ区切りで複数指定可能。指定がなければ、属性は受け入れない（常に全データに対して集計をかけるということになる）が、その場合は「{}」という記載をする必要がある。もしくは、query_groups()と指定すると、検索バーで指定された属性はすべて受け入れる。
• フィルタ：query_filters()と指定すると、検索バーで指定されたすべてのフィルタを受け入れるが、「{}」とだけ記載すると、すべてのフィルタを無視する。

 

クエリグループもフィルタも、query_groups()/query_filters()に対して、「+/-」で条件を追加したり削除したりすることができます。特に日付型のフィールドの場合は、「-」で現在の粒度を削除し、自分の好きな粒度で指定し直すことが可能です。

 

group_aggregate式は、ドキュメントでは「柔軟な集計機能」として紹介されています。実際のところ、この関数があれば他のグループ集計式は不要ですが、その分表記が複雑なため、他のグループ集計式が利用できるのであればそちらの方が手軽です。また、フィルタを無視できるのはこの関数を使ったときのみです。

 

サンプル

• 日付のクエリを削除し、好きな粒度で追加する

年単位：group_aggregate ( sum( 販売価格 ) , query_groups()-{ 販売日 }+{start_of_year( 販売日 )} , query_filters ())

 

このサンプルでは、指定された販売日の粒度が無視され、常に年単位での販売価格の合計が行われます。さらに、query_groups()が指定されているため、販売日以外の属性が有効になります。これを、以下のように可視化してみます（販売日を月単位でX軸に指定、「年単位」をY軸にし、色付きスライスにカテゴリを指定しグラフ化）。

 

 

X軸の時間バケットが月単位になっているため、一年を通じて同じ値になっています。一方で、上のスクリーンショットは、販売日の時間バケットを年単位にすると次のようなグラフで描かれます。

 

• 検索バーの特定の項目を抜いて集計する

販売価格合計：group_aggregate( sum ( 販売価格 ) , query_groups () - { カテゴリ } , query_filters () )

この計算式では、常にカテゴリの影響を無視して販売価格が合計されます。以下の通り、曜日、カテゴリを検索バーに入れましたが、「販売価格合計」は、常にカテゴリの集計レベルが無視され、曜日ごとの集計結果が表示されています。

 

 

 

• フィルタをかけても全体期間での平均を表示する

過去全期間を含めた平均を、今年度のデータに表示したいとします。

 

 

上のスクリーンショットでは、全体での平均は「group_average   ( 販売価格 )」で算出しています。そのため、2024でフィルタをかけると、2024年全体での平均となっています。一方、「全体期間での平均」については、「group_aggregate(average ( 販売価格 ) , {} , {} )」で算出しているため、フィルタ、属性の影響を受けず全体期間での平均を算出していることになります。

 

一方で、過去期間（2024年より前）での平均を表示するにはどうすればいいでしょうか？

例えば、「group_aggregate(average ( 販売価格 ) , {} , {year(販売日) != 2024} )」という数式が利用できます。ここではフィルタ条件として、「year(販売日) != 2024」を指定することで、2023年までのフィルタがかかっています。

 

 

  

参考：ThoughtSpot Document 「Group aggregation functions」

参考：ThoughtSpot Document 「Flexible aggregation functions」

 

累積式（Cumulative）

累積式（Cumulative）は、データの開始から現在の場所までの計算を行います。

 

書式：cumulative_XXXX（メジャー, 属性 ）

• メジャー：累積式を適用したいメジャーを指定
• 属性：受け入れる属性。カンマ区切りで複数指定可能。指定がなければ、属性は受け入れない（常に全データに対してということになる）。

 

利用可能な関数：

• cumulative_average：始点から現在までの平均値
• cumulative_max：始点から現在までの最大値
• cumulative_min：始点から現在までの最小値
• cumulative_sum：始点から現在までの合計

 

サンプル：

• 累計：cumulative_sum( 販売価格 , 販売日 )

• 過去最高Sales：cumulative_max( Sales , Date )

※各レコードのうち最大値となるため、日商（1日の合計売上）、月商（月間の合計売上）で最大を取りたい場合は次の計算式になります。

 

• 過去最高月商：cumulative_max( group_aggregate( sum( Sales ) , { Date } , query_filters() ) , Date) 

事前にSalesを月単位（この数式は時間バケットに依存します）で合計してから累積式で最高値を出しています（以下のスクショの項目では「過去最大売上（月単位）」）。

 

参考：ThoughtSpot Document 「Cumulative functions」

 

移動式（Moving）

移動式は、移動平均など、ある一定の区間について集計を行います（あるデータの前後n個内で計算を行うという意味です）。

 

書式：moving_XXXX（メジャー, 整数1, 整数2, 属性 ）

• メジャー：移動式を適用したいメジャーを指定。
• 整数1：ある地点から前方に対して何個のレコードを計算に含めるか？
• 整数2：ある地点から後方に対して何個のレコードを計算に含めるか？
• 属性：受け入れる属性。カンマ区切りで複数指定可能。指定がなければ、属性は受け入れない（常に全データに対してということになる）。

 

なお、整数1、2には負の数を指定することができます。この性質を利用し、一つ前のレコード、一つ後ろのレコードの値を持ってくることができます。つまり、前月との差分などの計算が可能です。

 

利用可能な関数：

• moving_average：移動平均
• moving_max：移動最大
• moving_min：移動最小
• moving_sum：移動合計

 

サンプル：

• 前月のSales合計：Moving_sum(Sales, 1,-1,Date)

※メジャーは「Sales」とします。属性は「Date」とします。

• 今月、前月、前々月の三ヶ月のSales平均：Moving_average( 販売価格 , 2 , 0 , 販売日 )

※月単位で集計された後、三ヶ月で平均が取られています（時間バケットを月単位にしている場合）

日の平均に対しての三ヶ月平均の場合は、以下のような計算式になります。

moving_average ( group_aggregate ( average( 販売価格 ) , query_groups( ) , query_filters( ) ) , 2 , 0 , 販売日 )

※Salesは最も細かい単位での平均になります

  

参考：ThoughtSpot Document 「Moving functions」

 

応用

• group_aggregateとmoving_sumを併用し、フィルタをかけた前月の売上を表示する

group_aggregateはフィルタを無視できますが、moving関数は指定期間の値を持ってくることが可能です。このため、併用することでフィルタをかけた期間以外のデータを持ってくることができます。

 

前月売上：group_aggregate ( moving_sum ( 販売価格 ,1 ,-1 ,month(販売日) ) , query_groups (), {} ) 

 とすると、指定した年月の一ヶ月前のデータを持ってくることが可能です。

「 moving_sum ( 販売価格 ,1 ,-1 ,month(販売日)」は、前月のデータを合計したものを算出します。これに対して、group_aggregateでフィルタを無視するようにしています。

 

 

Tableauユーザーの方へ

これらのグループ集計式が、Tableauでは何に該当するのかを簡単にご紹介します。

 

グループ集計関数は、LOD表現です。Group_XXXは、ビューの詳細レベルに関係なく指定したディメンションを使用して計算するため、FIXEDに該当します。Group_aggregate関数は、query_groupsを使うことで、INCLUDE、EXCLUDEの表現が可能です。Query_groupsを使用しなければ、FIXEDとして動作します。

 

また、累積式、移動式は表計算に該当します。