视图语句：
CREATE
MATERIALIZED VIEW ProductPageList_01
comment ‘ProductPageList接口_001’
DISTRIBUTED BY HASH(UserId)
REFRESH ASYNC
PARTITION BY date_trunc(‘month’, BelongDate)
PROPERTIES(
“partition_ttl_number” = “3”,
“partition_refresh_number” = “1”
)
AS
SELECT t1.Id,
t4.BelongDate,
t4.UserId,
t1.AsinId,
t1.SkuImg AS ‘ImagePath’,
t2.IsReviewMonitor,
t1.Asin,
t1.Sku,
t1.Site,
t1.Title,
t2.FromUrl,
t1.SkuBrand Brand,
t1.AccountId,
t1.AccountName,
t2.BsrRank,
t2.BsrCategory,
t2.Review AS ‘ReviewScore’,
t2.ReviewCount,
t2.VpReviewCount,
t2.ActualReviewCount,
t2.AsinReviewScore,
t2.VpReviewScore,
t1.Price,
t1.PriceUsd,
t1.MaxPrice ‘maxPrice’,
t1.MinPrice ‘minPrice’,
t2.IsAddOn,
t1.ShipType AS ‘IsFba’,
t1.Inbound,
t1.WorkInbound,
t1.ShipInbound,
t1.Available,
t1.Unvailable,
t1.Reserved,
t1.ReservedTransfersQty,
t1.ReservedProcessingQty,
t1.CustomerOrdersReservedQty,
t1.VariantType,
t2.DspStatus,
t6.Sales AS ‘YesterdaySales’,
t6.SalesUsd AS ‘YesterdaySalesUsd’,
t6.Quantities AS ‘YesterdayQuantity’,
t6.Orders AS ‘YesterdayOrders’,
-abs(t6.Spend) AS ‘YesterdaySpend’,
-abs(t6.SpendUsd) AS ‘YesterdaySpendUsd’,
t6.Profits YesterdayProfits,
t6.ProfitsUsd YesterdayProfitsUsd,
ROUND(t6.ProfitsUsd * 100 / (t6.SalesUsd), 2) AS YesterdayProfitsRate,
t6.AdSales YesterdayAdSales,
t6.AdSalesUsd YesterdayAdSalesUsd,
ROUND(t6.SpendUsd * 100 / t6.AdSalesUsd, 2) AS ‘YesterdayAcos’,
ROUND(t6.AdSalesUsd * 100 / t6.SalesUsd, 2) AS ‘YesterdayAsoAs’,
ROUND(t6.SpendUsd * 100 / t6.SalesUsd, 2) AS ‘YesterdayACoAS’,
t7.Quantities AS ‘LastWeekQuantity’,
t7.Orders AS ‘LastWeekOrders’,
t7.Sales AS ‘LastWeekSales’,
t7.SalesUsd AS ‘LastWeekSalesUsd’,
-abs(t7.Spend) AS ‘LastWeekSpend’,
-abs(t7.SpendUsd) AS ‘LastWeekSpendUsd’,
t7.Profits LastWeekProfits,
t7.ProfitsUsd LastWeekProfitsUsd,
ROUND(t7.ProfitsUsd * 100 / (t7.SalesUsd), 2) AS LastWeekProfitsRate,
t7.AdSales LastWeekAdSales,
t7.AdSalesUsd LastWeekAdSalesUsd,
ROUND(t7.SpendUsd * 100 / t7.AdSalesUsd, 2) AS ‘LastWeekAcos’,
ROUND(t7.AdSalesUsd * 100 / t7.SalesUsd, 2) AS ‘LastWeekAsoAs’,
ROUND(t7.SpendUsd * 100 / t7.SalesUsd, 2) AS ‘LastWeekACoAS’,
(case
when t6.MaxPrice = 0 then t2.Price
when t6.MaxPrice is null then t2.Price
else t6.MaxPrice end) maxYesterdayPrice,
(case
when t6.MinPrice = 0 then t2.Price
when t6.MinPrice is null then t2.Price
else t6.MinPrice end) minYesterdayPrice,
(case
when t6.MaxPriceUsd = 0 then t2.PriceUsd
when t6.MaxPriceUsd is null then t2.PriceUsd
else t6.MaxPriceUsd end) maxYesterdayPriceUsd,
(case
when t6.MinPriceUsd = 0 then t2.PriceUsd
when t6.MinPriceUsd is null then t2.PriceUsd
else t6.MinPriceUsd end) minYesterdayPriceUsd,
(CASE
WHEN t7.MaxPrice = 0 THEN t2.Price
WHEN t7.MaxPrice IS NULL THEN t2.Price
ELSE t7.MaxPrice END) maxLastWeekPrice,
(CASE
WHEN t7.MinPrice = 0 THEN t2.Price
WHEN t7.MinPrice IS NULL THEN t2.Price
ELSE t7.MinPrice END) minLastWeekPrice,
(CASE
WHEN t7.MaxPriceUsd = 0 THEN t2.PriceUsd
WHEN t7.MaxPriceUsd IS NULL THEN t2.PriceUsd
ELSE t7.MaxPriceUsd END) maxLastWeekPriceUsd,
(CASE
WHEN t7.MinPriceUsd = 0 THEN t2.PriceUsd
WHEN t7.MinPriceUsd IS NULL THEN t2.PriceUsd
ELSE t7.MinPriceUsd END) minLastWeekPriceUsd,
(case when t6.Price = 0 then t2.Price when t6.Price is null then t2.Price else t6.Price end) YesterdayPrice,
(case
when t6.PriceUsd = 0 then t2.PriceUsd
when t6.PriceUsd is null then t2.PriceUsd
else t6.PriceUsd end) YesterdayPriceUsd,
(case when t7.Price = 0 then t2.Price when t7.Price is null then t2.Price else t7.Price end) LastWeekPrice,
(case
when t7.PriceUsd = 0 then t2.PriceUsd
when t7.PriceUsd is null then t2.PriceUsd
else t7.PriceUsd end) LastWeekPriceUsd,
ROUND(t4.ProfitsUsd * 100 / (t4.SalesUsd), 2) AS ‘ProfitsRate’,
t1.ProductGroup,
t1.HasAd AS ‘IsSetAds’,
t2.BsrRankLs,
t1.ProductStatus AS ‘productStatus’,
t2.BsrCategoryLs,
t2.ParentAsin,
t4.Orders AS ‘Orders’,
-abs(t4.Spend) AS ‘Spend’,
-abs(t4.SpendUsd) AS ‘SpendUsd’,
t4.Orders AS ‘TodayOrders’,
t4.Quantities AS ‘TodayQuantity’,
t4.Sales AS ‘TodaySales’,
t4.Quantities AS ‘Quantity’,
t4.Sales AS ‘Sales’,
t4.SalesUsd AS ‘SalesUsd’,
t4.Profits AS ‘Profits’,
t4.ProfitsUsd AS ‘ProfitsUsd’,
t4.GoodsSale AS ‘GoodsSale’,
t4.SalesUsd AS ‘TodaySalesUsd’,
t4.AdSales,
t4.AdSalesUsd,
t4.AmzCost,
t4.AmzCostUsd,
ROUND(t4.SpendUsd * 100 / t4.AdSalesUsd, 2) AS ‘Acos’,
ROUND(t4.AdSalesUsd * 100 / t4.SalesUsd, 2) AS ‘AsoAs’,
ROUND(t4.SpendUsd * 100 / t4.SalesUsd, 2) AS ‘ACoAS’,
t4.AdOrders AdOrders,
t4.ReplaceProductCount ReplaceProductCount,
abs(t1.FbaFees) AS EstimatedFulfillmentFee,
abs(ROUND(t1.Price * t1.AmzFees, 2)) AS EstimatedCommission,
t1.LineName,
t1.ShapeName,
t1.PrincipalName,
t1.AvailableDays,
abs(t1.AmzFees) AmzFees,
abs(t1.FbaFeesUsd) AS EstimatedFulfillmentFeeUsd,
abs(ROUND(t1.PriceUsd * t1.AmzFees, 2)) as EstimatedCommissionUsd,
t4.ReturnProductCount ‘RefundQuantity’,
ROUND(t4.ReturnProductCount * 100 / t4.Quantities, 2) AS ‘RefundRate’
FROM t_amzdb_product_day_sales t4
left JOIN t_amadb_product t1 ON t4.ProductId = t1.Id and t4.UserId = t1.UserId
LEFT JOIN t_amz_product_info t2
ON t1.UserId = t2.UserId AND t1.AccountId = t2.AccountId AND t1.Site = t2.Country AND
t1.AsinId = t2.Id
LEFT JOIN t_amzdb_product_day_sales t6
ON t2.UserId = t6.UserId and t4.ProductId = t6.ProductId and t6.BelongDate = CURDATE()
LEFT JOIN t_amzdb_product_day_sales t7
ON t2.UserId = t7.UserId and t4.ProductId = t7.ProductId and t7.BelongDate = DAYS_SUB(CURDATE(), 95)
查询语句：
explain analyze
SELECT t1.Id,
t4.BelongDate,
t4.UserId,
t1.AsinId,
t1.SkuImg AS ‘ImagePath’,
t2.IsReviewMonitor,
t1.Asin,
t1.Sku,
t1.Site,
t1.Title,
t2.FromUrl,
t1.SkuBrand Brand,
t1.AccountId,
t1.AccountName,
t2.BsrRank,
t2.BsrCategory,
t2.Review AS ‘ReviewScore’,
t2.ReviewCount,
t2.VpReviewCount,
t2.ActualReviewCount,
t2.AsinReviewScore,
t2.VpReviewScore,
t1.Price,
t1.PriceUsd,
t1.MaxPrice ‘maxPrice’,
t1.MinPrice ‘minPrice’,
t2.IsAddOn,
t1.ShipType AS ‘IsFba’,
t1.Inbound,
t1.WorkInbound,
t1.ShipInbound,
t1.Available,
t1.Unvailable,
t1.Reserved,
t1.ReservedTransfersQty,
t1.ReservedProcessingQty,
t1.CustomerOrdersReservedQty,
t1.VariantType,
t2.DspStatus,
t6.Sales AS ‘YesterdaySales’,
t6.SalesUsd AS ‘YesterdaySalesUsd’,
t6.Quantities AS ‘YesterdayQuantity’,
t6.Orders AS ‘YesterdayOrders’,
-abs(t6.Spend) AS ‘YesterdaySpend’,
-abs(t6.SpendUsd) AS ‘YesterdaySpendUsd’,
t6.Profits YesterdayProfits,
t6.ProfitsUsd YesterdayProfitsUsd,
ROUND(t6.ProfitsUsd * 100 / (t6.SalesUsd), 2) AS YesterdayProfitsRate,
t6.AdSales YesterdayAdSales,
t6.AdSalesUsd YesterdayAdSalesUsd,
ROUND(t6.SpendUsd * 100 / t6.AdSalesUsd, 2) AS ‘YesterdayAcos’,
ROUND(t6.AdSalesUsd * 100 / t6.SalesUsd, 2) AS ‘YesterdayAsoAs’,
ROUND(t6.SpendUsd * 100 / t6.SalesUsd, 2) AS ‘YesterdayACoAS’,
t7.Quantities AS ‘LastWeekQuantity’,
t7.Orders AS ‘LastWeekOrders’,
t7.Sales AS ‘LastWeekSales’,
t7.SalesUsd AS ‘LastWeekSalesUsd’,
-abs(t7.Spend) AS ‘LastWeekSpend’,
-abs(t7.SpendUsd) AS ‘LastWeekSpendUsd’,
t7.Profits LastWeekProfits,
t7.ProfitsUsd LastWeekProfitsUsd,
ROUND(t7.ProfitsUsd * 100 / (t7.SalesUsd), 2) AS LastWeekProfitsRate,
t7.AdSales LastWeekAdSales,
t7.AdSalesUsd LastWeekAdSalesUsd,
ROUND(t7.SpendUsd * 100 / t7.AdSalesUsd, 2) AS ‘LastWeekAcos’,
ROUND(t7.AdSalesUsd * 100 / t7.SalesUsd, 2) AS ‘LastWeekAsoAs’,
ROUND(t7.SpendUsd * 100 / t7.SalesUsd, 2) AS ‘LastWeekACoAS’,
(case
when t6.MaxPrice = 0 then t2.Price
when t6.MaxPrice is null then t2.Price
else t6.MaxPrice end) maxYesterdayPrice,
(case
when t6.MinPrice = 0 then t2.Price
when t6.MinPrice is null then t2.Price
else t6.MinPrice end) minYesterdayPrice,
(case
when t6.MaxPriceUsd = 0 then t2.PriceUsd
when t6.MaxPriceUsd is null then t2.PriceUsd
else t6.MaxPriceUsd end) maxYesterdayPriceUsd,
(case
when t6.MinPriceUsd = 0 then t2.PriceUsd
when t6.MinPriceUsd is null then t2.PriceUsd
else t6.MinPriceUsd end) minYesterdayPriceUsd,
(CASE
WHEN t7.MaxPrice = 0 THEN t2.Price
WHEN t7.MaxPrice IS NULL THEN t2.Price
ELSE t7.MaxPrice END) maxLastWeekPrice,
(CASE
WHEN t7.MinPrice = 0 THEN t2.Price
WHEN t7.MinPrice IS NULL THEN t2.Price
ELSE t7.MinPrice END) minLastWeekPrice,
(CASE
WHEN t7.MaxPriceUsd = 0 THEN t2.PriceUsd
WHEN t7.MaxPriceUsd IS NULL THEN t2.PriceUsd
ELSE t7.MaxPriceUsd END) maxLastWeekPriceUsd,
(CASE
WHEN t7.MinPriceUsd = 0 THEN t2.PriceUsd
WHEN t7.MinPriceUsd IS NULL THEN t2.PriceUsd
ELSE t7.MinPriceUsd END) minLastWeekPriceUsd,
(case when t6.Price = 0 then t2.Price when t6.Price is null then t2.Price else t6.Price end) YesterdayPrice,
(case
when t6.PriceUsd = 0 then t2.PriceUsd
when t6.PriceUsd is null then t2.PriceUsd
else t6.PriceUsd end) YesterdayPriceUsd,
(case when t7.Price = 0 then t2.Price when t7.Price is null then t2.Price else t7.Price end) LastWeekPrice,
(case
when t7.PriceUsd = 0 then t2.PriceUsd
when t7.PriceUsd is null then t2.PriceUsd
else t7.PriceUsd end) LastWeekPriceUsd,
ROUND(t4.ProfitsUsd * 100 / (t4.SalesUsd), 2) AS ‘ProfitsRate’,
t1.ProductGroup,
t1.HasAd AS ‘IsSetAds’,
t2.BsrRankLs,
t1.ProductStatus AS ‘productStatus’,
t2.BsrCategoryLs,
t2.ParentAsin,
t4.Orders AS ‘Orders’,
-abs(t4.Spend) AS ‘Spend’,
-abs(t4.SpendUsd) AS ‘SpendUsd’,
t4.Orders AS ‘TodayOrders’,
t4.Quantities AS ‘TodayQuantity’,
t4.Sales AS ‘TodaySales’,
t4.Quantities AS ‘Quantity’,
t4.Sales AS ‘Sales’,
t4.SalesUsd AS ‘SalesUsd’,
t4.Profits AS ‘Profits’,
t4.ProfitsUsd AS ‘ProfitsUsd’,
t4.GoodsSale AS ‘GoodsSale’,
t4.SalesUsd AS ‘TodaySalesUsd’,
t4.AdSales,
t4.AdSalesUsd,
t4.AmzCost,
t4.AmzCostUsd,
ROUND(t4.SpendUsd * 100 / t4.AdSalesUsd, 2) AS ‘Acos’,
ROUND(t4.AdSalesUsd * 100 / t4.SalesUsd, 2) AS ‘AsoAs’,
ROUND(t4.SpendUsd * 100 / t4.SalesUsd, 2) AS ‘ACoAS’,
t4.AdOrders AdOrders,
t4.ReplaceProductCount ReplaceProductCount,
abs(t1.FbaFees) AS EstimatedFulfillmentFee,
abs(ROUND(t1.Price * t1.AmzFees, 2)) AS EstimatedCommission,
t1.LineName,
t1.ShapeName,
t1.PrincipalName,
t1.AvailableDays,
abs(t1.AmzFees) AmzFees,
abs(t1.FbaFeesUsd) AS EstimatedFulfillmentFeeUsd,
abs(ROUND(t1.PriceUsd * t1.AmzFees, 2)) as EstimatedCommissionUsd,
t4.ReturnProductCount ‘RefundQuantity’,
ROUND(t4.ReturnProductCount * 100 / t4.Quantities, 2) AS ‘RefundRate’
FROM t_amzdb_product_day_sales t4
left JOIN t_amadb_product t1 ON t4.ProductId = t1.Id and t4.UserId = t1.UserId
LEFT JOIN t_amz_product_info t2
ON t1.UserId = t2.UserId AND t1.AccountId = t2.AccountId AND t1.Site = t2.Country AND
t1.AsinId = t2.Id
LEFT JOIN t_amzdb_product_day_sales t6
ON t2.UserId = t6.UserId and t4.ProductId = t6.ProductId and t6.BelongDate = ‘2024-06-25’
LEFT JOIN t_amzdb_product_day_sales t7
ON t2.UserId = t7.UserId and t4.ProductId = t7.ProductId and t7.BelongDate = ‘2024-06-18’
WHERE t4.UserId = 27
and ((t4.AccountId = 911 and t4.Site = ‘ca’) or (t4.AccountId = 911 and t4.Site = ‘mx’) or
(t4.AccountId = 911 and t4.Site = ‘us’))
AND t4.BelongDate IN (‘2024-06-25’)
AND t4.ProductId in (SELECT t1.Id
FROM t_amadb_product t1
LEFT JOIN t_amz_product_info t2
ON t1.UserId = t2.UserId AND t1.AccountId = t2.AccountId AND
t1.Site = t2.Country AND t1.AsinId = t2.Id
where t1.UserId = 27
and ((t1.AccountId = 911 and t1.Site = ‘ca’) or (t1.AccountId = 911 and t1.Site = ‘mx’) or
(t1.AccountId = 911 and t1.Site = ‘us’))
AND t1.ProductStatus in (0)
AND t1.VariantType in (0, 1))
order by Orders desc, t1.Id ASC
limit 0, 50
查询计划：
