集約関数

入力値のセットから 単一 の結果を計算するための関数です。Elasticsearch SQLは、グループ化（暗黙的または明示的）とともにのみ集約関数をサポートします。

一般的な目的

AVG

概要:

Sql

  1. AVG(numeric_field)

入力:

数値フィールド。このフィールドにnull値のみが含まれている場合、関数はnullを返します。そうでない場合、関数はこのフィールドのnull値を無視します。

出力: double 数値

説明: 入力値の平均（算術平均）を返します。

Sql

  1. SELECT AVG(salary) AS avg FROM emp;
  3.    avg
  4. ---------------
  5. 48248.55

Sql

  1. SELECT AVG(salary / 12.0) AS avg FROM emp;
  3.    avg
  4. ---------------
  5. 4020.7125

COUNT

概要:

Sql

  1. COUNT(expression)

入力:

フィールド名、ワイルドカード（*）または任意の数値。COUNT(*)またはCOUNT(<literal>)の場合、すべての値が考慮され、nullまたは欠損値も含まれます。COUNT(<field_name>)の場合、null値は考慮されません。

出力: 数値

説明: 入力値の合計数（カウント）を返します。

Sql

  1. SELECT COUNT(*) AS count FROM emp;
  3.    count
  4. ---------------
  5. 100

COUNT(ALL)

概要:

Sql

  1. COUNT(ALL field_name)

入力:

フィールド名。このフィールドにnull値のみが含まれている場合、関数はnullを返します。そうでない場合、関数はこのフィールドのnull値を無視します。

出力: 数値

説明: すべての非NULL入力値の合計数（カウント）を返します。COUNT(<field_name>)とCOUNT(ALL <field_name>)は同等です。

Sql

  1. SELECT COUNT(ALL last_name) AS count_all, COUNT(DISTINCT last_name) count_distinct FROM emp;
  3.    count_all   |  count_distinct
  4. ---------------+------------------
  5. 100            |96

Sql

  1. SELECT COUNT(ALL CASE WHEN languages IS NULL THEN -1 ELSE languages END) AS count_all, COUNT(DISTINCT CASE WHEN languages IS NULL THEN -1 ELSE languages END) count_distinct FROM emp;
  3.    count_all   |  count_distinct
  4. ---------------+---------------
  5. 100            |6

COUNT(DISTINCT)

概要:

Sql

  1. COUNT(DISTINCT field_name)

入力:

フィールド名

出力: 数値。このフィールドにnull値のみが含まれている場合、関数はnullを返します。そうでない場合、関数はこのフィールドのnull値を無視します。

説明: 入力値の異なる非NULL値の合計数を返します。

Sql

  1. SELECT COUNT(DISTINCT hire_date) unique_hires, COUNT(hire_date) AS hires FROM emp;
  3.   unique_hires  |     hires
  4. ----------------+---------------
  5. 99              |100

Sql

  1. SELECT COUNT(DISTINCT DATE_TRUNC('YEAR', hire_date)) unique_hires, COUNT(DATE_TRUNC('YEAR', hire_date)) AS hires FROM emp;
  3.  unique_hires  |     hires
  4. ---------------+---------------
  5. 14             |100

FIRST/FIRST_VALUE

概要:

Sql

  1. FIRST(
  2.    field_name
  3.    [, ordering_field_name])

入力:

集約の対象フィールド
並べ替えに使用されるオプションのフィールド

出力: 入力と同じ型

説明: ordering_field_name列でソートされたfield_name入力列の最初の非null値（存在する場合）を返します。ordering_field_nameが提供されていない場合、field_name列のみがソートに使用されます。例:


| a | b |
| :— | :— |
| 100 | 1 |
| 200 | 1 |
| 1 | 2 |
| 2 | 2 |
| 10 | null |
| 20 | null |
| null | null |

Sql

  1. SELECT FIRST(a) FROM t

結果は次のようになります:

FIRST(a)
1

そして

Sql

  1. SELECT FIRST(a, b) FROM t

結果は次のようになります:

FIRST(a, b)
100

Sql

  1. SELECT FIRST(first_name) FROM emp;
  3.    FIRST(first_name)
  4. --------------------
  5. Alejandro

Sql

  1. SELECT gender, FIRST(first_name) FROM emp GROUP BY gender ORDER BY gender;
  3.    gender   |   FIRST(first_name)
  4. ------------+--------------------
  5. null        |   Berni
  6. F           |   Alejandro
  7. M           |   Amabile

Sql

  1. SELECT FIRST(first_name, birth_date) FROM emp;
  3.    FIRST(first_name, birth_date)
  4. --------------------------------
  5. Remzi

