All Products
Search

This Product

    MaxCompute:Fungsi agregat

    Document Center

    MaxCompute:Fungsi agregat

    Last Updated:Apr 21, 2026

    Fungsi agregat menggabungkan beberapa record input menjadi satu nilai output. Anda dapat menggunakan fungsi agregat bersama klausa group by dalam MaxCompute SQL. Topik ini menjelaskan format perintah, parameter, dan contoh fungsi agregat yang didukung oleh MaxCompute SQL serta memandu Anda dalam pengembangan data menggunakan fungsi-fungsi tersebut.

    Tabel berikut menjelaskan fungsi agregat yang didukung oleh MaxCompute SQL.

    Function

    Features

    ANY

    Memeriksa apakah setidaknya salah satu nilai input bernilai True.

    ANY_VALUE

    Mengembalikan nilai dari rentang yang ditentukan.

    APPROX_DISTINCT

    Mengembalikan perkiraan jumlah nilai input unik 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 kolom tertentu.

    AVG

    Anda dapat menghitung nilai rata-rata.

    BITWISE_AND_AGG

    Mengagregasi nilai input berdasarkan operasi bitwise AND.

    BITWISE_OR_AGG

    Mengagregasi nilai input berdasarkan operasi bitwise OR.

    BITWISE_XOR_AGG

    Mengagregasi nilai input berdasarkan operasi bitwise XOR.

    BOOL_AND

    Menjalankan operasi logika AND pada sekumpulan nilai Boolean.

    BOOL_OR

    Menjalankan operasi logika OR pada sekumpulan nilai Boolean.

    COLLECT_LIST

    Mengagregasi kolom yang ditentukan ke dalam array.

    COLLECT_SET

    Mengagregasi nilai unik dari kolom tertentu ke dalam array.

    CORR

    Menghitung Koefisien Korelasi Pearson dari dua kolom.

    COUNT

    Menghitung jumlah record.

    COUNT_IF

    Mengembalikan jumlah record yang nilai expr-nya bernilai True.

    COVAR_POP

    Menghitung kovarians populasi dari dua kolom numerik yang ditentukan.

    COVAR_SAMP

    Menghitung kovarians sampel dari dua kolom numerik yang ditentukan.

    HISTOGRAM

    Mengembalikan map yang berisi jumlah kemunculan setiap nilai input.

    MAP_AGG

    Anda dapat membuat Map dari dua bidang input.

    MAP_UNION

    Mengembalikan map baru yang merupakan gabungan dari semua map input.

    MAP_UNION_SUM

    Mengembalikan map baru yang merupakan gabungan dari semua map input. Map hasil menjumlahkan nilai-nilai dari kunci yang sama di semua map input.

    MAX

    Anda dapat menghitung nilai maksimum.

    MAX_BY

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

    MEDIAN

    Anda dapat menghitung median.

    MIN

    Menghitung nilai minimum.

    MIN_BY

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

    MULTIMAP_AGG

    Mengembalikan map yang dibuat menggunakan a dan b. a adalah kunci dalam map. b digunakan untuk membuat array, yang digunakan sebagai nilai kunci dalam map.

    NUMERIC_HISTOGRAM

    Mengembalikan histogram perkiraan berdasarkan kolom tertentu.

    PERCENTILE

    Menghitung persentil eksak. Fungsi ini cocok untuk skenario dengan volume data kecil.

    PERCENTILE_APPROX

    Mengembalikan persentil perkiraan. Fungsi ini berlaku untuk skenario dengan volume data besar.

    PERCENTILE_CONT

    Menghitung persentil eksak.

    PERCENTILE_DISC

    Menghitung nilai persentil tertentu.

    STDDEV

    Mengembalikan simpangan baku populasi dari semua nilai input.

    STDDEV_SAMP

    Mengembalikan deviasi standar sampel dari semua nilai input.

    SUM

    Mengembalikan jumlah dari suatu kolom.

    VAR_SAMP

    Menghitung varians sampel dari kolom numerik tertentu.

    VARIANCE/VAR_POP

    Menghitung varians dari kolom numerik tertentu.

    WM_CONCAT

    Menggabungkan string dengan delimiter tertentu.

    Perhatian

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

    • Tingkat sesi: Untuk menggunakan tipe data baru, tambahkan pernyataan set odps.sql.type.system.odps2=true; sebelum pernyataan SQL Anda dan kirimkan keduanya secara bersamaan untuk dieksekusi.

    • Pemilik Proyek dapat mengonfigurasi pengaturan di tingkat proyek sesuai kebutuhan. Perubahan akan berlaku dalam waktu 10 hingga 15 menit. Perintahnya sebagai berikut.

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

      Untuk informasi selengkapnya tentang setproject, lihat Operasi proyek. Untuk informasi selengkapnya tentang pertimbangan saat mengaktifkan tipe data di tingkat proyek, lihat Versi tipe data.

    • Satu worker dapat berisi maksimal 2 juta elemen.

    Jika Anda menggunakan pernyataan SQL yang mencakup beberapa fungsi agregat dan sumber daya proyek tidak mencukupi, overflow memori dapat terjadi. Kami menyarankan Anda mengoptimalkan pernyataan SQL atau membeli sumber daya komputasi sesuai kebutuhan.

    Sintaksis fungsi agregat

    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 spesifik bergantung pada sintaksis fungsi agregat tersebut.

    • WITHIN GROUP (ORDER BY <col1>[,<col2>…]): Jika fungsi agregat berisi ekspresi ini, data input dari <col1>[,<col2>…] diurutkan secara ascending secara default. Untuk mengurutkan data secara descending, gunakan ekspresi WITHIN GROUP (ORDER BY <col1>[,<col2>…] DESC).

      Perhatikan hal berikut saat menggunakan ekspresi ini:

      • Anda hanya dapat menggunakan ekspresi ini untuk WM_CONCAT, COLLECT_LIST, COLLECT_SET, dan UDAF.

      • Jika beberapa fungsi agregat dalam pernyataan SELECT berisi ekspresi WITHIN GROUP (ORDER BY <col1>[,<col2>…]), klausa ORDER BY <col1>[,<col2>…] harus identik di semua fungsi tersebut.

      • Jika parameter fungsi agregat mencakup kata kunci DISTINCT, hanya kolom DISTINCT yang dapat digunakan dalam klausa ORDER BY <col1>[,<col2>…]. Kumpulan kolom dalam klausa ORDER BY harus merupakan subset dari kolom DISTINCT. Selain itu, tipe data bidang dalam <col1>[,<col2>…] harus sesuai dengan tipe data parameter input fungsi agregat.

        Catatan

        Fungsi agregat yang mendukung ekspresi WITHIN GROUP (ORDER BY <col1>[,<col2>…]) hanya menerima satu parameter input. Oleh karena itu, jika fungsi agregat menggunakan kata kunci DISTINCT, klausa ORDER BY hanya dapat mencakup satu kolom, dan tipe datanya harus sesuai dengan tipe data parameter input fungsi agregat.

        Sebagai contoh, parameter input fungsi WM_CONCAT harus bertipe STRING, sehingga bidang setelah klausa ORDER BY juga harus bertipe STRING. Untuk informasi selengkapnya, lihat Contoh 4 di bawah. Untuk detail pembuatan tabel emp yang digunakan dalam contoh, lihat Data sampel.

      Contoh:

      -- Contoh 1: Urutkan data input secara ascending lalu kembalikan 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 descending lalu kembalikan 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
