Hologres：Solusi penghitungan UV dalam kueri ad hoc

• Kelebihan dan Kekurangan

  • Kelebihan: Solusi ini mendukung penghitungan UV secara real-time dan fleksibel dalam kueri ad hoc. Anda dapat menentukan periode waktu sesuai kebutuhan bisnis tanpa memerlukan pra-komputasi atau konfigurasi penjadwalan. Fungsi COUNT DISTINCT dioptimalkan secara otomatis di Hologres untuk meningkatkan kinerja komputasi secara signifikan.

  • Kekurangan: Jika volume data meningkat, efisiensi komputasi dan permintaan per detik (QPS) yang didukung mungkin menurun.

• Skenario Penggunaan

  Solusi ini ideal untuk penghitungan UV pada puluhan juta data.

Menghitung jumlah UV dalam tabel fakta lebar

Dalam contoh ini, bidang uid digunakan dalam fungsi COUNT DISTINCT untuk menghitung jumlah UV dalam tabel fakta lebar.

1. Buat tabel fakta lebar bernama ods_app_detail.

   -- Buat tabel fakta lebar.
   BEGIN;
   CREATE TABLE IF NOT EXISTS ods_app_detail (
        uid int,
        country text,
        prov text,
        city text,
        channel text,
        operator text,
        brand text,
        ip text,
        click_time text,
        year text,
        month text,
        day text,
        ymd text NOT NULL
   );
   CALL set_table_property('ods_app_detail', 'orientation', 'column');
   CALL set_table_property('ods_app_detail', 'bitmap_columns', 'country,prov,city,channel,operator,brand,ip,click_time, year, month, day, ymd');
   -- Tentukan kunci distribusi untuk tabel berdasarkan kebutuhan kueri real-time. Data didistribusikan ke shard berdasarkan kunci distribusi.
   CALL set_table_property('ods_app_detail', 'distribution_key', 'uid');
   CALL set_table_property('ods_app_detail', 'clustering_key', 'ymd');
   CALL set_table_property('ods_app_detail', 'event_time_column', 'ymd');
   COMMIT;

2. Gunakan fungsi COUNT DISTINCT untuk menghitung jumlah UV.

   -- Hitung jumlah UV dan jumlah tampilan halaman (PV) dalam bulan tertentu.
   SELECT  
    COUNT (DISTINCT uid) AS uv,
     country,
     prov,
     city,
    COUNT(1) AS pv
   FROM public.ods_app_detail
   WHERE ymd >= '20240301' AND ymd <= '20240331'
   GROUP BY country,prov,city;

Menghitung jumlah UV dengan menggabungkan tabel fakta dengan tabel dimensi

Dalam contoh ini, tabel dimensi dan tabel fakta digabungkan untuk menghitung jumlah UV, operasi yang sering diperlukan dalam skenario bisnis tertentu.

1. Siapkan tabel yang diperlukan.

   -- Buat tabel fakta yang menyimpan detail operasi pengguna.
   BEGIN;
   CREATE TABLE IF NOT EXISTS ods_app_detail (
        uid int,
        channel text,
        operator text,
        brand text,
        ip text,
        click_time text,
        year text,
        month text,
        day text,
        ymd text NOT NULL
   );
   CALL set_table_property('ods_app_detail', 'orientation', 'column');
   CALL set_table_property('ods_app_detail', 'bitmap_columns', 'channel,operator,brand,ip,click_time, year, month, day, ymd');
   -- Tentukan kunci distribusi untuk tabel berdasarkan kebutuhan kueri real-time. Data didistribusikan ke shard berdasarkan kunci distribusi.
   CALL set_table_property('ods_app_detail', 'distribution_key', 'uid');
   -- Tentukan bidang yang dapat digunakan dalam klausa WHERE. Kami sarankan Anda mengonfigurasi bidang yang berisi informasi tahun, bulan, dan tanggal sebagai kunci klaster dan kolom waktu acara.
   CALL set_table_property('ods_app_detail', 'clustering_key', 'ymd');
   CALL set_table_property('ods_app_detail', 'event_time_column', 'ymd');
   COMMIT;
   
   -- Buat tabel dimensi yang menyimpan informasi properti pengguna.
   BEGIN;
   CREATE TABLE dim_uid_info (
       uid int NOT NULL,
       name text NOT NULL,
       gender text NOT NULL,
       country text,
       prov text,
       city text
   );
   CALL set_table_property('dim_uid_info', 'orientation', 'column');
   CALL set_table_property('dim_uid_info', 'bitmap_columns', 'country,prov,city');
   CALL set_table_property('dim_uid_info', 'distribution_key', 'uid');
   COMMIT;

2. Hitung jumlah UV dalam periode waktu tertentu dengan menggabungkan tabel fakta dan tabel dimensi.

   -- Hitung jumlah UV dan jumlah PV pengguna pria dalam bulan tertentu.
   SELECT  
    COUNT (DISTINCT B.uid) AS uv,
     country,
     prov,
     city,
     COUNT(1)  AS pv
   FROM 
   ( SELECT uid,country,prov,city FROM dim_uid_info WHERE gender = 'man'
   ) AS A
   LEFT JOIN ods_app_detail  AS B ON A.uid = B.uid
   WHERE B.ymd >= '20240301' AND B.ymd <= '20240331'
   GROUP BY country,prov,city;