Sql

  1. SELECT gender, FIRST(first_name, birth_date) FROM emp GROUP BY gender ORDER BY gender;
  3.    gender    |   FIRST(first_name, birth_date)
  4. --------------+--------------------------------
  5. null          |   Lillian
  6. F             |   Sumant
  7. M             |   Remzi
  3. #### Sql
  5. ``````sql
  6. SELECT gender, FIRST_VALUE(first_name, birth_date) FROM emp GROUP BY gender ORDER BY gender;
  8.    gender    |   FIRST_VALUE(first_name, birth_date)
  9. --------------+--------------------------------------
  10. null          |   Lillian
  11. F             |   Sumant
  12. M             |   Remzi
  13. `

Sql

  1. SELECT gender, FIRST_VALUE(SUBSTRING(first_name, 2, 6), birth_date) AS "first" FROM emp GROUP BY gender ORDER BY gender;
  3.    gender     |     first
  4. ---------------+---------------
  5. null           |illian
  6. F              |umant
  7. M              |emzi
  2. `````FIRST`````は、フィールドが[キーワードとして保存されている](a85566ae42da41c4.md#before-enabling-fielddata)場合を除き、[`````text`````](/read/elasticsearch-8-15/a85566ae42da41c4.md)型の列で使用できません。
  5. ## LAST/LAST_VALUE
  7. **概要:**
  10. #### Sql
  12. ``````sql
  13. LAST(
  14.    field_name
  15.    [, ordering_field_name])
  16. `

入力:

集約の対象フィールド
並べ替えに使用されるオプションのフィールド

出力: 入力と同じ型

説明: FIRST/FIRST_VALUEの逆です。field_name入力列の最後の非null値（存在する場合）をordering_field_name列で降順にソートして返します。ordering_field_nameが提供されていない場合、field_name列のみがソートに使用されます。例:


| a | b |
| :— | :— |
| 10 | 1 |
| 20 | 1 |
| 1 | 2 |
| 2 | 2 |
| 100 | null |
| 200 | null |
| null | null |

Sql

  1. SELECT LAST(a) FROM t

結果は次のようになります:

LAST(a)
200

そして

Sql

  1. SELECT LAST(a, b) FROM t

結果は次のようになります:

LAST(a, b)
2

Sql

  1. SELECT LAST(first_name) FROM emp;
  3.    LAST(first_name)
  4. -------------------
  5. Zvonko

Sql

  1. SELECT gender, LAST(first_name) FROM emp GROUP BY gender ORDER BY gender;
  3.    gender   |   LAST(first_name)
  4. ------------+-------------------
  5. null        |   Patricio
  6. F           |   Xinglin
  7. M           |   Zvonko

Sql

  1. SELECT LAST(first_name, birth_date) FROM emp;
  3.    LAST(first_name, birth_date)
  4. -------------------------------
  5. Hilari

Sql

  1. SELECT gender, LAST(first_name, birth_date) FROM emp GROUP BY gender ORDER BY gender;
  3.    gender  |   LAST(first_name, birth_date)
  4. -----------+-------------------------------
  5. null       |   Eberhardt
  6. F          |   Valdiodio
  7. M          |   Hilari
  3. #### Sql
  5. ``````sql
  6. SELECT gender, LAST_VALUE(first_name, birth_date) FROM emp GROUP BY gender ORDER BY gender;
  8.    gender  |   LAST_VALUE(first_name, birth_date)
  9. -----------+-------------------------------------
  10. null       |   Eberhardt
  11. F          |   Valdiodio
  12. M          |   Hilari
  13. `

Sql

  1. SELECT gender, LAST_VALUE(SUBSTRING(first_name, 3, 8), birth_date) AS "last" FROM emp GROUP BY gender ORDER BY gender;
  3.    gender     |     last
  4. ---------------+---------------
  5. null           |erhardt
  6. F              |ldiodio
  7. M              |lari
  2. `````LAST`````は、フィールドが[`````text`````](/read/elasticsearch-8-15/a85566ae42da41c4.md)型の場合を除き、`````saved as a keyword`````句で使用できません。
  5. ## MAX
  7. **概要:**
  10. #### Sql
  12. ``````sql
  13. MAX(field_name)
  14. `

入力:

数値フィールド。このフィールドにnull値のみが含まれている場合、関数はnullを返します。そうでない場合、関数はこのフィールドのnull値を無視します。

出力: 入力と同じ型

説明: フィールドfield_nameの入力値の中で最大の値を返します。

Sql

  1. SELECT MAX(salary) AS max FROM emp;
  3.    max
  4. ---------------
  5. 74999