-- Karena parameter fungsi agregat berisi kata kunci DISTINCT, parameter input sal bertipe BIGINT dalam fungsi wm_concat dikonversi secara implisit ke tipe STRING.
-- Agar konsisten dengan tipe parameter input fungsi wm_concat, Anda harus menggunakan cast untuk mengonversi sal ke tipe STRING dalam `order by sal`. Jika tidak, error 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 berisi ekspresi ini, fungsi tersebut hanya memproses data yang memenuhi <where_condition>. Untuk informasi selengkapnya tentang <where_condition>, lihat Klausa WHERE (where_condition).

      Perhatikan hal berikut saat menggunakan ekspresi ini:

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

      • count(*) mendukung ekspresi [FILTER (WHERE <where_condition>)].

      • 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 memfilter dan mengagregasi 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

    Untuk membantu Anda memahami cara menggunakan setiap fungsi, topik ini menyediakan data sumber dan contoh fungsi berdasarkan data tersebut. Perintah contoh berikut membuat tabel bernama emp dan menambahkan data ke dalamnya:

    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 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 sebagai gantinya.

    • Sintaksis

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

    • Deskripsi

      Semua fungsi agregat mendukung ekspresi filter. Jika Anda menentukan kondisi filter, hanya data baris yang memenuhi kondisi filter yang dapat diteruskan ke fungsi agregat terkait untuk pemrosesan data.

    • Deskripsi.

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

      • expression: wajib. Parameter fungsi agregat yang Anda pilih. Tentukan parameter ini berdasarkan deskripsi fungsi agregat yang Anda pilih.

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

    • Deskripsi nilai kembalian.

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

    • Contoh penggunaan

      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

    • Sintaksis

      BOOLEAN ANY(BOOLEAN <colname>)

    • Deskripsi

      Mengagregasi nilai-nilai dalam kolom yang ditentukan oleh colname ke dalam array dan memeriksa apakah setidaknya satu elemen bernilai TRUE. Jika setidaknya satu nilai bernilai TRUE, fungsi mengembalikan TRUE.

    • Deskripsi.

      colname: Wajib. Kolom harus bertipe BOOLEAN.

    • Nilai kembali

      Mengembalikan nilai bertipe BOOLEAN. Jika nilai colname adalah NULL, baris tersebut dikecualikan dari perhitungan.

    • Contoh

      -- Mengembalikan true.
SELECT ANY(colname) FROM VALUES (true), (false), (false) AS tab(colname);
-- Mengembalikan true.
SELECT ANY(colname) FROM VALUES (NULL), (true), (false) AS tab(colname);
-- Mengembalikan false.
SELECT ANY(colname) FROM VALUES (false), (false), (NULL) AS tab(colname);
-- Mengembalikan true.
SELECT ANY(colname1) FILTER(WHERE colname2 = 2) FROM VALUES (true, 1), (false, 1), (true, 2) AS tab(colname1, colname2);

    ANY_VALUE

    • Sintaksis

      any_value(<colname>)

    • Deskripsi

      Fungsi ekstensi MaxCompute V2.0 ini mengembalikan nilai arbitrer dari rentang yang ditentukan.

    • Parameter

      colname: Wajib. Kolom dapat bertipe 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 ini tidak digunakan untuk perhitungan.

    • Contoh

      • Contoh 1: Pilih salah satu karyawan. Pernyataan contoh:

        select any_value(ename) from emp;

        Hasil berikut dikembalikan:

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

      • Contoh 2: Gunakan fungsi ini dengan group by untuk mengelompokkan semua karyawan berdasarkan departemen (deptno) dan memilih karyawan acak dari setiap kelompok. Perintah contoh:

        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 Perintah

      Mengembalikan perkiraan jumlah nilai input unik dalam kolom tertentu. Fungsi ini merupakan fungsi tambahan MaxCompute V2.0.

    • Deskripsi

      colname: wajib. Nama kolom tempat duplikat perlu dihapus.

    • Nilai kembali

      Nilai bertipe BIGINT dikembalikan. Fungsi ini menghasilkan kesalahan standar sebesar 5%. Jika nilai kolom yang ditentukan oleh parameter colname adalah null, baris yang berisi nilai ini tidak digunakan untuk perhitungan.

    • Contoh

      • Contoh 1: Hitung perkiraan jumlah nilai unik dalam kolom sal. Pernyataan contoh:

        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 menghitung perkiraan jumlah nilai gaji (sal) unik dalam setiap kelompok. Perintah contoh:

        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 perintah.

      Menemukan baris yang berisi nilai valueToMaximize dan mengembalikan nilai valueToReturn pada baris tersebut. Fungsi ini merupakan fungsi tambahan MaxCompute V2.0.

    • Deskripsi

      • valueToMaximize: Wajib. Parameter ini menerima tipe data apa pun.

      • valueToReturn: Wajib. Parameter ini menerima nilai bertipe data apa pun.

    • Nilai kembali

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

    • Contoh

      • Contoh 1: Kembalikan nama karyawan dengan gaji tertinggi. Pernyataan contoh:

        select arg_max(sal, ename) from emp;

        Hasil berikut dikembalikan:

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

      • Contoh 2: Gunakan fungsi ini dengan group by untuk mengelompokkan semua karyawan berdasarkan departemen (deptno) dan mengembalikan nama karyawan dengan gaji tertinggi di setiap kelompok. Perintah contoh:

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

        Hasil berikut dikembalikan:

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

    ARG_MIN

    • Sintaksis

      arg_min(<valueToMinimize>, <valueToReturn>)

    • Deskripsi

      Menemukan baris yang berisi nilai minimum valueToMinimize dan mengembalikan nilai valueToReturn pada baris tersebut. Fungsi ini merupakan fungsi tambahan MaxCompute V2.0.

    • Deskripsi

      • valueToMinimize: wajib. Nilai bertipe data apa pun.

      • valueToReturn: wajib. Nilai bertipe data apa pun.

    • Nilai kembali dijelaskan di bawah ini.

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

    • Contoh

      • Contoh 1: Kembalikan nama karyawan dengan gaji terendah. Pernyataan contoh:

        select arg_min(sal, ename) from emp;

        Hasil berikut dikembalikan:

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

      • Contoh 2: Gunakan fungsi ini dengan group by untuk mengelompokkan semua karyawan berdasarkan departemen (deptno) dan mengembalikan nama karyawan dengan gaji terendah di setiap kelompok. Perintah contoh:

        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

      Menghitung nilai rata-rata.

    • Deskripsi.

      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 ini tidak digunakan untuk perhitungan. Tabel berikut menjelaskan pemetaan antara tipe data input dan tipe 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) semua karyawan. Pernyataan contoh:

        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 menghitung rata-rata gaji (sal) untuk setiap departemen. Perintah contoh:

        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

      Mengagregasi nilai input berdasarkan operasi bitwise AND.

    • Deskripsi.

      value: Wajib. Nilai bertipe BIGINT. Nilai NULL dikecualikan dari perhitungan.

    • Bagian ini menjelaskan nilai kembalian.

      Nilai bertipe BIGINT dikembalikan.

    • 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

    • Ini adalah deklarasi fungsi.

      bigint bitwise_or_agg(bigint value)

    • Deskripsi

      Mengagregasi nilai input berdasarkan operasi bitwise OR.

    • Deskripsi Metrik

      value: wajib. Nilai bertipe BIGINT. Nilai null tidak digunakan untuk perhitungan.

    • Deskripsi nilai kembalian.

      Nilai bertipe BIGINT dikembalikan.

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

    BITWISE_XOR_AGG

    • Deklarasi fungsi.

      BIGINT BITWISE_XOR_AGG(BIGINT|INT|SMALLINT|TINYINT value)

    • Deskripsi

      Mengagregasi nilai input menggunakan operasi bitwise XOR.

    • Deskripsi Metrik

      value: Wajib. Nilai bertipe BIGINT, INT, SMALLINT, atau TINYINT. Nilai NULL dikecualikan dari perhitungan.

    • Nilai kembali

      Mengembalikan nilai bertipe BIGINT. Aturan berikut berlaku:

      • Jika value bukan bertipe BIGINT, INT, SMALLINT, atau TINYINT, error dikembalikan.

      • Mengembalikan NULL jika value adalah NULL.

    • Contoh

      SELECT id, bitwise_xor_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       | 
