全部产品
Search

搜索本产品

    MaxCompute：Fungsi agregat

    文档中心

    MaxCompute：Fungsi agregat

    更新时间：Jun 19, 2025

    Fungsi agregat mengelompokkan beberapa catatan input untuk membentuk satu catatan output. Anda dapat menggunakan fungsi ini dengan klausa GROUP BY dalam MaxCompute SQL. Dokumen ini menjelaskan sintaksis, parameter, dan contoh fungsi agregat yang didukung oleh MaxCompute SQL serta membantu Anda memahami penggunaannya dalam pengembangan data.

    Tabel berikut menjabarkan fungsi agregat yang didukung oleh MaxCompute SQL:

    Fungsi

    Deskripsi

    ANY_VALUE

    Mengembalikan nilai acak dari kolom tertentu.

    APPROX_DISTINCT

    Mengembalikan jumlah perkiraan nilai input yang berbeda dalam kolom tertentu.

    ARG_MAX

    Mengembalikan nilai kolom dari baris yang sesuai dengan nilai maksimum kolom tertentu.

    ARG_MIN

    Mengembalikan nilai kolom dari baris yang sesuai dengan nilai minimum dari kolom tertentu.

    AVG

    Mengembalikan nilai rata-rata dari sebuah kolom.

    BITWISE_AND_AGG

    Mengagregasi nilai input berdasarkan operasi bitwise AND.

    BITWISE_OR_AGG

    Mengumpulkan nilai input berdasarkan operasi bitwise OR.

    COLLECT_LIST

    Mengumpulkan kolom yang ditentukan ke dalam sebuah larik.

    COLLECT_SET

    Mengumpulkan nilai-nilai unik dari kolom tertentu ke dalam sebuah array.

    COUNT

    Mengembalikan jumlah catatan yang sesuai dengan kriteria tertentu.

    COUNT_IF

    Mengembalikan jumlah rekaman yang nilai expr-nya adalah True.

    COVAR_POP

    Menghitung kovarian populasi dari dua kolom numerik yang ditentukan.

    COVAR_SAMP

    Menghitung kovarian sampel dari dua kolom numerik yang ditentukan.

    HISTOGRAM

    Mengembalikan peta yang berisi jumlah kemunculan setiap nilai input.

    MAP_AGG

    Mengembalikan peta yang dibuat dengan menggunakan a dan b. a adalah kunci dalam peta. b adalah nilai dari kunci dalam peta.

    MAP_UNION

    Mengembalikan peta baru yang merupakan gabungan dari semua peta input.

    MAP_UNION_SUM

    Mengembalikan peta baru yang merupakan gabungan dari semua peta input. Peta keluaran menjumlahkan nilai dari kunci yang cocok di semua peta input.

    MAKS

    Mengembalikan nilai maksimum dari sebuah kolom.

    MAX_BY

    Mengembalikan nilai kolom dari baris yang sesuai dengan nilai maksimum dari kolom tertentu.

    MEDIAN

    Mengembalikan nilai median dari sebuah kolom.

    MIN

    Mengembalikan nilai minimum dari sebuah kolom.

    MIN_BY

    Mengembalikan nilai kolom dari baris yang sesuai dengan nilai minimum dari kolom tertentu.

    MULTIMAP_AGG

    Mengembalikan peta yang dibuat dengan menggunakan a dan b. a adalah kunci dalam peta. b digunakan untuk membuat larik, yang digunakan sebagai nilai dari kunci dalam peta.

    NUMERIC_HISTOGRAM

    Mengembalikan histogram perkiraan berdasarkan kolom yang ditentukan.

    PERCENTILE

    Menghitung persentil yang tepat. Fungsi ini cocok untuk skenario di mana sejumlah kecil data dihitung.

    PERCENTILE_APPROX

    Mengembalikan persentil perkiraan. Fungsi ini berlaku untuk skenario di mana sejumlah besar data dihitung.

    STDDEV

    Mengembalikan deviasi standar populasi dari semua nilai input.

    STDDEV_SAMP

    Mengembalikan deviasi standar sampel dari semua nilai input.

    SUM

    Mengembalikan jumlah kolom.

    VAR_SAMP

    Menghitung varians sampel dari kolom numerik tertentu.

    VARIANCE/VAR_POP

    Menghitung varians dari kolom numerik tertentu.

    WM_CONCAT

    Menggabungkan string dengan pemisah tertentu.

    Pencegahan

    MaxCompute V2.0 menyediakan fungsi tambahan. Jika Anda menggunakan fungsi yang melibatkan tipe data baru yang didukung di edisi tipe data MaxCompute V2.0, Anda harus mengeksekusi pernyataan SET untuk mengaktifkan edisi tersebut. Tipe data baru mencakup TINYINT, SMALLINT, INT, FLOAT, VARCHAR, TIMESTAMP, dan BINARY.

    • Tingkat sesi: Untuk menggunakan edisi tipe data MaxCompute V2.0, tambahkan set odps.sql.type.system.odps2=true; sebelum pernyataan SQL yang ingin dieksekusi, lalu kirim dan jalankan secara bersamaan.

    • Tingkat proyek: Pemilik Proyek dapat menjalankan perintah berikut untuk mengaktifkan edisi tipe data MaxCompute V2.0 pada tingkat proyek sesuai kebutuhan. Konfigurasi akan berlaku dalam 10 hingga 15 menit. Contoh pernyataan:

      setproject odps.sql.type.system.odps2=true;

      Untuk informasi lebih lanjut tentang setproject, lihat Operasi Proyek. Untuk detail pencegahan saat mengaktifkan edisi tipe data MaxCompute V2.0 pada tingkat proyek, lihat Edisi Tipe Data.

    • Seorang pekerja dapat berisi maksimal 2 juta elemen.

    Jika Anda menggunakan pernyataan SQL yang mencakup beberapa fungsi agregat dan sumber daya proyek tidak memadai, mungkin terjadi kelebihan memori. Kami sarankan Anda mengoptimalkan pernyataan SQL atau menambah sumber daya komputasi sesuai kebutuhan bisnis Anda.

    Sintaksis

    Berikut adalah sintaksis fungsi agregat:

    <aggregate_name>(<expression>[,..]) [within group (order by <col1>[,<col2>…])] [filter (where <where_condition>)]

    • <aggregate_name>(<expression>[,..]): Fungsi agregat bawaan atau fungsi agregat yang didefinisikan pengguna (UDAF). Format fungsi agregat mengikuti sintaksis ini.

    • within group (order by <col1>[,<col2>…]): Jika sintaksis fungsi agregat mencakup ekspresi ini, sistem secara otomatis mengurutkan data input dari <col1>[,<col2>…] dalam urutan menaik. Untuk mengurutkan data input dalam urutan menurun, gunakan ekspresi within group (order by <col1>[,<col2>…] [desc]).

      Sebelum menggunakan ekspresi ini, perhatikan batasan berikut:

      • Ekspresi ini hanya dapat digunakan untuk WM_CONCAT, COLLECT_LIST, COLLECT_SET, dan UDAF.

      • Jika beberapa fungsi agregat dalam pernyataan SELECT mencakup ekspresi within group (order by <col1>[,<col2>…]), maka order by <col1>[,<col2>…] harus sama untuk semua fungsi tersebut.

      • Jika parameter fungsi agregat mencakup kata kunci DISTINCT, kolom dengan nilai unik harus ditentukan dalam ekspresi order by <col1>[,<col2>…]. Kolom yang ditentukan dalam klausa ORDER BY merupakan subset dari kolom dengan nilai unik. Tipe data bidang dalam ekspresi <col1>[,<col2>…] harus sesuai dengan tipe data parameter input fungsi agregat.

        null

        Hanya satu parameter input yang dapat ditentukan dalam fungsi agregat yang menggunakan ekspresi within group (order by <col1>[,<col2>…]). Oleh karena itu, jika parameter input dalam fungsi agregat mencakup kata kunci DISTINCT, hanya satu kolom yang dapat ditentukan dalam klausa ORDER BY. Tipe data kolom tersebut harus sesuai dengan tipe data parameter input fungsi agregat.

        Sebagai contoh, jika parameter input fungsi WM_CONCAT adalah tipe data STRING, tipe data bidang yang ditentukan dalam klausa ORDER BY juga harus STRING. Untuk informasi lebih lanjut, lihat Contoh 4. Untuk informasi lebih lanjut tentang cara membuat tabel emp, lihat Data sampel.

      Contoh:

      -- Contoh 1: Urutkan data input secara menaik dan kembalikan data output. 
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;
-- Hasil berikut dikembalikan: 
+------------+------------+
| x          | _c1        |
+------------+------------+
| k          | 1,2,3      |
+------------+------------+