Sql

  1. SELECT MAX(ABS(salary / -12.0)) AS max FROM emp;
  3.    max
  4. -----------------
  5. 6249.916666666667
  3. ## MIN
  5. **概要:**
  8. #### Sql
  10. ``````sql
  11. MIN(field_name)
  12. `

入力:

数値フィールド。このフィールドにnull値のみが含まれている場合、関数はnullを返します。そうでない場合、関数はこのフィールドのnull値を無視します。

出力: 入力と同じ型

説明: フィールドfield_nameの入力値の中で最小の値を返します。

Sql

  1. SELECT MIN(salary) AS min FROM emp;
  3.    min
  4. ---------------
  5. 25324
  3. ## SUM
  5. **概要:**
  8. #### Sql
  10. ``````sql
  11. SUM(field_name)
  12. `

入力:

数値フィールド。このフィールドにnull値のみが含まれている場合、関数はnullを返します。そうでない場合、関数はこのフィールドのnull値を無視します。

出力: 整数入力の場合はbigint、浮動小数点の場合はdouble

説明: フィールドfield_nameの入力値の合計を返します。

Sql

  1. SELECT SUM(salary) AS sum FROM emp;
  3.    sum
  4. ---------------
  5. 4824855

Sql

  1. SELECT ROUND(SUM(salary / 12.0), 1) AS sum FROM emp;
  3.    sum
  4. ---------------
  5. 402071.3

統計

KURTOSIS

概要:

Sql

  1. KURTOSIS(field_name)

入力:

数値フィールド。このフィールドにnull値のみが含まれている場合、関数はnullを返します。そうでない場合、関数はこのフィールドのnull値を無視します。

出力: double 数値

説明:

フィールドfield_nameの入力値の分布の形状を定量化します。

