JOIN句

JOIN句を使用すると、複数の文または指定した状態、あるいはその両方からのレコードを、その文の特定の属性間の関係に基づいて結合できます。

JOIN句(SQL標準のサブセットに従っている)は、指定された結合条件で結合を実行します。結合条件は、FROM文の属性を参照する任意のブール式として指定できます。式はカッコで囲まれている必要があります。

JOIN句は常に、FROM句に修正を加えます。2つの指定したソース(1つまたは両方が指定した状態である)をFROM句の中で指定できます。フィールドは、どのソースからのものかを示すようにドット修飾されている必要があります。ただし、単一表からの問合せを除きます。

自己結合がサポートされます。自己結合には、文の別名が必要です。

入力表は両方とも、DEFINE文またはRETURN文の結果(つまり、中間結果)であることが必要です。AllBaseRecordsとNavStateRecordsとの結合はできません。

任意の数の結合を1つの文で実行できます。

JOINの構文は次のとおりです。

FROM <statement1> [alias]
   [CROSS,LEFT,RIGHT,FULL] JOIN <statement2> [alias]
   ON (Boolean-expression) [JOIN <statementN> [alias] ON (Boolean-expression)]*

ここで、statementは、1つの文または1つの指定した状態です。複数のJOINがある場合は、1つの文につき1つのFROM文を使用して結合します。

結合のタイプ

EQLでは、次の結合タイプがサポートされます。

INNER JOIN: INNER JOINでは、左側と右側のレコードが結合されてから、結果レコードが結合条件によってフィルタリングされます。つまり、結合条件がTRUEである行のみが結果に含まれます。結合タイプを指定しない場合のJOINのデフォルトは、INNER JOINになります。
LEFT JOIN、RIGHT JOINおよびFULL JOIN: LEFT JOIN、RIGHT JOINおよびFULL JOIN(外部結合と総称される)は、INNER JOINの結果を拡張したものであり、結合条件に一致するレコードが他方の側にないレコードも結果に含まれます。このような追加レコードが一方の側から追加された場合は、結合結果内のそのレコードで、他方の側の属性はすべてNULLになります。LEFT JOINでは左側からのすべての行が含められ、RIGHT JOINでは右側からのすべての行が含められます。また、FULL JOINでは両側からのすべての行が含められます。
CROSS JOIN: CROSS JOINの結果は、左側と右側のデカルト積です。各結果レコードには、両側からの対応するレコードの割当てがあります。

正しく使用されていない場合は、結合が原因でEndeca ServerのRAMの空き領域をすべて使用してしまうことがあることに注意してください。これは、結合の結果が非常に大きくなることがあるためです。たとえば、100レコードの結果と200レコードの結果のCROSS JOINによって、20,000レコードが生成されます。2つのベスト・プラクティスがあります。1つは、可能であればCROSS JOINの使用を避けることで、もう1つはON条件について、妥当な数の結果になるように注意することです。

INNER JOINの例

次に示すINNER JOINの例では、従業員のうち、特定のサブカテゴリの売上がそのサブカテゴリの合計の10%を超える者を見つけます。

DEFINE EmployeeTotals AS 
SELECT 
   ARB(DimEmployee_FullName) AS Name, 
   SUM(FactSales_SalesAmount) AS Total 
GROUP BY DimEmployee_EmployeeKey, ProductSubcategoryName;

DEFINE SubcategoryTotals AS 
SELECT 
   SUM(FactSales_SalesAmount) AS Total 
GROUP BY ProductSubcategoryName;

RETURN Stars AS 
SELECT 
   EmployeeTotals.Name AS Name, 
   EmployeeTotals.ProductSubcategoryName AS Subcategory, 
   100 * EmployeeTotals.Total / SubcategoryTotals.Total AS Pct 
FROM EmployeeTotals 
   JOIN SubcategoryTotals 
   ON (EmployeeTotals.ProductSubcategoryName = SubcategoryTotals.ProductSubcategoryName) 
HAVING Pct > 10

自己結合の例

次に示す自己結合では、INNER JOINを使用して日別売上合計の累計を従業員ごとに計算します。

DEFINE Days AS 
SELECT 
   FactSales_OrderDateKey AS DateKey, 
   DimEmployee_EmployeeKey AS EmployeeKey, 
   ARB(DimEmployee_FullName) AS EmployeeName, 
   SUM(FactSales_SalesAmount) AS DailyTotal 
GROUP BY DateKey, EmployeeKey;

RETURN CumulativeDays AS 
SELECT 
   SUM(PreviousDays.DailyTotal) AS CumulativeTotal, 
   Day.DateKey AS DateKey, 
   Day.EmployeeKey AS EmployeeKey, 
   ARB(Day.EmployeeName) AS EmployeeName 
