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。