RisingWave 物化视图使用场景：订单数据看板

1背景

在电商场景中，商家需要通过各种数据指标来了解店铺的运营情况并进行决策。其中，订单数据是非常重要的指标之一。通过分析订单数据，商家可以了解自己的销售情况、用户行为以及营销活动的效果等。

以某 APP 为例子，本文将介绍如何通过 RisingWave 的物化视图实现其中订单支付金额、支付买家数、客单价指标的看板。从概况页面上可以看到，每个指标我们可以实时地看到当天店铺的数据，以及与昨日数据的对比。除此外用户还能够选择，例如昨日数据与前日数据的对比，近7日数据与 7 日环比，近 30日数据与 30 日环比，或者自定义时间段分析。我们可以看到这里有两个场景，一个是实时场景，另外一个是历史数据场景。其中实时场景关注的是当天的实时数据展示。而历史数据场景则涵盖了除当天之外的所有数据查看。

2订单数据接入

假定所有的订单数据已经在上游的 PostgreSQL 数据库当中，我们首先通过RisingWave 的 CDC 功能将订单表从上游数据库接入到 RisingWave 中。

例子只列举出订单表关键的字段，例如订单 ID（o_orderkey），买家 ID（o_custkey），订单价格（o_totalprice），下单时间（o_orderdate）。

CREATE TABLE orders (
      o_orderkey BIGINT,
      o_custkey INTEGER,
      o_totalprice NUMERIC,
      o_orderdate timestamp with time zone,
   ...
      PRIMARY KEY (o_orderkey)
) WITH (
 connector = 'postgres-cdc',
 hostname = '127.0.0.1',
 port = '5432',
 username = 'xxx',
 password = 'xxx',
 database.name = 'store',
 schema.name = 'public',
 table.name = 'orders'
);

3计算支付金额

不考虑任何的优化，我们来看看如何计算当天实时的支付金额。通过对订单表时间过滤出当天 0 点到当前时间并对订单价格进行求和即可。PS：date_trunc('day', now())
代表将当前时间截断至天级别。

select sum(o_totalprice) from orders where o_orderdate between date_trunc('day', now()) and now();

历史数据场景，我们以过去 7 天为例子：

select sum(o_totalprice) from orders where o_orderdate between date_trunc('day', now() - INTERVAL '7 days') and date_trunc('day', now());

从 SQL 上看，实时 SQL 和历史数据 SQL 只是过滤条件不同。然而当订单量数据比较大，时间维度较长，或者查询并发比较高的时候，上面的 SQL 就会变慢。其实不难发现对于不同的查询很多计算是可以复用的。我们经常说的通过物化视图来加速查询，需要遵循一定的原则：物化的查询本身是需要预先定义好的，不变的。而例子中用户输入的时间范围是任意的，我们不可能针对每个范围查询都创建一个物化视图，因此我们需要两个步骤：1. 将订单表按一定的可复用时间粒度进行预聚合，我们可以按分钟，小时或者天级别对订单表进行支付金额预聚合，并通过物化视图物化结果。2. 查询改为对预聚合物化视图的查询。在设计物化视图加速查询时，应该结合业务的查询特点尽可能地物化可被复用的计算结果。

分钟级别预聚合

如果我们希望用户的查询时间粒度是分钟，那么我们可以建立分钟级别的预聚合物化视图。下面的 SQL 将订单表按照每分钟的维度统计支付金额。

create materialized view orders_total_price_per_min as 
select date_trunc('minute', o_orderdate) minute, sum(o_totalprice) totalprice 
from orders group by date_trunc('minute', o_orderdate);

小时级别预聚合

RisingWave 支持层级构建物化视图。我们可以在分钟级别的物化视图之上，再聚合一个小时级别的维度。

create materialized view orders_total_price_per_hour as 
select date_trunc('hour', minute) hour, sum(totalprice) totalprice 
from orders_total_price_per_min group by date_trunc('hour', minute);

天级别预聚合

同理，构建天级别预聚合物化视图

create materialized view orders_total_price_per_day as 
select date_trunc('day', hour) date, sum(totalprice) totalprice 
from orders_total_price_per_hour group by date_trunc('day', hour);

4查询物化视图

有了上述的预聚合物化视图后，我们来看看如何基于它计算 7 天的支付金额。我们只需要将查询改为对 orders_total_price_per_day 物化视图的查询即可。与最初的原始订单表数据相比，查询所需扫描的数据将大大减少。

