はじめに
本記事は、AWS QuickSightの計算フィールドで使用できる関数の内、集計関数の代表的な関数とその使用方法を紹介します。公式リファレンス記載の例を具体的にQuickSight上で再現しながら補足していきます。
計算フィールドとは
計算フィールドは、データセットもしくは分析の編集において、1つまたは複数の関数と既存のカラム値を用いて計算を実行、結果を出力するカラムで、
Excelの関数を入力するように設定することができます。
平均値
avg関数
avg関数はグループ化した条件別に指定したカラムの平均値を算出する関数です。
サンプルデータセット
以下のサンプルのデータセットを用いてグループ別の平均値を算出します。
| group | salesAmount |
|---|---|
| A | 200.80 |
| B | 200.40 |
| A | 300.12 |
| B | 500.16 |
計算フィールドの例
avg({salesAmount})
計算フィールド設定後のデータセット
データセット上では、「使用できません」と表示されますが問題ありません。この状態のまま、分析の作成を行います。
分析での見え方
グループ化の条件にgroupカラムを指定することで、group別の平均値を出すことができます。
なお、このデータセットの内容の場合、salesAmountカラムの集計方法を合計から平均に変更することでも同様の計算が可能です。
avgIf関数
avgIf関数は、avg関数に条件式が追加された関数です。
サンプルデータセット
以下のサンプルのデータセットを用いて以下の条件の平均値を算出します。
- 条件
- 2022年4月中のデータ
- BasePeriodStartDate = 2022/4/1
- BasePeriodEndDate = 2022/4/30
- SourcingTypeがIndirect以外
- 2022年4月中のデータ
| CalendarDay | ProdRev | SourcingType |
|---|---|---|
| 2022-03-15T00:00:00.000Z | 1000 | Indirect |
| 2022-04-03T00:00:00.000Z | 2000 | Direct |
| 2022-04-06T00:00:00.000Z | 5000 | Direct |
| 2022-05-16T00:00:00.000Z | 4000 | Indirect |
計算フィールドの例
avgIf(
{ProdRev},{CalendarDay} >= ${BasePeriodStartDate}
AND {CalendarDay} <= ${BasePeriodEndDate}
AND {SourcingType} <> 'Indirect'
)
注） CalendarDayが日付値となっていない場合、エラーになることがあります。データセット編集においてデータタイプの変更で日付値に変更してください。
パラメータの設定
条件式に使用するパラメータを設定します。
- データセットを作成後、分析画面にてパラメータを新規作成します。
-
パラメータの内容を入力します。
-
パラメータの用途を指定します。今回の場合、計算フィールドを指定します。(BasePeriodEndDateも同様)
-
下図の通り、計算フィールド上で定義した変数をパラメータとして使用することができます。
分析での見え方
下図の通り、calendarDayの集計方法を月に変更することで、2022年4月の平均値を出すことができます。
今回の流れの場合は、データセットではなく分析に計算フィールドが直接設定されます。
値の個数計算
count関数
count関数は、指定したカラムの総数をグループ化した条件別に算出します。
サンプルデータセット
以下のサンプルのデータセットを用いてグループ化した条件別の値の総数を算出します。
| product_type | sales | person_in_charge |
|---|---|---|
| Table | 100 | Tom |
| Sofa | 50 | John |
| Table | 40 | Kristin |
| Chair | 250 | Tom |
| Bed | 100 | John |
計算フィールドの例
count({sales})
分析での見え方
countIf関数
countIf関数は、count関数に条件式が追加された関数になります。
使い方としては、avgIf関数と同様にパラメータの設定・条件式を定義します。
重複しない値の個数計算
distinct_count関数
distinct_count関数は、指定したカラムの値の内、値に重複がないカラムの総数をグループ化した条件別に算出します。
distinct_countIf関数
distinct_countIf関数は、distinct_count関数に条件式が追加された関数になります。使い方としては、avgIf関数と同様にパラメータの設定・条件式を定義します。
最大値・中央値・最小値
- max関数
- median関数
- min関数
サンプルデータセット
以下のサンプルのデータセットを用いてグループ化した条件別の値の総数を算出します。
| type | revenue |
|---|---|
| A | 500 |
| B | 1200 |
| C | 200 |
| B | 680 |
| A | 900 |
| C | 1400 |
| A | 500 |
| C | 800 |
| B | 90 |
計算フィールドの例
max({revenue})
median({revenue})
min({revenue})
計算フィールド設定後のデータセット
分析での見え方
パーセンタイル
パーセンタイルとは
計測値の分布（ばらつき）を小さい数字から大きい数字に並べ変え、パーセント表示することによって、小さい数字から大きな数字に並べ変えた計測値においてどこに位置するのかを測定する単位。例えば、計測値として100個ある場合、5パーセンタイルであれば小さい数字から数えて5番目に位置し、50パーセンタイルであれば小さい数字から数えて50番目に位置し、95パーセンタイルであれば小さい方から数えて95番目に位置する。
参考：https://www.pfa.or.jp/yogoshu/ha/ha17.html
percentileCount関数
データセットに存在しない可能性のある正確なパーセンタイル値を返す。
計算フィールドの例
percentileCont({test_points_of_English},20)
percentileDisc関数
データセットに存在する最も近いパーセンタイル値を返す。
計算フィールドの例
percentileDisc({test_points_of_English},20)
サンプルデータセット
100人の学生の英語の学力テストの点数を想定したサンプルデータ準備しました。
分析での見え方
ヒストグラム表示と各関数の値は下図のようになります。
合計
sum関数
sum関数は、指定したカラムの値の合計をグループ化した条件別に算出します。
サンプルデータセット
以下のサンプルのデータセットを用いてグループ化した条件別の値の合計を算出します。
| group | profit_amount |
|---|---|
| A | 200 |
| B | 100 |
| A | 300 |
| B | 400 |
| A | 500 |
| B | 250 |
計算フィールドの例
sum({profit_amount})
分析での見え方
sumIf関数
sumIf関数は、sum関数に条件式が追加された関数です。
使い方としては、avgIf関数と同様にパラメータの設定・条件式を定義します。
まとめ
以上が代表的な関数の計算フィールドの使い方となります。具体的な使い方が分からなくて困っている方の助けになれば幸いです。