+------------+------------+

    BOOL_AND

    • Sintaksis

      BOOLEAN BOOL_AND(<colname>)

    • Deskripsi perintah.

      Mengagregasi nilai-nilai dalam kolom yang ditentukan oleh colname ke dalam array dan melakukan operasi logika AND pada nilai Boolean.

    • Parameter

      colname: Wajib. Nama kolom tabel. Kolom harus bertipe BOOLEAN.

    • Nilai kembali

      Mengembalikan nilai bertipe BOOLEAN. Aturan berikut berlaku:

      • Jika semua nilai input bernilai true, fungsi mengembalikan true. Jika tidak, mengembalikan false.

      • Fungsi BOOL_AND() mengabaikan nilai NULL dalam kelompok.

    • Contoh

      -- Contoh 1: Lakukan operasi logika AND sederhana.
SELECT bool_and(colname) FROM VALUES (true), (false), (true) AS tab(colname);
-- Hasil berikut dikembalikan.
+------+
| _c0  | 
+------+
| false | 
+------+

-- Contoh 2: Fungsi BOOL_AND() mengabaikan nilai NULL dalam kelompok.
SELECT bool_and(colname) FROM VALUES (NULL), (true), (true) AS tab(colname);
-- Hasil berikut dikembalikan.
+------+
| _c0  | 
+------+
| true | 
+------+

-- Contoh 3: Agregasi hanya kolom tertentu.
SELECT bool_and(colname1) FROM VALUES (true, 1), (false, 2), (true, 1) AS tab(colname1, colname2);
-- Hasil berikut dikembalikan.
+------+
| _c0  | 
+------+
| false | 
+------+

-- Contoh 4: Lakukan operasi logika AND setelah pemfilteran.
SELECT bool_and(colname1) FILTER(WHERE colname2 = 1) FROM VALUES (true, 1), (false, 2), (true, 1) AS tab(colname1, colname2);
-- Hasil berikut dikembalikan.
+------+
| _c0  | 
+------+
| true | 
+------+

    BOOL_OR

    • Sintaksis

      BOOLEAN BOOL_OR(BOOLEAN <colname>)

    • Topik ini menjelaskan perintah tersebut.

      Mengagregasi nilai-nilai dalam kolom yang ditentukan oleh colname ke dalam array dan melakukan operasi logika OR pada nilai Boolean.

    • Detail Metrik

      colname: Wajib. Nama kolom tabel. Kolom harus bertipe BOOLEAN.

    • Nilai kembali

      Mengembalikan nilai bertipe BOOLEAN. Aturan berikut berlaku:

      • Jika setidaknya satu nilai input dalam kelompok bernilai true, fungsi mengembalikan true. Jika semua nilai bernilai false, fungsi mengembalikan false.

      • Fungsi BOOL_OR() mengabaikan nilai NULL dalam kelompok.

    • Contoh

      -- Contoh 1: Lakukan operasi logika OR sederhana.
SELECT bool_or(colname) FROM VALUES (true), (false), (false) AS tab(colname);
-- Hasil berikut dikembalikan.
+------+
| _c0  | 
+------+
| true | 
+------+

-- Contoh 2: Fungsi BOOL_OR() mengabaikan nilai NULL dalam kelompok.
SELECT bool_or(colname) FROM VALUES (NULL), (true), (false) AS tab(colname);
-- Hasil berikut dikembalikan.
+------+
| _c0  | 
+------+
| true | 
+------+

-- Contoh 3
SELECT bool_or(colname1) FROM VALUES (false), (false), (NULL) AS tab(colname1);
-- Hasil berikut dikembalikan.
+------+
| _c0  | 
+------+
| false | 
+------+