select sum(totalprice) from orders_total_price_per_day where date between date_trunc('day', now() - INTERVAL '7 days') and date_trunc('day', now());

而当天的实时支付金额查询也会变得非常简单，因为 RisingWave 的物化视图是实时更新的，所以我们直接对 orders_total_price_per_day 查询当天时间范围的数据即可获得实时数据。这里实际上只有一行数据。

select sum(totalprice) from orders_total_price_per_day where date between date_trunc('day', now()) and now();

总的来说，我们通过 RisingWave 的实时物化视图高效地既满足了实时计算支付金额的需求，又满足了历史任意时间范围（按分钟，小时或天粒度）分析查询的需求。

5计算买家数

相信通过上面的支付金额的例子，大家已经熟悉如何使用物化视图来构建数据看板了。我们再来看一个指标：买家数。电商中买家数一般按照天级别进行去重，也就是一个用户同一天下了很多单，对应的买家数只算一次。让我们直接写出对应的物化视图。

create materialized view orders_unique_customer_per_day as 
select date_trunc('day', o_orderdate) date, count(distinct o_custkey) custnum 
from orders group by date_trunc('day', o_orderdate);

同理通过查询 orders_unique_customer_per_day 我们可以计算出任意天范围买家数指标。近 7 天买家数。

select sum(custnum) from orders_unique_customer_per_day where date between date_trunc('day', now() - INTERVAL '7 days') and date_trunc('day', now());

6计算客单价

我们知道 客单价 = 支付金额 买家数。实际上，有了上述两个物化视图 orders_total_price_per_day 和 orders_unique_customer_per_day，我们可以直接将它们的指标相除，以得到所需时间范围内的客单价。客单价这个指标实际上说明了包含平均计算逻辑的指标（例如转化率、支付率等），需要分别维护两个独立的指标（两阶段计算）来满足最终任意时间范围的客单价查询。我们无法通过维护一个指标来满足可以在任意时间范围内复用的客单价查询的要求。（PS：类似的聚合函数还有方差，标准差：stddev_pop
，stddev_samp
，var_pop
，var_samp
）

计算近7天的客单价：

select 
(select sum(totalprice) from orders_total_price_per_day where date between date_trunc('day', now() - INTERVAL '7 days') and date_trunc('day', now()))
/
(select sum(custnum) from orders_unique_customer_per_day where date between date_trunc('day', now() - INTERVAL '7 days') and date_trunc('day', now()));

7实时趋势图

前面给出的例子实际上只需要使用天粒度的物化视图就可以满足需求。而我们之前还创建了一个分钟和小时级别的支付金额物化视图，在实时趋势图中它就可以派上用场了。显然，我们可以绘制分钟级别或小时级别的数据曲线图，然后比较任意两天的数据。在实时场景中，还可以实时计算当天相对于昨天的增长率，这也适用于电商大促活动日对实时数据比较感兴趣的情况。

得益于 RisingWave 兼容 PostgreSQL 协议，我们可以直接利用 Grafana 对RisingWave 的数据进行可视化展示。下面我们使用 Grafana 选择 RisingWave 作为 PostgreSQL 协议兼容数据源，查询小时粒度的物化视图 orders_total_price_per_hour 中的时序数据。例子中我们挑选 2024-01-11 作为对比日，2024-01-12 作为当前日。

对比日 SQL：

SELECT hour, totalprice FROM orders_total_price_per_hour where hour between '2024-01-11 00:00:00+00:00' and ' 2024-01-11 23:59:59+00:00' order by hour;

当前日 SQL：

由于在 Grafana 中对比我们希望把时间对齐到同一时段比较，我们对当前日时间左移一天到对比日的时间上。

SELECT hour - interval '1' day, totalprice as totalprice2 FROM orders_total_price_per_hour where hour between ' 2024-01-12 00:00:00+00:00' and ' 2024-01-12 23:59:59+00:00' order by hour;

这样我们就能够在 Grafana 中对比趋势了。

8总结

相信看完文本的读者已经知道如何使用 RisingWave 来处理数据看板的需求了。我们首先通过 CDC 把订单数据从上游同步进 RisingWave，通过层级物化视图预聚合计算分钟、小时和天级别的订单表的支付金额和买家数指标。接着按照用户选择的时间范围构造合适的时间范围，并查询物化视图。在设计物化视图加速查询时，应该结合业务的查询特点，尽可能地物化可被复用的计算结果。最后我们通过 Grafana 可视化 RisingWave 中的数据，完成趋势对比的功能。