-- Contoh 2: Urutkan data input secara menurun dan kembalikan data output. 
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;
-- Hasil berikut dikembalikan: 
+------------+------------+
| x          | _c1        |
+------------+------------+
| k          | 3,2,1      |
+------------+------------+


-- Contoh 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;

-- Hasil berikut dikembalikan: 
+------------+------------+
| id         | _c1        |
+------------+------------+
| k          | 3,2,1      |
+------------+------------+


-- Contoh 4
-- Parameter fungsi agregat mencakup kata kunci DISTINCT. Dalam hal ini, jika input parameter sal dari fungsi WM_CONCAT adalah tipe data BIGINT, tipe data parameter input secara implisit dikonversi menjadi nilai tipe data STRING. 
-- Untuk memastikan konsistensi tipe data, Anda harus menggunakan fungsi CAST untuk mengonversi tipe data parameter input sal menjadi nilai tipe data STRING. Jika tidak, kesalahan akan dilaporkan. 
select deptno,
wm_concat(distinct ',', sal) 
within group (order by cast(sal as STRING ) desc) 
from emp group by deptno order by deptno;

-- Hasil berikut dikembalikan: 
+------------+------------+
| deptno     | _c1        |
+------------+------------+
| 10         | 5000,2450,1300 |
| 20         | 800,3000,2975,1100 |
| 30         | 950,2850,1600,1500,1250 |
+------------+------------+

    • [filter (where <where_condition>)]: Jika fungsi agregat mencakup ekspresi ini, fungsi agregat hanya memproses data yang memenuhi kondisi yang ditentukan oleh <where_condition>. Untuk informasi lebih lanjut tentang <where_condition>, lihat Klausa WHERE (where_condition).

      Sebelum menggunakan ekspresi ini, perhatikan batasan berikut:

      • Hanya fungsi agregat bawaan yang mendukung ekspresi ini. UDAF tidak mendukung ekspresi ini.

      • count(*) mendukung ekspresi [filter (where <where_condition>)].

      • Fungsi COUNT_IF tidak mendukung ekspresi [filter (where <where_condition>)].

      Contoh:

      -- Contoh 1: Filter dan agregasi 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);
-- Hasil berikut dikembalikan: 
+------------+------------+------------+
| _c0        | _c1        | _c2        |
+------------+------------+------------+
| 6          | 3          | 2          |
+------------+------------+------------+

-- Contoh 2: Gunakan beberapa fungsi agregat untuk menyaring dan mengumpulkan 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);
-- Hasil berikut dikembalikan: 
+------------+------------+------------+
| _c0        | _c1        | _c2        |
+------------+------------+------------+
| 1          | 3          | 2          |
+------------+------------+------------+

    Data sampel

    Bagian ini menyediakan data sumber sampel dan contoh untuk membantu Anda memahami penggunaan fungsi-fungsi tersebut. Buat tabel bernama emp dan masukkan data sampel ke dalamnya. Perintah sampel:

    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;

    File emp.txt berisi data sampel berikut:

    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

    Ekspresi filter

    • Batasan

      • Hanya fungsi agregat bawaan MaxCompute yang mendukung ekspresi filter. UDAF tidak mendukung ekspresi filter.

      • COUNT(*) tidak dapat digunakan dengan ekspresi filter. Gunakan fungsi COUNT_IF dengan ekspresi filter.

    • Sintaksis

      <aggregate_name>(<expression>[,...]) [filter (where <where_condition>)]

    • Deskripsi

      Semua fungsi agregat mendukung ekspresi filter. Jika kondisi filter ditentukan, hanya baris data yang memenuhi kondisi tersebut yang akan diteruskan ke fungsi agregat terkait untuk diproses.

    • Parameter

      • aggregate_name: wajib. Nama fungsi agregat. Pilih fungsi agregat yang dijelaskan dalam topik ini sesuai dengan kebutuhan bisnis Anda.

      • expression: wajib. Parameter dari fungsi agregat yang dipilih. Tentukan parameter ini berdasarkan deskripsi fungsi agregat yang bersangkutan.

      • where_condition: opsional. Kondisi filter. Untuk informasi lebih lanjut tentang where_condition, lihat Klausa WHERE (where_condition).

    • Nilai Kembali

      Untuk informasi lebih lanjut, lihat deskripsi nilai kembali untuk setiap fungsi agregat.

    • Pernyataan Contoh:

      select sum(sal) filter (where deptno=10), sum(sal) filter (where deptno=20), sum(sal) filter (where deptno=30) from emp;

      Hasil berikut dikembalikan:

      +------------+------------+------------+
| _c0        | _c1        | _c2        |
+------------+------------+------------+
| 17500      | 10875      | 9400       |
+------------+------------+------------+

    ANY_VALUE

    • Sintaksis

      any_value(<colname>)

    • Deskripsi

      Mengembalikan nilai acak dari kolom tertentu. Fungsi ini merupakan bagian tambahan dari MaxCompute V2.0.

    • Parameter

      colname: wajib. Nama kolom, yang dapat berupa tipe data apa pun.

    • Nilai Kembali

      Tipe data nilai kembali sama dengan tipe data parameter colname. Jika nilai parameter colname adalah null, baris yang berisi nilai tersebut tidak digunakan dalam perhitungan.

    • Contoh

      • Contoh 1: Pilih salah satu karyawan. Contoh pernyataan:

        select any_value(ename) from emp;

        Hasil berikut dikembalikan:

        +------------+
| _c0        |
+------------+
| SMITH      |
+------------+

      • Contoh 2: Gunakan fungsi ini dengan GROUP BY untuk mengelompokkan semua karyawan berdasarkan kolom deptno dan pilih satu karyawan dari setiap grup. Contoh pernyataan:

        select deptno, any_value(ename) from emp group by deptno;

        Hasil berikut dikembalikan:

        +------------+------------+
| deptno     | _c1        |
+------------+------------+
| 10         | CLARK      |
| 20         | SMITH      |
| 30         | ALLEN      |
+------------+------------+

    APPROX_DISTINCT

    • Sintaksis

      approx_distinct(<colname>)

    • Deskripsi

      Mengembalikan jumlah perkiraan nilai input yang berbeda dalam kolom tertentu. Fungsi ini merupakan bagian dari MaxCompute V2.0.

    • Parameter

      colname: wajib. Nama kolom tempat duplikat perlu dihapus.

    • Nilai Kembali

      Mengembalikan nilai tipe BIGINT. Fungsi ini memiliki kesalahan standar sebesar 5%. Jika nilai kolom yang ditentukan oleh parameter colname adalah null, baris yang berisi nilai tersebut tidak digunakan dalam perhitungan.

    • Contoh

      • Contoh 1: Hitung jumlah perkiraan nilai yang berbeda dalam kolom sal. Contoh pernyataan:

        select approx_distinct(sal) from emp;

        Hasil berikut dikembalikan:

        +-------------------+
| numdistinctvalues |
+-------------------+
| 12                |
+-------------------+

      • Contoh 2: Gunakan fungsi ini dengan GROUP BY untuk mengelompokkan semua karyawan berdasarkan departemen (deptno) dan hitung jumlah perkiraan nilai yang berbeda dalam kolom sal. Contoh pernyataan:

        select deptno, approx_distinct(sal) from emp group by deptno;

        Hasil berikut dikembalikan:

        +------------+-------------------+
| deptno     | numdistinctvalues |
+------------+-------------------+
| 10         | 3                 |
| 20         | 4                 |
| 30         | 5                 |
+------------+-------------------+

    ARG_MAX

    • Sintaksis

      arg_max(<valueToMaximize>, <valueToReturn>)

    • Deskripsi

      Fungsi ini mencari baris dengan nilai valueToMaximize tertinggi dan mengembalikan nilai valueToReturn dari baris tersebut. Fungsi ini merupakan bagian tambahan dari MaxCompute V2.0.

    • Parameter

      • valueToMaximize: Wajib. Nama kolom, dapat berupa tipe data apa pun.

      • valueToReturn: Wajib. Nama kolom, dapat berupa tipe data apa pun.

    • Nilai Kembali

      Tipe data nilai kembali sama dengan tipe data parameter valueToReturn. Jika beberapa baris memiliki nilai terbesar dari valueToMaximize, nilai valueToReturn dari salah satu baris tersebut akan dikembalikan secara acak. Jika nilai valueToMaximize adalah null, baris yang memuat nilai ini tidak digunakan dalam perhitungan.

    • Contoh

      • Contoh 1: Mengembalikan nama karyawan dengan gaji tertinggi. Contoh pernyataan:

        select arg_max(sal, ename) from emp;

        Hasil yang dikembalikan:

        +------------+
