Real-Time Sales Reports for an E-Commerce Platform

Scenario

An e-commerce platform needs to view sales reports in real time.

Data volume: 1+ billion records in the sales details table

How to query aggregation statistics by date, region, and product category

Business Requirements

Business requirements: real-time sales dashboard

Refresh frequency: minute-level

Query delay: < 1 second

Solution

Use a three-layer materialized view architecture.

Create the DWD, DWS, and ADS layers of the data warehouse as materialized views. Enable real-time data processing through incremental calculation to meet the requirements of the e-commerce platform for viewing sales reports in real time.

Example

1. Create the DWD layer: data cleansing and standardization

   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. Create the DWS layer: Multi-dimensional summary

   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. Create the ADS layer: instant query

   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. Query business: Today's sales overview

   1 2	SELECT * FROM mv_ads_daily_summary WHERE sale_date = CURRENT_DATE;