集計Beast Modeで非集計列を使用する

​

はじめに

このガイドでは、集計Beast Modeで非集計列を使用する方法を紹介します。 集計Beast Modeに非集計列を適用する場合は、SQLについて基本レベルから中級レベルの知識と Domoサポート へのアクセス権を持っていることが理想的です。

---

• GROUP_BY関数を使用する
• 非集計列を使用する集計
• 総計行と小計行

---

​

GROUP BY関数を使用する

SELECT ステートメントで、集計された関数と非集計列を使用するSQLクエリを作成する場合、すべての非集計列をクエリの GROUP BY ステートメントに含めるする必要があります。

以下のDataSetには、顧客による個々の売上のレコードが含まれています。「Customer ID」列には顧客ごとに一意の識別子が含まれ、「Ship Mode」列には「Second Class」と「Standard Class」の2つの値が含まれます。「Sales」列には、注文時に各顧客に販売された金額が含まれます。

Order ID	Customer ID	Ship Mode	Sales
1	CG-12520	Second Class	261.98
2	CG-12520	Second Class	731.94
3	DV-13045	Second Class	14.62
4	SO-20335	Standard Class	957.5775
5	SO-20335	Standard Class	22.368
6	BH-11710	Standard Class	48.86
7	BH-11710	Standard Class	7.28
8	BH-11710	Standard Class	907.152
9	BH-11710	Second Class	18.504
10	BH-11710	Second Class	114.9
11	BH-11710	Standard Class	1706.184
12	BH-11710	Standard Class	911.424
13	AA-10480	Standard Class	15.552
14	IM-15070	Standard Class	407.976
15	HP-14815	Standard Class	68.81
16	HP-14815	Standard Class	2.544
17	PK-19075	Standard Class	665.88
18	AG-10270	Second Class	5.55

この表を使用し、Beast Modeの結果のみを示す単一値チャートを作成できます（「Sales」列を合計した「Total Sales」列）。

このチャートを求めるクエリの形式は、以下のようになります。

SELECT SUM(`Sales`) AS `Total Sales` FROM DataSet

この例では、 SELECT ステートメントの値のみが、集計Beast Modeの「Total Sales」です。非集計列はないため、 GROUP BY ステートメントは不要です。

「Ship Mode」別の「Total Sales」Beast Modeの棒グラフを作成する場合、「Ship Mode」ごとに「Total Sales」を取得する必要があります。それが以下のクエリになります。

SELECT `Ship Mode`, SUM(`Sales`) AS `Total Sales` FROM Dataset GROUP BY `Ship Mode`

SELECT ステートメントには、集計Beast Mode、「Total Sales」、および非集計列「Ship Mode」が含まれます。したがって、非集計列「Ship Mode」は GROUP BY ステートメントに含まれている必要があります。

このページのトップへ

​

非集計列を使用する集計

非集計列を使用する集計Beast Modeの操作は難しくなりますが、以下のヒントをもとに実行することができます。

この例では、以下のBeast Mode関数を使用して、顧客別の総売上を表示するピボット表を用意しています。

Total Sales  
-----------  
SUM (`Sales`)

これは次のクエリになります。

SELECT `Customer ID,` SUM(`Sales`) AS `Total Sales` FROM DataSet GROUP BY `Customer ID`

クエリには、 SELECT ステートメントおよび非集計列「Customer ID」の集計であるBeast Modeが含まれています。このクエリを正しく機能させるには、「Customer ID」を GROUP BY ステートメントに含める必要があります。

この例では、「Standard Class」を使用して出荷された注文は、「Second Class」を使用して出荷された注文よりも10%高くなります。チャートを作成するときは、「Standard Class」を使用した総売上を10%削減する必要があります。これを行うには、「Total Sales」Beast Modeを変更して、この出荷モードを使用して売上を10%削減します。

更新されたBeast Modeは、以下のようになります。

Total Sales  
-----------  
CASE  
  WHEN `Ship Mode` = `Standard Class` THEN SUM(`Sales` *.9)  
  WHEN 'Ship Mode` = `Second Class` THEN SUM(`Sales`)  
END

このBeast Modeは、集計と見なされます。なぜなら、 WHEN 節ごとの THEN ステートメントでは、集計関数 SUM を使用するためです。「Ship Mode」関数は非集計であるため、クエリを再構築する場合、ピボットテーブルは次のようになります。

SELECT `Customer ID`  
CASE  
 WHEN `Ship Mode` = `Standard Class` THEN SUM(`Sales` *.9)  
 WHEN `Ship Mode` = `Standard Class` THEN SUM(`Sales`)  
END AS `Total Sales`  
FROM DataSet GROUP BY `Customer ID,` `Ship Mode`

「Ship Mode」列は、 SELECT ステートメントでは直接使用されませんが、非集計フォームでは、Beast Modeの「Total Sales」を通じて SELECT ステートメントで使用されます。このクエリを機能させるには、「Ship Mode」列を GROUP BY ステートメントに含める必要があります。

ピボットテーブルは次のようになります。

ピボットテーブルのデータには、「Customer ID」BH-11710が「Total Sales」列のエラーとともに表示されます。このエラーは、顧客が2つの異なる「Ship Mode」値を注文で使用しているために発生したものです。「Standard Class」と「Second Class」の両方の値があるため、結果の出力には2行が含まれています。これがエラーの原因になります。なぜなら、ピボットテーブルでは2行ではなく1行が想定されているためです。

「Ship Mode」列は、 SELECT ステートメントに含まれていないため、表示されません。テーブルを表示している他のユーザーは、「Ship Mode」に入る値を把握していません。また、顧客ごとの実際の「Total Sales」を確認することもできません。

このページのトップへ

​

表の総計行と小計行

ピボットテーブルの「Grand Total」行には、すべての顧客の実際の「Total Sales」値を取得できる2番目のクエリが含まれている必要があります。通常、「合計」行を計算する場合、クエリから特定の列を削除しますが、この場合はメインクエリを使用して「Customer ID」を削除します。Beast Modeは非集計列を使用するため、 GROUP BY ステートメントに追加する必要があります。

「Customer ID」列を削除する場合、次のクエリを使用できます。

SELECT  
CASE  
  WHEN `Ship Mode` = `Standard Class` THEN SUM(`Sales` *.9)  
  WHEN `Ship Mode` = `Second Class` THEN SUM(`Sales`)  
END AS `Total Sales`  
FROM DataSet GROUP BY `Ship Mode`

このクエリでは、すべての顧客の「Total Sales」を取得します。ただし、このBeast Modeは非集計列を使用するため、 GROUP BY ステートメントにその列を含める必要があります。

このクエリでは、次の2行のデータが生成されます。

Total Sales
1197.424
5721.6075

2つの行には、「Ship Mode」の値ごとに合計が1つずつ含まれています。合計行の値には、両方の値を合計した行が必要です。この値を取得して行を合計するには、元のクエリをサブクエリに入れる必要があります

SELECT SUM(subquery. `Total Sales`) FROM  
(SELECT `Customer ID`,  
CASE  
  WHEN `Ship Mode` = `Standard Class` THEN SUM(`Sales` *.9)  
  WHEN `Ship Mode` = `Second Class` THEN SUM(`Sales`)  
END AS `Total Sales`  
FROM DataSet GROUP BY `Customer ID,` `Ship Mode`) AS subquery

このクエリは、「Customer ID」および「Ship Mode」ごとに複数の行が存在する可能性に備えた形式になっているため、テーブル全体のクエリを実行し、サブクエリに配置して、サブクエリに基づいて SUM を計算する必要があります。

このページのトップへ

日本語