| _c0        |
+------------+
| KING       |
+------------+

      • Contoh 2: Menggunakan fungsi ini bersama GROUP BY untuk mengelompokkan karyawan berdasarkan departemen (deptno) dan mengembalikan nama karyawan dengan gaji tertinggi di setiap grup. Contoh pernyataan:

        select deptno, arg_max(sal, ename) from emp group by deptno;

        Hasil yang dikembalikan:

        +------------+------------+
| deptno     | _c1        |
+------------+------------+
| 10         | KING       |
| 20         | SCOTT      |
| 30         | BLAKE      |
+------------+------------+

    ARG_MIN

    • Sintaksis

      arg_min(<valueToMinimize>, <valueToReturn>)

    • Deskripsi

      Fungsi ini menemukan baris dengan nilai minimum dari valueToMinimize dan mengembalikan nilai valueToReturn dari baris tersebut. Fungsi ini merupakan bagian tambahan dari MaxCompute V2.0.

    • Parameter

      • valueToMinimize: Wajib. Nilai dari tipe data apa pun.

      • valueToReturn: Wajib. Nilai dari tipe data apa pun.

    • Nilai Kembali

      Tipe data nilai kembali sama dengan tipe data parameter valueToReturn. Jika beberapa baris memiliki nilai terkecil dari valueToMinimize, nilai valueToReturn dari salah satu baris tersebut akan dikembalikan secara acak. Jika nilai valueToMinimize adalah null, baris yang memuat nilai ini tidak digunakan dalam perhitungan.

    • Contoh

      • Contoh 1: Mengembalikan nama karyawan dengan gaji terendah. Contoh pernyataan:

        select arg_min(sal, ename) from emp;

        Hasil berikut dikembalikan:

        +------------+
| _c0        |
+------------+
| SMITH      |
+------------+

      • Contoh 2: Menggunakan fungsi ini dengan GROUP BY untuk mengelompokkan semua karyawan berdasarkan departemen (deptno) dan mengembalikan nama karyawan dengan gaji terendah di setiap grup. Contoh pernyataan:

        select deptno, arg_min(sal, ename) from emp group by deptno;

        Hasil berikut dikembalikan:

        +------------+------------+
| deptno     | _c1        |
+------------+------------+
| 10         | MILLER     |
| 20         | SMITH      |
| 30         | JAMES      |
+------------+------------+

    AVG

    • Sintaksis:

      DECIMAL|DOUBLE avg(<colname>)

    • Deskripsi:

      Mengembalikan nilai rata-rata dari kolom.

    • Parameter:

      colname: Wajib. Nilai kolom mendukung semua tipe data dan dapat dikonversi ke tipe DOUBLE sebelum perhitungan.

    • Nilai Kembali:

      Jika nilai colname adalah null, baris yang berisi nilai tersebut tidak digunakan dalam perhitungan. Tabel berikut menjelaskan pemetaan antara tipe data input dan nilai kembali.

      Tipe input

      Tipe nilai kembali

      TINYINT

      DOUBLE

      SMALLINT

      DOUBLE

      INT

      DOUBLE

      BIGINT

      DOUBLE

      FLOAT

      DOUBLE

      DOUBLE

      DOUBLE

      DECIMAL

      DECIMAL

    • Contoh:

      • Contoh 1: Hitung nilai rata-rata gaji (sal) dari semua karyawan. Contoh pernyataan:

        select avg(sal) from emp;

        Hasil berikut dikembalikan:

        +------------+
| _c0        |
+------------+
| 2222.0588235294117 |
+------------+

      • Contoh 2: Gunakan fungsi ini dengan GROUP BY untuk mengelompokkan semua karyawan berdasarkan departemen (deptno) dan hitung nilai rata-rata gaji (sal) dari karyawan di setiap departemen. Contoh pernyataan:

        select deptno, avg(sal) from emp group by deptno;

        Hasil berikut dikembalikan:

        +------------+------------+
| deptno     | _c1        |
+------------+------------+
| 10         | 2916.6666666666665 |
| 20         | 2175.0     |
| 30         | 1566.6666666666667 |
+------------+------------+

    BITWISE_AND_AGG

    • Sintaksis

      bigint bitwise_and_agg(bigint value)

    • Deskripsi

      Menggabungkan nilai input menggunakan operasi bitwise AND.

    • Parameter

      value: Wajib. Nilai bertipe BIGINT. Nilai null diabaikan dalam perhitungan.

    • Nilai Kembali

      Mengembalikan nilai bertipe BIGINT.

    • Contoh

      select id, bitwise_and_agg(v) from
    values (1L, 2L), (1L, 1L), (2L, null), (1L, null) t(id, v) group by id;

      Hasil berikut dikembalikan:

      +------------+------------+
| id         | _c1        |
+------------+------------+
| 1          | 0          |
| 2          | NULL       |
+------------+------------+

    BITWISE_OR_AGG

    • Sintaksis

      bigint bitwise_or_agg(bigint value)

    • Deskripsi

      Menggabungkan nilai input menggunakan operasi bitwise OR.

    • Parameter

      value: wajib. Nilai bertipe BIGINT. Nilai null diabaikan dalam perhitungan.

    • Nilai Kembali

      Mengembalikan nilai bertipe BIGINT.

    • Contoh

      select id, bitwise_or_agg(v) from
    values (1L, 2L), (1L, 1L), (2L, null), (1L, null) t(id, v) group by id;

      Hasil berikut dikembalikan:

      +------------+------------+
| id         | _c1        |
+------------+------------+
| 1          | 3          |
| 2          | NULL       |
+------------+------------+

    COLLECT_LIST

    • Sintaksis

      array collect_list(<colname>)

    • Deskripsi

      Menggabungkan nilai yang ditentukan oleh colname ke dalam array. Fungsi ini merupakan bagian dari MaxCompute V2.0.

    • Parameter

      colname: wajib. Nama kolom, yang dapat berupa tipe data apa pun.

    • Nilai Kembali

      Mengembalikan nilai tipe ARRAY. Jika nilai kolom yang ditentukan oleh colname adalah null, baris yang berisi nilai tersebut tidak digunakan dalam perhitungan.

    • Contoh

      • Contoh 1: Menggabungkan nilai gaji (sal) dari semua karyawan ke dalam array. Contoh pernyataan:

        select collect_list(sal) from emp;

        Hasil berikut dikembalikan:

        +------------+
| _c0        |
+------------+
| [800,1600,1250,2975,1250,2850,2450,3000,5000,1500,1100,950,3000,1300,5000,2450,1300] |
+------------+

      • Contoh 2: Menggunakan fungsi ini dengan GROUP BY untuk mengelompokkan semua karyawan berdasarkan kolom deptno dan menggabungkan nilai dalam kolom sal dari setiap grup ke dalam array. Contoh pernyataan:

        select deptno, collect_list(sal) from emp group by deptno;

        Hasil berikut dikembalikan:

        +------------+------------+
| deptno     | _c1        |
+------------+------------+
| 10         | [2450,5000,1300,5000,2450,1300] |
| 20         | [800,2975,3000,1100,3000] |
| 30         | [1600,1250,1250,2850,1500,950] |
+------------+------------+

      • Contoh 3: Menggunakan fungsi ini dengan GROUP BY untuk mengelompokkan semua karyawan berdasarkan kolom deptno dan menggabungkan nilai dalam kolom sal dari setiap grup setelah duplikat dihapus. Contoh pernyataan:

        select deptno, collect_list(distinct sal) from emp group by deptno;

        Hasil berikut dikembalikan:

        +------------+------------+
| deptno     | _c1        |
+------------+------------+
| 10         | [1300,2450,5000] |
| 20         | [800,1100,2975,3000] |
| 30         | [950,1250,1500,1600,2850] |
+------------+------------+

    COLLECT_SET

    • Sintaksis

      array collect_set(<colname>)

    • Deskripsi

      Menggabungkan nilai yang ditentukan oleh colname ke dalam array dengan hanya nilai unik. Fungsi ini merupakan bagian dari MaxCompute V2.0.

    • Parameter

      colname: wajib. Nama kolom, yang dapat berupa tipe data apa pun.

    • Nilai Kembali

      Mengembalikan nilai tipe ARRAY. Jika nilai kolom yang ditentukan oleh colname adalah null, baris yang berisi nilai tersebut tidak digunakan dalam perhitungan.

    • Contoh

      • Contoh 1: Menggabungkan nilai gaji (sal) dari semua karyawan ke dalam array dengan hanya nilai unik. Contoh pernyataan:

        select collect_set(sal) from emp;

        Hasil berikut dikembalikan:

        +------------+
