任务描述
1. 问题分析

  智能刊登gaussdb 慢sql  top 10

  查看执行计划需要41步，大多全表扫描，需要500+秒，分析是缺少索引

<derived2> ALL Using temporary; Using filesort 4235200

2.解决方案

索引优化建议：
  1. 建议在数据表 irobotbox_marketpublish.pp_phoenixplan_ebay_queue 的 StrategyType,Status,CustomerId,Time 列上添加如下索引，以提高查询性能：
    ALTER TABLE `irobotbox_marketpublish`.`pp_phoenixplan_ebay_queue` ADD INDEX `idx_StrategyType_Status_CustomerId_Time` (`StrategyType`, `Status`, `CustomerId`, `Time`)
  2. 建议在数据表 irobotbox_marketpublish.pp_phoenixplan_kogan_queue 的 StrategyType,Status,CustomerId,Time 列上添加如下索引，以提高查询性能：
    ALTER TABLE `irobotbox_marketpublish`.`pp_phoenixplan_kogan_queue` ADD INDEX `idx_StrategyType_Status_CustomerId_Time` (`StrategyType`, `Status`, `CustomerId`, `Time`)
  3. 建议在数据表 irobotbox_marketpublish.pp_phoenixplan_lazada_queue 的 StrategyType,Status,CustomerId,Time 列上添加如下索引，以提高查询性能：
    ALTER TABLE `irobotbox_marketpublish`.`pp_phoenixplan_lazada_queue` ADD INDEX `idx_StrategyType_Status_CustomerId_Time` (`StrategyType`, `Status`, `CustomerId`, `Time`)
  4. 建议在数据表 irobotbox_marketpublish.ap_autopublish_ebay_queue 的 CustomerId,Time 列上添加如下索引，以提高查询性能：
    ALTER TABLE `irobotbox_marketpublish`.`ap_autopublish_ebay_queue` ADD INDEX `idx_CustomerId_Time` (`CustomerId`, `Time`)
  5. 建议在数据表 irobotbox_marketpublish.pp_phoenixplan_shopee_queue 的 CustomerId,Time 列上添加如下索引，以提高查询性能：
    ALTER TABLE `irobotbox_marketpublish`.`pp_phoenixplan_shopee_queue` ADD INDEX `idx_CustomerId_Time` (`CustomerId`, `Time`)
  6. 建议在数据表 irobotbox_marketpublish.pp_phoenixplan_ebay_queue 的 CustomerId,Time 列上添加如下索引，以提高查询性能：
    ALTER TABLE `irobotbox_marketpublish`.`pp_phoenixplan_ebay_queue` ADD INDEX `idx_CustomerId_Time` (`CustomerId`, `Time`)
  7. 建议在数据表 irobotbox_marketpublish.pp_phoenixplan_shopee_queue 的 StrategyType,Status,CustomerId,Time 列上添加如下索引，以提高查询性能：
    ALTER TABLE `irobotbox_marketpublish`.`pp_phoenixplan_shopee_queue` ADD INDEX `idx_StrategyType_Status_CustomerId_Time` (`StrategyType`, `Status`, `CustomerId`, `Time`)
  8. 建议在数据表 irobotbox_marketpublish.pp_phoenixplan_cdiscount_queue 的 Status,StrategyType,CustomerId,Time 列上添加如下索引，以提高查询性能：
    ALTER TABLE `irobotbox_marketpublish`.`pp_phoenixplan_cdiscount_queue` ADD INDEX `idx_Status_StrategyType_CustomerId_Time` (`Status`, `StrategyType`, `CustomerId`, `Time`)
  9. 建议在数据表 irobotbox_marketpublish.ap_autopublish_wish_queue 的 CustomerId,Time 列上添加如下索引，以提高查询性能：
    ALTER TABLE `irobotbox_marketpublish`.`ap_autopublish_wish_queue` ADD INDEX `idx_CustomerId_Time` (`CustomerId`, `Time`)
  10. 建议在数据表 irobotbox_marketpublish.pp_phoenixplan_wish_queue 的 StrategyType,Status,CustomerId,Time 列上添加如下索引，以提高查询性能：
    ALTER TABLE `irobotbox_marketpublish`.`pp_phoenixplan_wish_queue` ADD INDEX `idx_StrategyType_Status_CustomerId_Time` (`StrategyType`, `Status`, `CustomerId`, `Time`)
  11. 建议在数据表 irobotbox_marketpublish.pp_phoenixplan_lazada_queue 的 CustomerId,Time 列上添加如下索引，以提高查询性能：
    ALTER TABLE `irobotbox_marketpublish`.`pp_phoenixplan_lazada_queue` ADD INDEX `idx_CustomerId_Time` (`CustomerId`, `Time`)
  12. 建议在数据表 irobotbox_marketpublish.pp_phoenixplan_wish_queue 的 CustomerId,Time 列上添加如下索引，以提高查询性能：
    ALTER TABLE `irobotbox_marketpublish`.`pp_phoenixplan_wish_queue` ADD INDEX `idx_CustomerId_Time` (`CustomerId`, `Time`)
  13. 建议在数据表 irobotbox_marketpublish.ap_autopublish_aliexpress_queue 的 CustomerId,Time 列上添加如下索引，以提高查询性能：
    ALTER TABLE `irobotbox_marketpublish`.`ap_autopublish_aliexpress_queue` ADD INDEX `idx_CustomerId_Time` (`CustomerId`, `Time`)
  14. 建议在数据表 irobotbox_marketpublish.ap_autopublish_allegro_queue 的 CustomerId,Time 列上添加如下索引，以提高查询性能：
    ALTER TABLE `irobotbox_marketpublish`.`ap_autopublish_allegro_queue` ADD INDEX `idx_CustomerId_Time` (`CustomerId`, `Time`)
  15. 建议在数据表 irobotbox_marketpublish.ap_autopublish_cdiscount_queue 的 CustomerId,Time 列上添加如下索引，以提高查询性能：
    ALTER TABLE `irobotbox_marketpublish`.`ap_autopublish_cdiscount_queue` ADD INDEX `idx_CustomerId_Time` (`CustomerId`, `Time`)
  16. 建议在数据表 irobotbox_marketpublish.ap_autopublish_kogan_queue 的 CustomerId,Time 列上添加如下索引，以提高查询性能：
    ALTER TABLE `irobotbox_marketpublish`.`ap_autopublish_kogan_queue` ADD INDEX `idx_CustomerId_Time` (`CustomerId`, `Time`)
  17. 建议在数据表 irobotbox_marketpublish.pp_phoenixplan_aliexpress_queue 的 CustomerId,Time 列上添加如下索引，以提高查询性能：
    ALTER TABLE `irobotbox_marketpublish`.`pp_phoenixplan_aliexpress_queue` ADD INDEX `idx_CustomerId_Time` (`CustomerId`, `Time`)
  18. 建议在数据表 irobotbox_marketpublish.pp_phoenixplan_cdiscount_queue 的 CustomerId,Time 列上添加如下索引，以提高查询性能：
    ALTER TABLE `irobotbox_marketpublish`.`pp_phoenixplan_cdiscount_queue` ADD INDEX `idx_CustomerId_Time` (`CustomerId`, `Time`)
  19. 建议在数据表 irobotbox_marketpublish.ap_autopublish_lazada_queue 的 CustomerId,Time 列上添加如下索引，以提高查询性能：
    ALTER TABLE `irobotbox_marketpublish`.`ap_autopublish_lazada_queue` ADD INDEX `idx_CustomerId_Time` (`CustomerId`, `Time`)
  20. 建议在数据表 irobotbox_marketpublish.ap_autopublish_amazon_queue 的 CustomerId,Time 列上添加如下索引，以提高查询性能：
    ALTER TABLE `irobotbox_marketpublish`.`ap_autopublish_amazon_queue` ADD INDEX `idx_CustomerId_Time` (`CustomerId`, `Time`)
  21. 建议在数据表 irobotbox_marketpublish.ap_autopublish_mercadolibre_queue 的 CustomerId,Time 列上添加如下索引，以提高查询性能：
    ALTER TABLE `irobotbox_marketpublish`.`ap_autopublish_mercadolibre_queue` ADD INDEX `idx_CustomerId_Time` (`CustomerId`, `Time`)
  22. 建议在数据表 irobotbox_marketpublish.pp_phoenixplan_kogan_queue 的 CustomerId,Time 列上添加如下索引，以提高查询性能：
    ALTER TABLE `irobotbox_marketpublish`.`pp_phoenixplan_kogan_queue` ADD INDEX `idx_CustomerId_Time` (`CustomerId`, `Time`)
  23. 建议在数据表 irobotbox_marketpublish.pp_phoenixplan_aliexpress_queue 的 StrategyType,Status,CustomerId,Time 列上添加如下索引，以提高查询性能：
    ALTER TABLE `irobotbox_marketpublish`.`pp_phoenixplan_aliexpress_queue` ADD INDEX `idx_StrategyType_Status_CustomerId_Time` (`StrategyType`, `Status`, `CustomerId`, `Time`)
  24. 建议在数据表 irobotbox_marketpublish.pp_phoenixplan_mercadolibre_queue 的 StrategyType,Status,CustomerId,Time 列上添加如下索引，以提高查询性能：
    ALTER TABLE `irobotbox_marketpublish`.`pp_phoenixplan_mercadolibre_queue` ADD INDEX `idx_StrategyType_Status_CustomerId_Time` (`StrategyType`, `Status`, `CustomerId`, `Time`)
  25. 建议在数据表 irobotbox_marketpublish.ap_autopublish_shopee_queue 的 CustomerId,Time 列上添加如下索引，以提高查询性能：
    ALTER TABLE `irobotbox_marketpublish`.`ap_autopublish_shopee_queue` ADD INDEX `idx_CustomerId_Time` (`CustomerId`, `Time`)
  26. 建议在数据表 irobotbox_marketpublish.pp_phoenixplan_allegro_queue 的 CustomerId,Time 列上添加如下索引，以提高查询性能：
    ALTER TABLE `irobotbox_marketpublish`.`pp_phoenixplan_allegro_queue` ADD INDEX `idx_CustomerId_Time` (`CustomerId`, `Time`)
  27. 建议在数据表 irobotbox_marketpublish.pp_phoenixplan_mercadolibre_queue 的 CustomerId,Time 列上添加如下索引，以提高查询性能：
    ALTER TABLE `irobotbox_marketpublish`.`pp_phoenixplan_mercadolibre_queue` ADD INDEX `idx_CustomerId_Time` (`CustomerId`, `Time`)
  28. 建议在数据表 irobotbox_marketpublish.pp_phoenixplan_allegro_queue 的 StrategyType,Status,CustomerId,Time 列上添加如下索引，以提高查询性能：
    ALTER TABLE `irobotbox_marketpublish`.`pp_phoenixplan_allegro_queue` ADD INDEX `idx_StrategyType_Status_CustomerId_Time` (`StrategyType`, `Status`, `CustomerId`, `Time`)