Sql

  1. SELECT MIN(salary) AS min, MAX(salary) AS max, KURTOSIS(salary) AS k FROM emp;
  3.    min      |      max      |        k
  4. ---------------+---------------+------------------
  5. 25324          |74999          |2.0444718929142986
  3. #### Sql
  5. ``````sql
  6. SELECT KURTOSIS(salary / 12.0), gender FROM emp GROUP BY gender
  7. `

MAD

概要:

Sql

  1. MAD(field_name)

入力:

数値フィールド。このフィールドにnull値のみが含まれている場合、関数はnullを返します。そうでない場合、関数はこのフィールドのnull値を無視します。

出力: double 数値

説明:

フィールドfield_nameの入力値の変動性を測定します。

Sql

  1. SELECT MIN(salary) AS min, MAX(salary) AS max, AVG(salary) AS avg, MAD(salary) AS mad FROM emp;
  3.    min      |      max      |      avg      |      mad
  4. ---------------+---------------+---------------+---------------
  5. 25324          |74999          |48248.55       |10096.5

Sql

  1. SELECT MIN(salary / 12.0) AS min, MAX(salary / 12.0) AS max, AVG(salary/ 12.0) AS avg, MAD(salary / 12.0) AS mad FROM emp;
  3.    min        |       max       |      avg      |       mad
  4. ------------------+-----------------+---------------+-----------------
  5. 2110.3333333333335|6249.916666666667|4020.7125      |841.3750000000002

PERCENTILE

概要:

Sql

  1. PERCENTILE(
  2.    field_name,
  3.    percentile[,
  4.    method[,
  5.    method_parameter]])

入力:

数値フィールド。このフィールドにnull値のみが含まれている場合、関数はnullを返します。そうでない場合、関数はこのフィールドのnull値を無視します。
数値式（定数であり、フィールドに基づいてはならない）。もしnullの場合、関数はnullを返します。
パーセンタイルアルゴリズムのためのオプションの文字列リテラル。可能な値: tdigestまたはhdr。デフォルトはtdigestです。
パーセンタイルアルゴリズムを構成するオプションの数値リテラル。compressionのためのtdigestまたはnumber_of_significant_value_digitsのためのhdrを構成します。デフォルトはバックエンドアルゴリズムと同じです。

出力: double 数値

説明:

フィールドfield_nameの入力値のnth パーセンタイル（numeric_expパラメータで表される）を返します。

Sql

  1. SELECT languages, PERCENTILE(salary, 95) AS "95th" FROM emp
  2.    GROUP BY languages;
  4.    languages   |      95th
  5. ---------------+-----------------
  6. null           |74482.4
  7. 1              |71122.8
  8. 2              |70271.4
  9. 3              |71926.0
  10. 4              |69352.15
  11. 5              |56371.0

Sql

  1. SELECT languages, PERCENTILE(salary / 12.0, 95) AS "95th" FROM emp
  2.    GROUP BY languages;
  4.    languages   |       95th
  5. ---------------+------------------
  6. null           |6206.866666666667
  7. 1              |5926.9
  8. 2              |5855.949999999999
  9. 3              |5993.833333333333
  10. 4              |5779.345833333333
  11. 5              |4697.583333333333

Sql

  1. SELECT
  2.    languages,
  3.    PERCENTILE(salary, 97.3, 'tdigest', 100.0) AS "97.3_TDigest",
  4.    PERCENTILE(salary, 97.3, 'hdr', 3) AS "97.3_HDR"
  5. FROM emp
  6. GROUP BY languages;
  8.    languages   | 97.3_TDigest    |   97.3_HDR
  9. ---------------+-----------------+---------------
  10. null           |74720.036        |74992.0
  11. 1              |72316.132        |73712.0
  12. 2              |71792.436        |69936.0
  13. 3              |73326.23999999999|74992.0
  14. 4              |71753.281        |74608.0
  15. 5              |61176.16000000001|56368.0

PERCENTILE_RANK

概要:

Sql

  1. PERCENTILE_RANK(
  2.    field_name,
  3.    value[,
  4.    method[,
  5.    method_parameter]])

入力:

数値フィールド。このフィールドにnull値のみが含まれている場合、関数はnullを返します。そうでない場合、関数はこのフィールドのnull値を無視します。
数値式（定数であり、フィールドに基づいてはならない）。もしnullの場合、関数はnullを返します。
パーセンタイルアルゴリズムのためのオプションの文字列リテラル。可能な値: tdigestまたはhdr。デフォルトはtdigestです。
パーセンタイルアルゴリズムを構成するオプションの数値リテラル。compressionのためのtdigestまたはnumber_of_significant_value_digitsのためのhdrを構成します。デフォルトはバックエンドアルゴリズムと同じです。

出力: double 数値

説明:

フィールドfield_nameの入力値のnth パーセンタイルランク（numeric_expパラメータで表される）を返します。

Sql

  1. SELECT languages, PERCENTILE_RANK(salary, 65000) AS rank FROM emp GROUP BY languages;
  3.    languages   |      rank
  4. ---------------+-----------------
  5. null           |73.65766569962062
  6. 1              |73.7291625157734
  7. 2              |88.88005607010643
  8. 3              |79.43662623295829
  9. 4              |85.70446389643493
  10. 5              |96.79075152940749

Sql

  1. SELECT languages, PERCENTILE_RANK(salary/12, 5000) AS rank FROM emp GROUP BY languages;
  3.    languages   |       rank
  4. ---------------+------------------
  5. null           |66.91240875912409
  6. 1              |66.70766707667076
  7. 2              |84.13266895048271
  8. 3              |61.052992625621684
  9. 4              |76.55646443990001
  10. 5              |94.00696864111498

Sql

  1. SELECT
  2.    languages,
  3.    ROUND(PERCENTILE_RANK(salary, 65000, 'tdigest', 100.0), 2) AS "rank_TDigest",
  4.    ROUND(PERCENTILE_RANK(salary, 65000, 'hdr', 3), 2) AS "rank_HDR"
  5. FROM emp
  6. GROUP BY languages;
  8.    languages   | rank_TDigest  |   rank_HDR
  9. ---------------+---------------+---------------
  10. null           |73.66          |80.0
  11. 1              |73.73          |73.33
  12. 2              |88.88          |89.47
  13. 3              |79.44          |76.47
  14. 4              |85.7           |83.33
  15. 5              |96.79          |95.24

SKEWNESS

概要:

Sql

  1. SKEWNESS(field_name)

入力:

数値フィールド。このフィールドにnull値のみが含まれている場合、関数はnullを返します。そうでない場合、関数はこのフィールドのnull値を無視します。

出力: double 数値

説明:

フィールドfield_nameの入力値の非対称分布を定量化します。

Sql

  1. SELECT MIN(salary) AS min, MAX(salary) AS max, SKEWNESS(salary) AS s FROM emp;
  3.    min      |      max      |        s
  4. ---------------+---------------+------------------
  5. 25324          |74999          |0.2707722118423227
  3. #### Sql
  5. ``````sql
  6. SELECT SKEWNESS(ROUND(salary / 12.0, 2), gender FROM emp GROUP BY gender
  7. `

STDDEV_POP

概要:

Sql

  1. STDDEV_POP(field_name)

入力:

数値フィールド。このフィールドにnull値のみが含まれている場合、関数はnullを返します。そうでない場合、関数はこのフィールドのnull値を無視します。

