ウィンドウ関数 - - Alibaba Cloud ドキュメントセンター

MaxCompute SQLでは、ウィンドウ関数を使用して、ワークフローを柔軟に分析および処理できます。ウィンドウ関数は 'select' 句でのみ指定できます。ただし、ウィンドウ関数では、ネストしたウィンドウ関数と集計関数を使用できません。また、集計関数と同レベルで一緒に使用することもできません。

現在、MaxCompute SQL 文では、5 種類のウィンドウ関数を使用できます。

ウィンドウ関数の構文:

window_func() over (partition by [col1,col2…]
[order by [col1[asc|desc], col2[asc|desc]…]] windowing_clause)

partition by では、ウィンドウ関数の対象となる列を指定します。列が区切られ、同じ値を持つ行が同じウィンドウ関数で処理されます。現在、ウィンドウ関数では、最大 100,000,000 行のデータを指定できます。 5,000,000 行を超えないようにすることを推奨します。超えると、実行時にエラーが発生します。
order by 句では、ウィンドウ関数でデータを並び替える方法を指定します。
windowing_clause 部分には、rows メソッドを使用して、ウィンドウ関数の対象範囲を指定します。 2 通りの方法を以下に示します。
- Rows between x preceding|following and y preceding|following は、ウィンドウ関数の範囲が、x 行前または x 行後から y 行前または y 行後までであることを示します。
- Rows x preceding|following: ウィンドウ関数の範囲は、現在の行から x 行前または x 行後までになります。
- x、y は 0 以上の整数定数で、対応する値の範囲は 0〜10000 です。値が 0 の場合、現在の行を示します。 order by 句を指定した場合は、rows メソッドを使用してウィンドウ関数の範囲を指定します。

注すべてのウィンドウ関数で、rows メソッドを使用してウィンドウ関数の対象範囲を指定できるわけではありません。ウィンドウ関数では、AVG、count、Max、min、StdDev、sum などを使用できます。

COUNT

関数の定義:

Bigint count([distinct] expr) over(partition by [col1, col2…]
 [order by [col1[asc|desc], col2[asc|desc]…]] [windowing_clause])

使用法:

取得した行の総数を計算します。

パラメーターの説明:

expr: 任意のデータ型です。 NULL の行はカウントされません。 distinct キーワードが指定されている場合は、一意のカウント値が使用されます。
partition by [col1, col2…]: ウィンドウ関数で使用する列を指定します。
order by col1[asc|desc], col2[asc|desc]: order by 句を指定していない場合、現在のウィンドウの expr のカウント値が返されます。 order by 句を指定すると、返される結果は指定した順序に従って並べ替えられ、現在のウィンドウの開始行から現在行までの間のカウント値が返されます。

戻り値:

Bigint 型。

注キーワード distinct が指定されている場合、order by 句は使用できません。

例:

テーブル test_src は既に存在し、このテーブルには Bigint 型の列 user_id があるとします。

select user_id,
        count(user_id) over (partition by user_id) as count
    from test_src;
    
    | user_id | count |
    
    | 1 | 3 |
    | 1 | 3 |
    | 1 | 3 |
    | 2 | 1 |
    | 3 | 1 |
    
    -- order by 句が指定されていません。現在のウィンドウのカウント値を戻します。
    select user_id,
        count(user_id) over (partition by user_id order by user_id) as count
    from test_src;
    
    | user_id | count |
    
    | 1 | 1 | -- ウィンドウの開始行
    | 1 | 2 | --開始行から現在の行までに 2 レコード存在します。 2 を戻します。
    | 1 | 3 |
    | 2 | 1 |
    | 3 | 1 |
    
    -- order by 句が指定されていないので、現在のウィンドウの開始行から現在の行までの累積カウント値を戻します。

AVG

関数の定義:

avg([distinct] expr) over(partition by [col1, col2…]
 [order by [col1[asc|desc], col2[asc|desc]…]] [windowing_clause])

使用法:

平均を計算します。

パラメーターの説明:

