Background information

Cainiao is a significant part of Alibaba Group that handles numerous packages. Packages in warehouses must undergo spot checks. Relevant personnel view spot check reports generated within the last seven days on a regular basis. These reports contain information such as product information, spot check ratio, pass rate, and number of packages. Large amounts of computing resources are consumed to generate reports. When the number of packages surges due to events such as the Double 11 shopping festival, it takes even longer to generate reports. When a large number of personnel view the reports, large queries sent to databases on an irregular basis also cause instability in the system.

Business effects

Materialized views are used to present the spot check data in the last seven days and are refreshed on an hourly basis. When employees need to view the spot check data, they need only to query the materialized views by adding time-related filter conditions to query statements. The following code block shows an example of query statements:

CREATE MATERIALIZED VIEW xxx_report
REFRESH NEXT now + interval 1 hour
AS
SELECT ...
WHERE create_time >= select current_date() - interval 7 day