千萬級資料量即時統計UV - Hologres

Hologres針對小規模資料量（千萬級）的UV計算情境，提供即席UV查詢方案，支援您使用COUNT DISTINCT對明細表進行UV計算，尤其當您需要從未經處理資料中擷取最準確、最即時的結果時。本方案避免了使用預彙總表或其他複雜的資料處理技術，以簡化查詢過程。

方案介紹

對於小規模資料量（千萬級）的UV計算情境，在Hologres中可以使用COUNT DISTINCT直接查詢明細表，並通過篩選時間周期來實現任意長周期的UV計算。同時Hologres對COUNT DISTINCT進行了多種最佳化，可以高效地支援單個或多個欄位的COUNT DISTINCT，滿足大部分的UV計算情境。在明細表JOIN維表的情境，可以設定合理的索引來進一步提升效能。該方案的優缺點及適用情境如下：

優缺點
- 優點：即時性好，可以滿足使用者的即時UV計算需求，同時計算靈活，可以按需選擇時間周期進行UV計算，無需預計算和調度等配置即可實現即席的UV、PV查詢。結合Hologres內建的COUNT DISTINCT自動最佳化，計算效率相比其他產品有顯著提升。
- 缺點：資料量變大時，計算時效可能會降低，支援的QPS也會降低。
適用情境
適用於小規模資料量（千萬級）UV查詢情境。

方案流程

明細寬表計算UV

若只有一張明細寬表，可以直接對UID欄位使用COUNT DISTINCT實現UV計算，樣本如下：

準備一張明細寬表ods_app_detail。

--明細寬表
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');
--distribution_key根據需求設定，根據該表的即時查詢需求，從什麼維度做分區能夠取得較好效果即可
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;

使用COUNT DISTINCT計算UV。

--查詢一個月的UV、PV
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;

明細表和維表JOIN計算UV

部分業務情境可能需要使用維表和明細表進行JOIN才能實現UV計算，常見的樣本如下：

準備基礎資料表。

--明細表，記錄使用者的操作明細
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');
--distribution_key根據需求設定，根據該表的即時查詢需求，從什麼維度做分區能夠取得較好效果即可
CALL set_table_property('ods_app_detail', 'distribution_key', 'uid');
--用於做where過濾條件，包含完整年月日時間欄位推薦設為clustering_key和event_time_column
CALL set_table_property('ods_app_detail', 'clustering_key', 'ymd');
CALL set_table_property('ods_app_detail', 'event_time_column', 'ymd');
COMMIT;

--維表，記錄使用者的屬性資訊
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;

通過明細表JOIN維表計算任意周期的UV。

--查詢一個月內性別為男的使用者的UV、PV
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;