3.原sql

SELECT t.CustomerId, t.日期, b.MarketId AS "平台编号", b.NAME AS "平台", t.OrderSourceId AS "店铺ID"
, c.OrderSourceName AS "店铺名称", c.OrderSourceCountry AS "店铺国家", t.StrategyId, t.策略名称, t.策略类型
, t.上架listing总数, t.上架listing成功数, t.开始刊登小时, t.结束刊登小时, t.每小时上架个数
, t.小时, t1.智能刊登上架数, t1.智能刊登下架数
FROM (SELECT a.CustomerId, a.OrderSourceId, a.MarketId, DATE_FORMAT(a.Time, '%Y-%m-%d') AS "日期", a.StrategyId
, d.NAME AS "策略名称", '自动刊登' AS 策略类型, COUNT(0) AS '上架listing总数', COUNT(CASE WHEN a.STATUS = 'success' THEN a.Id ELSE NULL END) AS '上架listing成功数', d.BeginPublishHour AS "开始刊登小时"
, d.EndPublishHour AS "结束刊登小时", d.DailyHourPublishs AS "每小时上架个数", d.EndPublishHour - d.BeginPublishHour AS "小时"
FROM (SELECT MarketId, CustomerId, StrategyId, OrderSourceId, TIME
, STATUS, id
FROM ap_autopublish_aliexpress_queue
WHERE CustomerId = 1
AND TIME >= DATE_ADD(CURDATE(), INTERVAL -1 DAY)
AND TIME < CURDATE()
UNION ALL
SELECT MarketId, CustomerId, StrategyId, OrderSourceId, TIME
, STATUS, id
FROM ap_autopublish_allegro_queue
WHERE CustomerId = 1
AND TIME >= DATE_ADD(CURDATE(), INTERVAL -1 DAY)
AND TIME < CURDATE()
UNION ALL
SELECT MarketId, CustomerId, StrategyId, OrderSourceId, TIME
, STATUS, id
FROM ap_autopublish_amazon_queue
WHERE CustomerId = 1
AND TIME >= DATE_ADD(CURDATE(), INTERVAL -1 DAY)
AND TIME < CURDATE()
UNION ALL
SELECT MarketId, CustomerId, StrategyId, OrderSourceId, TIME
, STATUS, id
FROM ap_autopublish_cdiscount_queue
WHERE CustomerId = 1
AND TIME >= DATE_ADD(CURDATE(), INTERVAL -1 DAY)
AND TIME < CURDATE()
UNION ALL
SELECT MarketId, CustomerId, StrategyId, OrderSourceId, TIME
, STATUS, id
FROM ap_autopublish_ebay_queue
WHERE CustomerId = 1
AND TIME >= DATE_ADD(CURDATE(), INTERVAL -1 DAY)
AND TIME < CURDATE()
UNION ALL
SELECT MarketId, CustomerId, StrategyId, OrderSourceId, TIME
, STATUS, id
FROM ap_autopublish_jdid_queue
WHERE CustomerId = 1
AND TIME >= DATE_ADD(CURDATE(), INTERVAL -1 DAY)
AND TIME < CURDATE()
UNION ALL
SELECT MarketId, CustomerId, StrategyId, OrderSourceId, TIME
, STATUS, id
FROM ap_autopublish_joom_queue
WHERE CustomerId = 1
AND TIME >= DATE_ADD(CURDATE(), INTERVAL -1 DAY)
AND TIME < CURDATE()
UNION ALL
SELECT MarketId, CustomerId, StrategyId, OrderSourceId, TIME
, STATUS, id
FROM ap_autopublish_jumia_queue
WHERE CustomerId = 1
AND TIME >= DATE_ADD(CURDATE(), INTERVAL -1 DAY)
AND TIME < CURDATE()
UNION ALL
SELECT MarketId, CustomerId, StrategyId, OrderSourceId, TIME
, STATUS, id
FROM ap_autopublish_kogan_queue
WHERE CustomerId = 1
AND TIME >= DATE_ADD(CURDATE(), INTERVAL -1 DAY)
AND TIME < CURDATE()
UNION ALL
SELECT MarketId, CustomerId, StrategyId, OrderSourceId, TIME
, STATUS, id
FROM ap_autopublish_lazada_queue
WHERE CustomerId = 1
AND TIME >= DATE_ADD(CURDATE(), INTERVAL -1 DAY)
AND TIME < CURDATE()
UNION ALL
SELECT MarketId, CustomerId, StrategyId, OrderSourceId, TIME
, STATUS, id
FROM ap_autopublish_mercadolibre_queue
WHERE CustomerId = 1
AND TIME >= DATE_ADD(CURDATE(), INTERVAL -1 DAY)
AND TIME < CURDATE()
UNION ALL
SELECT MarketId, CustomerId, StrategyId, OrderSourceId, TIME
, STATUS, id
FROM ap_autopublish_newegg_queue
WHERE CustomerId = 1
AND TIME >= DATE_ADD(CURDATE(), INTERVAL -1 DAY)
AND TIME < CURDATE()
UNION ALL
SELECT MarketId, CustomerId, StrategyId, OrderSourceId, TIME
, STATUS, id
FROM ap_autopublish_realde_queue
WHERE CustomerId = 1
AND TIME >= DATE_ADD(CURDATE(), INTERVAL -1 DAY)
AND TIME < CURDATE()
UNION ALL
SELECT MarketId, CustomerId, StrategyId, OrderSourceId, TIME
, STATUS, id
FROM ap_autopublish_shopee_queue
WHERE CustomerId = 1
AND TIME >= DATE_ADD(CURDATE(), INTERVAL -1 DAY)
AND TIME < CURDATE()
UNION ALL
SELECT MarketId, CustomerId, StrategyId, OrderSourceId, TIME
, STATUS, id
FROM ap_autopublish_tophatter_queue
WHERE CustomerId = 1
AND TIME >= DATE_ADD(CURDATE(), INTERVAL -1 DAY)
AND TIME < CURDATE()
UNION ALL
SELECT MarketId, CustomerId, StrategyId, OrderSourceId, TIME
, STATUS, id
FROM ap_autopublish_vova_queue
WHERE CustomerId = 1
AND TIME >= DATE_ADD(CURDATE(), INTERVAL -1 DAY)
AND TIME < CURDATE()
UNION ALL
SELECT MarketId, CustomerId, StrategyId, OrderSourceId, TIME
, STATUS, id
FROM ap_autopublish_walmart_queue
WHERE CustomerId = 1
AND TIME >= DATE_ADD(CURDATE(), INTERVAL -1 DAY)
AND TIME < CURDATE()
UNION ALL
SELECT MarketId, CustomerId, StrategyId, OrderSourceId, TIME
, STATUS, id
FROM ap_autopublish_wayfair_queue
WHERE CustomerId = 1
AND TIME >= DATE_ADD(CURDATE(), INTERVAL -1 DAY)
AND TIME < CURDATE()
UNION ALL
SELECT MarketId, CustomerId, StrategyId, OrderSourceId, TIME
, STATUS, id
FROM ap_autopublish_wish_queue
WHERE CustomerId = 1
AND TIME >= DATE_ADD(CURDATE(), INTERVAL -1 DAY)
AND TIME < CURDATE()
) a
LEFT JOIN ap_autopublish_strategy d ON a.CustomerID = d.CustomerId
AND a.StrategyId = d.Id
GROUP BY a.CustomerId, a.OrderSourceId, a.MarketId, DATE_FORMAT(a.Time, '%Y-%m-%d'), a.StrategyId
UNION ALL
SELECT a.CustomerId, a.OrderSourceId, a.MarketId, DATE_FORMAT(a.Time, '%Y-%m-%d') AS "日期", a.StrategyId
, d.NAME AS "策略名称", CASE d.Type WHEN 2 THEN '重新刊登' WHEN 3 THEN '自动下架' WHEN 4 THEN '自动调价' WHEN 5 THEN '自动促销' END AS 策略类型, COUNT(0) AS '上架listing总数', COUNT(CASE WHEN a.STATUS = 'success' THEN a.Id ELSE NULL END) AS '上架listing成功数', d.BeginPublishHour AS "开始刊登小时"
, d.EndPublishHour AS "结束刊登小时", d.DailyHourNumberOfTimes AS "每小时上架个数", d.EndPublishHour - d.BeginPublishHour AS "小时"
FROM (SELECT MarketId, CustomerId, StrategyId, OrderSourceId, TIME
, STATUS, id
FROM pp_phoenixplan_aliexpress_queue
WHERE CustomerId = 1
AND TIME >= DATE_ADD(CURDATE(), INTERVAL -1 DAY)
AND TIME < CURDATE()
UNION ALL
SELECT MarketId, CustomerId, StrategyId, OrderSourceId, TIME
, STATUS, id
FROM pp_phoenixplan_jdid_queue
WHERE CustomerId = 1
AND TIME >= DATE_ADD(CURDATE(), INTERVAL -1 DAY)
AND TIME < CURDATE()
UNION ALL
SELECT MarketId, CustomerId, StrategyId, OrderSourceId, TIME
, STATUS, id
FROM pp_phoenixplan_joom_queue
WHERE CustomerId = 1
AND TIME >= DATE_ADD(CURDATE(), INTERVAL -1 DAY)
AND TIME < CURDATE()
UNION ALL
SELECT MarketId, CustomerId, StrategyId, OrderSourceId, TIME
, STATUS, id
FROM pp_phoenixplan_jumia_queue
WHERE CustomerId = 1
AND TIME >= DATE_ADD(CURDATE(), INTERVAL -1 DAY)
AND TIME < CURDATE()
UNION ALL
SELECT MarketId, CustomerId, StrategyId, OrderSourceId, TIME
, STATUS, id
FROM pp_phoenixplan_newegg_queue
WHERE CustomerId = 1
AND TIME >= DATE_ADD(CURDATE(), INTERVAL -1 DAY)
AND TIME < CURDATE()
UNION ALL
SELECT MarketId, CustomerId, StrategyId, OrderSourceId, TIME
, STATUS, id
FROM pp_phoenixplan_realde_queue
WHERE CustomerId = 1
AND TIME >= DATE_ADD(CURDATE(), INTERVAL -1 DAY)
AND TIME < CURDATE()
UNION ALL
SELECT MarketId, CustomerId, StrategyId, OrderSourceId, TIME
, STATUS, id
FROM pp_phoenixplan_tophatter_queue
WHERE CustomerId = 1
AND TIME >= DATE_ADD(CURDATE(), INTERVAL -1 DAY)
AND TIME < CURDATE()
UNION ALL
SELECT MarketId, CustomerId, StrategyId, OrderSourceId, TIME
, STATUS, id
FROM pp_phoenixplan_vova_queue
WHERE CustomerId = 1
AND TIME >= DATE_ADD(CURDATE(), INTERVAL -1 DAY)
AND TIME < CURDATE()
UNION ALL
SELECT MarketId, CustomerId, StrategyId, OrderSourceId, TIME
, STATUS, id
FROM pp_phoenixplan_walmart_queue
WHERE CustomerId = 1
AND TIME >= DATE_ADD(CURDATE(), INTERVAL -1 DAY)
AND TIME < CURDATE()
UNION ALL
SELECT MarketId, CustomerId, StrategyId, OrderSourceId, TIME
, STATUS, id
FROM pp_phoenixplan_wayfair_queue
WHERE CustomerId = 1
AND TIME >= DATE_ADD(CURDATE(), INTERVAL -1 DAY)
AND TIME < CURDATE()
UNION ALL
SELECT MarketId, CustomerId, StrategyId, OrderSourceId, TIME
, STATUS, id
FROM pp_phoenixplan_wish_queue
WHERE CustomerId = 1
AND TIME >= DATE_ADD(CURDATE(), INTERVAL -1 DAY)
AND TIME < CURDATE()
UNION ALL
SELECT MarketId, CustomerId, StrategyId, OrderSourceId, TIME
, STATUS, id
FROM pp_phoenixplan_allegro_queue
WHERE CustomerId = 1
AND TIME >= DATE_ADD(CURDATE(), INTERVAL -1 DAY)
AND TIME < CURDATE()
UNION ALL
SELECT MarketId, CustomerId, StrategyId, OrderSourceId, TIME
, STATUS, id
FROM pp_phoenixplan_amazon_queue
WHERE CustomerId = 1
AND TIME >= DATE_ADD(CURDATE(), INTERVAL -1 DAY)
AND TIME < CURDATE()
UNION ALL
SELECT MarketId, CustomerId, StrategyId, OrderSourceId, TIME
, STATUS, id
FROM pp_phoenixplan_cdiscount_queue
WHERE CustomerId = 1
AND TIME >= DATE_ADD(CURDATE(), INTERVAL -1 DAY)
AND TIME < CURDATE()
UNION ALL
SELECT MarketId, CustomerId, StrategyId, OrderSourceId, TIME
, STATUS, id
FROM pp_phoenixplan_ebay_queue
WHERE CustomerId = 1
AND TIME >= DATE_ADD(CURDATE(), INTERVAL -1 DAY)
AND TIME < CURDATE()
UNION ALL
SELECT MarketId, CustomerId, StrategyId, OrderSourceId, TIME
, STATUS, id
FROM pp_phoenixplan_kogan_queue
WHERE CustomerId = 1
AND TIME >= DATE_ADD(CURDATE(), INTERVAL -1 DAY)
AND TIME < CURDATE()
UNION ALL
SELECT MarketId, CustomerId, StrategyId, OrderSourceId, TIME
, STATUS, id
FROM pp_phoenixplan_lazada_queue
WHERE CustomerId = 1
AND TIME >= DATE_ADD(CURDATE(), INTERVAL -1 DAY)
AND TIME < CURDATE()
UNION ALL
SELECT MarketId, CustomerId, StrategyId, OrderSourceId, TIME
, STATUS, id
FROM pp_phoenixplan_mercadolibre_queue
WHERE CustomerId = 1
AND TIME >= DATE_ADD(CURDATE(), INTERVAL -1 DAY)
AND TIME < CURDATE()
UNION ALL
SELECT MarketId, CustomerId, StrategyId, OrderSourceId, TIME
, STATUS, id
FROM pp_phoenixplan_shopee_queue
WHERE CustomerId = 1
AND TIME >= DATE_ADD(CURDATE(), INTERVAL -1 DAY)
AND TIME < CURDATE()
) a
LEFT JOIN sys_market b ON a.CustomerId = b.CustomerId
AND a.MarketId = b.MarketId
LEFT JOIN sys_ordersource c ON a.CustomerId = c.CustomerId
AND a.OrderSourceId = c.OrderSourceId
LEFT JOIN pp_phoenixplan_strategy d ON a.CustomerId = d.CustomerId
AND a.StrategyId = d.Id
GROUP BY a.CustomerId, a.OrderSourceId, a.MarketId, DATE_FORMAT(a.Time, '%Y-%m-%d'), a.StrategyId, d.NAME, CASE d.Type WHEN 2 THEN '重新刊登' WHEN 3 THEN '自动下架' WHEN 4 THEN '自动调价' WHEN 5 THEN '自动促销' END
) t
LEFT JOIN sys_market b ON t.CustomerId = b.CustomerId
AND t.MarketId = b.MarketId
LEFT JOIN sys_ordersource c ON t.CustomerId = c.CustomerId
AND t.OrderSourceId = c.OrderSourceId
LEFT JOIN (SELECT f.OrderSourceId, f.StrategyId, COUNT(0) AS 智能刊登下架数, COUNT(CASE WHEN f.StrategyType = 2 THEN 0 ELSE NULL END) AS 智能刊登上架数
FROM (SELECT *
FROM pp_phoenixplan_aliexpress_queue
WHERE CustomerId = 1
AND STATUS = 'success'
AND StrategyType IN (2, 3)
AND TIME >= DATE_ADD(CURDATE(), INTERVAL -1 DAY)
AND TIME < CURDATE()
UNION ALL
SELECT *
FROM pp_phoenixplan_jdid_queue
WHERE CustomerId = 1
AND STATUS = 'success'
AND StrategyType IN (2, 3)
AND TIME >= DATE_ADD(CURDATE(), INTERVAL -1 DAY)
AND TIME < CURDATE()
UNION ALL
SELECT *
FROM pp_phoenixplan_joom_queue
WHERE CustomerId = 1
AND STATUS = 'success'
AND StrategyType IN (2, 3)
AND TIME >= DATE_ADD(CURDATE(), INTERVAL -1 DAY)
AND TIME < CURDATE()
UNION ALL
SELECT *
FROM pp_phoenixplan_jumia_queue
WHERE CustomerId = 1
AND STATUS = 'success'
AND StrategyType IN (2, 3)
AND TIME >= DATE_ADD(CURDATE(), INTERVAL -1 DAY)
AND TIME < CURDATE()
UNION ALL
SELECT *
FROM pp_phoenixplan_newegg_queue
WHERE CustomerId = 1
AND STATUS = 'success'
AND StrategyType IN (2, 3)
AND TIME >= DATE_ADD(CURDATE(), INTERVAL -1 DAY)
AND TIME < CURDATE()
UNION ALL
SELECT *
FROM pp_phoenixplan_realde_queue
WHERE CustomerId = 1
AND STATUS = 'success'
AND StrategyType IN (2, 3)
AND TIME >= DATE_ADD(CURDATE(), INTERVAL -1 DAY)
AND TIME < CURDATE()
UNION ALL
SELECT *
FROM pp_phoenixplan_tophatter_queue
WHERE CustomerId = 1
AND STATUS = 'success'
AND StrategyType IN (2, 3)
AND TIME >= DATE_ADD(CURDATE(), INTERVAL -1 DAY)
AND TIME < CURDATE()
UNION ALL
SELECT *
FROM pp_phoenixplan_vova_queue
WHERE CustomerId = 1
AND STATUS = 'success'
AND StrategyType IN (2, 3)
AND TIME >= DATE_ADD(CURDATE(), INTERVAL -1 DAY)
AND TIME < CURDATE()
UNION ALL
SELECT *
FROM pp_phoenixplan_walmart_queue
WHERE CustomerId = 1
AND STATUS = 'success'
AND StrategyType IN (2, 3)
AND TIME >= DATE_ADD(CURDATE(), INTERVAL -1 DAY)
AND TIME < CURDATE()
UNION ALL
SELECT *
FROM pp_phoenixplan_wayfair_queue
WHERE CustomerId = 1
AND STATUS = 'success'
AND StrategyType IN (2, 3)
AND TIME >= DATE_ADD(CURDATE(), INTERVAL -1 DAY)
AND TIME < CURDATE()
UNION ALL
SELECT *
FROM pp_phoenixplan_wish_queue
WHERE CustomerId = 1
AND STATUS = 'success'
AND StrategyType IN (2, 3)
AND TIME >= DATE_ADD(CURDATE(), INTERVAL -1 DAY)
AND TIME < CURDATE()
UNION ALL
SELECT *
FROM pp_phoenixplan_allegro_queue
WHERE CustomerId = 1
AND STATUS = 'success'
AND StrategyType IN (2, 3)
AND TIME >= DATE_ADD(CURDATE(), INTERVAL -1 DAY)
AND TIME < CURDATE()
UNION ALL
SELECT *
FROM pp_phoenixplan_amazon_queue
WHERE CustomerId = 1
AND STATUS = 'success'
AND StrategyType IN (2, 3)
AND TIME >= DATE_ADD(CURDATE(), INTERVAL -1 DAY)
AND TIME < CURDATE()
UNION ALL
SELECT *
FROM pp_phoenixplan_cdiscount_queue
WHERE CustomerId = 1
AND STATUS = 'success'
AND StrategyType IN (2, 3)
AND TIME >= DATE_ADD(CURDATE(), INTERVAL -1 DAY)
AND TIME < CURDATE()
UNION ALL
SELECT *
FROM pp_phoenixplan_ebay_queue
WHERE CustomerId = 1
AND STATUS = 'success'
AND StrategyType IN (2, 3)
AND TIME >= DATE_ADD(CURDATE(), INTERVAL -1 DAY)
AND TIME < CURDATE()
UNION ALL
SELECT *
FROM pp_phoenixplan_kogan_queue
WHERE CustomerId = 1
AND STATUS = 'success'
AND StrategyType IN (2, 3)
AND TIME >= DATE_ADD(CURDATE(), INTERVAL -1 DAY)
AND TIME < CURDATE()
UNION ALL
SELECT *
FROM pp_phoenixplan_lazada_queue
WHERE CustomerId = 1
AND STATUS = 'success'
AND StrategyType IN (2, 3)
AND TIME >= DATE_ADD(CURDATE(), INTERVAL -1 DAY)
AND TIME < CURDATE()
UNION ALL
SELECT *
FROM pp_phoenixplan_mercadolibre_queue
WHERE CustomerId = 1
AND STATUS = 'success'
AND StrategyType IN (2, 3)
AND TIME >= DATE_ADD(CURDATE(), INTERVAL -1 DAY)
AND TIME < CURDATE()
UNION ALL
SELECT *
FROM pp_phoenixplan_shopee_queue
WHERE CustomerId = 1
AND STATUS = 'success'
AND StrategyType IN (2, 3)
AND TIME >= DATE_ADD(CURDATE(), INTERVAL -1 DAY)
AND TIME < CURDATE()
) f
WHERE f.CustomerId = 1
AND f.StrategyType IN (2, 3)
AND f.STATUS = 'success'
AND f.Time >= DATE_ADD(CURDATE(), INTERVAL -1 DAY)
AND f.Time < CURDATE()
GROUP BY f.OrderSourceId, f.StrategyId
) t1 ON t.OrderSourceId = t1.OrderSourceId
AND t.StrategyId = t1.StrategyId
ORDER BY b.NAME, t.OrderSourceId, t.`策略名称`