-- Contoh 4: Lakukan operasi logika OR setelah pemfilteran.
SELECT bool_or(colname1) FILTER(WHERE colname2 = 1) FROM VALUES (true, 1), (false, 1), (true, 2) AS tab(colname1, colname2);
-- Hasil berikut dikembalikan.
+------+
| _c0  | 
+------+
| true | 
+------+

    COLLECT_LIST

    • Sintaksis

      array collect_list(<colname>)

    • Deskripsi

      Mengagregasi nilai-nilai dalam kolom yang ditentukan oleh colname ke dalam array. Fungsi ini merupakan ekstensi yang disediakan oleh MaxCompute V2.0.

    • Parameter

      colname: Wajib. Nama kolom tabel. Kolom dapat bertipe data apa pun.

    • Nilai kembali

      Nilai bertipe ARRAY dikembalikan. Jika nilai kolom yang ditentukan oleh colname adalah null, baris yang berisi nilai ini tidak digunakan untuk perhitungan.

    • Contoh

      • Contoh 1: Agregasi nilai gaji (sal) semua karyawan ke dalam array. Pernyataan contoh:

        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: Gunakan fungsi ini dengan group by untuk mengelompokkan semua karyawan berdasarkan departemen (deptno) dan mengagregasi gaji (sal) karyawan dalam kelompok yang sama ke dalam array. Perintah contoh:

        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: Gunakan fungsi ini dengan group by untuk mengelompokkan semua karyawan berdasarkan departemen (deptno) dan mengagregasi nilai gaji (sal) unik karyawan dalam kelompok yang sama ke dalam array. Perintah contoh:

        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

      Mengagregasi nilai-nilai yang ditentukan oleh colname ke dalam array yang hanya berisi nilai unik. Fungsi ini merupakan fungsi tambahan MaxCompute V2.0.

    • Deskripsi

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

    • Nilai kembali

      Nilai bertipe ARRAY dikembalikan. Jika nilai kolom yang ditentukan oleh colname adalah null, baris yang berisi nilai ini tidak digunakan untuk perhitungan.

    • Contoh

      • Contoh 1: Agregasi nilai gaji (sal) semua karyawan ke dalam array yang hanya berisi nilai unik. Pernyataan contoh:

        select collect_set(sal) from emp;

        Hasil berikut dikembalikan:

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

      • Contoh 2: Gunakan fungsi ini dengan group by untuk mengelompokkan semua karyawan berdasarkan departemen (deptno) dan mengagregasi gaji (sal) karyawan dalam kelompok yang sama ke dalam array nilai unik. Perintah contoh:

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

    CORR

    • Sintaksis

      double corr(<col1>, <col2>)

    • Deskripsi

      Menghitung Koefisien Korelasi Pearson dari dua kolom data. Ini merupakan fungsi ekstensi dalam MaxCompute V2.0.

    • Parameter

      col1 dan col2: Wajib. Nama dua kolom dalam tabel yang ingin Anda hitung Koefisien Korelasi Pearson-nya. Kolom harus bertipe DOUBLE, BIGINT, INT, SMALLINT, TINYINT, FLOAT, atau DECIMAL. Tipe data col1 dan col2 dapat berbeda.

    • Nilai kembali

      Nilai bertipe DOUBLE dikembalikan. Jika baris dalam kolom input berisi nilai NULL, baris tersebut tidak digunakan dalam perhitungan.

    • Contoh

      Berdasarkan data sampel, perintah berikut menghitung Koefisien Korelasi Pearson dari kolom double_data dan float_data.

      select corr(double_data,float_data) from mf_math_fun_t;

      Nilai kembali adalah 1.0.

    COUNT

    Sintaksis

    -- Hitung jumlah record.
BIGINT COUNT([DISTINCT|ALL] <colname>)

-- Hitung jumlah record dalam window.
BIGINT COUNT(*) OVER ([partition_clause] [orderby_clause] [frame_clause])
BIGINT COUNT([DISTINCT] <expr>[,...]) OVER ([partition_clause] [orderby_clause] [frame_clause])

    Parameter

    • DISTINCT|ALL: Opsional. Menentukan apakah record duplikat dihapus sebelum penghitungan. Nilai default adalah ALL, yang menghitung semua record. Jika Anda menentukan DISTINCT, fungsi hanya menghitung record unik.

    • colname: Wajib. Kolom yang nilainya ingin Anda hitung. Parameter ini dapat bertipe data apa pun. Anda dapat menentukan * untuk colname, seperti dalam COUNT(*). Dalam kasus ini, fungsi mengembalikan jumlah total baris. Baris tempat nilai colname adalah NULL tidak termasuk dalam perhitungan.

    • expr: Wajib. Ekspresi. Parameter ini dapat bertipe data apa pun. Baris tempat ekspresi mengevaluasi ke NULL tidak termasuk dalam perhitungan. Jika Anda menentukan kata kunci DISTINCT, fungsi mengembalikan jumlah nilai unik.

      COUNT([DISTINCT] <expr>[,...]): Menghitung jumlah baris dalam window tertentu tempat nilai semua ekspresi yang ditentukan bukan NULL. Jika Anda menentukan kata kunci DISTINCT, fungsi menghitung baris setelah menghapus baris duplikat.

    • partition_clause, orderby_clause, dan frame_clause: Untuk informasi selengkapnya, lihat windowing_definition.

    Nilai kembali

    Fungsi mengembalikan nilai BIGINT. Baris tempat colname adalah NULL tidak termasuk dalam perhitungan.

    Contoh

    Persiapkan data uji

    Jika Anda sudah memiliki data, Anda dapat melewati langkah ini.

    1. Unduh data uji test_data.txt.

    2. Buat tabel uji.

      CREATE TABLE IF NOT EXISTS emp(
  empno BIGINT,
  ename STRING,
  job STRING,
  mgr BIGINT,
  hiredate DATETIME,
  sal BIGINT,
  comm BIGINT,
  deptno BIGINT
);

    3. Muat data.

      Ganti FILE_PATH dengan path dan nama file data yang sebenarnya.

      TUNNEL UPLOAD FILE_PATH emp;

    Contoh 1: Tentukan kolom untuk window dan kembalikan jumlah kumulatif tanpa pengurutan

    Contoh ini mempartisi window berdasarkan kolom gaji (sal). Data tidak diurutkan. Fungsi mengembalikan jumlah kumulatif semua baris dalam window saat ini. Window berisi semua baris yang memiliki nilai sal yang sama.

    • Perintah contoh

      SELECT sal, COUNT(sal) OVER (PARTITION BY sal) AS count FROM emp;

    • Hasil

      +------------+------------+
| sal        | count      | 
+------------+------------+
| 800        | 1          | 
| 950        | 1          | 
| 1100       | 1          | 
| 1250       | 2          |  -- Partisi berisi dua baris dengan sal 1250. Fungsi mengembalikan 2 untuk kedua baris.
| 1250       | 2          |  -- Jumlah juga 2 untuk baris kedua dalam partisi.
| 1300       | 2          | 
| 1300       | 2          | 
| 1500       | 1          | 
| 1600       | 1          | 
| 2450       | 2          | 
| 2450       | 2          | 
| 2850       | 1          | 
| 2975       | 1          | 
| 3000       | 2          | 
| 3000       | 2          | 
| 5000       | 2          | 
| 5000       | 2          | 
+------------+------------+

    Contoh 2: Dalam mode non-Hive compatible, tentukan kolom untuk window dan kembalikan jumlah kumulatif setelah pengurutan

    Dalam mode non-Hive compatible, contoh ini mempartisi window berdasarkan kolom gaji (sal) dan mengurutkan data. Fungsi mengembalikan jumlah kumulatif dari baris pertama hingga baris saat ini dalam window saat ini. Window berisi semua baris yang memiliki nilai sal yang sama.

    • Perintah contoh

      -- Nonaktifkan mode Hive compatible.
SET odps.sql.hive.compatible=false;

SELECT sal, COUNT(sal) OVER (PARTITION BY sal ORDER BY sal) AS count FROM emp;

    • Nilai kembali

      +------------+------------+