| _c0        |
+------------+
| [800,950,1100,1250,1300,1500,1600,2450,2850,2975,3000,5000] |
+------------+

      • Contoh 2: Menggunakan fungsi ini dengan GROUP BY untuk mengelompokkan semua karyawan berdasarkan departemen (deptno) dan menggabungkan nilai dalam kolom sal dari karyawan di setiap grup ke dalam array dengan hanya nilai unik. Contoh pernyataan:

        select deptno, collect_set(sal) from emp group by deptno;

        Hasil berikut dikembalikan:

        +------------+------------+
| deptno     | _c1        |
+------------+------------+
| 10         | [1300,2450,5000] |
| 20         | [800,1100,2975,3000] |
| 30         | [950,1250,1500,1600,2850] |
+------------+------------+

    COUNT

    • Sintaksis

      bigint count([distinct|all] <colname>)

    • Deskripsi

      Mengembalikan jumlah catatan yang memenuhi kriteria tertentu.

    • Parameter

      • distinct|all: Opsional. Menentukan apakah duplikat dihapus selama penghitungan. Nilai default adalah all, yang mencakup semua catatan. Jika disetel ke distinct, hanya catatan dengan nilai unik yang dihitung.

      • colname: Wajib. Nama kolom, dapat berupa tipe data apa pun. Nilai colname dapat berupa asterisk (*). Penggunaan count(*) mengembalikan jumlah total baris.

    • Nilai Kembali

      Mengembalikan nilai tipe BIGINT. Jika nilai kolom yang ditentukan oleh colname adalah null, baris tersebut tidak digunakan dalam perhitungan.

    • Contoh

      • Contoh 1: Hitung jumlah total karyawan di semua departemen. Contoh pernyataan:

        select count(*) from emp;

        Hasil berikut dikembalikan:

        +------------+
| _c0        |
+------------+
| 17         |
+------------+

      • Contoh 2: Gunakan fungsi ini dengan GROUP BY untuk mengelompokkan karyawan berdasarkan departemen (deptno) dan hitung jumlah karyawan di setiap departemen. Contoh pernyataan:

        select deptno, count(*) from emp group by deptno;

        Hasil berikut dikembalikan:

        +------------+------------+
| deptno     | _c1        |
+------------+------------+
| 10         | 6          |
| 20         | 5          |
| 30         | 6          |
+------------+------------+

      • Contoh 3: Hapus duplikat saat menghitung jumlah departemen. Contoh pernyataan:

        select count(distinct deptno) from emp;

        Hasil berikut dikembalikan:

        +------------+
| _c0        |
+------------+
| 3          |
+------------+

    COUNT_IF

    • Sintaksis

      bigint count_if(boolean <expr>)

    • Deskripsi

      Mengembalikan jumlah catatan dengan nilai expr bernilai True.

    • Parameter

      expr: wajib, bertipe ekspresi BOOLEAN.

    • Nilai Kembali

      Mengembalikan nilai tipe BIGINT. Jika nilai parameter expr adalah False atau nilai kolom tertentu dalam expr adalah null, baris yang memuat nilai tersebut tidak dihitung.

    • Contoh

      select count_if(sal > 1000), count_if(sal <=1000) from emp;

      Hasil berikut diberikan:

      +------------+------------+
| _c0        | _c1        |
+------------+------------+
| 15         | 2          |
+------------+------------+

    COVAR_POP

    • Sintaksis

      double covar_pop(<colname1>, <colname2>)

    • Deskripsi

      Menghitung kovarians populasi dari dua kolom numerik tertentu. Fungsi ini merupakan bagian tambahan dari MaxCompute V2.0.

    • Parameter

      colname1 dan colname2: wajib. Kolom dengan tipe data numerik. Jika kolom yang ditentukan bukan numerik, nilai null akan dikembalikan.

    • Contoh

      Jalankan pernyataan berikut untuk menambahkan data ke tabel emp:

      -- sal_new adalah kolom gaji baru. 
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;

      • Contoh 1: Hitung kovarians populasi dari kolom sal dan sal_new. Contoh pernyataan:

        select covar_pop(sal, sal_new) from emp;

        Hasil berikut dikembalikan:

        +------------+
| _c0        |
+------------+
| 1594550.1730103805 |
+------------+

      • Contoh 2: Gunakan fungsi ini dengan GROUP BY untuk mengelompokkan semua karyawan berdasarkan departemen (deptno) dan hitung kovarians populasi dari kolom sal dan sal_new dalam grup yang sama. Contoh pernyataan:

        select deptno, covar_pop(sal, sal_new) from emp group by deptno;

        Hasil berikut dikembalikan:

        +------------+------------+
| deptno     | _c1        |
+------------+------------+
| 10         | 2390555.5555555555 |
| 20         | 1009500.0  |
| 30         | 372222.2222222222 |
+------------+------------+

    COVAR_SAMP

    • Sintaksis

      double covar_samp(<colname1>, <colname2>)

    • Deskripsi

      Menghitung kovarians sampel dari dua kolom numerik tertentu. Fungsi ini merupakan bagian tambahan dari MaxCompute V2.0.

    • Parameter

      colname1 dan colname2: wajib. Kolom dengan tipe data numerik. Jika kolom yang ditentukan bukan numerik, nilai null akan dikembalikan.

    • Contoh

      Jalankan pernyataan berikut untuk menambahkan data ke tabel emp:

      -- sal_new adalah kolom gaji baru. 
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;

      • Contoh 1: Hitung kovarians sampel dari kolom sal dan sal_new. Contoh pernyataan:

        select covar_samp(sal, sal_new) from emp;

        Hasil berikut dikembalikan:

        +------------+
| _c0        |
+------------+
| 1694209.5588235292 |
+------------+

      • Contoh 2: Gunakan fungsi ini dengan GROUP BY untuk mengelompokkan semua karyawan berdasarkan departemen (deptno) dan hitung kovarians sampel dari kolom sal dan sal_new dalam grup yang sama. Contoh pernyataan:

        select deptno, covar_samp(sal, sal_new) from emp group by deptno;

        Hasil berikut dikembalikan:

        +------------+------------+
| deptno     | _c1        |
+------------+------------+
| 10         | 2868666.6666666665 |
| 20         | 1261875.0  |
| 30         | 446666.6666666666 |
+------------+------------+

    HISTOGRAM

    • Sintaksis

      map<K, bigint> histogram(K input);

    • Deskripsi

      Mengembalikan peta yang berisi jumlah kemunculan setiap nilai input. Kunci dalam peta adalah nilai input, dan setiap nilai dalam peta menunjukkan jumlah kemunculan nilai input tersebut. Nilai null diabaikan.

    • Parameter

      input: Nilai input yang digunakan sebagai kunci dalam peta.

    • Nilai Kembali

      Peta yang berisi jumlah kemunculan setiap nilai input.

    • Contoh

      select histogram(a) from values
    ('hi'), (null), ('apple'), ('pie'), ('apple') t(a);

      Hasil berikut dikembalikan:

      +----------------------------+
| _c0                        |
+----------------------------+
| {"pie":1,"hi":1,"apple":2} |
+----------------------------+

    MAP_AGG

    • Sintaksis

      map<K, V> map_agg(K a, V b);

    • Deskripsi

      Mengembalikan peta yang dibuat menggunakan a dan b. a adalah kunci dalam peta, sedangkan b adalah nilai dari kunci tersebut. Jika kunci bernilai null, kunci tersebut diabaikan. Jika terdapat nilai duplikat pada bidang kunci, salah satu nilai dipertahankan secara acak.

    • Parameter

      • a: Bidang input yang digunakan sebagai kunci dalam peta.

      • b: Bidang input yang digunakan sebagai nilai dalam peta.

    • Nilai Kembali

      Mengembalikan sebuah peta baru.

    • Contoh

      select map_agg(a, b) from
        values (1L, 'apple'), (2L, 'hi'), (null, 'good'), (1L, 'pie') t(a, b);

      Hasil berikut dikembalikan:

      +------------------------+
| _c0                    |
+------------------------+
| {"2":"hi","1":"apple"} |
+------------------------+

    MAP_UNION

    • Sintaksis

      map<K, V> map_union(map<K, V> input);

    • Deskripsi

      Mengembalikan peta baru yang merupakan gabungan dari semua peta input. Jika suatu kunci ada di beberapa peta input, salah satu nilai yang sesuai dengan kunci tersebut dipilih secara acak.

    • Parameter

      input: Peta input.

    • Nilai Pengembalian

      Peta baru akan dikembalikan.

    • Contoh

      select map_union(a) from values
    (map(1L, 'hi', 2L, 'apple', 3L, 'pie')), (map(1L, 'good', 4L, 'this')), (null) t(a);

      Hasil berikut akan dikembalikan:

      +-----------------------------------------------+
