ウィンドウ関数

ウィンドウ関数では、隣接したグループから集計されたデータを使用して 1 つのグループのデータを計算できます。ウィンドウを使用しても、クエリから返される行数に変更はありません。ウィンドウでは、グループ内ではなく複数グループにわたる集計を行い、集計基準として有効な数値射影を受け入れます。

集計関数を使用するウィンドウでは次の構文を使用します。

1<windowfunction>(<projection expression>) over (<row range> partition by <reset groups> order by <order clause>) as <label>

順位付け関数を使用している場合は次の構文を使用します。

1<rankfunction> over([..] partition by <reset groups> order by <order clause>) as <label>

この場合、次のようになります。

windowfunction

ウィンドウをサポートする集計関数。現在サポートされている関数は、avg、sum、min、max、count、median、percentile_disc、percentile_cont です。

rankfunction

パーティション内の各行の順位値を返します。サポートされている順位付け関数は rank()、dense_rank()、cume_dist()、および row_number() です。例については、「順位付け関数」セクションを参照してください。

projection expression

指定した列の値から射影を生成するために使用される式。

row range

次の構文を使用して行の範囲を指定します。

範囲	意味
[.. 0]	リセットグループの開始行から現在の行まで。
[0 ..]	リセットグループの現在の行から最後の行まで。
[-2 .. 0]	現在の行の 2 行前から。ウィンドウの対象は 3 行です。
[0 .. 2]	現在の行から現在の行の 2 行後まで。ウィンドウの対象は 3 行です。
[-1 .. -1]	現在の行の 1 行前。ウィンドウには 1 行が含まれます。
[.. -2]	リセットグループの開始行から現在の行の 2 行前まで。
[..]	リセットグループ全体を集計します。

reset groups

値が変更されたときにウィンドウ集計をリセットする列。all のリセットグループは、ウィンドウ集計ですべての列をリセットすることを示します。

order clause

並び替えに使用する列を指定します。ウィンドウ関数が評価される前に、行が並び替えられます。

order 句は、行の範囲が [..] でウィンドウ関数が sum、avg、min、または max の式には使用できません。たとえば、sum(sum(Sales)) over([..] partition by Year order by Quarter) は無効です。

メモ

label

出力列の名前。

メモ

グループ化されたクエリ

ウィンドウ関数はグループ化されたクエリでのみ有効です。次の例は無効です。

1a = load "dataset";
2b = foreach a generate sum(sum(sales)) over([.. 0] partition by all order by all);

複数のリセットと複数の並び替え

複数のリセットと複数の並び替えは有効です。次に例を示します。

1sum(sum(Sales)) over([-2 .. 0] partition by (OrderDate_Year, OrderDate_Quarter) order by OrderDate_Year)
2       
3sum(sum(Sales)) over([-2 .. 0] partition by (Year, Quarter) order by (Year asc, sum(Sales) desc))

共通グループ

ウィンドウ関数は共通グループクエリで使用できます。次に例を示します。

sum(sum(a[Sales])) over([-2 .. 0] partition by (a[Year], a[Quarter]) order by (a[Year] asc, sum(a[Sales]) desc))

各ウィンドウ関数は 1 つの共通グループストリームでのみ使用できます。次の例は無効です。

1a = load "dataset1";
2b = load "dataset2";
3c = group a by column1, b by column2;
4d = foreach c generate sum(sum(a[sales])) over([.. 0] partition by b[column2] order by all)

メモ

関数の使用についての詳細は、「集計関数」を参照してください。

例 - 上位 5 人の営業担当を動的に表示する