出力: double 数値

説明:

フィールドfield_nameの入力値の母集団標準偏差を返します。

Sql

  1. SELECT MIN(salary) AS min, MAX(salary) AS max, STDDEV_POP(salary) AS stddev FROM emp;
  3.    min      |      max      |      stddev
  4. ---------------+---------------+------------------
  5. 25324          |74999          |13765.125502787832

Sql

  1. SELECT MIN(salary / 12.0) AS min, MAX(salary / 12.0) AS max, STDDEV_POP(salary / 12.0) AS stddev FROM emp;
  3.    min        |       max       |     stddev
  4. ------------------+-----------------+-----------------
  5. 2110.3333333333335|6249.916666666667|1147.093791898986

STDDEV_SAMP

概要:

Sql

  1. STDDEV_SAMP(field_name)

入力:

数値フィールド。このフィールドにnull値のみが含まれている場合、関数はnullを返します。そうでない場合、関数はこのフィールドのnull値を無視します。

出力: double 数値

説明:

フィールドfield_nameの入力値のサンプル標準偏差を返します。

Sql

  1. SELECT MIN(salary) AS min, MAX(salary) AS max, STDDEV_SAMP(salary) AS stddev FROM emp;
  3.    min      |      max      |      stddev
  4. ---------------+---------------+------------------
  5. 25324          |74999          |13834.471662090747

Sql

  1. SELECT MIN(salary / 12.0) AS min, MAX(salary / 12.0) AS max, STDDEV_SAMP(salary / 12.0) AS stddev FROM emp;
  3.    min        |       max       |     stddev
  4. ------------------+-----------------+-----------------
  5. 2110.3333333333335|6249.916666666667|1152.872638507562

SUM_OF_SQUARES

概要:

Sql

  1. SUM_OF_SQUARES(field_name)

入力:

数値フィールド。このフィールドにnull値のみが含まれている場合、関数はnullを返します。そうでない場合、関数はこのフィールドのnull値を無視します。

出力: double 数値

説明:

フィールドfield_nameの入力値の二乗和を返します。

Sql

  1. SELECT MIN(salary) AS min, MAX(salary) AS max, SUM_OF_SQUARES(salary) AS sumsq
  2.    FROM emp;
  4.    min      |      max      |     sumsq
  5. ---------------+---------------+----------------
  6. 25324          |74999          |2.51740125721E11

Sql

  1. SELECT MIN(salary / 24.0) AS min, MAX(salary / 24.0) AS max, SUM_OF_SQUARES(salary / 24.0) AS sumsq FROM emp;
  3.    min        |       max        |       sumsq
  4. ------------------+------------------+-------------------
  5. 1055.1666666666667|3124.9583333333335|4.370488293767361E8

VAR_POP

概要:

Sql

  1. VAR_POP(field_name)

入力:

数値フィールド。このフィールドにnull値のみが含まれている場合、関数はnullを返します。そうでない場合、関数はこのフィールドのnull値を無視します。

出力: double 数値

説明:

フィールドfield_nameの入力値の母集団分散を返します。

Sql

  1. SELECT MIN(salary) AS min, MAX(salary) AS max, VAR_POP(salary) AS varpop FROM emp;
  3.    min      |      max      |     varpop
  4. ---------------+---------------+----------------
  5. 25324          |74999          |1.894786801075E8

Sql

  1. SELECT MIN(salary / 24.0) AS min, MAX(salary / 24.0) AS max, VAR_POP(salary / 24.0) AS varpop FROM emp;
  3.    min        |       max        |      varpop
  4. ------------------+------------------+------------------
  5. 1055.1666666666667|3124.9583333333335|328956.04185329855

VAR_SAMP

概要:

Sql

  1. VAR_SAMP(field_name)

入力:

数値フィールド。このフィールドにnull値のみが含まれている場合、関数はnullを返します。そうでない場合、関数はこのフィールドのnull値を無視します。

出力: double 数値

説明:

フィールドfield_nameの入力値のサンプル分散を返します。

Sql

  1. SELECT MIN(salary) AS min, MAX(salary) AS max, VAR_SAMP(salary) AS varsamp FROM emp;
  3.    min      |      max      |     varsamp
  4. ---------------+---------------+----------------
  5. 25324          |74999          |1.913926061691E8

Sql

  1. SELECT MIN(salary / 24.0) AS min, MAX(salary / 24.0) AS max, VAR_SAMP(salary / 24.0) AS varsamp FROM emp;
  3.    min        |       max        |     varsamp
  4. ------------------+------------------+----------------
  5. 1055.1666666666667|3124.9583333333335|332278.830154847