# 电商平台实时销售报表案例
#### 场景描述
某电商平台需要实时查看销售报表。
**数据量：** 销售明细表10亿+条记录
**查询模式：** 按日期、地区、商品类别聚合统计
#### 业务需求
业务需求：实时销售大屏。
刷新频率：分钟级。
查询延迟要求：\< 1秒。
#### 解决方案
三层物化视图架构。
将数仓的DWD\>DWS\>ADS都创建为物化视图。通过增量计算实现实时的数据加工，满足电商平台需要实时查看销售报表的需求。
#### 应用示例
1. DWD层：数据清洗与标准化。 
   ```
   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层：多维度汇总。 
   ```
   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层：即时查询。 
   ```
   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. 业务查询示例：今日销售概况。 
   ```
   SELECT * FROM mv_ads_daily_summary
   WHERE sale_date = CURRENT_DATE;
   ```