1q = load "DTC_Opportunity_SAMPLE";
2q = group q by ('Billing_Country', 'Account_Owner');
3
4q = foreach q generate 'Billing_Country', 'Account_Owner', 
5
6-- sum(Amount) is the total amount for a single rep in the current country
7-- sum(sum('Amount') is the total amount for ALL reps in the current country
8-- sum(Amount) / sum(sum('Amount') calculates the percentage that each rep contributed
9-- to the total amount in the current country
10((sum('Amount')/sum(sum('Amount')) 
11
12
13-- [..] means "include all records in the partition"
14-- "by Billing_Country" means partition, or group, by country
15over ([..] partition by 'Billing_Country')) * 100) as 'Percent_AmountContribution', 
16
17-- rank the percent contribution and partition by the country
18rank() over ([..] partition by ('Billing_Country') order by sum('Amount') desc ) as 'Rep_Rank';
19
20-- filter to include only the top 5 reps
21q = filter q by 'Rep_Rank' <=5;

作成されたグラフには、各国の上位 5 人の営業担当と、各営業担当のランキングが表示されます。

例

累計値 (リセットなし)

次のクエリは、四半期ごとの売上合計の累計値を計算します。「partition by all」は、合計がどの列でもリセットされないことを示します。

1q = load "dataset";
2q = group q by (OrderDate_Year, OrderDate_Quarter);
3q = foreach q generate OrderDate_Year as Year, OrderDate_Quarter as Quarter, sum(Sales) as sum_amt, sum(sum(Sales)) over([.. 0] partition by all order by (OrderDate_Year, OrderDate_Quarter)) as r_sum;

年ごとの累計値

累計値は毎年リセットされます。

1q = load "dataset";
2q = group q by (OrderDate_Year, OrderDate_Quarter);
3q = foreach q generate OrderDate_Year as Year, OrderDate_Quarter as Quarter, sum(Sales) as sum_amt, sum(sum(Sales)) over([.. 0] partition by OrderDate_Year order by (OrderDate_Year, OrderDate_Quarter)) as r_sum;

移動 3 四半期の最小売上 (移動最小)

最後の 2 行から現在の行までの範囲の移動最小値を調べます。

1q = load "dataset";
2q = group q by (OrderDate_Year, OrderDate_Quarter);
3q = foreach q generate OrderDate_Year as Year, OrderDate_Quarter as Quarter, sum(Sales) as sumSales, min(sum(Sales)) over([-2 .. 0] partition by OrderDate_Year order by (OrderDate_Year, OrderDate_Quarter)) as m_min;

パーセント合計

次のクエリは、その年の四半期売上のパーセントを計算します。行の範囲 [..] は、パーセントを計算する数式で使用される、各年の小計を計算します。

1q = load "dataset";
2q = group q by (OrderDate_Year, OrderDate_Quarter);
3q = foreach q generate OrderDate_Year as Year, OrderDate_Quarter as Quarter, sum(Sales) as sumSales, (sum(Sales) * 100) / sum(sum(Sales)) over([..] partition by OrderDate_Year) as p_tot;

年間の差異

次のクエリは、前四半期と比較した売上の成長を計算します。[-1 .. -1] は、その行の四半期の前の四半期を示します。結果表の空白スペースは null 値を表します。

1q = load "dataset";
2q = group q by (OrderDate_Year, OrderDate_Quarter);
3q = foreach q generate OrderDate_Year as Year, OrderDate_Quarter as Quarter, sum(Sales) as sumSales, sum(Sales) - sum(sum(Sales)) over([-1 .. -1] partition by OrderDate_Year order by (OrderDate_Year, OrderDate_Quarter)) as diff;

順位付け関数

rank()

並び替えに基づいて順位を割り当てます。値が同じ場合は同じ順位を繰り返し、次の一致しない値で繰り返した数だけ順位をスキップします。

dense_rank()

rank() と同じですが、前の繰り返し数に基づいて値をスキップしません。

cume_dist()

リセットグループ内のデータの累積分布 (相対位置) を計算します。

row_number()

リセットグループ内のすべての行に 1 ずつ増分する番号を割り当てます。

例

1q = load "dataset";
2q = group q by (Year, Quarter);
3q = foreach q generate Year, Quarter, sum(Sales) as sum_amt, rank() over([..] partition by Year order by sum(Sales)) as rank;

次の表には、上記のコードで rank() を dense_rank()、cume_dist()、および row_number() 関数に置き換えた場合の結果列も示されています。

次のクエリは、年内で実績の高い上位 3 四半期を表示します。

1q = load "dataset";
2q = group q by (Year, Quarter);
3q = foreach q generate Year, Quarter, sum(Sales) as sum_amt, rank() over([..] partition by Year order by sum(Sales)) as rank;
4q = filter q by rank <= 3;

次のクエリは、95 パーセントを表示します。

1q = load "Oppty_Products_Scored";
2q = group q by (ProductName);
3q = foreach q generate ProductName, sum(TotalPrice) as sum_Price, percentile_cont(0.95) within group (order by 'TotalPrice') as 'sum_95Percentile';
4q = limit q 5;

関数の使用についての詳細は、「集計関数」を参照してください。