CREATE MATERIALIZED VIEW GP_Order_MV
PARTITION BY (date_trunc(‘month’, CreateTime))
DISTRIBUTED BY HASH(Id)
ORDER BY
(ShipmentDate,
AccountId,
SiteId)
REFRESH ASYNC EVERY(INTERVAL 10 MINUTE)
PROPERTIES (
“replicated_storage” = “true”,
“replication_num” = “3”,
“partition_ttl” = “12 MONTH”,
“partition_refresh_number” = “3”,
“query_rewrite_consistency” = “LOOSE”,
“storage_medium” = “HDD”
)
AS
SELECT
god.Id,
god.SiteId,
god.SalesCurrnecy,
goc.IsFreeShip,
goc.ShipAmtActual,
t0.ShopName,
god.Period,
god.AccountId,
god.Sku,
god.ItemEDSku,
god.OrderNo,
god.ItemId,
god.SalesQty,
FROM
house.tableA AS goc
INNER JOIN house.tableB AS god ON
goc.OrderDetailId = god.Id
INNER JOIN house.tableC AS t0 ON
(god.AccountId = t0.AccountId)
AND (god.SiteID = t0.SiteId)
INNER JOIN house.tableD AS t1 ON
(god.SalesCurrnecy = t1.CurrencyName)
AND (god.Period = t1.DayPeriod);
刷新的时候表查询改写会出现重复数据，而后又正常
重复针状：
ID是重复的，但是两条数据结果有些许差异，应该是合并的时候一条旧的一条新的问题