| sal        | count      |
+------------+------------+
| 800        | 1          |
| 950        | 1          |
| 1100       | 1          |
| 1250       | 1          |   -- Partisi dimulai. Jumlah berjalan untuk baris pertama adalah 1.
| 1250       | 2          |   -- Jumlah berjalan untuk baris kedua adalah 2.
| 1300       | 1          |
| 1300       | 2          |
| 1500       | 1          |
| 1600       | 1          |
| 2450       | 1          |
| 2450       | 2          |
| 2850       | 1          |
| 2975       | 1          |
| 3000       | 1          |
| 3000       | 2          |
| 5000       | 1          |
| 5000       | 2          |
+------------+------------+

    Contoh 3: Dalam mode Hive compatible, tentukan kolom untuk window dan kembalikan jumlah kumulatif setelah pengurutan

    Dalam mode Hive compatible, contoh ini mempartisi window berdasarkan kolom gaji (sal) dan mengurutkan data. Fungsi mengembalikan jumlah kumulatif dari baris pertama hingga baris terakhir dalam window saat ini. Window berisi semua baris yang memiliki nilai sal yang sama.

    • Perintah contoh

      -- Aktifkan mode Hive compatible.
SET odps.sql.hive.compatible=true;

SELECT sal, COUNT(sal) OVER (PARTITION BY sal ORDER BY sal) AS count FROM emp;

    • Hasil

      +------------+------------+
| sal        | count      |
+------------+------------+
| 800        | 1          |
| 950        | 1          |
| 1100       | 1          |
| 1250       | 2          |   -- Partisi berisi dua baris dengan sal 1250. Fungsi mengembalikan 2 untuk kedua baris.
| 1250       | 2          |   -- Jumlah juga 2 untuk baris kedua dalam partisi.
| 1300       | 2          |
| 1300       | 2          |
| 1500       | 1          |
| 1600       | 1          |
| 2450       | 2          |
| 2450       | 2          |
| 2850       | 1          |
| 2975       | 1          |
| 3000       | 2          |
| 3000       | 2          |
| 5000       | 2          |
| 5000       | 2          |
+------------+------------+

    Contoh 4: Kembalikan jumlah total baris

    Hitung jumlah total karyawan di semua departemen.

    • Perintah contoh

      SELECT COUNT(*) FROM emp;

    • Hasil

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

    Contoh 5: Kelompokkan data dan hitung total untuk setiap kelompok

    Contoh ini menggunakan fungsi COUNT dengan GROUP BY untuk mengelompokkan semua karyawan berdasarkan departemen (deptno) dan menghitung jumlah karyawan di setiap departemen.

    • Perintah contoh

      SELECT deptno, COUNT(*) FROM emp GROUP BY deptno;

    • Hasil

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

    Contoh 6: Hitung nilai unik

    Contoh ini menggunakan DISTINCT untuk menghapus nilai duplikat dan menghitung jumlah departemen.

    • Perintah contoh

      SELECT COUNT(DISTINCT deptno) FROM emp;

    • Nilai kembali

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

    COUNT_IF

    • Sintaksis

      bigint count_if(boolean <expr>)

    • Deskripsi

      Mengembalikan jumlah record yang nilai expr-nya bernilai True.

    • Parameter

      expr: wajib. Ekspresi BOOLEAN.

    • Nilai kembali

      Nilai bertipe BIGINT dikembalikan. Jika nilai parameter expr adalah False atau nilai kolom tertentu dalam expr adalah null, baris yang berisi nilai ini tidak digunakan untuk perhitungan.

    • Contoh

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

      Hasil berikut dikembalikan:

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

    COVAR_POP

    • Sintaksis

      double covar_pop(<colname1>, <colname2>)

    • Deskripsi

      Menghitung kovarians populasi dari dua kolom numerik yang ditentukan. Fungsi ini merupakan fungsi tambahan MaxCompute V2.0.

    • Deskripsi Parameter

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

    • Contoh

      Jalankan perintah 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 kolom sal dan sal_new. Pernyataan contoh:

        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 menghitung kovarians populasi kolom sal dan sal_new untuk setiap kelompok. Perintah contoh:

        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 yang ditentukan. Fungsi ini merupakan fungsi tambahan MaxCompute V2.0.

    • Deskripsi

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

    • Contoh

      Jalankan perintah 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 kolom sal dan sal_new. Pernyataan contoh:

        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 menghitung kovarians sampel kolom sal dan sal_new untuk setiap kelompok. Perintah contoh:

        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

    • Mendeklarasikan fungsi.

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

    • Menjelaskan perintah.

      Mengembalikan map yang berisi jumlah kemunculan setiap nilai input. Kunci dalam map adalah nilai input. Setiap nilai dalam map adalah jumlah kemunculan nilai input tersebut. Nilai null diabaikan.

    • Deskripsi

      input: nilai input, yang digunakan sebagai kunci dalam map.

    • Nilai kembali

      Map yang berisi jumlah kemunculan setiap nilai input dikembalikan.

    • 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

    • Deklarasi Fungsi

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

    • Topik ini menjelaskan perintah tersebut.

      Mengembalikan map yang dibuat menggunakan a dan b. a adalah kunci dalam map. b adalah nilai kunci dalam map. Jika kunci dalam map adalah null, kunci tersebut diabaikan. Jika bidang kunci memiliki nilai duplikat, salah satu nilai dipertahankan secara acak.

    • Deskripsi.

      • a: bidang input yang digunakan sebagai kunci dalam map.

      • b: bidang input yang digunakan sebagai nilai dalam map.

    • Deskripsi nilai kembalian.

      Map baru dikembalikan.

    • 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

    • Deklarasi Fungsi

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

    • Bagian ini menjelaskan perintah-perintah tersebut.

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

    • Parameter

      input: map input.

    • Deskripsi nilai kembalian.

      Map baru 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 dikembalikan:

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

    MAP_UNION_SUM

    • Deklarasi Fungsi

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

    • Deskripsi

      Mengembalikan map baru yang merupakan gabungan dari semua map input. Map output menjumlahkan nilai-nilai dari kunci yang sama di semua map input. Jika nilai yang sesuai dengan kunci adalah NULL, nilai tersebut dikonversi menjadi 0.

      Catatan

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

    • Deskripsi parameter.

      input: map input.

    • Deskripsi nilai kembalian.

      Map baru dikembalikan.

      Catatan

      Nilai dalam map 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} |