distinct: キーワード distinct が指定されている場合、一意の値の平均が計算されます。
expr: Double 型。
- 入力値が String 型または Bigint 型の場合、暗黙の変換によって Double 型に変換されてから、演算に使用されます。別のデータ型の場合は、例外が発生します。
- 入力値が NULL の場合、その行は計算に使用されません。
- データ型が Boolean 型の場合、その行は計算から除外されます。
partition by [col1, col2…]: ウィンドウ関数で使用する列を指定します。
order by col1[asc|desc], col2[asc|desc]: order by 句を指定していない場合、現在のウィンドウのすべての値の平均が返されます。 order by 句を指定すると、返される結果は指定した順序に従って並べ替えられ、現在のウィンドウの開始行から現在の行までの間の累加平均が返されます。

戻り値:

Double 型。

注キーワード distinct が指定されている場合、order by 句は使用できません。

MAX

関数の定義:

max([distinct] expr) over(partition by [col1, col2…]
[order by [col1[asc|desc], col2[asc|desc]…]] [windowing_clause])

使用法:

最大値を計算します。

パラメーターの説明:

expr: Boolean 型以外の任意のデータ型です。入力値が NULL の場合、その行は計算に使用されません。キーワード distinct が指定されている場合は、一意の値の最大値が求められます。
partition by [col1, col2…]: ウィンドウ関数を使用する列を指定します。
order by col1[asc|desc], col2[asc|desc]: order by 句を指定していない場合、現在のウィンドウの最大値が返されます。 order by 句を指定すると、返される結果は指定した順序に従って並べ替えられ、現在のウィンドウの開始行から現在の行までの間の最大値が返されます。

戻り値:

expr のデータ型と同じになります。

注キーワード distinct が指定されている場合、order by 句は使用できません。

MIN

関数の定義:

min([distinct] expr) over(partition by [col1, col2…]
[order by [col1[asc|desc], col2[asc|desc]…]] [windowing_clause])

使用法:

列の最小値を計算します。

パラメーターの説明:

expr Boolean 型以外の任意のデータ型です。パラメーター値が NULL の場合、この行は計算に使用されません。キーワード distinct が指定されている場合は、一意の最小値を使用することを示します。
partition by [col1, col2…]: ウィンドウ関数で使用する列を指定します。
order by col1[asc|desc], col2[asc|desc]: order by 句を指定していない場合、現在のウィンドウの最小値が返されます。 order by 句を指定している場合、返される結果は指定した順序に従って並べ替えられ、現在のウィンドウの開始行から現在の行までの間の最小値が返されます。

戻り値:

expr と同じデータ型になります。

注キーワード distinct が指定されている場合、order by 句は使用できません。

MEDIAN

関数の定義:

Double median(Double number1,number2...) over(partition by [col1, col2…])
Decimal median(Decimal number1,number2...) over(partition by [col1,col2…])

使用法:

中央値を計算します。

パラメーターの説明:

number1,number1…: 1 to 255 digits of a Double or Decimal type.
- 入力値が String 型または Bigint 型の場合、暗黙の変換で Double 型になった後に演算が実行されます。それ以外の型の場合は、例外が発生します。
- 入力値が NULL の場合は、NULL が返されます。
- 入力値が Double 型の場合、デフォルトで Double 型の配列に変換されます。
partition by [col1, col2…]: ウィンドウ関数で使用する列を指定します。

戻り値:

Double 型。

STDDEV

関数の定義:

Double stddev([distinct] expr) over(partition by [col1, col2…]
[order by [col1[asc|desc], col2[asc|desc]…]] [windowing_clause])
Decimal stddev([distinct] expr) over(partition by [col1, col2…] 
[order by [col1[asc|desc], col2[asc|desc]…]] [windowing_clause])

使用法:

母集団の標準偏差を計算します。

パラメーターの説明:

expr: Double 型です。
- 入力値が String 型または Bigint 型の場合、Double 型に変換されてから、演算で使用されます。別のデータ型の場合は、例外が発生します。
- 入力値が NULL の場合、この行は除外されます。
- キーワード distinct が指定されている場合、一意の値の母集団標準偏差が計算されます。
Partition by [col1, col2..]: ウィンドウ関数で使用する列を指定します。
order by col1[asc|desc], col2[asc|desc]: order by 句を指定していない場合、現在のウィンドウの母集団標準偏差が返されます。 order by 句を指定している場合、返される結果は指定した順序に従って並べ替えられ、現在のウィンドウの開始行から現在の行までの間の母集団標準偏差が返されます。

戻り値:

入力値が Decimal 型の場合は、Decimal 型が返されます。そうでなければ、Double 型が返されます。