| _c0                                           |
+-----------------------------------------------+
| {"4":"this","1":"good","2":"apple","3":"pie"} |
+-----------------------------------------------+

    MAP_UNION_SUM

    • Sintaksis

      map<K, V> map_union_sum(map<K, V> input);

    • Deskripsi

      Mengembalikan peta baru yang merupakan gabungan dari semua peta input. Peta output menjumlahkan nilai dari kunci yang cocok di semua peta input. Jika nilai yang sesuai dengan suatu kunci adalah NULL, nilai tersebut dikonversi menjadi 0.

      null

      Nilai dalam peta input harus bertipe data BIGINT, INT, SMALLINT, TINYINT, FLOAT, DOUBLE, atau DECIMAL.

    • Parameter

      input: rentang masukan.

    • Nilai Pengembalian

      Mengembalikan sebuah peta baru.

      null

      Nilai-nilai dalam peta baru bertipe BIGINT, DOUBLE, atau DECIMAL.

    • Contoh

      select map_union_sum(a) from values
    (map('hi', 2L, 'apple', 3L, 'pie', 1L)), (map('apple', null, 'hi', 4L)), (null) t(a);

      Hasil berikut dikembalikan:

      +----------------------------+
| _c0                        |
+----------------------------+
| {"apple":3,"hi":6,"pie":1} |
+----------------------------+

    MAKS

    • Sintaksis

      max(<colname>)

    • Deskripsi

      Mengembalikan nilai maksimum dari sebuah kolom.

    • Parameter

      colname: Wajib. Nama kolom, yang dapat berupa tipe data apa pun selain BOOLEAN.

    • Nilai Pengembalian

      Tipe nilai kembaliannya sama dengan tipe parameter colname. Nilai kembalian bervariasi berdasarkan aturan berikut:

      • Jika nilai dari colname adalah null, baris yang berisi nilai ini tidak digunakan dalam perhitungan.

      • Jika nilai dari colname bertipe BOOLEAN, nilai tersebut tidak digunakan dalam perhitungan.

    • Contoh

      • Contoh 1: Hitung gaji tertinggi (sal) dari semua karyawan. Contoh pernyataan:

        select max(sal) from emp;

        Hasil berikut dikembalikan:

        +------------+
| _c0        |
+------------+
| 5000       |
+------------+

      • Contoh 2: Gunakan fungsi ini dengan GROUP BY untuk mengelompokkan semua karyawan berdasarkan departemen (deptno) dan hitung gaji tertinggi karyawan di setiap departemen. Contoh pernyataan:

        select deptno, max(sal) from emp group by deptno;

        Hasil berikut dikembalikan:

        +------------+------------+
| deptno     | _c1        |
+------------+------------+
| 10         | 5000       |
| 20         | 3000       |
| 30         | 2850       |
+------------+------------+

    MAX_BY

    • Sintaksis

      max_by(<valueToReturn>,<valueToMaximize>)

    • Deskripsi

      null

      Fungsi MAX_BY menyediakan fitur yang sama dengan fungsi ARG_MAX. Perbedaannya terletak pada urutan parameter. Fungsi MAX_BY diperkenalkan di MaxCompute untuk menjaga kompatibilitas dengan sintaksis sumber terbuka.

      Menemukan baris di mana nilai valueToMaximize termasuk dan mengembalikan nilai dari valueToReturn dalam baris tersebut. Fungsi ini merupakan tambahan dari MaxCompute V2.0.

    • Parameter

      • valueToMaximize: wajib. Nilai dari tipe data apa pun.

      • valueToReturn: wajib. Nilai dari tipe data apa pun.

    • Nilai Pengembalian

      Tipe data nilai pengembalian sama dengan tipe data parameter valueToReturn. Jika beberapa baris memiliki nilai terbesar dari valueToMaximize, salah satu nilai valueToReturn dari baris tersebut akan dipilih secara acak. Jika nilai valueToMaximize adalah null, baris yang memuat nilai ini tidak digunakan dalam perhitungan.

    • Contoh

      • Contoh 1: Kembalikan nama karyawan dengan gaji tertinggi. Contoh pernyataan:

        select max_by(ename,sal) from emp;

        Hasil berikut dikembalikan:

        +------------+
| _c0        |
+------------+
| KING       |
+------------+

      • Contoh 2: Gunakan fungsi ini dengan GROUP BY untuk mengelompokkan semua karyawan berdasarkan kolom deptno dan mengembalikan nama karyawan dengan gaji tertinggi di setiap kelompok. Contoh pernyataan:

        select deptno, max_by(ename,sal) from emp group by deptno;

        Hasil berikut dikembalikan:

        +------------+------------+
| deptno     | _c1        |
+------------+------------+
| 10         | KING       |
| 20         | SCOTT      |
| 30         | BLAKE      |
+------------+------------+

    MEDIAN

    • Sintaksis

      double median(double <colname>)
decimal median(decimal <colname>)

    • Deskripsi

      Mengembalikan nilai median dari suatu kolom.

    • Parameter

      colname: wajib. Nama kolom, yang dapat bertipe DOUBLE atau DECIMAL. Jika nilai masukan bertipe STRING atau BIGINT, maka akan dikonversi secara implisit ke tipe DOUBLE sebelum perhitungan.

    • Nilai Kembali

      Jika nilai colname adalah null, baris yang berisi nilai ini tidak digunakan dalam perhitungan. Tabel berikut menjelaskan pemetaan antara tipe data input dan nilai kembali.

      Tipe input

      Tipe nilai kembali

      TINYINT

      DOUBLE

      SMALLINT

      DOUBLE

      INT

      DOUBLE

      BIGINT

      DOUBLE

      FLOAT

      DOUBLE

      DOUBLE

      DOUBLE

      DECIMAL

      DECIMAL

    • Contoh

      • Contoh 1: Hitung nilai median gaji (sal) dari semua karyawan. Contoh pernyataan:

        select median(sal) from emp;

        Hasil berikut dikembalikan:

        +------------+
| _c0        |
+------------+
| 1600.0     |
+------------+

      • Contoh 2: Gunakan fungsi ini dengan GROUP BY untuk mengelompokkan semua karyawan berdasarkan departemen (deptno) dan hitung nilai median gaji karyawan di setiap departemen. Contoh pernyataan:

        select deptno, median(sal) from emp group by deptno;

        Hasil berikut dikembalikan:

        +------------+------------+
| deptno     | _c1        |
+------------+------------+
| 10         | 2450.0     |
| 20         | 2975.0     |
| 30         | 1375.0     |
+------------+------------+

    MIN

    • Sintaksis

      min(<colname>)

    • Deskripsi

      Mengembalikan nilai minimum dari suatu kolom.

    • Parameter

      colname: Wajib. Nama kolom, yang dapat berupa tipe data apa pun selain BOOLEAN.

    • Nilai Kembali

      Tipe nilai kembali sama dengan tipe parameter colname. Nilai kembali mengikuti aturan berikut:

      • Jika nilai colname adalah null, baris yang memuat nilai ini tidak digunakan dalam perhitungan.

      • Jika nilai colname bertipe BOOLEAN, nilai tersebut tidak digunakan dalam perhitungan.

    • Contoh

      • Contoh 1: Hitung gaji terendah (sal) dari semua karyawan. Contoh pernyataan:

        select min(sal) from emp;

        Hasil berikut dikembalikan:

        +------------+
| _c0        |
+------------+
| 800        |
+------------+

      • Contoh 2: Gunakan fungsi ini dengan GROUP BY untuk mengelompokkan semua karyawan berdasarkan departemen (deptno) dan hitung gaji terendah karyawan di setiap departemen. Contoh pernyataan:

        select deptno, min(sal) from emp group by deptno;

        Hasil berikut dikembalikan:

        +------------+------------+
| deptno     | _c1        |
+------------+------------+
| 10         | 1300       |
| 20         | 800        |
| 30         | 950        |
+------------+------------+

    MIN_BY

    • Sintaks

      min_by(<valueToReturn>,<valueToMinimize>)

    • Deskripsi

      null

      Fungsi MIN_BY menyediakan fitur yang sama dengan fungsi ARG_MIN, tetapi urutan parameternya berbeda. Fungsi ini diperkenalkan di MaxCompute untuk menjaga kompatibilitas dengan sintaks sumber terbuka.

      Fungsi ini menemukan baris dengan nilai minimum dari valueToMinimize dan mengembalikan nilai dari valueToReturn pada baris tersebut. Fungsi ini merupakan tambahan dari MaxCompute V2.0.

    • Parameter

      • valueToMinimize: wajib. Nilai dari tipe data apa pun.

      • valueToReturn: wajib. Nilai dari tipe data apa pun.

    • Nilai Pengembalian

      Tipe data nilai pengembalian sama dengan tipe data parameter valueToReturn. Jika beberapa baris memiliki nilai terkecil dari valueToMinimize, nilai valueToReturn dari salah satu baris akan dipilih secara acak. Jika nilai valueToMinimize adalah null, baris yang memuat nilai tersebut tidak digunakan dalam perhitungan.

    • Contoh

      • Contoh 1: Mengembalikan nama karyawan dengan gaji terendah. Contoh pernyataan:

         select min_by(ename,sal) from emp;

        Hasil berikut dikembalikan:

        +------------+