FROM Days Day 
   JOIN Days PreviousDays 
   ON (PreviousDays.DateKey <= Day.DateKey) 
GROUP BY DateKey, EmployeeKey

LEFT JOINの例

次に示すLEFT JOINの例では、上位5件のサブカテゴリとOtherバケットを計算します。これは、円グラフで使用するためです。

DEFINE Totals AS 
SELECT 
   SUM(FactSales_SalesAmount) AS Total 
GROUP BY ProductSubcategoryName;

DEFINE Top5 AS 
SELECT 
   ARB(Total) AS Total 
FROM Totals 
ORDER BY Total DESC PAGE(0,5);

RETURN Chart AS 
SELECT 
   COALESCE(Top5.ProductSubcategoryName, 'Other') AS Subcategory, 
   SUM(Totals.Total) AS Total 
FROM Totals 
   LEFT JOIN Top5 
   ON (Totals.ProductSubcategoryName = Top5.ProductSubcategoryName) 
GROUP BY Subcategory

次に示すLEFT JOINでは、特定の地域の製品別メトリックを計算します。その地域でまったく売上のない製品であっても、すべての製品がリストに含まれるようにする必要があります。

DEFINE Product AS 
SELECT 
   ProductAlternateKey AS Key, 
   ARB(ProductName) AS Name GROUP BY Key;

DEFINE RegionTrans AS 
SELECT 
   ProductAlternateKey AS ProductKey, 
   FactSales_SalesAmount AS Amount 
WHERE DimSalesTerritory_SalesTerritoryRegion='United Kingdom';

RETURN Results AS 
SELECT 
   Product.Key AS ProductKey, 
   ARB(Product.Name) AS ProductName, 
   COALESCE(SUM(RegionTrans.Amount), 0) AS SalesTotal, 
   COUNT(RegionTrans.Amount) AS TransactionCount 
FROM Product 
   LEFT JOIN RegionTrans 
   ON (Product.Key = RegionTrans.ProductKey) 
GROUP BY ProductKey

FULL JOINの例

次に示すFULL JOINの例では、上位10人の従業員の売上合計を上位10製品について計算します。各従業員と各製品がリストに含まれている必要があります。

DEFINE TopEmployees AS 
SELECT 
   DimEmployee_EmployeeKey AS Key, 
   ARB(DimEmployee_FullName) AS Name, 
   SUM(FactSales_SalesAmount) AS SalesTotal 
GROUP BY Key 
ORDER BY SalesTotal DESC 
PAGE (0,10);

DEFINE TopProducts AS 
SELECT 
   ProductAlternateKey AS Key, 
   ARB(ProductName) AS Name, 
   SUM(FactSales_SalesAmount) AS SalesTotal 
GROUP BY Key 
ORDER BY SalesTotal DESC 
PAGE (0,10);

DEFINE EmployeeProductTotals AS 
SELECT 
   DimEmployee_EmployeeKey AS EmployeeKey, 
   ProductAlternateKey AS ProductKey, 
   SUM(FactSales_SalesAmount) AS SalesTotal 
GROUP BY EmployeeKey, ProductKey 
HAVING [EmployeeKey] IN TopEmployees AND [ProductKey] IN TopProducts;

RETURN Results AS 
SELECT 
   TopEmployees.Key AS EmployeeKey, 
   TopEmployees.Name AS EmployeeName, 
   TopEmployees.SalesTotal AS EmployeeTotal, 
   TopProducts.Key AS ProductKey, 
   TopProducts.Name AS ProductName, 
   TopProducts.SalesTotal AS ProductTotal, 
   EmployeeProductTotals.SalesTotal AS EmployeeProductTotal 
FROM EmployeeProductTotals 
   FULL JOIN TopEmployees 
   ON (EmployeeProductTotals.EmployeeKey = TopEmployees.Key) 
   FULL JOIN TopProducts 
   ON (EmployeeProductTotals.ProductKey = TopProducts.Key)

CROSS JOINの例

次に示すCROSS JOINの例では、各製品サブカテゴリが売上合計に占める割合(%)を計算します。

DEFINE GlobalTotal AS 
SELECT 
   SUM(FactSales_SalesAmount) AS GlobalTotal
GROUP;

DEFINE SubcategoryTotals AS 
SELECT 
   SUM(FactSales_SalesAmount) AS SubcategoryTotal 
GROUP BY ProductSubcategoryName;

RETURN SubcategoryContributions AS 
SELECT 
   SubcategoryTotals.ProductSubcategoryName AS Subcategory, 
   SubcategoryTotals.SubcategoryTotal / GlobalTotal.GlobalTotal AS Contribution 
FROM SubcategoryTotals 
   CROSS JOIN GlobalTotal