OpenSearch:GROUP BY

Deskripsi

Klausa GROUP BY mengelompokkan data berdasarkan satu atau beberapa bidang untuk menghitung nilai agregat seperti maksimum, minimum, dan rata-rata.

Sintaks

select:
    SELECT [ ALL | DISTINCT ]
    { * | projectItem [, projectItem ]* }
    FROM tableExpression
    [ WHERE booleanExpression ]
    [ GROUP BY { groupItem [, groupItem ]* } ]
    [ HAVING booleanExpression ]

UDAF Bawaan

Tabel berikut menjelaskan UDAF bawaan.

No.	Function name	Features
1	SUM	Menghitung jumlah setelah agregasi.
2	MIN	Menemukan nilai minimum setelah agregasi.
3	MAX	Menemukan nilai maksimum setelah agregasi.
4	COUNT	Menghitung jumlah baris.
5	AVG	Menghitung nilai rata-rata setelah agregasi.
6	ARBITRARY	Memilih nilai input arbitrer sebagai output.

Anda dapat menyesuaikan UDAF untuk memperluas logika agregasi.

Contoh

1. SUM:

SELECT brand, SUM(price) FROM phone WHERE nid < 8 GROUP BY brand

2. MIN:

SELECT brand, MIN(price) FROM phone WHERE nid < 8 GROUP BY brand

3. MAX:

SELECT brand, MAX(price) FROM phone WHERE nid < 8 GROUP BY brand

4. COUNT:

SELECT brand, COUNT(*) FROM phone WHERE nid < 8 GROUP BY brand

5. AVG:

SELECT brand, AVG(price) FROM phone WHERE nid < 8 GROUP BY brand

6. HAVING clause:

SELECT brand FROM phone GROUP BY brand HAVING COUNT(brand) > 10