| _c0        |
+------------+
| SMITH      |
+------------+

      • Contoh 2: Gunakan fungsi ini dengan GROUP BY untuk mengelompokkan semua karyawan berdasarkan kolom deptno dan mengembalikan nama karyawan dengan gaji terendah di setiap grup. Contoh pernyataan:

        select deptno, min_by(ename,sal) from emp group by deptno;

        Hasil berikut dikembalikan:

        +------------+------------+
| deptno     | _c1        |
+------------+------------+
| 10         | MILLER     |
| 20         | SMITH      |
| 30         | JAMES      |
+------------+------------+

    MULTIMAP_AGG

    • Sintaksis

      map<K, array<V>> multimap_agg(K a, V b);

    • Deskripsi

      Mengembalikan peta (map) yang dibuat menggunakan a sebagai kunci dan b untuk membentuk larik (array) sebagai nilai dari kunci tersebut. Jika suatu kunci bernilai null, kunci tersebut diabaikan.

    • Parameter

      • a: Bidang masukan yang digunakan sebagai kunci dalam peta.

      • b: Bidang masukan yang digunakan sebagai nilai dalam peta. Nilai-nilai dengan kunci yang sama dikelompokkan ke dalam larik yang sama dan digunakan sebagai nilai dalam peta.

    • Nilai Pengembalian

      Mengembalikan sebuah peta baru.

    • Contoh

      select multimap_agg(a, b) from
        values (1L, 'apple'), (2L, 'hi'), (null, 'good'), (1L, 'pie') t(a, b);

      Hasil berikut dikembalikan:

      +----------------------------------+
| _c0                              |
+----------------------------------+
| {"2":["hi"],"1":["apple","pie"]} |
+----------------------------------+

    NUMERIC_HISTOGRAM

    • Sintaksis

      map<double key, double value> numeric_histogram(bigint <buckets>,
                                                double <colname>
                                                [, double <weight>])

    • Deskripsi

      Mengembalikan histogram perkiraan berdasarkan kolom tertentu. Fungsi ini merupakan bagian dari MaxCompute V2.0.

    • Parameter

      • buckets: Wajib. Nilai bertipe BIGINT. Menentukan jumlah maksimum bucket dalam kolom yang histogram perkiraannya dihitung.

      • colname: Wajib. Nilai bertipe DOUBLE. Menentukan kolom yang akan digunakan untuk menghitung histogram perkiraan.

      • weight: Opsional. Bobot data di setiap baris, bertipe DOUBLE.

    • Nilai Kembali

      Nilai bertipe map<double key, double value> dikembalikan. Dalam hasil, key menunjukkan sumbu X dari histogram perkiraan, dan value menunjukkan tinggi perkiraan sumbu Y. Nilai kembali mengikuti aturan berikut:

      • Jika nilai buckets adalah null, maka null dikembalikan.

      • Jika nilai colname adalah null, baris yang berisi nilai tersebut tidak digunakan dalam perhitungan.

    • Contoh

      • Kembalikan histogram perkiraan dari kolom sal. Contoh pernyataan:

        select numeric_histogram(5, sal) from emp;

        Hasil berikut dikembalikan:

        +------------+
| _c0        |
+------------+
| {"1328.5714285714287":7.0,"2450.0":2.0,"5000.0":2.0,"875.0":2.0,"2956.25":4.0} |
+------------+

      • Kembalikan histogram perkiraan dari kolom sal. deptno dalam setiap baris data menunjukkan bobot departemen. Contoh pernyataan:

        select numeric_histogram(5, sal, deptno) from emp;

        Hasil berikut dikembalikan:

        +------------+
| _c0        |
+------------+
| {"2944.4444444444443":90.0,"2450.0":20.0,"5000.0":20.0,"890.0":50.0,"1350.0":160.0} |
+------------+

    PERCENTILE

    • Sintaksis

      double percentile(bigint <colname>, <p>)
-- Mengembalikan beberapa persentil eksak sebagai array. 
array percentile(bigint <colname>, array(<p1> [, <p2>...]))

    • Deskripsi

      Menghitung persentil eksak. Fungsi ini cocok untuk skenario dengan sejumlah kecil data. Fungsi ini mengurutkan data dalam kolom yang ditentukan secara menaik dan mengembalikan persentil p × 100 dari kolom tersebut. Parameter p harus berupa nilai antara 0 dan 1. Nilai dalam kolom yang ditentukan untuk percentile diberi nomor urut mulai dari 0. Sebagai contoh, jika nilai dalam kolom adalah 100, 200, dan 300, nomor urutnya adalah 0, 1, dan 2. Jika fungsi ini digunakan untuk menghitung persentil ke-30 dari kolom tersebut, nilai percentile adalah 0,6, dihitung menggunakan rumus: 2 × 0,3 = 0,6. Nilai ini berada di antara nomor urut 0 dan 1. Hasilnya dihitung menggunakan rumus: 100 + (200 - 100) × 0,6 = 160. Fungsi ini merupakan bagian dari MaxCompute V2.0.

    • Parameter

      • colname: Wajib. Kolom bertipe BIGINT.

      • p: Wajib. Persentil. Rentang nilai valid: [0.0,1.0].

    • Nilai Kembali

      Mengembalikan nilai bertipe DOUBLE atau ARRAY.

    • Contoh

      • Contoh 1: Hitung persentil ke-30 dalam kolom sal. Contoh pernyataan:

        select percentile(sal, 0.3) from emp;

        Hasil yang dikembalikan:

        +------------+
| _c0        |
+------------+
| 1290.0     |
+------------+

      • Contoh 2: Gunakan fungsi ini dengan GROUP BY untuk mengelompokkan semua karyawan berdasarkan departemen (deptno) dan hitung persentil ke-30 dari karyawan dalam setiap grup pada kolom sal. Contoh pernyataan:

        select deptno, percentile(sal, 0.3) from emp group by deptno;

        Hasil yang dikembalikan:

        +------------+------------+
| deptno     | _c1        |
+------------+------------+
| 10         | 1875.0     |
| 20         | 1475.0     |
| 30         | 1250.0     |
+------------+------------+

      • Contoh 3: Gunakan fungsi ini dengan GROUP BY untuk mengelompokkan semua karyawan berdasarkan departemen (deptno) dan hitung persentil ke-30, ke-50, dan ke-80 dari karyawan dalam setiap grup pada kolom sal. Contoh pernyataan:

        set odps.sql.type.system.odps2=true;
select deptno, percentile(sal, array(0.3, 0.5, 0.8)) from emp group by deptno;

        Hasil yang dikembalikan:

        +------------+------------+
| 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

    • Sintaksis

      double percentile_approx (double <colname>[, double <weight>], <p> [, <B>]))