+----------------------------+

    MAX

    • Sintaksis

      max(<colname>)

    • Deskripsi

      Mengembalikan nilai maksimum kolom.

    • Deskripsi parameter.

      colname: wajib. Nama kolom, yang dapat bertipe data apa pun kecuali BOOLEAN.

    • Nilai kembali

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

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

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

    • Contoh

      • Contoh 1: Hitung gaji tertinggi (sal) semua karyawan. Pernyataan contoh:

        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 menghitung gaji tertinggi (sal) di setiap departemen. Perintah contoh:

        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

      Catatan

      Fungsi MAX_BY menyediakan fitur yang sama dengan fungsi ARG_MAX. Perbedaannya terletak pada urutan parameternya. Fungsi MAX_BY diperkenalkan dalam MaxCompute untuk menjaga kompatibilitas dengan sintaksis open source.

      Menemukan baris yang berisi nilai valueToMaximize dan mengembalikan nilai valueToReturn pada baris tersebut. Fungsi ini merupakan fungsi tambahan MaxCompute V2.0.

    • Deskripsi Parameter

      • valueToMaximize: wajib. Nilai bertipe data apa pun.

      • valueToReturn: wajib. Nilai bertipe 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 dikembalikan secara acak. Jika nilai valueToMaximize adalah null, baris yang berisi nilai ini tidak digunakan untuk perhitungan.

    • Contoh

      • Contoh 1: Kembalikan nama karyawan dengan gaji tertinggi. Pernyataan contoh:

        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 departemen (deptno) dan mengembalikan nama karyawan dengan gaji tertinggi di setiap kelompok. Perintah contoh:

        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 kolom.

    • Deskripsi

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

    • Nilai kembali

      Jika nilai colname adalah null, baris yang berisi nilai ini tidak digunakan untuk perhitungan. Tabel berikut menjelaskan pemetaan antara tipe data input dan tipe 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) semua karyawan. Pernyataan contoh:

        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 menghitung median gaji (sal) untuk setiap departemen. Perintah contoh:

        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 perintah.

      Menghitung nilai minimum.

    • Parameter

      colname: wajib. Nama kolom, yang dapat bertipe data apa pun kecuali BOOLEAN.

    • Nilai kembali

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

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

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

    • Contoh

      • Contoh 1: Hitung gaji terendah (sal) semua karyawan. Pernyataan contoh:

        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 menghitung gaji terendah (sal) di setiap departemen. Perintah contoh:

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

        Hasil berikut dikembalikan:

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

    MIN_BY

    • Sintaksis

      min_by(<valueToReturn>,<valueToMinimize>)

    • Deskripsi

      Catatan

      Fungsi MIN_BY menyediakan fitur yang sama dengan fungsi ARG_MIN. Namun, fungsi-fungsi ini berbeda dalam urutan parameternya. Fungsi MIN_BY diperkenalkan dalam MaxCompute untuk menjaga kompatibilitas dengan sintaksis open source.

      Menemukan baris yang berisi nilai minimum valueToMinimize dan mengembalikan nilai valueToReturn pada baris tersebut. Fungsi ini merupakan fungsi tambahan MaxCompute V2.0.

    • Deskripsi Parameter

      • valueToMinimize: wajib. Nilai bertipe data apa pun.

      • valueToReturn: wajib. Nilai bertipe data apa pun.

    • Deskripsi nilai kembalian.

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

    • Contoh

      • Contoh 1: Kembalikan nama karyawan dengan gaji terendah. Pernyataan contoh:

         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 departemen (deptno) dan mengembalikan nama karyawan dengan gaji terendah di setiap kelompok. Perintah contoh:

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

        Hasil berikut dikembalikan:

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

    MULTIMAP_AGG

    • Deklarasi Fungsi

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

    • Deskripsi

      Mengembalikan map yang dibuat menggunakan a dan b. a adalah kunci dalam map. b digunakan untuk membuat array, yang digunakan sebagai nilai kunci dalam map. Jika kunci dalam map adalah null, kunci tersebut diabaikan.

    • Parameter

      • a: bidang input yang digunakan sebagai kunci dalam map.

      • b: bidang input yang digunakan sebagai nilai dalam map. Bidang yang sesuai dengan kunci yang sama ditempatkan dalam array yang sama dan digunakan sebagai nilai dalam map.

    • Deskripsi nilai kembalian.

      Map baru dikembalikan.

    • 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 fungsi tambahan MaxCompute V2.0.

    • Deskripsi Parameter

      • buckets: wajib. Nilai bertipe BIGINT. Parameter ini menentukan jumlah maksimum bucket dalam kolom yang histogram perkiraannya dikembalikan.

      • colname: wajib. Nilai bertipe DOUBLE. Parameter ini menentukan kolom yang histogram perkiraannya perlu dihitung.

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

    • Deskripsi nilai kembalian.

      Mengembalikan nilai bertipe map<double key, double value>. Dalam nilai kembali, kunci merepresentasikan koordinat sumbu-x histogram perkiraan, dan nilai merepresentasikan perkiraan tinggi pada sumbu-y. Aturan berikut berlaku:

      • Jika nilai buckets adalah null, null dikembalikan.

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

    • Contoh

      • Kembalikan histogram perkiraan kolom sal. Pernyataan contoh:

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

      • Hitung histogram perkiraan untuk kolom gaji (sal), di mana deptno di setiap baris merepresentasikan bobot departemen. Perintah contoh:

        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>)
-- Kembalikan beberapa persentil eksak sebagai array.
array percentile(bigint <colname>, array(<p1> [, <p2>...]))

    • Deskripsi

      Menghitung persentil eksak. Fungsi ini cocok untuk volume data kecil. Fungsi ini pertama-tama mengurutkan kolom yang ditentukan secara ascending lalu mengambil persentil ke-p yang eksak. p harus berada di antara 0 dan 1. Perhitungan percentile dimulai dari indeks 0. Sebagai contoh, jika kolom berisi nilai 100, 200, dan 300, indeksnya adalah 0, 1, dan 2. Untuk menghitung persentil 0,3, hasil percentile adalah 2 × 0,3 = 0,6. Artinya, nilai berada di antara indeks 0 dan 1. Hasilnya adalah 100 + (200 - 100) × 0,6 = 160. Fungsi ini merupakan fungsi ekstensi MaxCompute V2.0.

    • Deskripsi

      • colname: wajib. Kolom bertipe BIGINT.

      • p: Wajib. Persentil yang ditentukan, yang harus berada dalam rentang [0.0, 1.0].

    • Deskripsi nilai kembalian.

      Nilai bertipe DOUBLE atau ARRAY dikembalikan.

    • Contoh

      • Contoh 1: Perintah berikut menghitung persentil 0,3 dari gaji (sal):

        select percentile(sal, 0.3) from emp;

        Hasil berikut dikembalikan:

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

      • Contoh 2: Gunakan fungsi ini dengan group by untuk mengelompokkan semua karyawan berdasarkan departemen (deptno) dan menghitung persentil 0,3 dari gaji (sal) untuk setiap kelompok. Perintah contoh:

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

        Hasil berikut 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 menghitung persentil 0,3, 0,5, dan 0,8 dari gaji (sal) untuk setiap kelompok. Perintah contoh:

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

        Hasil berikut 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>]))
