Hologres：SQL管理物化視圖

物化視圖當前支援如下彙總函式。

• SUM

• COUNT

• AVG

• MIN

• MAX

• RB_BUILD_CARDINALITY_AGG（只支援BIGINT，需建立Extension roaringbitmap）

精確UV計算是計算複雜度非常高的運算元，通常是系統的效能瓶頸部分。Hologres支援RB_BUILD_CARDINALITY_AGG彙總函式，通過利用RoaringBitmap資料結構，可以對BIGINT類資料（通常是表示業務ID欄位）進行物化視圖預彙總，實現UV統計即時去重，可按照如下方式建立物化視圖，當前僅支援BIGINT類欄位的彙總去重。

--UV計算依賴RoaringBitmap資料類型，需要提前建立RoaringBitmap extension
CREATE EXTENSION if not exists roaringbitmap;

BEGIN;
CREATE TABLE base_sales_r(
  day text not null,
  hour int ,
  ts timestamptz,
  amount float,
  userid bigint,
  pk text not null primary key
);
CALL SET_TABLE_PROPERTY('base_sales_r', 'mutate_type', 'appendonly');

CREATE MATERIALIZED VIEW mv_sales_r AS
  SELECT
    day,
    hour,
    avg(amount) AS amount_avg,
    rb_build_cardinality_agg(userid) as user_count
  FROM base_sales_r
  GROUP BY day, hour;

COMMIT;

insert into base_sales_r values(to_char(now(),'YYYYMMDD'),'12',now(),100,1,'pk1');
insert into base_sales_r values(to_char(now(),'YYYYMMDD'),'12',now(),200,2,'pk2');
insert into base_sales_r values(to_char(now(),'YYYYMMDD'),'12',now(),300,3,'pk3');
select user_count as UV from mv_sales_r where day = to_char(now(),'YYYYMMDD') AND hour = 12;

通過rb_build_cardinality_agg計算去重數，mv_sales_r中user_count代表userid去重數，查詢user_count可獲得去重數。

假設定義了上述的mv_sales物化視圖，且明細表base_sales中當前含有以下詳細資料。

直接查詢sales_mv將會有如下結果。

postgres=> select * from mv_sales;
    day    |   hour  |   amount_avg
-----------+---------+--------------
  20210101 |    12   |     3
  20210101 |    13   |     6

這時如果想更改查詢物化視圖的彙總維度，例如使用維度day進行avg彙總計算，則會得到的會是一個錯誤的結果，因為avg的avg不等於total的avg。

postgres=> select day, avg(amount_avg) from mv_sales group by day;
    day    |   avg
-----------+--------
  20210101 |   4.5

這時候一種辦法是再建一張以day為維度進行彙總的物化視圖，但這樣會導致物化視圖的數量膨脹，Hologres提供了一種基於彙總中間狀態的實現，使得使用者僅用一張物化視圖，實現不同維度彙總查詢。這裡以Avg為例，修改彙總檢視的定義如下。

BEGIN;
CREATE TABLE base_sales(
  day text not null,
  hour int ,
  ts timestamptz,
  amount float,
  pk text not null primary key
);
CALL SET_TABLE_PROPERTY('base_sales', 'mutate_type', 'appendonly');

CREATE MATERIALIZED VIEW mv_sales_partial AS
  SELECT
    day,
    hour,
    avg(amount) as avg,
    avg_partial(amount) AS amt_avg_partial
  FROM base_sales
  GROUP BY day, hour;

COMMIT;

原先的avg彙總函式重新定義為avg_partial彙總函式，amount_avg_partial列儲存的是彙總結果的中間狀態，查詢時需要修改查詢函數，將avg彙總函式改寫為avg_final最終彙總函式，聲明是對彙總結果中間狀態的最終彙總。

postgres=> select day, avg(avg) as avg_avg, avg_final(amt_avg_partial) as real_avg from mv_sales_partial group by day;
    day    |   avg_avg |  real_avg
-----------+-----------+----------
  20210101 |    4.5    |     4

目前支援以下彙總函式及對應的partial彙總函式。

為base_sales_table設定了TTL，如果資料由於TTL到期被回收掉，那麼此時查詢明細表的結果如下所示。

postgres=> SELECT
    day,
    hour,
    avg(amount) AS amount_avg
  FROM base_sales
  GROUP BY day, hour;

--查詢結果
    day    |   hour  |   amount_avg
-----------+---------+--------------
  20210101 |    12   |     4
  20210101 |    13   |     6

但是由於被回收的資料，已經物化到了物化視圖的資料中，所以查詢物化視圖時有可能得到的結果如下。

postgres=> select * from mv_sales;
--查詢結果
    day    |   hour  |   amount_avg
-----------+---------+--------------
  20210101 |    12   |     3
  20210101 |    13   |     6

此時查詢結果不一致，建議改進方案如下。

• 明細表不要設定TTL。

• 明細表設定TTL，但是物化視圖的GROUP BY含有資料的時間欄位，且查詢物化視圖的時候，不會去查詢在TTL臨界點附近的資料。

• 明細表建成分區表，不設定TTL，回收資料通過刪除（Drop）分區表來實現。

查詢時不需要顯式指定物化視圖表名稱，可以像之前一樣基於基礎資料表進行查詢。如果有匹配的物化視圖表，最佳化器會智能路由到最佳的物化視圖表來加速查詢。在查詢時，物化視圖表的選擇規則如下：

• 選擇包含所有查詢列或可以通過間接計算得到的物化視圖表。

• 選擇GROUP BY欄欄位包含原始查詢GROUP BY所有列的物化視圖表。

• 當有多個物化視圖表符合條件時，選擇GROUP BY欄欄位少的物化視圖表。

當前支援智能路由的彙總函式有SUM、COUNT、MIN和MAX。