Menggunakan ekstensi hll untuk memperkirakan elemen yang berbeda - PolarDB

Topik ini menjelaskan ekstensi hll, yang mendukung tipe data HyperLogLog untuk memperkirakan metrik seperti jumlah tampilan halaman (PV) dan pengunjung unik (UV).

Prasyarat

Ekstensi ini didukung pada kluster PolarDB for PostgreSQL yang menjalankan mesin berikut:

PostgreSQL 14 (versi revisi 14.5.1.0 atau lebih baru)
PostgreSQL 11 (versi revisi 1.1.28 atau lebih baru)

Catatan

Anda dapat mengeksekusi pernyataan berikut untuk melihat versi minor yang digunakan oleh PolarDB for PostgreSQL:

PostgreSQL 14:

SELECT version();

PostgreSQL 11:

SHOW polar_version;

Informasi latar belakang

Ekstensi hll mendukung tipe data HyperLogLog (hll), yang dapat diperluas dan mirip himpunan untuk memperkirakan elemen DISTINCT dengan akurasi tertentu. Sebagai contoh, Anda dapat menggunakan 1.280 byte data hll untuk memperkirakan miliaran elemen DISTINCT secara akurat. Ekstensi hll cocok untuk industri yang membutuhkan analisis perkiraan, seperti analisis iklan Internet untuk memperkirakan PV dan UV.

Membuat ekstensi hll

CREATE EXTENSION hll;

Operasi dasar

Eksekusi pernyataan berikut untuk membuat tabel yang berisi bidang hll:
```
CREATE TABLE agg (id INT PRIMARY KEY, userids hll);
```
Eksekusi pernyataan berikut untuk mengonversi data INT menjadi data hll_hashval:
```
SELECT 1::hll_hashval;
```
Hasil berikut dikembalikan:
```
 hll_hashval 
-------------
 1
(1 row)
```

Operator dasar

Tipe data hll mendukung operator berikut:

Contoh:

SELECT hll_add_agg(1::hll_hashval) = hll_add_agg(2::hll_hashval);

Hasil berikut dikembalikan:

 ?column? 
----------
 f
(1 row)

SELECT hll_add_agg(1::hll_hashval) || hll_add_agg(2::hll_hashval);

Hasil berikut dikembalikan:

                 ?column?                 
------------------------------------------
 \x128b7f00000000000000010000000000000002
(1 row)

SELECT #hll_add_agg(1::hll_hashval);

Hasil berikut dikembalikan:

 ?column? 
----------
        1
(1 row)

Tipe data hll_hashval mendukung operator berikut:

Contoh:

SELECT 1::hll_hashval = 2::hll_hashval;

Hasil berikut dikembalikan:

 ?column? 
----------
 f
(1 row)

SELECT 1::hll_hashval <> 2::hll_hashval;

Hasil berikut dikembalikan:

 ?column? 
----------
 t
(1 row)

Fungsi dasar

Ekstensi hll mendukung fungsi hash seperti hll_hash_boolean, hll_hash_smallint, dan hll_hash_bigint. Contoh:

SELECT hll_hash_boolean(true);

Hasil berikut dikembalikan:

  hll_hash_boolean   
---------------------
 8849112093580131862
(1 row)

SELECT hll_hash_integer(1);

Hasil berikut dikembalikan:

   hll_hash_integer   
----------------------
 -8604791237420463362
(1 row)

Ekstensi hll mendukung fungsi hll_add_agg untuk mengonversi tipe data dari INT ke hll. Contoh:

SELECT hll_add_agg(1::hll_hashval);

Hasil berikut dikembalikan:

       hll_add_agg        
--------------------------
 \x128b7f0000000000000001
(1 row)

Ekstensi hll mendukung fungsi hll_union untuk melakukan operasi UNION pada data hll. Contoh:

SELECT hll_union(hll_add_agg(1::hll_hashval), hll_add_agg(2::hll_hashval));

Hasil berikut dikembalikan:

                hll_union                 
------------------------------------------
 \x128b7f00000000000000010000000000000002
(1 row)

Ekstensi hll mendukung fungsi hll_set_defaults untuk mengatur akurasi. Contoh:
```
SELECT hll_set_defaults(15,5,-1,1);
```
Hasil berikut dikembalikan:
```
 hll_set_defaults 
------------------
 (11,5,-1,1)
(1 row)
```

Ekstensi hll mendukung fungsi hll_print untuk menampilkan informasi debug. Contoh:

SELECT hll_print(hll_add_agg(1::hll_hashval));

Hasil berikut dikembalikan:

                                  hll_print                                  
-----------------------------------------------------------------------------
 EXPLICIT, 1 elements, nregs=32768, nbits=5, expthresh=-1(2560), sparseon=1:+
 0:                    1 
(1 row)

Menghapus ekstensi hll

DROP EXTENSION hll;

Contoh

Siapkan data.

CREATE TABLE access_date (acc_date DATE UNIQUE, userids hll);
INSERT INTO access_date
     SELECT current_date, hll_add_agg(hll_hash_integer(user_id))
     FROM generate_series(1,10000) t(user_id);
INSERT INTO access_date
     SELECT current_date-1, hll_add_agg(hll_hash_integer(user_id))
     FROM generate_series(5000,20000) t(user_id);
INSERT INTO access_date
     SELECT current_date-2, hll_add_agg(hll_hash_integer(user_id))
     FROM generate_series(9000,40000) t(user_id);

Eksekusi kueri berikut.

SELECT #userids FROM access_date WHERE acc_date=current_date;

Hasil:

     ?column?
------------------
 9725.852733707077
(1 row)

SELECT #userids FROM access_date WHERE acc_date=current_date-1;

Hasil:

     ?column?
------------------
 14968.65968832792
(1 row)

SELECT #userids FROM access_date WHERE acc_date=current_date-2;

Hasil:

     ?column?
------------------
 29361.520914991113
(1 row)