-- Kembalikan beberapa persentil perkiraan sebagai array.
array<double> percentile_approx (double <colname>
                                 [, double <weight>],
                                 array(<p1> [, <p2>...])
                                 [, <B>])

    • Deskripsi

      Ini adalah fungsi ekstensi untuk MaxCompute V2.0. Perhitungan percentile_approx berbasis indeks 1. Untuk menghitung persentil ke-p untuk kolom dengan n entri data, fungsi percentile_approx pertama-tama mengurutkan kolom secara ascending. Data kolom yang diurutkan diperlakukan sebagai array bernama arr, dan hasil percentile_approx adalah res. Indeks untuk persentil dihitung sebagai index = n * p.

      • Jika index <= 1, maka res = arr[0].

      • Jika index >= n - 1, maka res = arr[n-1].

      • Jika 1 < index < n - 1, hitung diff = index + 0.5 - ceil(index):

        Jika kondisi abs(diff) < 0,5 terpenuhi, res dihitung berdasarkan rumus berikut: res = arr[ceil(index) - 1].

        Jika kondisi abs(diff) = 0,5 terpenuhi, res dihitung berdasarkan rumus berikut: res = arr[index - 1] + (arr[index] - arr[index - 1]) × 0,5.

        Nilai abs(diff) tidak dapat lebih besar dari 0,5.

      Sebagai contoh, jika kolom col berisi nilai 100, 200, 300, dan 400, indeksnya adalah 1, 2, 3, dan 4. Maka:

      • 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).

      Catatan

      Perbedaan antara percentile_approx dan percentile adalah sebagai berikut:

      • percentile_approx menghitung persentil perkiraan, sedangkan percentile menghitung persentil eksak. Untuk volume data besar, percentile mungkin gagal karena batasan memori, tetapi percentile_approx tidak memiliki masalah ini.

      • Implementasi percentile_approx konsisten dengan fungsi percentile_approx di Hive. Namun, algoritma perhitungannya berbeda dari percentile. Oleh karena itu, untuk beberapa skenario dengan volume data sangat kecil, hasil percentile_approx mungkin berbeda dari hasil percentile.

    • Deskripsi.

      • colname: wajib. Nama kolom, yang dapat bertipe DOUBLE.

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

      • p: Wajib. Persentil perkiraan, yang harus berada dalam rentang [0.0, 1.0].

      • B: akurasi nilai kembali. Akurasi yang lebih tinggi menunjukkan nilai yang lebih akurat. Jika Anda tidak menentukan parameter ini, nilai 10000 digunakan.

    • Deskripsi nilai kembalian.

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

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

      • Jika nilai p atau B adalah null, error dikembalikan.

    • Contoh

      • Contoh 1: Perintah berikut menghitung persentil 0,3 dari gaji (sal):

        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 menghitung persentil 0,3 dari gaji (sal) untuk setiap kelompok. Perintah contoh:

        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 semua karyawan berdasarkan departemen (deptno) dan menghitung persentil 0,3, 0,5, dan 0,8 dari gaji (sal) untuk setiap kelompok. Perintah contoh:

        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 (dengan bobot): Gunakan fungsi ini dengan group by untuk mengelompokkan semua karyawan berdasarkan departemen (deptno) dan menghitung persentil 0,3, 0,5, dan 0,8 dari gaji (sal) untuk setiap kelompok. Kolom cnt dalam tabel emp merepresentasikan jumlah orang dengan gaji tersebut. Perintah contoh:

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

    PERCENTILE_CONT

    • Sintaksis

      -- Hitung persentil eksak
PERCENTILE_CONT(<col_name>, DOUBLE <percentile>[, BOOLEAN <isIgnoreNull>])

-- Hitung persentil eksak dalam window
PERCENTILE_CONT(<col_name>, DOUBLE <percentile>[, BOOLEAN <isIgnoreNull>]) OVER ([partition_clause] [orderby_clause])

    • Deskripsi

      Menghitung persentil eksak. Fungsi ini menggunakan algoritma interpolasi linear, mengurutkan kolom yang ditentukan secara ascending, dan mengembalikan nilai eksak pada persentil yang ditentukan.

    • Parameter

      • col_name: Wajib. Kolom bertipe DOUBLE atau DECIMAL.

      • percentile: Wajib. Persentil yang dihitung. Konstanta DOUBLE dalam rentang [0, 1].

      • isIgnoreNull: Opsional. Menentukan apakah nilai NULL diabaikan. Konstanta BOOLEAN. Nilai default adalah TRUE. Jika diatur ke FALSE, nilai NULL diperlakukan sebagai nilai minimum selama pengurutan.

      • partition_clause dan orderby_clause: Untuk informasi selengkapnya, lihat Fungsi window.

    • Nilai kembali

      Mengembalikan nilai persentil yang dihitung sebagai DOUBLE.

    • Contoh

      • Contoh 1: Abaikan nilai NULL dan hitung persentil eksak dalam window.

        SELECT
  PERCENTILE_CONT(x, 0) OVER() AS min,
  PERCENTILE_CONT(x, 0.01) OVER() AS percentile1,
  PERCENTILE_CONT(x, 0.5) OVER() AS median,
  PERCENTILE_CONT(x, 0.9) OVER() AS percentile90,
  PERCENTILE_CONT(x, 1) OVER() AS max
FROM VALUES(0D),(3D),(NULL),(1D),(2D) AS tbl(x) LIMIT 1;

-- Hasil kembali
+------------+-------------+------------+--------------+------------+
| min        | percentile1 | median     | percentile90 | max        | 
+------------+-------------+------------+--------------+------------+
| 0.0        | 0.03        | 1.5        | 2.7          | 3.0        | 
+------------+-------------+------------+--------------+------------+

      • Contoh 2: Jangan abaikan nilai NULL. Nilai NULL diperlakukan sebagai nilai minimum selama pengurutan. Hitung persentil eksak dalam window.

        SELECT
  PERCENTILE_CONT(x, 0, false) OVER() AS min,
  PERCENTILE_CONT(x, 0.01, false) OVER() AS percentile1,
  PERCENTILE_CONT(x, 0.5, false) OVER() AS median,
  PERCENTILE_CONT(x, 0.9, false) OVER() AS percentile90,
  PERCENTILE_CONT(x, 1, false) OVER() AS max
FROM VALUES(0D),(3D),(NULL),(1D),(2D) AS tbl(x) LIMIT 1;

-- Hasil kembali
+------------+-------------+------------+--------------+------------+
| min        | percentile1 | median     | percentile90 | max        | 
+------------+-------------+------------+--------------+------------+
| NULL       | 0.0         | 1.0        | 2.6          | 3.0        | 
+------------+-------------+------------+--------------+------------+

    PERCENTILE_DISC

    • Sintaksis

      -- Hitung nilai persentil tertentu
PERCENTILE_DISC(<col_name>, DOUBLE <percentile>[, BOOLEAN <isIgnoreNull>])