-- Mengembalikan beberapa persentil perkiraan sebagai array. 
array<double> percentile_approx (double <colname>
                                 [, double <weight>],
                                 array(<p1> [, <p2>...])
                                 [, <B>])

    • Deskripsi

      Fungsi ini merupakan bagian dari MaxCompute V2.0. Nilai dalam kolom yang ditentukan untuk percentile_approx diberi nomor urut mulai dari 1. Sebagai contoh, persentil p × 100th dari kolom dengan n baris data dihitung. Fungsi PERCENTILE_APPROX pertama-tama mengurutkan nilai dalam kolom secara menaik, lalu menghitung persentil yang diminta. Untuk mempermudah pemahaman logika perhitungan, arr digunakan untuk merepresentasikan array nilai yang telah diurutkan dalam kolom, dan res digunakan untuk merepresentasikan hasil fungsi. Rumus perhitungan res bervariasi berdasarkan indeks yang dihitung menggunakan rumus berikut: .

      • Jika kondisi index ≤ 1 terpenuhi, res dihitung menggunakan rumus: res = arr[0].

      • Jika kondisi index ≥ n - 1 terpenuhi, res dihitung menggunakan rumus: res = arr[n-1].

      • Jika kondisi 1 < index < n - 1 terpenuhi, diff dihitung terlebih dahulu menggunakan rumus: diff = index + 0.5 - ceil(index).

        Jika kondisi abs(diff) < 0.5 terpenuhi, res dihitung menggunakan rumus: res = arr[ceil(index) - 1].

        Jika kondisi abs(diff) = 0.5 terpenuhi, res dihitung menggunakan rumus: res = arr[index - 1] + (arr[index] - arr[index - 1]) × 0.5.

        Nilai abs(diff) tidak dapat lebih besar dari 0.5.

      Sebagai contoh, kolom col berisi nilai 100, 200, 300, dan 400, dengan nomor urut 1, 2, 3, dan 4. Persentil ke-25, ke-50, dan ke-75 dari kolom ini dihitung sebagai berikut:

      • percentile_approx(col, 0.25) = 100 (index = 1)

      • percentile_approx(col, 0.5) = 200 + (300 - 200) × 0.5 = 250 (index = 2)

      • percentile_approx(col, 0.75) = 400 (index = 3)

      null

      Perbedaan antara PERCENTILE_APPROX dan PERCENTILE:

      • PERCENTILE_APPROX digunakan untuk menghitung persentil perkiraan, sedangkan PERCENTILE digunakan untuk menghitung persentil eksak. Jika jumlah data besar, PERCENTILE mungkin gagal karena batasan memori, tetapi PERCENTILE_APPROX tidak memiliki masalah ini.

      • Implementasi PERCENTILE_APPROX konsisten dengan implementasi PERCENTILE_APPROX di Hive, namun algoritma perhitungan PERCENTILE_APPROX berbeda dari percentile. Jika jumlah data kecil, hasil eksekusi PERCENTILE_APPROX akan berbeda dari PERCENTILE.

    • Parameter

      • colname: wajib. Nama kolom, bertipe DOUBLE.

      • weight: opsional. Nilai bobot data di setiap baris, bertipe DOUBLE.

      • p: wajib. Persentil perkiraan. Nilai valid: [0.0,1.0].

      • B: akurasi nilai kembali. Akurasi yang lebih tinggi menunjukkan hasil yang lebih presisi. Jika parameter ini tidak ditentukan, nilai default adalah 10000.

    • Nilai Kembali

      Nilai tipe DOUBLE atau ARRAY dikembalikan. Nilai kembali bervariasi berdasarkan aturan berikut:

      • Jika nilai colname adalah null, baris yang berisi nilai tersebut tidak digunakan dalam perhitungan.

      • Jika nilai p atau B adalah null, kesalahan akan dikembalikan.

    • Contoh

      • Contoh 1: Hitung persentil ke-30 dalam kolom sal. Contoh pernyataan:

        select percentile_approx(sal, 0.3) from emp;

        Hasil berikut dikembalikan:

        +------------+
| _c0        |
+------------+
| 1252.5     |
+------------+

      • Contoh 2: Gunakan fungsi ini dengan GROUP BY untuk mengelompokkan semua karyawan berdasarkan departemen (deptno) dan hitung persentil ke-30 dari karyawan dalam setiap grup pada kolom sal. Contoh pernyataan:

        select deptno, percentile_approx(sal, 0.3) from emp group by deptno;

        Hasil berikut dikembalikan:

        +------------+------------+
| deptno     | _c1        |
+------------+------------+
| 10         | 1300.0     |
| 20         | 950.0      |
| 30         | 1070.0     |
+------------+------------+

      • Contoh 3: Gunakan fungsi ini dengan GROUP BY untuk mengelompokkan karyawan berdasarkan departemen (deptno) dan hitung persentil ke-30, ke-50, serta ke-80 dari karyawan dalam setiap grup pada kolom sal. Contoh pernyataan:

        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;

        Hasil berikut dikembalikan:

        +------------+------------+
| deptno     | _c1        |
+------------+------------+
| 10         | [1300.0,1875.0,3470.000000000001] |
| 20         | [950.0,2037.5,2987.5] |
| 30         | [1070.0,1250.0,1580.0] |
+------------+------------+

      • Contoh 4 (contoh dengan bobot yang ditentukan): Gunakan fungsi ini bersama dengan GROUP BY untuk mengelompokkan semua karyawan berdasarkan departemen (deptno) dan menghitung persentil ke-30, ke-50, serta ke-80 dari karyawan dalam setiap grup pada kolom sal. Nilai dalam kolom cnt tabel emp menunjukkan jumlah karyawan dengan gaji dalam persentil tertentu. Contoh pernyataan:

        select deptno, percentile_approx(sal, deptno, array(0.3, 0.5, 0.8), 1000)
  from emp group by deptno;

        Hasil berikut dikembalikan:

        +------------+------------+
| deptno     | _c1        |
+------------+------------+
| 10         | [1300.0,1875.0,3470.0] |
| 20         | [950.0,2037.5,2987.5] |
| 30         | [1070.0,1250.0,1580.0] |
+------------+------------+

    STDDEV

    • Sintaksis

      double stddev(double <colname>)
decimal stddev(decimal <colname>)

    • Deskripsi

      Mengembalikan deviasi standar populasi dari semua nilai input.

    • Parameter

      colname: Wajib. Nama kolom, yang dapat berupa tipe DOUBLE atau DECIMAL. Jika nilai input adalah tipe STRING atau BIGINT, nilai tersebut akan dikonversi secara implisit ke tipe DOUBLE sebelum perhitungan.

    • Nilai Kembali

      Jika nilai colname adalah null, baris yang berisi nilai ini tidak digunakan dalam perhitungan. Tabel berikut menjelaskan pemetaan antara tipe data input dan nilai kembali.

      Tipe input

      Tipe nilai kembali

      TINYINT

      DOUBLE

      SMALLINT

      DOUBLE

      INT

      DOUBLE

      BIGINT

      DOUBLE

      FLOAT

      DOUBLE

      DOUBLE

      DOUBLE

      DECIMAL

      DECIMAL

    • Contoh

      • Contoh 1: Hitung deviasi standar populasi dari nilai gaji (sal) semua karyawan. Contoh pernyataan:

        select stddev(sal) from emp;

        Hasil berikut dikembalikan:

        +------------+
| _c0        |
+------------+
| 1262.7549932628976 |
+------------+

      • Contoh 2: Gunakan fungsi ini dengan GROUP BY untuk mengelompokkan semua karyawan berdasarkan departemen (deptno) dan hitung deviasi standar populasi dari nilai gaji (sal) karyawan di setiap departemen. Contoh pernyataan:

        select deptno, stddev(sal) from emp group by deptno;

        Hasil berikut dikembalikan:

        +------------+------------+
| deptno     | _c1        |
+------------+------------+
| 10         | 1546.1421524412158 |
| 20         | 1004.7387720198718 |
| 30         | 610.1001739241043 |
+------------+------------+

    STDDEV_SAMP

    • Sintaksis

      double stddev_samp(double <colname>)
decimal stddev_samp(decimal <colname>)

    • Deskripsi

      Mengembalikan deviasi standar sampel dari semua nilai input.

    • Parameter

      colname: wajib. Nama kolom, yang dapat berupa tipe DOUBLE atau DECIMAL. Jika nilai input adalah tipe STRING atau BIGINT, nilai tersebut secara implisit dikonversi ke tipe DOUBLE sebelum perhitungan.

    • Nilai Kembali

      Jika nilai colname adalah null, baris yang berisi nilai ini tidak digunakan dalam perhitungan. Tabel berikut menjelaskan pemetaan antara tipe data input dan nilai kembali.

      Tipe input

      Tipe nilai kembali

      TINYINT

      DOUBLE

      SMALLINT

      DOUBLE

      INT

      DOUBLE

      BIGINT

      DOUBLE

      FLOAT

      DOUBLE

      DOUBLE

      DOUBLE

      DECIMAL

      DECIMAL

    • Contoh

      • Contoh 1: Hitung deviasi standar sampel dari nilai gaji (sal) semua karyawan. Contoh pernyataan:

        select stddev_samp(sal) from emp;

        Hasil berikut dikembalikan:

        +------------+
| _c0        |
+------------+
| 1301.6180541247609 |
+------------+

      • Contoh 2: Gunakan fungsi ini dengan GROUP BY untuk mengelompokkan semua karyawan berdasarkan departemen (deptno) dan hitung deviasi standar sampel dari nilai gaji (sal) karyawan di setiap departemen. Contoh pernyataan:

        select deptno, stddev_samp(sal) from emp group by deptno;

        Hasil berikut dikembalikan:

        +------------+------------+