例:

select window, seq, stddev_pop('1\01') over (partition by window order by seq) from dual;

注

キーワード distinct が指定されている場合、order by 句は使用できません。
Stddev_pop stddev 関数の別名です。使用方法は stddev と同じです。

STDDEV_SAMP

関数の定義:

Double stddev_samp([distinct] expr) over(partition by [col1, col2…]
[order by [col1[asc|desc], col2[asc|desc]…]] [windowing_clause])
Decimal stddev_samp([distinct] expr) over((partition by [col1,col2…] 
[order by [col1[asc|desc], col2[asc|desc]…]] [windowing_clause])

使用法:

標準偏差を計算します。

パラメーターの説明:

Expr: Double 型です。
- 入力値が String 型または Bigint 型の場合、Double 型に変換されてから、演算で使用されます。別のデータ型の場合は、例外が報告されます。
- 入力値が NULL の場合、この行は除外されます。
- キーワード distinct が指定されている場合、一意の値の標準偏差が計算されます。
Partition by [col1, col2..]: ウィンドウ関数で使用する列を指定します。
Order by col1[asc|desc], col2[asc|desc]:order by 句を指定していない場合、現在のウィンドウのサンプル標準偏差が返されます。 order by 句を指定している場合、返される結果は指定した順序に従って並べ替えられ、現在のウィンドウの開始行から現在の行までの間のサンプル標準偏差が返されます。

戻り値:

入力値が Decimal 型の場合は、Decimal 型が返されます。そうでなければ、Double 型が返されます。

注キーワード distinct が指定されている場合、order by 句は使用できません。

SUM

関数の定義:

sum([distinct] expr) over(partition by [col1, col2…]
[order by [col1[asc|desc], col2[asc|desc]…]] [windowing_clause])

使用法:

要素の合計を計算します。

パラメーターの説明:

Expr: Double 型。
- 入力値が String 型または Bigint 型の場合、Double 型に変換されてから、演算で使用されます。別のデータ型の場合は、例外が報告されます。
- 入力値が NULL の場合、この行は除外されます。
- キーワード distinct が指定されている場合、一意の値の合計が計算されます。
Partition by [col1, col2..]: ウィンドウ関数で使用する列を指定します。
Order by col1[asc|desc], col2[asc|desc]:「order by」句を指定していない場合、現在のウィンドウの合計が返されます。 order by 句を指定している場合、返される結果は指定した順序に従って並べ替えられ、現在のウィンドウの開始行から現在の行までの合計が返されます。

戻り値:

入力パラメーター値が Bigint 型の場合は、Bigint 型が返されます。
入力パラメーター値が Decimal 型の場合は、Decimal 型が返されます。
入力パラメーター値が Double 型または String 型の場合は、Double 型が返されます。

注キーワード distinct が指定されている場合、order by 句は使用できません。

DENSE_RANK

関数の定義:

Bigint dense_rank() over(partition by [col1, col2…]
order by [col1[asc|desc], col2[asc|desc]…])

使用法:

密集ランクを計算します。col2 と同じ行にあるデータは、同じランクになります。

パラメーターの説明:

Partition by [col1, col2..]: ウィンドウ関数で使用する列を指定します。
order by col1[asc|desc], col2[asc|desc]: ランクの基準となる値を指定します。

戻り値:

Bigint 型。

例:

テーブル emp のデータは、次のとおりです。

| empno | ename | job | mgr | hiredate| sal| comm | deptno |
7369,SMITH,CLERK,7902,1980-12-17 00:00:00,800,,20
7499,ALLEN,SALESMAN,7698,1981-02-20 00:00:00,1600,300,30
7521,WARD,SALESMAN,7698,1981-02-22 00:00:00,1250,500,30
7566,JONES,MANAGER,7839,1981-04-02 00:00:00,2975,,20
7654,MARTIN,SALESMAN,7698,1981-09-28 00:00:00,1250,1400,30
7698,BLAKE,MANAGER,7839,1981-05-01 00:00:00,2850,,30
7782,CLARK,MANAGER,7839,1981-06-09 00:00:00,2450,,10
7788,SCOTT,ANALYST,7566,1987-04-19 00:00:00,3000,,20
7839,KING,PRESIDENT,,1981-11-17 00:00:00,5000,,10
7844,TURNER,SALESMAN,7698,1981-09-08 00:00:00,1500,0,30
7876,ADAMS,CLERK,7788,1987-05-23 00:00:00,1100,,20
7900,JAMES,CLERK,7698,1981-12-03 00:00:00,950,,30
7902,FORD,ANALYST,7566,1981-12-03 00:00:00,3000,,20
7934,MILLER,CLERK,7782,1982-01-23 00:00:00,1300,,10
7948,JACCKA,CLERK,7782,1981-04-12 00:00:00,5000,,10
7956,WELAN,CLERK,7649,1982-07-20 00:00:00,2450,,10
7956,TEBAGE,CLERK,7748,1982-12-30 00:00:00,1300,,10

すべての従業員を部門別にグループ化し、各グループを SAL に従って降順にソートして、グループ独自のシリアル番号を取得する必要があるとします。

SELECT deptno
        , ename
        , sal
        , DENSE_RANK() OVER (PARTITION BY deptno ORDER BY sal DESC) AS nums--Deptno as a window column, and sort in descending order according to sal.
    FROM emp;
--結果は次のとおりです。

| deptno | ename | sal | nums |

| 10 | JACCKA | 5000.0 | 1 |
| 10 | KING | 5000.0 | 1 |
| 10 | CLARK | 2450.0 | 2 |
| 10 | WELAN | 2450.0 | 2 |
| 10 | TEBAGE | 1300.0 | 3 |
| 10 | MILLER | 1300.0 | 3 |
| 20 | SCOTT | 3000.0 | 1 |
| 20 | FORD | 3000.0 | 1 |
| 20 | JONES | 2975.0 | 2 |
| 20 | ADAMS | 1100.0 | 3 |
| 20 | SMITH | 800.0 | 4 |
| 30 | BLAKE | 2850.0 | 1 |
| 30 | ALLEN | 1600.0 | 2 |
| 30 | TURNER | 1500.0 | 3 |
| 30 | MARTIN | 1250.0 | 4 |
| 30 | WARD | 1250.0 | 4 |
| 30 | JAMES | 950.0 | 5 |

RANK

関数の定義:

Bigint rank() over(partition by [col1, col2…]
order by [col1[asc|desc], col2[asc|desc]…])

使用法:

ランクを計算します。 col2 と同じ行データのランキングは下がります。

パラメーターの説明:

Partition by [col1, col2..]: ウィンドウ関数で使用する列を指定します。
Order by col1[asc|desc], col2[asc|desc]: ランクの基準となる値を指定します。

戻り値:

Bigint 型。

例:

テーブル emp のデータは次のとおりです。

| empno | ename | job | mgr | hiredate| sal| comm | deptno |
7369,SMITH,CLERK,7902,1980-12-17 00:00:00,800,,20
7499,ALLEN,SALESMAN,7698,1981-02-20 00:00:00,1600,300,30
7521,WARD,SALESMAN,7698,1981-02-22 00:00:00,1250,500,30
7566,JONES,MANAGER,7839,1981-04-02 00:00:00,2975,,20
7654,MARTIN,SALESMAN,7698,1981-09-28 00:00:00,1250,1400,30
7698,BLAKE,MANAGER,7839,1981-05-01 00:00:00,2850,,30
7782,CLARK,MANAGER,7839,1981-06-09 00:00:00,2450,,10
7788,SCOTT,ANALYST,7566,1987-04-19 00:00:00,3000,,20
7839,KING,PRESIDENT,,1981-11-17 00:00:00,5000,,10
7844,TURNER,SALESMAN,7698,1981-09-08 00:00:00,1500,0,30
7876,ADAMS,CLERK,7788,1987-05-23 00:00:00,1100,,20
7900,JAMES,CLERK,7698,1981-12-03 00:00:00,950,,30
7902,FORD,ANALYST,7566,1981-12-03 00:00:00,3000,,20
7934,MILLER,CLERK,7782,1982-01-23 00:00:00,1300,,10
7948,JACCKA,CLERK,7782,1981-04-12 00:00:00,5000,,10
7956,WELAN,CLERK,7649,1982-07-20 00:00:00,2450,,10
7956,TEBAGE,CLERK,7748,1982-12-30 00:00:00,1300,,10

SELECT deptno
        , ename
        , sal
        , RANK() OVER (PARTITION BY deptno ORDER BY sal DESC) AS nums--Deptno as a window column, and sort in descending order according to sal.
    FROM emp;
--結果は次のとおりです。

| deptno | ename | sal | nums |

| 10 | JACCKA | 5000.0 | 1 |
| 10 | KING | 5000.0 | 1 |
| 10 | CLARK | 2450.0 | 3 |
| 10 | WELAN | 2450.0 | 3 |
| 10 | TEBAGE | 1300.0 | 5 |
| 10 | MILLER | 1300.0 | 5 |
| 20 | SCOTT | 3000.0 | 1 |
| 20 | FORD | 3000.0 | 1 |
| 20 | JONES | 2975.0 | 3 |
| 20 | ADAMS | 1100.0 | 4 |
| 20 | SMITH | 800.0 | 5 |
| 30 | BLAKE | 2850.0 | 1 |
| 30 | ALLEN | 1600.0 | 2 |
| 30 | TURNER | 1500.0 | 3 |
| 30 | MARTIN | 1250.0 | 4 |
| 30 | WARD | 1250.0 | 4 |
| 30 | JAMES | 950.0 | 6 |

LAG

関数の定義:

lag(expr，Bigint offset, default) over(partition by [col1, col2…]
[order by [col1[asc|desc], col2[asc|desc]…]])

コマンドの説明:

offset に従って現在行の n 行前の値を取得します。現在の行番号がrnの場合は、行番号が rn - offset である行の値を取得します。

パラメーターの説明:

expr: 任意のデータ型です。
offset: Bigint 型の定数です。入力値が String 型または Double 型の場合は、暗黙の変換によって Bigint 型に変換されます。 Offset > 0 ｡
default: 指定したオフセットの範囲が制限を超えた場合のデフォルト値を定義します。定数で、デフォルトは NULL です。
Partition by [col1, col2..]: ウィンドウ関数で使用する列を指定します。
order by col1[asc|desc], col2[asc|desc]: Specifies the order method for return result.

戻り値:

expr と同じデータ型が返されます。

LEAD

コマンド形式:

lead(expr，Bigint offset, default) over(partition by [col1, col2…]
[order by [col1[asc|desc], col2[asc|desc]…]])

コマンドの説明:

offset に従って､現在行の n 行後にある値を取得します。現在の行番号が rn の場合、行番号が rn + offset である行の値が取得されます。

パラメーターの説明:

expr: 任意のデータ型です。
offset: Bigint 型の定数です。入力値が String 型、Decimal 型、Double 型の場合は、暗黙の変換によって Bigint 型に変換されます。 Offset > 0 です。
default: 指定したオフセットの範囲が制限を超えた場合のデフォルト値を定義します。定数です。
Partition by [col1, col2..]: ウィンドウ関数で使用する列を指定します。
order by col1[asc|desc], col2[asc|desc]: 戻される結果の並び替え方法を指定します。

戻り値:

expr と同じデータ型が返されます。

例:

select c_Double_a,c_String_b,c_int_a,lead(c_int_a,1) over(partition by c_Double_a order by c_String_b) from dual;
select c_String_a,c_time_b,c_Double_a,lead(c_Double_a,1) over(partition by c_String_a order by c_time_b) from dual;
select c_String_in_fact_num,c_String_a,c_int_a,lead(c_int_a) over(partition by c_String_in_fact_num order by c_String_a) from dual;

PERCENT_RANK

コマンド形式:

Percent_rank () over (partition by [col1, col2...]
order by [col1[asc|desc], col2[asc|desc]…])

コマンドの説明:

データグループ内の特定の行の相対的な順位を計算します。

パラメーターの説明:

Partition by [col1, col2..]: ウィンドウ関数で使用する列を指定します。
order by col1[asc|desc], col2[asc|desc]: ランクの基準となる値を指定します。

戻り値:

Double 型が返されます。値の範囲は [0、1]です。相対的な順位の計算式は、 (rank-1)/(number of rows -1)です。

注現在、単一ウィンドウ内の行の制限数は、最大 10,000,000 です。

ROW_NUMBER

コマンド形式:

row_number() over(partition by [col1, col2…]
order by [col1[asc|desc], col2[asc|desc]…])

コマンドの説明:

1 から始まる行番号を計算します。

パラメーターの説明:

Partition by [col1, col2..]: ウィンドウ関数で使用する列を指定します。
order by col1[asc|desc], col2[asc|desc]: 戻される結果の並び替え方法を指定します。

戻り値:

Bigint 型が返されます。

例:

テーブル emp のデータは次のとおりです。

| empno | ename | job | mgr | hiredate| sal| comm | deptno |
7369,SMITH,CLERK,7902,1980-12-17 00:00:00,800,,20
7499,ALLEN,SALESMAN,7698,1981-02-20 00:00:00,1600,300,30
7521,WARD,SALESMAN,7698,1981-02-22 00:00:00,1250,500,30
7566, Jones, Manager, fig-04-02 00:00:00, 2975, 20
7654,MARTIN,SALESMAN,7698,1981-09-28 00:00:00,1250,1400,30
7698,BLAKE,MANAGER,7839,1981-05-01 00:00:00,2850,,30
7782,CLARK,MANAGER,7839,1981-06-09 00:00:00,2450,,10
7788, Scott, analyst, fig-04-19 00:00:00, 3000, 20
7839,KING,PRESIDENT,,1981-11-17 00:00:00,5000,,10
7844,TURNER,SALESMAN,7698,1981-09-08 00:00:00,1500,0,30
7876,ADAMS,CLERK,7788,1987-05-23 00:00:00,1100,,20
7900,JAMES,CLERK,7698,1981-12-03 00:00:00,950,,30
7902,FORD,ANALYST,7566,1981-12-03 00:00:00,3000,,20
7934,MILLER,CLERK,7782,1982-01-23 00:00:00,1300,,10
7948,JACCKA,CLERK,7782,1981-04-12 00:00:00,5000,,10
7956,WELAN,CLERK,7649,1982-07-20 00:00:00,2450,,10
7956, tebage, clerk, maid-12-30 00:00:00, 1300, 10

SELECT deptno
        , ename
        , Sal
        , Row_number () over (partition by deptno order by Sal DESC) as Nums  --Deptno as a window column, and sort in descending order according to sal.
    FROM emp;
--結果は次のとおりです。

| deptno | ename | sal | nums |

| 10 | JACCKA | 5000.0 | 1 |
| 10 | KING | 5000.0 | 2 |
| 10 | CLARK | 2450.0 | 3 |
| 10 | WELAN | 2450.0 | 4 |
| 10 | TEBAGE | 1300.0 | 5 |
| 10 | MILLER | 1300.0 | 6 |
| 20 | SCOTT | 3000.0 | 1 |
| 20 | FORD | 3000.0 | 2 |
| 20 | JONES | 2975.0 | 3 |
| 20 | ADAMS | 1100.0 | 4 |
| 20 | SMITH | 800.0 | 5 |
| 30 | BLAKE | 2850.0 | 1 |
| 30 | ALLEN | 1600.0 | 2 |
| 30 | TURNER | 1500.0 | 3 |
| 30 | MARTIN | 1250.0 | 4 |
| 30 | WARD | 1250.0 | 5 |
| 30 | JAMES | 950.0 | 6 |

CLUSTER_SAMPLE

コマンド形式:

boolean cluster_sample([Bigint x, Bigint y])
over(partition by [col1, col2..])

コマンドの説明:

グループサンプリングに使用します。

パラメーターの説明:

x: Bigint 型の定数で、x>=1 です。パラメータ y を指定した場合、x はウィンドウを x 個の部分に分割することを示します。パラメータ y を指定しない場合、x はウィンドウ内で x 行のレコードを選択することを示します (x 行がこのウィンドウ内にあれば、true が返されます)。 x が NULL の場合、NULL が返されます。
y: Bigint 型の定数で、y>=1、y<=x です。ウィンドウ内の x 部分から y 部分レコードを選択することを示します (つまり、y 部分のレコードが存在する場合、戻り値は true になります)。y が NULL の場合、NULL が返されます。
[col1、col2]: ウィンドウ関数を使用する列を指定します。

戻り値:

Boolean 型が返されます。

例:

テーブル test_tbl に 2 つの列 key と value がある場合、key がグループ化フィールドです。以下に示すように、key に対応する値には groupa と groupb があり、value フィールドはキー値を示します。


    | key | value |
    
    | groupa | -1.34764165478145 |
    | groupa | 0.740212609046718 |
    | groupa | 0.167537127858695 |
    | groupa | 0.630314566185241 |
    | GroupA | 0.0112401388646925 |
    | groupa | 0.199165745875297 |
    | groupa | -0.320543343353587 |
    | groupa | -0.273930924365012 |
    | groupa | 0.386177958942063 |
    | groupa | -1.09209976687047 |
    | groupb | -1.10847690938643 |
    | groupb | -0.725703978381499 |
    | groupb | 1.05064697475759 |
    | groupb | 0.135751224393789 |
    | groupb | 2.13313102040396 |
    | groupb | -1.11828960785008 |
    | groupb | -0.849235511508911 |
    | groupb | 1.27913806620453 |
    | groupb | -0.330817716670401 |
    | groupb | -0.300156896191195 |
    | groupb | 2.4704244205196 |
    | groupb | -1.28051882084434 |

各グループから 10％の値を選択するには、以下の MaxCompute SQL を推奨します。

Select key, Value
    from (
        Select key, value, cluster_sample (10, 1) over (partition by key) as flag
        from tbl
        ) sub
    where flag = true;

| Key | value |

| groupa | 0.167537127858695 |
| groupb | 0.135751224393789 |

NTILE

コマンド形式:

BIGINT ntile(BIGINT n) over(partition by [col1, col2…]  
[order by [col1[asc|desc], col2[asc|desc]…]] [windowing_clause]))