历史记录  
  1. 2022-08-02 10:10:56, 由 陈少军 创建。
  2. 2022-08-04 18:22:32, 由 陈少军 指派给 胡笑锋。
    修改了 预计剩余，旧值为 "0"，新值为 "2"。
     参考解决方案，创建索引
  3. 2022-08-09 18:31:08, 由 胡笑锋 完成。
    修改了 总计消耗，旧值为 "0"，新值为 "2"。
    修改了 指派给 ，旧值为 "huxiaofeng"，新值为 "chenshaojun"。
    修改了 实际完成，旧值为 ""，新值为 "2022-08-09 18:30:57"。
    修改了 任务状态，旧值为 "wait"，新值为 "done"。
    修改了 实际开始，旧值为 "0000-00-00 00:00:00"，新值为 "2022-08-09 18:31:08"。
    修改了 预计剩余，旧值为 "2"，新值为 "0"。
    修改了 由谁完成，旧值为 ""，新值为 "huxiaofeng"。
    已增加相关索引
  4. 2023-10-31 14:53:13, 由 周杰 关闭。
    修改了 任务状态，旧值为 "done"，新值为 "closed"。
    修改了 指派给 ，旧值为 "chenshaojun"，新值为 "closed"。
    修改了 由谁关闭，旧值为 ""，新值为 "zhoujie"。
    修改了 关闭时间，旧值为 "0000-00-00 00:00:00"，新值为 "2023-10-31 14:53:13"。
    修改了 关闭原因，旧值为 ""，新值为 "done"。
返回
所属迭代 系统管理部 - 系统服务优化
所属模块 /
相关需求 暂无
指派给 Closed 于 2023-10-31 14:53:13
任务类型 后端
任务标签 初始
开发组
任务状态 已关闭
进度 100%
优先级 P2
关键词
抄送给 陈少军
由谁创建 陈少军 于 2022-08-02 10:10:56
由谁完成 胡笑锋 于 2022-08-09 18:30:57
由谁取消 暂无
由谁关闭 周杰 于 2023-10-31 14:53:13
关闭原因 已完成
最后编辑 周杰 于 2023-10-31 14:53:13
团队 最初预计 总计消耗 预计剩余
工时信息
最初预计 0工时
总计消耗 2工时
预计剩余 0工时
预计开始 2022-08-02
实际开始 2022-08-09
截止日期 2022-08-03