| deptno     | _c1        |
+------------+------------+
| 10         | 1693.7138680032901 |
| 20         | 1123.3320969330487 |
| 30         | 668.3312551921141 |
+------------+------------+

    SUM

    • Sintaksis

      DECIMAL|DOUBLE|BIGINT sum(<colname>)

    • Deskripsi

      Mengembalikan jumlah nilai dalam kolom.

    • Parameter

      colname: Wajib. Nilai kolom mendukung semua tipe data yang dapat dikonversi ke tipe DOUBLE sebelum perhitungan. Nama kolom dapat berupa tipe DOUBLE, DECIMAL, atau BIGINT. Jika nilai input adalah tipe STRING, nilai tersebut akan secara implisit dikonversi ke tipe DOUBLE sebelum perhitungan.

    • Nilai Kembali

      Jika nilai colname adalah null, baris yang berisi nilai ini tidak digunakan dalam perhitungan. Tabel berikut menjelaskan pemetaan antara tipe data input dan nilai kembali.

      Tipe input

      Tipe nilai kembali

      TINYINT

      BIGINT

      SMALLINT

      BIGINT

      INT

      BIGINT

      BIGINT

      BIGINT

      FLOAT

      DOUBLE

      DOUBLE

      DOUBLE

      DECIMAL

      DECIMAL

    • Contoh

      • Contoh 1: Hitung jumlah nilai gaji (sal) semua karyawan. Contoh pernyataan:

        select sum(sal) from emp;

        Hasil berikut dikembalikan:

        +------------+
| _c0        |
+------------+
| 37775      |
+------------+

      • Contoh 2: Gunakan fungsi ini dengan GROUP BY untuk mengelompokkan semua karyawan berdasarkan departemen (deptno) dan hitung jumlah nilai gaji (sal) karyawan di setiap departemen. Contoh pernyataan:

        select deptno, sum(sal) from emp group by deptno;

        Hasil berikut dikembalikan:

        +------------+------------+
| deptno     | _c1        |
+------------+------------+
| 10         | 17500      |
| 20         | 10875      |
| 30         | 9400       |
+------------+------------+

    VAR_SAMP

    • Sintaksis

      double var_samp(<colname>)

    • Deskripsi

      Menghitung varians sampel dari kolom numerik tertentu. Fungsi ini merupakan bagian tambahan dari MaxCompute V2.0.

    • Parameter

      colname: wajib. Kolom dengan tipe data numerik. Jika kolom yang ditentukan bukan numerik, nilai null akan dikembalikan.

    • Nilai Kembali

      Mengembalikan nilai bertipe DOUBLE.

    • Contoh

      • Contoh 1: Hitung varians sampel dari nilai gaji (sal) semua karyawan. Contoh pernyataan:

        select var_samp(sal) from emp;

        Hasil berikut dikembalikan:

        +------------+
| _c0        |
+------------+
| 1694209.5588235292 |
+------------+

      • Contoh 2: Gunakan fungsi ini dengan GROUP BY untuk mengelompokkan semua karyawan berdasarkan departemen (deptno) dan hitung varians sampel dari nilai gaji (sal) karyawan dalam grup yang sama. Contoh pernyataan:

        select deptno, var_samp(sal) from emp group by deptno;

        Hasil berikut dikembalikan:

        +------------+------------+
| deptno     | _c1        |
+------------+------------+
| 10         | 2868666.666666667 |
| 20         | 1261875.0  |
| 30         | 446666.6666666667 |
+------------+------------+

    VARIANCE/VAR_POP

    • Sintaksis

      double variance(<colname>)
double var_pop(<colname>)

    • Deskripsi

      Menghitung varians dari kolom numerik tertentu.

    • Parameter

      colname: Wajib. Kolom dengan tipe data numerik. Jika kolom yang ditentukan bukan numerik, nilai null akan dikembalikan. Fungsi ini merupakan bagian tambahan dari MaxCompute V2.0.

    • Nilai Kembali

      Mengembalikan nilai bertipe DOUBLE.

    • Contoh

      • Contoh 1: Hitung varians dari nilai gaji (sal) semua karyawan. Contoh pernyataan:

        select variance(sal) from emp;
-- Pernyataan di atas setara dengan pernyataan berikut: 
select var_pop(sal) from emp;

        Hasil berikut dikembalikan:

        +------------+
| _c0        |
+------------+
| 1594550.1730103805 |
+------------+

      • Contoh 2: Gunakan fungsi ini dengan GROUP BY untuk mengelompokkan semua karyawan berdasarkan departemen (deptno) dan hitung varians dari nilai gaji (sal) karyawan dalam grup yang sama. Contoh pernyataan:

        select deptno, variance(sal) from emp group by deptno;
-- Pernyataan di atas setara dengan pernyataan berikut: 
select deptno, var_pop(sal) from emp group by deptno;

        Hasil berikut dikembalikan:

        +------------+------------+
| deptno     | _c1        |
+------------+------------+
| 10         | 2390555.5555555555 |
| 20         | 1009500.0  |
| 30         | 372222.22222222225 |
+------------+------------+

    WM_CONCAT

    • Sintaksis

      string wm_concat(string <separator>, string <colname>)

    • Deskripsi

      Menggabungkan nilai dalam colname menggunakan pemisah yang ditentukan oleh separator.

    • Parameter

      • separator: Wajib. Pemisah, berupa konstanta bertipe STRING.

      • colname: Wajib. Nilai bertipe STRING. Jika nilai input bertipe BIGINT, DOUBLE, atau DATETIME, nilai tersebut akan dikonversi secara implisit ke tipe STRING sebelum perhitungan.

    • Nilai Kembali (Klausa GROUP BY ditentukan, dan klausa ORDER BY tidak ditentukan.)

      Nilai bertipe STRING dikembalikan. Nilai kembali bervariasi berdasarkan aturan berikut:

      • Jika nilai separator bukan konstanta bertipe STRING, kesalahan akan dikembalikan.

      • Jika nilai colname bukan bertipe STRING, BIGINT, DOUBLE, atau DATETIME, kesalahan akan dikembalikan.

      • Jika nilai colname adalah null, baris yang berisi nilai ini tidak digunakan dalam perhitungan.

      null

      Jika nilai table_name dalam pernyataan select wm_concat(',', name) from table_name; adalah himpunan kosong, null akan dikembalikan.

    • Contoh

      • Contoh 1: Menggabungkan nama (ename) semua karyawan. Contoh pernyataan:

        select wm_concat(',', ename) from emp;

        Hasil berikut dikembalikan:

        +------------+
| _c0        |
+------------+
| SMITH,ALLEN,WARD,JONES,MARTIN,BLAKE,CLARK,SCOTT,KING,TURNER,ADAMS,JAMES,FORD,MILLER,JACCKA,WELAN,TEBAGE |
+------------+

      • Contoh 2: Menggunakan fungsi ini dengan GROUP BY untuk mengelompokkan semua karyawan berdasarkan departemen (deptno) dan menggabungkan nama (ename) karyawan di setiap departemen. Contoh pernyataan:

        select deptno, wm_concat(',', ename) from emp group by deptno order by deptno;

        Hasil berikut dikembalikan:

        +------------+------------+
| deptno     | _c1        |
+------------+------------+
| 10         | CLARK,KING,MILLER,JACCKA,WELAN,TEBAGE |
| 20         | SMITH,JONES,SCOTT,ADAMS,FORD |
| 30         | ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES |
+------------+------------+

      • Contoh 3: Menggunakan fungsi ini dengan GROUP BY untuk mengelompokkan semua karyawan berdasarkan departemen (deptno) dan menggabungkan nilai gaji karyawan di setiap departemen setelah duplikat dihapus. Contoh pernyataan:

        select deptno, wm_concat(distinct ',', sal) from emp group by deptno order by deptno;

        Hasil berikut dikembalikan:

        +------------+------------+
| deptno     | _c1        |
+------------+------------+
| 10         | 1300,2450,5000 |
| 20         | 1100,2975,3000,800 |
| 30         | 1250,1500,1600,2850,950 |
+------------+------------+

      • Contoh 4: Menggunakan fungsi ini dengan GROUP BY dan ORDER BY untuk mengelompokkan semua karyawan berdasarkan departemen (deptno), menggabungkan nilai gaji (sal) semua karyawan di setiap departemen, dan mengurutkan nilai gaji (sal) dalam urutan tertentu. Contoh pernyataan:

        select deptno, wm_concat(',',sal) within group(order by sal) from emp group by deptno order by deptno;

        Hasil berikut dikembalikan:

        +------------+------------+
|deptno|_c1|
+------------+------------+
|10|1300,1300,2450,2450,5000,5000|
|20|800,1100,2975,3000,3000|
|30|950,1250,1250,1500,1600,2850|
+------------+------------+