コマンドの説明:

グループ化されたデータを順番に N 個のスライスにカットして､現在のスライス値を返すために使用します。スライスが不均等な場合、最初のスライスの分布はデフォルトで増加します。

パラメーターの説明:

N: Bigint データ型。

戻り値:

Bigint 型が返されます。

例：

テーブル EMP のデータは、以下のとおりです。

| Empno | ename | job | Mgr | hiredate | Sal | REM | deptno |
7369, Smith, clerk, maid-12-17 00:00:00, 800, 20
7499, Allen, salesman, maid-02-20 00:00:00, 1600,300, 30
7521, Ward, salesman, maid-02-22 00:00:00, 1250,500, 30
7566, Jones, Manager, fig-04-02 00:00:00, 2975, 20
7654 Martin, salesman, fig-09-28 00:00:00, fig, 30
7698, Blake, Manager, fig-05-01 00:00:00, 2850, 30
7782, Clark, Manager, fig-06-09 00:00:00, 2450, 10
7788, Scott, analyst, fig-04-19 00:00:00, 3000, 20
00:00:00, King, President, 1991-11-17 5000, 7839, 10
7844, Turner, salesman, fig-09-08 00:00:00, 1500,0, 30
7876, Adams, clerk, maid-05-23 00:00:00, 1100, 20
7900 James, clerk, maid-12-03 00:00:00, 950, 30
7902 Ford, analyst, fig-12-03 00:00:00, 3000, 20
7934 Miller, clerk, fig-01-23 00:00:00, 1300, 10
7948, jaccka, clerk, fig-04-12 00:00:00, 5000, 10
7956, welan, clerk, fig-07-20 00:00:00, 2450, 10
7956, tebage, clerk, maid-12-30 00:00:00, 1300, 10

全従業員を Sal の高低に応じて 3 つのグループに分け、従業員自身のグループのシリアル番号を取得する必要があるとします。

Select deptno, ename, Sal, ntile (3) over (partition by depno order by Sal DESC) as nt3 from EMP;
-- 実行結果は次のとおりです

| Deptno | ename | Sal | nt3 |

| 10 | jaccka | 5000.0 | 1 |
| 10 | King | 5000.0 | 1 |
| 10 | welan | 2450.0 | 2 |
| 10 | Clark | 2450.0 | 2 |
| 10 | tebage | 1300.0 | 3 |
10 | Miller | 1300.0 | 3 |
| 20 | Scott | 3000.0 | 1 |
| 20 | Ford | 3000.0 | 1 |
| 20 | Jones | 2975.0 | 2 |
| 20 | Adams | 1100.0 | 2 |
| 20 | Smith | 800.0 | 3 |
| 30 | Blake | 2850.0 | 1 |
| 30 | Allen | 1600.0 | 1 |
| 30 | Turner | 1500.0 | 2 |
| 30 | Martin | 1250.0 | 2 |
| 30 | ward | 1250.0 | 3 |
| 30 | James | 950.0 | 3 |