集計関数は、複数の入力レコードをグループ化して単一の出力レコードを形成します。 MaxCompute SQLのGROUP BY句で集計関数を使用できます。 このトピックでは、MaxCompute SQLでサポートされている集計関数の構文、パラメーター、および例について説明します。 集計関数を使用してデータ開発をガイドします。
次の表に、MaxCompute SQLでサポートされている集計関数を示します。
関数 | 説明 |
特定の列からランダムな値を返します。 | |
指定した列の個別の入力値のおおよその数を返します。 | |
指定した列の最大値に対応する行の列値を返します。 | |
特定の列の最小値に対応する行の列値を返します。 | |
列の平均値を返します。 | |
ビット単位のAND演算に基づいて入力値を集計します。 | |
ビット単位のOR演算に基づいて入力値を集計します。 | |
指定した列を配列に集約します。 | |
指定した列の異なる値を配列に集約します。 | |
指定された条件に一致するレコードの数を返します。 | |
expr値がTrueのレコードの数を返します。 | |
指定した2つの数値列の母集団共分散を計算します。 | |
指定した2つの数値列のサンプル共分散を計算します。 | |
各入力値が表示される回数を含むマップを返します。 | |
aとbを使用して作成されたマップを返します。 aはマップ内のキーです。 bはマップ内のキーの値です。 | |
すべての入力マップの和集合である新しいマップを返します。 | |
すべての入力マップの和集合である新しいマップを返します。 出力マップは、すべての入力マップの一致するキーの値を合計します。 | |
列の最大値を返します。 | |
指定した列の最大値に対応する行の列値を返します。 | |
列の中央値を返します。 | |
列の最小値を返します。 | |
特定の列の最小値に対応する行の列値を返します。 | |
aとbを使用して作成されたマップを返します。 aはマップ内のキーです。 bは配列を作成するために使用され、これはマップのキーの値として使用されます。 | |
指定した列に基づく近似ヒストグラムを返します。 | |
正確なパーセンタイルを計算します。 この関数は、少量のデータが計算されるシナリオに適しています。 | |
近似パーセンタイルを返します。 この関数は、大量のデータが計算されるシナリオに適用されます。 | |
すべての入力値の母標準偏差を返します。 | |
すべての入力値のサンプル標準偏差を返します。 | |
列の合計を返します。 | |
指定した数値列のサンプル分散を計算します。 | |
指定した数値列の分散を計算します。 | |
指定された区切り文字で文字列を連結します。 |
注意事項
MaxCompute V2.0は追加機能を提供します。 使用する関数にMaxCompute V2.0データ型エディションでサポートされている新しいデータ型が含まれている場合は、SETステートメントを実行してMaxCompute V2.0データ型エディションを有効にする必要があります。 新しいデータ型には、TINYINT、SMALLINT、INT、FLOAT、VARCHAR、TIMESTAMP、およびBINARYが含まれます。
セッションレベル: MaxCompute V2.0データ型エディションを使用するには、実行するsql文の前に
set odps. SQL. type.system.odps2=true;を追加し、一緒にコミットして実行する必要があります。プロジェクトレベル: プロジェクトの所有者は、次のコマンドを実行して、プロジェクトの要件に基づいてプロジェクトのMaxCompute V2.0データ型エディションを有効にできます。 設定は10〜15分後に有効になります。 例：
setproject odps.sql.type.system.odps2=true;setprojectの詳細については、「プロジェクト操作」をご参照ください。 プロジェクトレベルでMaxCompute V2.0データ型エディションを有効にする際の注意事項の詳細については、「データ型エディション」をご参照ください。ワーカーには最大200万個の要素を含めることができます。
複数の集計関数を含むSQL文を使用していて、プロジェクトのリソースが不足している場合、メモリオーバーフローが発生する可能性があります。 ビジネス要件に基づいて、SQLステートメントを最適化するか、コンピューティングリソースを購入することを推奨します。
構文
集計関数の構文:
<aggregate_name>(<expression>[,..]) [within group (order by <col1>[,<col2>…])] [filter (where <where_condition>)]<aggregate_name>(<expression>[,..]): 組み込みの集計関数またはユーザー定義の集計関数 (UDAF) 。 集計関数の形式は、その構文に基づいています。within group (order by <col1>[,<col2>…]): 集計関数の構文にこの式が含まれている場合、システムは自動的に<col1>[,<col2>…]の入力データを昇順でソートします。 入力データを降順でソートするには、group (order by <col1>[,<col2>…] [desc])内の式を使用します。この式を使用する前に、次の制限に注意してください。
この式は、WM_CONCAT、COLLECT_LIST、COLLECT_SET、およびUDAFにのみ使用できます。
SELECTステートメントの複数の集計関数に
group (order by <col1>[,<col2>...])内の式が含まれている場合、order by <col1>[,<col2>...]はこれらの関数で同じである必要があります。集計関数のパラメーターにDISTINCTキーワードが含まれている場合、異なる値を持つ列は、式の順序で
<col1>[,<col2>…]で指定する必要があります。ORDER BY句で指定された列は、異なる値を持つ列のサブセットです。 式<col1>[,<col2>…]のフィールドのデータ型は、集計関数の入力パラメーターのデータ型と同じである必要があります。説明group (order by <col1>[,<col2>…])の式を使用する集計関数で指定できる入力パラメーターは1つだけです。 したがって、集計関数の入力パラメーターにDISTINCTキーワードが含まれている場合、ORDER BY句で指定できる列は1つだけです。 列のデータ型は、集計関数の入力パラメーターのデータ型と同じである必要があります。たとえば、WM_CONCAT関数の入力パラメーターがSTRINGデータ型の場合、
ORDER BY句で指定するフィールドのデータ型もSTRINGである必要があります。 詳細については、「例4」をご参照ください。 empテーブルの作成方法の詳細については、「サンプルデータ」をご参照ください。
例：
-- Example 1: Sort the input data in ascending order and return the output data. select x, wm_concat(',', y) within group (order by y) from values('k', 1),('k', 3),('k', 2) as t(x, y) group by x; -- The following result is returned: +------------+------------+ | x | _c1 | +------------+------------+ | k | 1,2,3 | +------------+------------+ -- Example 2: Sort the input data in descending order and return the output data. select x, wm_concat(',', y) within group (order by y desc) from values('k', 1),('k', 3),('k', 2) as t(x, y) group by x; -- The following result is returned: +------------+------------+ | x | _c1 | +------------+------------+ | k | 3,2,1 | +------------+------------+ -- Example 3 select id, wm_concat(distinct ',', name) within group (order by name desc) from values('k', '1'),('k', '3'),('k', '2') as t(id, name) group by id; -- The following result is returned: +------------+------------+ | id | _c1 | +------------+------------+ | k | 3,2,1 | +------------+------------+ -- Example 4 -- The parameters of an aggregate function include the DISTINCT keyword. In this case, if the input parameter sal of the WM_CONCAT function is of the BIGINT data type, the data type of the input parameter is implicitly converted into a value of the STRING data type. -- To ensure data type consistency, you must use the CAST function to convert the data type of the input parameter sal into a value of the STRING data type. Otherwise, an error is reported. select deptno, wm_concat(distinct ',', sal) within group (order by cast(sal as STRING ) desc) from emp group by deptno order by deptno; -- The following result is returned: +------------+------------+ | deptno | _c1 | +------------+------------+ | 10 | 5000,2450,1300 | | 20 | 800,3000,2975,1100 | | 30 | 950,2850,1600,1500,1250 | +------------+------------+[filter (where <where_condition>)]: 集計関数にこの式が含まれている場合、集計関数は<where_condition>で指定された条件を満たすデータのみを処理します。<where_condition>の詳細については、「WHERE句 (where_condition) 」をご参照ください。この式を使用する前に、次の制限に注意してください。
組み込みの集計関数のみがこの式をサポートしています。 UDAFはこの式をサポートしていません。
count(*)は、[filter (where <where_condition>)]という式をサポートしています。COUNT_IF関数は、
[filter (where <where_condition>)]という式をサポートしていません。
例：
-- Example 1: Filter and aggregate data. select sum(x), sum(x) filter (where y > 1), sum(x) filter (where y > 2) from values(null, 1),(1, 2),(2, 3),(3, null) as t(x, y); -- The following result is returned: +------------+------------+------------+ | _c0 | _c1 | _c2 | +------------+------------+------------+ | 6 | 3 | 2 | +------------+------------+------------+ -- Example 2: Use multiple aggregate functions to filter and aggregate data. select count_if(x > 2), sum(x) filter (where y > 1), sum(x) filter (where y > 2) from values(null, 1),(1, 2),(2, 3),(3, null) as t(x, y); -- The following result is returned: +------------+------------+------------+ | _c0 | _c1 | _c2 | +------------+------------+------------+ | 1 | 3 | 2 | +------------+------------+------------+
サンプルデータ
このセクションでは、関数の使用方法を理解するためのサンプルソースデータと例を示します。 empという名前のテーブルを作成し、サンプルデータをテーブルに挿入します。 サンプルコマンド：
create table if not exists emp
(empno bigint,
ename string,
job string,
mgr bigint,
hiredate datetime,
sal bigint,
comm bigint,
deptno bigint);
tunnel upload emp.txt emp;emp.txtファイルには、次のサンプルデータが含まれています。
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フィルター式
制限事項
MaxComputeの組み込み集計関数のみがフィルター式をサポートしています。 UDAFはフィルター式をサポートしていません。
COUNT(*)はフィルター式では使用できません。 フィルター式でCOUNT_IF関数を使用します。
構文
<aggregate_name>(<expression>[,...]) [filter (where <where_condition>)]説明
すべての集計関数はフィルター式をサポートします。 フィルター条件を指定した場合、フィルター条件を満たす行データのみを、データ処理のために関連する集計関数に渡すことができます。
パラメーター
aggregate_name: 必須です。 集計関数の名前。 ビジネス要件に基づいて、このトピックで説明されている集計関数を選択します。
expression: 必須です。 選択した集計関数のパラメーター。 選択した集計関数の説明に基づいて、このパラメーターを指定します。
where_condition: オプション。 The filter condition. where_conditionの詳細については、「WHERE句 (where_condition) 」をご参照ください。
戻り値
詳細については、各集計関数の戻り値の説明をご参照ください。
例：
select sum(sal) filter (where deptno=10), sum(sal) filter (where deptno=20), sum(sal) filter (where deptno=30) from emp;次の応答が返されます。
+------------+------------+------------+ | _c0 | _c1 | _c2 | +------------+------------+------------+ | 17500 | 10875 | 9400 | +------------+------------+------------+
ANY_VALUE
構文
any_value(<colname>)説明
特定の列からランダムな値を返します。 この関数は、MaxCompute V2.0の追加関数です。
パラメーター
colname: 必須です。 列の名前。任意のデータ型を指定できます。
戻り値
戻り値のデータ型は、colnameパラメーターのデータ型と同じです。 colnameパラメーターの値がnullの場合、この値を含む行は計算に使用されません。
例
例1: 従業員の1人を選択します。 例：
select any_value(ename) from emp;次の応答が返されます。
+------------+ | _c0 | +------------+ | SMITH | +------------+例2:
GROUP BYでこの関数を使用して、deptno列に基づいてすべての従業員をグループ化し、各グループから1人の従業員を選択します。 例：select deptno, any_value(ename) from emp group by deptno;次の応答が返されます。
+------------+------------+ | deptno | _c1 | +------------+------------+ | 10 | CLARK | | 20 | SMITH | | 30 | ALLEN | +------------+------------+
APPROX_DISTINCT
構文
approx_distinct(<colname>)説明
指定した列の個別の入力値のおおよその数を返します。 この関数は、MaxCompute V2.0の追加関数です。
パラメーター
colname: 必須です。 重複を削除する必要がある列の名前。
戻り値
BIGINT型の値が返されます。 この関数は5% の標準エラーを生成します。 colnameパラメーターで指定された列の値がnullの場合、この値を含む行は計算に使用されません。
例
例1: sal列の個別の値のおおよその数を計算します。 例：
select approx_distinct(sal) from emp;次の応答が返されます。
+-------------------+ | numdistinctvalues | +-------------------+ | 12 | +-------------------+例2:
GROUP BYでこの関数を使用して、すべての従業員を部署 (deptno) ごとにグループ化し、sal列の個別値のおおよその数を計算します。 例：select deptno, approx_distinct(sal) from emp group by deptno;次の応答が返されます。
+------------+-------------------+ | deptno | numdistinctvalues | +------------+-------------------+ | 10 | 3 | | 20 | 4 | | 30 | 5 | +------------+-------------------+
ARG_MAX
構文
arg_max(<valueToMaximize>, <valueToReturn>)説明
valueToMaximizeの値が含まれている行を検索し、行のvalueToReturnの値を返します。 この関数は、MaxCompute V2.0の追加関数です。
パラメーター
valueToMaximize: 必須です。 列の名前。任意のデータ型を指定できます。
valueToReturn: 必須です。 列の名前。任意のデータ型を指定できます。
戻り値
戻り値のデータ型は、valueToReturnパラメーターのデータ型と同じです。 複数の行にvalueToMaximizeの最大値が含まれている場合、いずれかの行のvalueToReturnの値がランダムに返されます。 valueToMaximizeの値がnullの場合、この値を含む行は計算に使用されません。
例
例1: 給与が最も高い従業員の名前を返します。 例：
select arg_max(sal, ename) from emp;次の応答が返されます。
+------------+ | _c0 | +------------+ | KING | +------------+例2:
GROUP BYでこの関数を使用して、すべての従業員を部門 (deptno) ごとにグループ化し、各グループで最も給与の高い従業員の名前を返します。 例：select deptno, arg_max(sal, ename) from emp group by deptno;次の応答が返されます。
+------------+------------+ | deptno | _c1 | +------------+------------+ | 10 | KING | | 20 | SCOTT | | 30 | BLAKE | +------------+------------+
ARG_MIN
構文
arg_min(<valueToMinimize>, <valueToReturn>)説明
valueToMinimizeの最小値が含まれる行を検索し、行のvalueToReturnの値を返します。 この関数は、MaxCompute V2.0の追加関数です。
パラメーター
valueToMinimize: 必須です。 任意のデータ型の値。
valueToReturn: 必須です。 任意のデータ型の値。
戻り値
戻り値のデータ型は、valueToReturnパラメーターのデータ型と同じです。 複数の行にvalueToMinimizeの最小値が含まれている場合、いずれかの行のvalueToReturnの値がランダムに返されます。 valueToMinimizeの値がnullの場合、この値を含む行は計算に使用されません。
例
例1: 給与が最も低い従業員の名前を返します。 例：
select arg_min(sal, ename) from emp;次の応答が返されます。
+------------+ | _c0 | +------------+ | SMITH | +------------+例2:
GROUP BYでこの関数を使用して、すべての従業員を部門 (deptno) ごとにグループ化し、各グループで最も給与の低い従業員の名前を返します。 例：select deptno, arg_min(sal, ename) from emp group by deptno;次の応答が返されます。
+------------+------------+ | deptno | _c1 | +------------+------------+ | 10 | MILLER | | 20 | SMITH | | 30 | JAMES | +------------+------------+
AVG
構文
DECIMAL|DOUBLE avg(<colname>)説明
列の平均値を返します。
パラメーター
colname: 必須です。 列値はすべてのデータ型をサポートし、計算前にDOUBLE型に変換できます。
戻り値
colnameの値がnullの場合、この値を含む行は計算に使用されません。 入力データと戻り値のデータ型の対応関係を次の表に示します。
入力タイプ
戻り値タイプ
TINYINT
DOUBLE
SMALLINT
DOUBLE
INT
DOUBLE
BIGINT
DOUBLE
FLOAT
DOUBLE
DOUBLE
DOUBLE
DECIMAL
DECIMAL
例
例1: すべての従業員の平均給与 (sal) 値を計算します。 例：
select avg(sal) from emp;次の応答が返されます。
+------------+ | _c0 | +------------+ | 2222.0588235294117 | +------------+例2:
GROUP BYでこの関数を使用して、すべての従業員を部門 (deptno) ごとにグループ化し、各部門の従業員の平均給与 (sal) 値を計算します。 例：select deptno, avg(sal) from emp group by deptno;次の応答が返されます。
+------------+------------+ | deptno | _c1 | +------------+------------+ | 10 | 2916.6666666666665 | | 20 | 2175.0 | | 30 | 1566.6666666666667 | +------------+------------+
BITWISE_AND_AGG
構文
bigint bitwise_and_agg(bigint value)説明
ビット単位のAND演算に基づいて入力値を集計します。
パラメーター
value: 必須です。 BIGINT型の値。 null値は計算には使用されません。
戻り値
BIGINT型の値が返されます。
例
select id, bitwise_and_agg(v) from values (1L, 2L), (1L, 1L), (2L, null), (1L, null) t(id, v) group by id;次の応答が返されます。
+------------+------------+ | id | _c1 | +------------+------------+ | 1 | 0 | | 2 | NULL | +------------+------------+
BITWISE_OR_AGG
構文
bigint bitwise_or_agg(bigint value)説明
ビット単位のOR演算に基づいて入力値を集計します。
パラメーター
value: 必須です。 BIGINT型の値。 null値は計算には使用されません。
戻り値
BIGINT型の値が返されます。
例
select id, bitwise_or_agg(v) from values (1L, 2L), (1L, 1L), (2L, null), (1L, null) t(id, v) group by id;次の応答が返されます。
+------------+------------+ | id | _c1 | +------------+------------+ | 1 | 3 | | 2 | NULL | +------------+------------+
COLLECT_LIST
構文
array collect_list(<colname>)説明
colnameで指定された値を配列に集計します。 この関数は、MaxCompute V2.0の追加関数です。
パラメーター
colname: 必須です。 列の名前。任意のデータ型を指定できます。
戻り値
ARRAY型の値が返されます。 colnameで指定した列の値がnullの場合、この値を含む行は計算に使用されません。
例
例1: すべての従業員のサラリー (sal) 値を配列に集約します。 例：
select collect_list(sal) from emp;次の応答が返されます。
+------------+ | _c0 | +------------+ | [800,1600,1250,2975,1250,2850,2450,3000,5000,1500,1100,950,3000,1300,5000,2450,1300] | +------------+例2:
GROUP BYでこの関数を使用して、deptno列に基づいてすべての従業員をグループ化し、各グループのsal列の値を配列に集計します。 例：select deptno, collect_list(sal) from emp group by deptno;次の応答が返されます。
+------------+------------+ | deptno | _c1 | +------------+------------+ | 10 | [2450,5000,1300,5000,2450,1300] | | 20 | [800,2975,3000,1100,3000] | | 30 | [1600,1250,1250,2850,1500,950] | +------------+------------+例3:
GROUP BYでこの関数を使用して、deptno列に基づいてすべての従業員をグループ化し、重複が削除された後に各グループのsal列の値を集計します。 例：select deptno, collect_list(distinct sal) from emp group by deptno;次の応答が返されます。
+------------+------------+ | deptno | _c1 | +------------+------------+ | 10 | [1300,2450,5000] | | 20 | [800,1100,2975,3000] | | 30 | [950,1250,1500,1600,2850] | +------------+------------+
COLLECT_SET
構文
array collect_set(<colname>)説明
colnameで指定された値を、異なる値のみを持つ配列に集約します。 この関数は、MaxCompute V2.0の追加関数です。
パラメーター
colname: 必須です。 列の名前。任意のデータ型を指定できます。
戻り値
ARRAY型の値が返されます。 colnameで指定した列の値がnullの場合、この値を含む行は計算に使用されません。
例
例1: すべての従業員のサラリー (sal) 値を、異なる値のみを持つ配列に集計します。 例：
select collect_set(sal) from emp;次の応答が返されます。
+------------+ | _c0 | +------------+ | [800,950,1100,1250,1300,1500,1600,2450,2850,2975,3000,5000] | +------------+例2:
GROUP BYでこの関数を使用して、deptno列に基づいてすべての従業員をグループ化し、各グループの従業員のsal列の値を、異なる値のみを持つ配列に集計します。 例：select deptno, collect_set(sal) from emp group by deptno;次の応答が返されます。
+------------+------------+ | deptno | _c1 | +------------+------------+ | 10 | [1300,2450,5000] | | 20 | [800,1100,2975,3000] | | 30 | [950,1250,1500,1600,2850] | +------------+------------+
COUNT
構文
bigint count([distinct|all] <colname>)説明
指定された条件に一致するレコードの数を返します。
パラメーター
distinct | all: オプション。 このパラメーターは、カウント中に重複を削除するかどうかを指定します。 デフォルト値はallで、すべてのレコードがカウントされます。 このパラメーターがdistinctに設定されている場合、異なる値を持つレコードのみがカウントされます。
colname: 必須です。 列の名前。任意のデータ型を指定できます。 colnameの値はアスタリスク (
*) です。count(*)は、すべての行の数が返されることを示します。
戻り値
BIGINT型の値が返されます。 colnameで指定した列の値がnullの場合、この値を含む行は計算に使用されません。
例
例1: すべての部門の従業員の総数を計算します。 例：
select count(*) from emp;次の応答が返されます。
+------------+ | _c0 | +------------+ | 17 | +------------+例2:
GROUP BYでこの関数を使用して、すべての従業員を部門 (deptno) ごとにグループ化し、各部門の従業員数を計算します。 例：select deptno, count(*) from emp group by deptno;次の応答が返されます。
+------------+------------+ | deptno | _c1 | +------------+------------+ | 10 | 6 | | 20 | 5 | | 30 | 6 | +------------+------------+例3: 部門の数を計算するときに重複を削除します。 サンプル文:
select count(distinct deptno) from emp;次の応答が返されます。
+------------+ | _c0 | +------------+ | 3 | +------------+
COUNT_IF
構文
bigint count_if(boolean <expr>)説明
expr値がTrueのレコードの数を返します。
パラメーター
expr: 必須です。 ブーリアン表現。
戻り値
BIGINT型の値が返されます。 exprパラメーターの値がFalseの場合、またはexprの特定の列の値がnullの場合、この値を含む行は計算に使用されません。
例
select count_if(sal > 1000), count_if(sal <=1000) from emp;次の応答が返されます。
+------------+------------+ | _c0 | _c1 | +------------+------------+ | 15 | 2 | +------------+------------+
COVAR_POP
構文
double covar_pop(<colname1>, <colname2>)説明
指定した2つの数値列の母集団共分散を計算します。 この関数は、MaxCompute V2.0の追加関数です。
パラメーター
colname1およびcolname2: 必須です。 数値データ型の列。 指定された列が数値列でない場合、null値が返されます。
例
次の文を実行して、empテーブルにデータを追加します。
-- sal_new is the new salary column. alter table emp add columns (sal_new bigint); insert overwrite table emp select empno, ename, job, mgr, hiredate, sal, comm, deptno, sal+1000 from emp;例1: sal列とsal_new列の母集団共分散を計算します。 例：
select covar_pop(sal, sal_new) from emp;次の応答が返されます。
+------------+ | _c0 | +------------+ | 1594550.1730103805 | +------------+例2:
GROUP BYでこの関数を使用して、すべての従業員を部署 (deptno) ごとにグループ化し、同じグループの従業員のsal列とsal_new列の母集団共分散を計算します。 例：select deptno, covar_pop(sal, sal_new) from emp group by deptno;次の応答が返されます。
+------------+------------+ | deptno | _c1 | +------------+------------+ | 10 | 2390555.5555555555 | | 20 | 1009500.0 | | 30 | 372222.2222222222 | +------------+------------+
COVAR_SAMP
構文
double covar_samp(<colname1>, <colname2>)説明
指定した2つの数値列のサンプル共分散を計算します。 この関数は、MaxCompute V2.0の追加関数です。
パラメーター
colname1およびcolname2: 必須です。 数値データ型の列。 指定された列が数値列でない場合、null値が返されます。
例
次の文を実行して、empテーブルにデータを追加します。
-- sal_new is the new salary column. alter table emp add columns (sal_new bigint); insert overwrite table emp select empno, ename, job, mgr, hiredate, sal, comm, deptno, sal+1000 from emp;例1: sal列とsal_new列のサンプル共分散を計算します。 例：
select covar_samp(sal, sal_new) from emp;次の応答が返されます。
+------------+ | _c0 | +------------+ | 1694209.5588235292 | +------------+例2:
GROUP BYでこの関数を使用して、すべての従業員を部門 (deptno) ごとにグループ化し、同じグループの従業員のsal列とsal_new列のサンプル共分散を計算します。 例：select deptno, covar_samp(sal, sal_new) from emp group by deptno;次の応答が返されます。
+------------+------------+ | deptno | _c1 | +------------+------------+ | 10 | 2868666.6666666665 | | 20 | 1261875.0 | | 30 | 446666.6666666666 | +------------+------------+
ヒストグラム
構文
map<K, bigint> histogram(K input);説明
各入力値が表示される回数を含むマップを返します。 マップ内のキーは入力値です。 マップ内の各値は、入力値が表示される回数です。 null値は無視されます。
パラメーター
input: マップのキーとして使用される入力値。
戻り値
各入力値が表示される回数を含むマップが返されます。
例
select histogram(a) from values ('hi'), (null), ('apple'), ('pie'), ('apple') t(a);次の応答が返されます。
+----------------------------+ | _c0 | +----------------------------+ | {"pie":1,"hi":1,"apple":2} | +----------------------------+
MAP_AGG
構文
map<K, V> map_agg(K a, V b);説明
aとbを使用して作成されたマップを返します。 aはマップ内のキーです。 bはマップ内のキーの値です。 マップ内のキーがnullの場合、キーは無視されます。 キーフィールドの値が重複している場合は、いずれかの値がランダムに保持されます。
パラメーター
a: マップのキーとして使用される入力フィールド。
b: マップの値として使用される入力フィールド。
戻り値
新しいマップが返されます。
例
select map_agg(a, b) from values (1L, 'apple'), (2L, 'hi'), (null, 'good'), (1L, 'pie') t(a, b);次の応答が返されます。
+------------------------+ | _c0 | +------------------------+ | {"2":"hi","1":"apple"} | +------------------------+
MAP_UNION
構文
map<K, V> map_union(map<K, V> input);説明
すべての入力マップの和集合である新しいマップを返します。 複数の入力マップにキーが存在する場合、そのキーに対応する値の1つがランダムに保持されます。
パラメーター
input: 入力マップ。
戻り値
新しいマップが返されます。
例
select map_union(a) from values (map(1L, 'hi', 2L, 'apple', 3L, 'pie')), (map(1L, 'good', 4L, 'this')), (null) t(a);次の応答が返されます。
+-----------------------------------------------+ | _c0 | +-----------------------------------------------+ | {"4":"this","1":"good","2":"apple","3":"pie"} | +-----------------------------------------------+
MAP_UNION_SUM
構文
map<K, V> map_union_sum(map<K, V> input);説明
すべての入力マップの和集合である新しいマップを返します。 出力マップは、すべての入力マップの一致するキーの値を合計します。 キーに対応する値がNULLの場合、値は0に変換されます。
説明入力マップの値は、BIGINT、INT、SMALLINT、TINYINT、FLOAT、DOUBLE、またはDECIMALデータ型である必要があります。
パラメーター
input: 入力マップ。
戻り値
新しいマップが返されます。
説明新しいマップの値は、BIGINT、DOUBLE、またはDECIMAL型です。
例
select map_union_sum(a) from values (map('hi', 2L, 'apple', 3L, 'pie', 1L)), (map('apple', null, 'hi', 4L)), (null) t(a);次の応答が返されます。
+----------------------------+ | _c0 | +----------------------------+ | {"apple":3,"hi":6,"pie":1} | +----------------------------+
MAX
構文
max(<colname>)説明
列の最大値を返します。
パラメーター
colname: 必須です。 列の名前。BOOLEAN以外の任意のデータ型にすることができます。
戻り値
戻り値の型は、colnameパラメーターの型と同じです。 戻り値は、次のルールによって異なります。
colnameの値がnullの場合、この値を含む行は計算に使用されません。
colnameの値がBOOLEAN型の場合、その値は計算には使用されません。
例
例1: すべての従業員の最高給与 (sal) を計算します。 例：
select max(sal) from emp;次の応答が返されます。
+------------+ | _c0 | +------------+ | 5000 | +------------+例2:
GROUP BYでこの関数を使用して、すべての従業員を部門 (deptno) ごとにグループ化し、各部門の従業員の最高給与を計算します。 例：select deptno, max(sal) from emp group by deptno;次の応答が返されます。
+------------+------------+ | deptno | _c1 | +------------+------------+ | 10 | 5000 | | 20 | 3000 | | 30 | 2850 | +------------+------------+
MAX_BY
構文
max_by(<valueToReturn>,<valueToMaximize>)説明
説明MAX_BY関数は、ARG_MAX関数と同じ機能を提供します。 違いはパラメータの順序にあります。 MaxComputeでは、オープンソース構文との互換性を維持するためにMAX_BY関数が導入されています。
valueToMaximizeの値が含まれている行を検索し、行のvalueToReturnの値を返します。 この関数は、MaxCompute V2.0の追加関数です。
パラメーター
valueToMaximize: 必須です。 任意のデータ型の値。
valueToReturn: 必須です。 任意のデータ型の値。
戻り値
戻り値のデータ型は、valueToReturnパラメーターのデータ型と同じです。 複数の行がvalueToMaximizeの最大値を持つ場合、いずれかの行のvalueToReturnの値がランダムに返されます。 valueToMaximizeの値がnullの場合、この値を含む行は計算に使用されません。
例
例1: 給与が最も高い従業員の名前を返します。 例：
select max_by(ename,sal) from emp;次の応答が返されます。
+------------+ | _c0 | +------------+ | KING | +------------+例2:
GROUP BYでこの関数を使用して、deptno列に基づいてすべての従業員をグループ化し、各グループで最も給与の高い従業員の名前を返します。 例：select deptno, max_by(ename,sal) from emp group by deptno;次の応答が返されます。
+------------+------------+ | deptno | _c1 | +------------+------------+ | 10 | KING | | 20 | SCOTT | | 30 | BLAKE | +------------+------------+
MEDIAN
構文
double median(double <colname>) decimal median(decimal <colname>)説明
列の中央値を返します。
パラメーター
colname: 必須です。 列の名前。DOUBLE型またはDECIMAL型にすることができます。 入力値がSTRING型またはBIGINT型の場合、計算前に暗黙的にDOUBLE型に変換されます。
戻り値
colnameの値がnullの場合、この値を含む行は計算に使用されません。 入力データと戻り値のデータ型の対応関係を次の表に示します。
入力タイプ
戻り値タイプ
TINYINT
DOUBLE
SMALLINT
DOUBLE
INT
DOUBLE
BIGINT
DOUBLE
FLOAT
DOUBLE
DOUBLE
DOUBLE
DECIMAL
DECIMAL
例
例1: すべての従業員の給与 (sal) 値の中央値を計算します。 例：
select median(sal) from emp;次の応答が返されます。
+------------+ | _c0 | +------------+ | 1600.0 | +------------+例2:
GROUP BYでこの関数を使用して、すべての従業員を部門 (deptno) ごとにグループ化し、各部門の従業員の給与の中央値を計算します。 例：select deptno, median(sal) from emp group by deptno;次の応答が返されます。
+------------+------------+ | deptno | _c1 | +------------+------------+ | 10 | 2450.0 | | 20 | 2975.0 | | 30 | 1375.0 | +------------+------------+
MIN
構文
min(<colname>)説明
列の最小値を返します。
パラメーター
colname: 必須です。 列の名前。BOOLEAN以外の任意のデータ型にすることができます。
戻り値
戻り値の型は、colnameパラメーターの型と同じです。 戻り値は、次のルールによって異なります。
colnameの値がnullの場合、この値を含む行は計算に使用されません。
colnameの値がBOOLEAN型の場合、その値は計算には使用されません。
例
例1: すべての従業員の最低給与 (sal) を計算します。 例：
select min(sal) from emp;次の応答が返されます。
+------------+ | _c0 | +------------+ | 800 | +------------+例2:
GROUP BYでこの関数を使用して、すべての従業員を部門 (deptno) ごとにグループ化し、各部門の従業員の最低給与を計算します。 例：select deptno, min(sal) from emp group by deptno;次の応答が返されます。
+------------+------------+ | deptno | _c1 | +------------+------------+ | 10 | 1300 | | 20 | 800 | | 30 | 950 | +------------+------------+
MIN_BY
構文
min_by(<valueToReturn>,<valueToMinimize>)説明
説明MIN_BY関数は、ARG_MIN関数と同じ機能を提供します。 ただし、関数はパラメータの順序が異なります。 MIN_BY関数は、オープンソース構文との互換性を維持するためにMaxComputeに導入されました。
valueToMinimizeの最小値が含まれる行を検索し、行のvalueToReturnの値を返します。 この関数は、MaxCompute V2.0の追加関数です。
パラメーター
valueToMinimize: 必須です。 任意のデータ型の値。
valueToReturn: 必須です。 任意のデータ型の値。
戻り値
戻り値のデータ型は、valueToReturnパラメーターのデータ型と同じです。 複数の行にvalueToMinimizeの最小値が含まれている場合、いずれかの行のvalueToReturnの値がランダムに返されます。 valueToMinimizeの値がnullの場合、この値を含む行は計算に使用されません。
例
例1: 給与が最も低い従業員の名前を返します。 例：
select min_by(ename,sal) from emp;次の応答が返されます。
+------------+ | _c0 | +------------+ | SMITH | +------------+例2:
GROUP BYでこの関数を使用して、deptno列に基づいてすべての従業員をグループ化し、各グループで最も給与の低い従業員の名前を返します。 例：select deptno, min_by(ename,sal) from emp group by deptno;次の応答が返されます。
+------------+------------+ | deptno | _c1 | +------------+------------+ | 10 | MILLER | | 20 | SMITH | | 30 | JAMES | +------------+------------+
MULTIMAP_AGG
構文
map<K, array<V>> multimap_agg(K a, V b);説明
aとbを使用して作成されたマップを返します。 aはマップ内のキーです。 bは配列を作成するために使用され、これはマップのキーの値として使用されます。 マップ内のキーがnullの場合、キーは無視されます。
パラメーター
a: マップのキーとして使用される入力フィールド。
b: マップの値として使用される入力フィールド。 同じキーに対応するフィールドは同じ配列に配置され、マップの値として使用されます。
戻り値
新しいマップが返されます。
例
select multimap_agg(a, b) from values (1L, 'apple'), (2L, 'hi'), (null, 'good'), (1L, 'pie') t(a, b);次の応答が返されます。
+----------------------------------+ | _c0 | +----------------------------------+ | {"2":["hi"],"1":["apple","pie"]} | +----------------------------------+
NUMERIC_HISTOGRAM
構文
map<double key, double value> numeric_histogram(bigint <buckets>, double <colname> [, double <weight>])説明
指定した列に基づく近似ヒストグラムを返します。 この関数は、MaxCompute V2.0の追加関数です。
パラメーター
buckets: 必須です。 BIGINT型の値。 このパラメーターは、近似ヒストグラムが返される列のバケットの最大数を指定します。
colname: 必須です。 DOUBLE型の値。 このパラメータは、近似ヒストグラムを計算する必要がある列を指定します。
weight: オプション。 各行のデータの重み値。 値はDOUBLE型です。
戻り値
map<double key, double value>型の値が返されます。 戻り値では、keyは近似ヒストグラムのX軸を示し、valueは近似ヒストグラムのY軸のおおよその高さを示します。 戻り値は、次のルールによって異なります。bucketsの値がnullの場合、nullが返されます。
colnameの値がnullの場合、この値を含む行は計算に使用されません。
例
sal列のおおよそのヒストグラムを返します。 例：
select numeric_histogram(5, sal) from emp;次の応答が返されます。
+------------+ | _c0 | +------------+ | {"1328.5714285714287":7.0,"2450.0":2.0,"5000.0":2.0,"875.0":2.0,"2956.25":4.0} | +------------+sal列のおおよそのヒストグラムを返します。 データの各行の
deptnoは部門の重みを示します。 例：select numeric_histogram(5, sal, deptno) from emp;次の応答が返されます。
+------------+ | _c0 | +------------+ | {"2944.4444444444443":90.0,"2450.0":20.0,"5000.0":20.0,"890.0":50.0,"1350.0":160.0} | +------------+
PERCENTILE
構文
double percentile(bigint <colname>, <p>) -- Return multiple exact percentiles as an array. array percentile(bigint <colname>, array(<p1> [, <p2>...]))説明
正確なパーセンタイルを計算します。 この関数は、少量のデータが計算されるシナリオに適しています。 この関数は、指定された列のデータを昇順でソートし、列のp × 100パーセンタイルを返します。 pは0から1までの値でなければなりません。
パーセンタイルに指定された列の値は0から番号が付けられます。 たとえば、列の値は100、200、および300であり、値には0、1、および2の番号が付けられています。 この関数を使用して列の30パーセンタイルを計算すると、パーセンタイルの値は0.6になります。これは、次の式を使用して計算されます。2x0.3 = 0.6。 値はシーケンス番号0と1の間です。 結果は、100 + (200 − 100) × 0.6 = 160の式を用いて計算される。 この関数は、MaxCompute V2.0の追加関数です。パラメーター
colname: 必須です。 BIGINT型の列。
p: 必須です。 パーセンタイル。 有効な値:
[0.0,1.0]
戻り値
DOUBLEまたはARRAY型の値が返されます。
例
例1: sal列の30パーセンタイルを計算します。 例：
select percentile(sal, 0.3) from emp;次の応答が返されます。
+------------+ | _c0 | +------------+ | 1290.0 | +------------+例2:
GROUP BYでこの関数を使用して、deptno列に基づいてすべての従業員をグループ化し、sal列の各グループの従業員の30パーセンタイルを計算します。 例：select deptno, percentile(sal, 0.3) from emp group by deptno;次の応答が返されます。
+------------+------------+ | deptno | _c1 | +------------+------------+ | 10 | 1875.0 | | 20 | 1475.0 | | 30 | 1250.0 | +------------+------------+例3:
GROUP BYでこの関数を使用して、deptno列に基づいてすべての従業員をグループ化し、sal列の各グループの従業員の30、50、および80パーセンタイルを計算します。 例：set odps.sql.type.system.odps2=true; select deptno, percentile(sal, array(0.3, 0.5, 0.8)) from emp group by deptno;次の応答が返されます。
+------------+------------+ | deptno | _c1 | +------------+------------+ | 10 | [1875.0,2450.0,5000.0] | | 20 | [1475.0,2975.0,3000.0] | | 30 | [1250.0,1375.0,1600.0] | +------------+------------+
PERCENTILE_APPROX
構文
double percentile_approx (double <colname>[, double <weight>], <p> [, <B>])) -- Returns multiple approximate percentiles as an array. array<double> percentile_approx (double <colname> [, double <weight>], array(<p1> [, <p2>...]) [, <B>])説明
この関数は、MaxCompute V2.0の追加関数です。
percentile_approxに指定された列の値は、1から番号が付けられます。 例えば、n行のデータを含む列のp × 100パーセンタイルが計算される。PERCENTILE_APPROX関数は、最初に列の値を昇順でソートしてから、必要なパーセンタイルを計算します。 このセクションでは、計算ロジックの理解を深めるために、arrを使用して列内のソートされた値の配列を表し、resを使用して関数の戻り値を表します。 resの算出式は、index= n × pの式を用いて算出されるindexに基づいて変化する。index ≤ 1の条件が満たされる場合、resは次の式に基づいて計算されます。res = arr[0]。index >= n − 1の条件が満たされる場合、resは、以下の式に基づいて計算される:res = arr[n-1]。1 < index < n − 1の条件が満たされる場合、diffは、以下の式:diff = index + 0.5 − ceil(index)に基づいて最初に計算される。abs(diff) < 0.5条件が満たされる場合、resは、以下の式に基づいて計算される: res = arr[ceil(index) − 1] 。
abs(diff) = 0.5条件が満たされる場合、resは、以下の式: res = arr[index − 1] + (arr[index] − arr[index − 1]) × 0.5に基づいて計算される。
abs(diff) の値は0.5より大きくすることはできません。
例えば、
col列は、100、200、300、400の値を含み、これらの値のシーケンス番号は、1、2、3、4である。 この列の25番目、50番目、および75番目のパーセンタイルは、次の式に基づいて計算されます。percentile_approx(col, 0.25) = 100(index = 1)パーセンタイル_約 (col、0.5) = 200 + (300 - 200) × 0.5 = 250(インデックス=2)percentile_approx(col, 0.75) = 400(index = 3)
説明PERCENTILE_APPROXとの違いPERCENTILE:PERCENTILE_APPROXは近似パーセンタイルを計算するために使用され、PERCENTILEは正確なパーセンタイルを計算するために使用される。 データ量が多い場合、メモリの制限によりPERCENTILEの実行に失敗することがありますが、PERCENTILE_APPROXにはこの問題はありません。PERCENTILE_APPROXの実装は、HiveのPERCENTILE_APPROXの実装と一致していますが、percentile_APPROXの計算アルゴリズムはパーセンタイルの計算アルゴリズムとは異なります。 データ量が少ない場合、PERCENTILE_APPROXの実行結果はPERCENTILEの実行結果と異なります。
パラメーター
colname: 必須です。 列の名前。DOUBLE型にすることができます。
weight: オプション。 各行のデータの重み値。 値はDOUBLE型です。
p: 必須です。 おおよそのパーセンタイル。 有効な値:
[0.0,1.0]B: 戻り値の精度。 より高い精度は、より正確な値を示す。 このパラメーターを指定しない場合は、10000が使用されます。
戻り値
DOUBLEまたはARRAY型の値が返されます。 戻り値は、次のルールによって異なります。
colnameの値がnullの場合、この値を含む行は計算に使用されません。
pまたはBの値がnullの場合、エラーが返されます。
例
例1: sal列の30パーセンタイルを計算します。 例：
select percentile_approx(sal, 0.3) from emp;次の応答が返されます。
+------------+ | _c0 | +------------+ | 1252.5 | +------------+例2:
GROUP BYでこの関数を使用して、deptno列に基づいてすべての従業員をグループ化し、sal列の各グループの従業員の30パーセンタイルを計算します。 例：select deptno, percentile_approx(sal, 0.3) from emp group by deptno;次の応答が返されます。
+------------+------------+ | deptno | _c1 | +------------+------------+ | 10 | 1300.0 | | 20 | 950.0 | | 30 | 1070.0 | +------------+------------+例3:
GROUP BYでこの関数を使用して、deptno列に基づいてすべての従業員をグループ化し、sal列の各グループの従業員の30、50、および80パーセンタイルを計算します。 例：set odps.sql.type.system.odps2=true; select deptno, percentile_approx(sal, array(0.3, 0.5, 0.8), 1000) from emp group by deptno;次の応答が返されます。
+------------+------------+ | deptno | _c1 | +------------+------------+ | 10 | [1300.0,1875.0,3470.000000000001] | | 20 | [950.0,2037.5,2987.5] | | 30 | [1070.0,1250.0,1580.0] | +------------+------------+例4 (重みを指定した例):
GROUP BYでこの関数を使用して、deptno列に基づいてすべての従業員をグループ化し、sal列の各グループの従業員の30、50、および80パーセンタイルを計算します。empテーブルのcnt列の値は、給与が指定されたパーセンタイルにある従業員の数を示します。 例：select deptno, percentile_approx(sal, deptno, array(0.3, 0.5, 0.8), 1000) from emp group by deptno;次の応答が返されます。
+------------+------------+ | deptno | _c1 | +------------+------------+ | 10 | [1300.0,1875.0,3470.0] | | 20 | [950.0,2037.5,2987.5] | | 30 | [1070.0,1250.0,1580.0] | +------------+------------+
STDDEV
構文
double stddev(double <colname>) decimal stddev(decimal <colname>)説明
すべての入力値の母標準偏差を返します。
パラメーター
colname: 必須です。 列の名前。DOUBLE型またはDECIMAL型にすることができます。 入力値がSTRING型またはBIGINT型の場合、計算前に暗黙的にDOUBLE型に変換されます。
戻り値
colnameの値がnullの場合、この値を含む行は計算に使用されません。 入力データと戻り値のデータ型の対応関係を次の表に示します。
入力タイプ
戻り値タイプ
TINYINT
DOUBLE
SMALLINT
DOUBLE
INT
DOUBLE
BIGINT
DOUBLE
FLOAT
DOUBLE
DOUBLE
DOUBLE
DECIMAL
DECIMAL
例
例1: 全従業員の給与 (sal) 値の人口標準偏差を計算します。 例：
select stddev(sal) from emp;次の応答が返されます。
+------------+ | _c0 | +------------+ | 1262.7549932628976 | +------------+例2:
GROUP BYでこの関数を使用して、すべての従業員を部門別にグループ化し (deptno) 、各部門の従業員の給与値の人口標準偏差 (sal) を計算します。 例：select deptno, stddev(sal) from emp group by deptno;次の応答が返されます。
+------------+------------+ | deptno | _c1 | +------------+------------+ | 10 | 1546.1421524412158 | | 20 | 1004.7387720198718 | | 30 | 610.1001739241043 | +------------+------------+
STDDEV_SAMP
構文
double stddev_samp(double <colname>) decimal stddev_samp(decimal <colname>)説明
すべての入力値のサンプル標準偏差を返します。
パラメーター
colname: 必須です。 列の名前。DOUBLE型またはDECIMAL型にすることができます。 入力値がSTRING型またはBIGINT型の場合、計算前に暗黙的にDOUBLE型に変換されます。
戻り値
colnameの値がnullの場合、この値を含む行は計算に使用されません。 入力データと戻り値のデータ型の対応関係を次の表に示します。
入力タイプ
戻り値タイプ
TINYINT
DOUBLE
SMALLINT
DOUBLE
INT
DOUBLE
BIGINT
DOUBLE
FLOAT
DOUBLE
DOUBLE
DOUBLE
DECIMAL
DECIMAL
例
例1: すべての従業員の給与 (sal) 値のサンプル標準偏差を計算します。 例：
select stddev_samp(sal) from emp;次の応答が返されます。
+------------+ | _c0 | +------------+ | 1301.6180541247609 | +------------+例2:
GROUP BYでこの関数を使用して、すべての従業員を部門 (deptno) ごとにグループ化し、各部門の従業員の給与値のサンプル標準偏差を計算します。 例：select deptno, stddev_samp(sal) from emp group by deptno;次の応答が返されます。
+------------+------------+ | deptno | _c1 | +------------+------------+ | 10 | 1693.7138680032901 | | 20 | 1123.3320969330487 | | 30 | 668.3312551921141 | +------------+------------+
SUM
構文
DECIMAL|DOUBLE|BIGINT sum(<colname>)説明
列の合計を返します。
パラメーター
colname: 必須です。 列値はすべてのデータ型をサポートし、計算前にDOUBLE型に変換できます。 列の名前。DOUBLE、DECIMAL、またはBIGINT型にすることができます。 入力値がSTRING型の場合、計算前に暗黙的にDOUBLE型に変換されます。
戻り値
colnameの値がnullの場合、この値を含む行は計算に使用されません。 入力データと戻り値のデータ型の対応関係を次の表に示します。
入力タイプ
戻り値タイプ
TINYINT
BIGINT
SMALLINT
BIGINT
INT
BIGINT
BIGINT
BIGINT
FLOAT
DOUBLE
DOUBLE
DOUBLE
DECIMAL
DECIMAL
例
例1: すべての従業員の給与 (sal) 値の合計を計算します。 例：
select sum(sal) from emp;次の応答が返されます。
+------------+ | _c0 | +------------+ | 37775 | +------------+例2:
GROUP BYでこの関数を使用して、すべての従業員を部門 (deptno) ごとにグループ化し、各部門の従業員の給与値の合計を計算します。 例：select deptno, sum(sal) from emp group by deptno;次の応答が返されます。
+------------+------------+ | deptno | _c1 | +------------+------------+ | 10 | 17500 | | 20 | 10875 | | 30 | 9400 | +------------+------------+
VAR_SAMP
構文
double var_samp(<colname>)説明
指定した数値列のサンプル分散を計算します。 この関数は、MaxCompute V2.0の追加関数です。
パラメーター
colname: 必須です。 数値データ型の列。 指定された列が数値列でない場合、null値が返されます。
戻り値
DOUBLE型の値が返されます。
例
例1: すべての従業員の給与値 (sal) のサンプル分散を計算します。 例：
select var_samp(sal) from emp;次の応答が返されます。
+------------+ | _c0 | +------------+ | 1694209.5588235292 | +------------+例2:
GROUP BYでこの関数を使用して、すべての従業員を部門 (deptno) ごとにグループ化し、同じグループの従業員の給与値 (sal) のサンプル分散を計算します。 例：select deptno, var_samp(sal) from emp group by deptno;次の応答が返されます。
+------------+------------+ | deptno | _c1 | +------------+------------+ | 10 | 2868666.666666667 | | 20 | 1261875.0 | | 30 | 446666.6666666667 | +------------+------------+
VARIANCE/VAR_POP
構文
double variance(<colname>) double var_pop(<colname>)説明
指定した数値列の分散を計算します。
パラメーター
colname: 必須です。 数値データ型の列。 指定された列が数値列でない場合、null値が返されます。 この関数は、MaxCompute V2.0の追加関数です。
戻り値
DOUBLE型の値が返されます。
例
例1: すべての従業員の給与値 (sal) の分散を計算します。 例：
select variance(sal) from emp; -- The preceding statement is equivalent to the following statement: select var_pop(sal) from emp;次の応答が返されます。
+------------+ | _c0 | +------------+ | 1594550.1730103805 | +------------+例2:
GROUP BYでこの関数を使用して、すべての従業員を部門別にグループ化し (deptno) 、同じグループの従業員の給与値の分散 (sal) を計算します。 例：select deptno, variance(sal) from emp group by deptno; -- The preceding statement is equivalent to the following statement: select deptno, var_pop(sal) from emp group by deptno;次の応答が返されます。
+------------+------------+ | deptno | _c1 | +------------+------------+ | 10 | 2390555.5555555555 | | 20 | 1009500.0 | | 30 | 372222.22222222225 | +------------+------------+
WM_CONCAT
構文
string wm_concat(string <separator>, string <colname>)説明
separatorで指定された区切り文字を使用して、colnameの値を連結します。
パラメーター
separator: 必須。 区切り文字。STRING型の定数です。
colname: 必須です。 STRING型の値。 入力値がBIGINT、DOUBLE、またはDATETIME型の場合、値は計算前に暗黙的にSTRING型に変換されます。
戻り値 (
GROUP BY句が指定され, ORDER BY句は指定されません)STRING型の値が返されます。 戻り値は、次のルールによって異なります。
separatorの値がSTRING型の定数でない場合、エラーが返されます。
colnameの値がSTRING、BIGINT、DOUBLE、またはDATETIME型でない場合、エラーが返されます。
colnameの値がnullの場合、この値を含む行は計算に使用されません。
説明select wm_concat(',', name) from table_name;ステートメントのtable_nameの値が空のセットの場合、nullが返されます。例
例1: すべての従業員の名前 (ename) を連結します。 例：
select wm_concat(',', ename) from emp;次の応答が返されます。
+------------+ | _c0 | +------------+ | SMITH,ALLEN,WARD,JONES,MARTIN,BLAKE,CLARK,SCOTT,KING,TURNER,ADAMS,JAMES,FORD,MILLER,JACCKA,WELAN,TEBAGE | +------------+例2:
GROUP BYでこの関数を使用して、すべての従業員を部署 (deptno) ごとにグループ化し、各部署の従業員の名前 (ename) を連結します。 例：select deptno, wm_concat(',', ename) from emp group by deptno order by deptno;次の応答が返されます。
+------------+------------+ | deptno | _c1 | +------------+------------+ | 10 | CLARK,KING,MILLER,JACCKA,WELAN,TEBAGE | | 20 | SMITH,JONES,SCOTT,ADAMS,FORD | | 30 | ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES | +------------+------------+例3:
GROUP BYでこの関数を使用して、すべての従業員を部門 (deptno) ごとにグループ化し、重複を削除した後に各部門の従業員の給与値を連結します。 例：select deptno, wm_concat(distinct ',', sal) from emp group by deptno order by deptno;次の応答が返されます。
+------------+------------+ | deptno | _c1 | +------------+------------+ | 10 | 1300,2450,5000 | | 20 | 1100,2975,3000,800 | | 30 | 1250,1500,1600,2850,950 | +------------+------------+例4:
GROUP BYおよびORDER BYでこの関数を使用して、すべての従業員を部門別にグループ化し (deptno) 、各部門のすべての従業員の給与値 (sal) を連結し、指定した順序で給与値 (sal) をソートします。 例：select deptno, wm_concat(',',sal) within group(order by sal) from emp group by deptno order by deptno;次の応答が返されます。
+------------+------------+ |deptno|_c1| +------------+------------+ |10|1300,1300,2450,2450,5000,5000| |20|800,1100,2975,3000,3000| |30|950,1250,1250,1500,1600,2850| +------------+------------+