Hologres:アドホッククエリにおける UV 計算ソリューション

• 長所と短所

  • 長所：このソリューションは、アドホッククエリにおけるリアルタイムで柔軟な UV 計算の要件を満たしています。ビジネス要件に基づいて期間を指定できます。事前計算とスケジューリングの構成は必要ありません。 COUNT DISTINCT 関数は Hologres で自動的に最適化され、計算パフォーマンスが大幅に向上します。

  • 短所：データ量が増加すると、計算効率とサポートされる 1 秒あたりのクエリ数（QPS）が低下する可能性があります。

• ユースケース

  このソリューションは、数千万件のデータレコードに対する UV 計算に適しています。

ワイドファクトテーブルの UV 数を計算する

この例では、uid フィールドが COUNT DISTINCT 関数で指定され、ワイドファクトテーブルの UV 数がカウントされます。

1. 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');
   -- リアルタイムクエリ要件に基づいて、テーブルの分散キーを指定します。データは、分散キーに基づいてシャードに分散されます。
   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. 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;

ファクトテーブルとディメンションテーブルを結合して UV 数をカウントする

この例では、ディメンションテーブルとファクトテーブルを結合して UV 数をカウントします。この操作は、特定のビジネスシナリオで必要です。

1. 必要なテーブルを準備します。

   -- ユーザーの操作の詳細を格納するファクトテーブルを作成します。
   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');
   -- リアルタイムクエリ要件に基づいて、テーブルの分散キーを指定します。データは、分散キーに基づいてシャードに分散されます。
   CALL set_table_property('ods_app_detail', 'distribution_key', 'uid');
   -- WHERE 句で使用できるフィールドを指定します。年、月、日時の情報を含むフィールドをクラスタリングキーおよびイベントタイムカラムとして構成することをお勧めします。
   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;

2. ファクトテーブルとディメンションテーブルを結合して、特定の期間の 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;