文档首页/ 数据仓库服务 DWS/ 开发指南/ 开发指南(9.1.1.x)/ DWS物化视图/ 物化视图应用案例/ 电商平台实时销售报表案例
更新时间：2026-05-18 GMT+08:00
查看PDF
分享

电商平台实时销售报表案例

场景描述

某电商平台需要实时查看销售报表。

数据量： 销售明细表10亿+条记录

查询模式： 按日期、地区、商品类别聚合统计

业务需求

业务需求：实时销售大屏。

刷新频率：分钟级。

查询延迟要求：< 1秒。

解决方案

三层物化视图架构。

将数仓的DWD>DWS>ADS都创建为物化视图。通过增量计算实现实时的数据加工，满足电商平台需要实时查看销售报表的需求。

应用示例

  1. DWD层：数据清洗与标准化。 

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
    		 
    CREATE MATERIALIZED VIEW mv_dwd_sales
REFRESH FAST EVERY (interval '5 min')
ENABLE QUERY REWRITE
WITH (orientation = COLUMN, enable_hstore_opt = ON)
AS
SELECT
TO_DATE(sale_date) as sale_date,
region_id,
category_id,
product_id,
customer_id,
COUNT(*) as order_count,
SUM(sale_amount) as total_amount,
SUM(cost_amount) as total_cost,
COUNT(ORDER BY DISTINCT customer_id) as customer_count
FROM raw_sales
WHERE status = 'completed'
AND sale_date >= '2024-01-01'
GROUP BY TO_DATE(sale_date), region_id, category_id, product_id, customer_id;

  2. DWS层：多维度汇总。 

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
    		 
    CREATE MATERIALIZED VIEW mv_dws_sales
REFRESH FAST EVERY (interval '15 min')
ENABLE QUERY REWRITE
PARTITION BY date_trunc('month', sale_date)
WITH (orientation = COLUMN, enable_hstore_opt = ON)
AS
SELECT
sale_date,
region_id,
category_id,
SUM(order_count) as order_count,
SUM(total_amount) as total_amount,
SUM(total_cost) as total_cost,
SUM(customer_count) as customer_count
FROM mv_dwd_sales
GROUP BY sale_date, region_id, category_id;

  3. ADS层：即时查询。 

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
    		 
    CREATE MATERIALIZED VIEW mv_ads_daily_summary
REFRESH FAST EVERY (interval '30 min')
ENABLE QUERY REWRITE
WITH (orientation = COLUMN, enable_hstore_opt = ON)
AS
SELECT
sale_date,
SUM(order_count) as total_orders,
SUM(total_amount) as total_sales,
SUM(total_cost) as total_cost,
SUM(customer_count) as total_customers,
SUM(total_amount - total_cost) as total_profit
FROM mv_dws_sales
GROUP BY sale_date;

  4. 业务查询示例：今日销售概况。 

    1
2
    		 
    SELECT * FROM mv_ads_daily_summary
WHERE sale_date = CURRENT_DATE;

父主题： 物化视图应用案例

相关文档