-- Hitung nilai persentil dalam window
PERCENTILE_DISC(<col_name>, DOUBLE <percentile>[, BOOLEAN <isIgnoreNull>]) OVER ([partition_clause] [orderby_clause])

    • Deskripsi

      Menghitung nilai persentil tertentu. Fungsi ini pertama-tama mengurutkan kolom yang ditentukan secara ascending lalu mengembalikan nilai pertama yang distribusi kumulatifnya lebih besar dari atau sama dengan persentil yang ditentukan.

    • Parameter

      • col_name: Wajib. Kolom dengan tipe data yang dapat diurutkan.

      • percentile: Wajib. Persentil yang dihitung. Konstanta DOUBLE dalam rentang [0, 1].

      • isIgnoreNull: Opsional. Menentukan apakah nilai NULL diabaikan. Konstanta BOOLEAN. Nilai default adalah TRUE. Jika diatur ke FALSE, nilai NULL diperlakukan sebagai nilai minimum selama pengurutan.

      • partition_clause dan orderby_clause: Untuk informasi selengkapnya, lihat Fungsi window.

    • Nilai kembali

      Mengembalikan nilai persentil yang dihitung. Tipe data sama dengan kolom input col_name.

    • Contoh

      • Contoh 1: Abaikan nilai NULL dan hitung nilai persentil dalam window.

        SELECT
  x,
  PERCENTILE_DISC(x, 0) OVER() AS min,
  PERCENTILE_DISC(x, 0.5) OVER() AS median,
  PERCENTILE_DISC(x, 1) OVER() AS max
FROM VALUES('c'),(NULL),('b'),('a') AS tbl(x);

-- Hasil kembali
+------------+------------+------------+------------+
| x          | min        | median     | max        | 
+------------+------------+------------+------------+
| c          | a          | b          | c          | 
| NULL       | a          | b          | c          | 
| b          | a          | b          | c          | 
| a          | a          | b          | c          | 
+------------+------------+------------+------------+

      • Contoh 2: Jangan abaikan nilai NULL. Nilai NULL diperlakukan sebagai nilai minimum selama pengurutan. Hitung nilai persentil dalam window.

        SELECT
  x,
  PERCENTILE_DISC(x, 0, false) OVER() AS min,
  PERCENTILE_DISC(x, 0.5, false) OVER() AS median,
  PERCENTILE_DISC(x, 1, false) OVER() AS max
FROM VALUES('c'),(NULL),('b'),('a') AS tbl(x);

-- Hasil kembali
+------------+------------+------------+------------+
| x          | min        | median     | max        | 
+------------+------------+------------+------------+
| c          | NULL       | a          | c          | 
| NULL       | NULL       | a          | c          | 
| b          | NULL       | a          | c          | 
| a          | NULL       | a          | c          | 
+------------+------------+------------+------------+

    STDDEV

    • Sintaksis

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

    • Deskripsi

      Mengembalikan simpangan baku populasi dari semua nilai input.

    • Parameter

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

    • Nilai kembali

      Jika nilai colname adalah null, baris yang berisi nilai ini tidak digunakan untuk perhitungan. Tabel berikut menjelaskan pemetaan antara tipe data input dan tipe 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 simpangan baku populasi nilai gaji (sal) semua karyawan. Pernyataan contoh:

        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 menghitung simpangan baku populasi gaji (sal) untuk setiap departemen. Perintah contoh:

        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 bertipe DOUBLE atau DECIMAL. Jika nilai input bertipe STRING atau BIGINT, nilai tersebut dikonversi secara implisit ke tipe DOUBLE sebelum perhitungan.

    • Nilai kembali

      Jika nilai colname adalah null, baris yang berisi nilai ini tidak digunakan untuk perhitungan. Tabel berikut menjelaskan pemetaan antara tipe data input dan tipe 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 nilai gaji (sal) semua karyawan. Pernyataan contoh:

        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 menghitung deviasi standar sampel gaji (sal) untuk setiap departemen. Perintah contoh:

        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

      Menghitung jumlah total.

    • Parameter

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

    • Bagian ini menjelaskan nilai kembalian.

      Jika nilai colname adalah null, baris yang berisi nilai ini tidak digunakan untuk perhitungan. Tabel berikut menjelaskan pemetaan antara tipe data input dan tipe 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 total nilai gaji (sal) semua karyawan. Pernyataan contoh:

        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 menghitung jumlah total gaji (sal) untuk setiap departemen. Perintah contoh:

        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 fungsi tambahan MaxCompute V2.0.

    • Parameter

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

    • Nilai kembali

      Nilai bertipe DOUBLE dikembalikan.

    • Contoh

      • Contoh 1: Hitung varians sampel nilai gaji (sal) semua karyawan. Pernyataan contoh:

        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 menghitung varians sampel gaji (sal) untuk setiap kelompok. Perintah contoh:

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

    • Topik ini menjelaskan perintah tersebut.

      Menghitung varians dari kolom numerik tertentu.

    • Deskripsi Metrik

      colname: wajib. Kolom bertipe data numerik. Jika kolom yang ditentukan bukan kolom numerik, nilai null dikembalikan. Fungsi ini merupakan fungsi tambahan MaxCompute V2.0.

    • Nilai kembali

      Nilai bertipe DOUBLE dikembalikan.

    • Contoh

      • Contoh 1: Hitung varians nilai gaji (sal) semua karyawan. Pernyataan contoh:

        select variance(sal) from emp;
-- Ini 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 menghitung varians gaji (sal) untuk setiap kelompok. Perintah contoh:

        select deptno, variance(sal) from emp group by deptno;
-- Ini 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 perintah.

      Menggabungkan nilai-nilai dalam colname menggunakan delimiter yang ditentukan oleh separator.

    • Deskripsi.

      • separator: wajib. Delimiter, yang merupakan konstanta bertipe STRING.

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

    • Nilai kembali (saat menggunakan group by untuk pengelompokan, nilai kembali dalam kelompok tidak diurutkan)

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

      • Jika nilai separator bukan konstanta bertipe STRING, error dikembalikan.

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

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

      Catatan

      Dalam pernyataan select wm_concat(',', name) from table_name;, jika table_name adalah himpunan kosong, pernyataan tersebut mengembalikan NULL.

    • Contoh

      • Contoh 1: Gabungkan nama (ename) semua karyawan. Pernyataan contoh:

        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: Gunakan fungsi ini dengan group by untuk mengelompokkan semua karyawan berdasarkan departemen (deptno) dan menggabungkan nama (ename) karyawan dalam kelompok yang sama. Perintah contoh:

        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: Gunakan fungsi ini dengan group by untuk mengelompokkan semua karyawan berdasarkan departemen (deptno) dan menggabungkan nilai gaji (sal) unik karyawan dalam kelompok yang sama. Perintah contoh:

        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: Gunakan fungsi ini dengan group by dan order by untuk mengelompokkan semua karyawan berdasarkan departemen (deptno), mengurutkan gaji mereka (sal), dan menggabungkannya. Perintah contoh:

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