MySql使用物化视图

MySql使用物化视图

一、主要是用来处理复杂的查询，可以使用视图的形式定时执行数据，直接看下面的sql：

create materialized view mv_status_of_orders_standard COMMENT "订单数达标状态"
DISTRIBUTED BY HASH(渠道ID)
REFRESH ASYNC START("2023-08-23 20:00:00") EVERY(INTERVAL 6 HOUR) as
SELECT
now() 更新时间,
    a.ordersourceid AS 渠道ID,
    a.ordersourcename AS 渠道名,
    b.RealShopName AS 店铺名,
    a.OrderSourceContry AS 站点,
    COUNT(DISTINCT CASE WHEN a.SalesRefunds = 0  AND a.OrderCode != '' THEN a.OrderCode END) AS 订单数,
    CASE WHEN ( (COUNT(DISTINCT CASE WHEN a.SalesRefunds = 0  AND a.OrderCode != '' THEN a.OrderCode END)>=10 and a.OrderSourceContry = 'BR') or 
    (COUNT(DISTINCT CASE WHEN a.SalesRefunds = 0  AND a.OrderCode != '' THEN a.OrderCode END)>=20 and a.OrderSourceContry = 'ID') or
    (COUNT(DISTINCT CASE WHEN a.SalesRefunds = 0  AND a.OrderCode != '' THEN a.OrderCode END)>=30 and a.OrderSourceContry = 'SG') or
    (COUNT(DISTINCT CASE WHEN a.SalesRefunds = 0  AND a.OrderCode != '' THEN a.OrderCode END)>=50 and a.OrderSourceContry = 'TW') or
    (COUNT(DISTINCT CASE WHEN a.SalesRefunds = 0  AND a.OrderCode != '' THEN a.OrderCode END)>=50 and a.OrderSourceContry = 'PH') or
    (COUNT(DISTINCT CASE WHEN a.SalesRefunds = 0  AND a.OrderCode != '' THEN a.OrderCode END)>=75 and a.OrderSourceContry = 'MY') or
    (COUNT(DISTINCT CASE WHEN a.SalesRefunds = 0  AND a.OrderCode != '' THEN a.OrderCode END)>=100 and a.OrderSourceContry = 'TH') or
    (COUNT(DISTINCT CASE WHEN a.SalesRefunds = 0  AND a.OrderCode != '' THEN a.OrderCode END)>=100 and a.OrderSourceContry = 'VN') )
    THEN '达标'
    else '未达标'
END AS '订单数达标状态'
FROM zhcxkj_dw_bi.rb_report_ordersettlement a
LEFT JOIN zhcxkj_dw_bi.rb_ordersource b ON a.Ordersourcename = b.Ordersourcename
WHERE a.OrderSourceType = 97 AND a.PayTime >= DATE_ADD(date_FORMAT(now(), 'yyyy-MM-dd'),interval -30 day) and a.OrderSourceContry in ('BR','ID','SG','TW','PH','MY','TH','VN') GROUP BY a.ordersourceid, a.ordersourcename, b.RealShopName, a.OrderSourceContry

解释：上述sql是6小时执行一次物化视图。

谢谢学习！！！