重现步骤
反馈人  李冠
严重级别：严重
所属项目：亚马逊-百货
问题原因：慢SQL，严重影响公共库其它业务正常运行，SQL为：（
with tmp_monitor_sale as (

select 'OR-OR-WF-Pink' msku, 16332 source_id

)
SELECT
sale.msku,
sale.source_id,
sum( CASE WHEN ( purchase_date_day >= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 6 - 2 DAY ), '%Y%m%d' ) AS UNSIGNED ) AND purchase_date_day <= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 2 DAY ),'%Y%m%d' ) AS UNSIGNED )) THEN quantity_ordered ELSE 0 END ) "saleNum7",
sum( CASE WHEN ( purchase_date_day >= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 13 - 2 DAY ), '%Y%m%d' ) AS UNSIGNED ) AND purchase_date_day <= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 2 DAY ),'%Y%m%d' ) AS UNSIGNED )) THEN quantity_ordered ELSE 0 END ) "saleNum14",
sum( CASE WHEN ( purchase_date_day >= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 27 - 2 DAY ), '%Y%m%d' ) AS UNSIGNED ) AND purchase_date_day <= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 2 DAY ),'%Y%m%d' ) AS UNSIGNED )) THEN quantity_ordered ELSE 0 END ) "saleNum28",
sum( CASE WHEN ( purchase_date_day >= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 30 - 2 DAY ), '%Y%m%d' ) AS UNSIGNED ) AND purchase_date_day <= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 2 DAY ),'%Y%m%d' ) AS UNSIGNED )) THEN quantity_ordered ELSE 0 END ) "saleNum30",
sum( CASE WHEN ( purchase_date_day >= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 59 - 2 DAY ), '%Y%m%d' ) AS UNSIGNED ) AND purchase_date_day <= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 2 DAY ),'%Y%m%d' ) AS UNSIGNED )) THEN quantity_ordered ELSE 0 END ) "saleNum60",
sum( CASE WHEN ( purchase_date_day >= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 89 - 2 DAY ), '%Y%m%d' ) AS UNSIGNED ) AND purchase_date_day <= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 2 DAY ),'%Y%m%d' ) AS UNSIGNED )) THEN quantity_ordered ELSE 0 END ) "saleNum90",
sum( CASE WHEN ( purchase_date_day >= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 119 - 2 DAY ), '%Y%m%d' ) AS UNSIGNED ) AND purchase_date_day <= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 2 DAY ),'%Y%m%d' ) AS UNSIGNED )) THEN quantity_ordered ELSE 0 END ) "saleNum120",
sum( CASE WHEN ( purchase_date_day >= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 179 - 2 DAY ), '%Y%m%d' ) AS UNSIGNED ) AND purchase_date_day <= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 2 DAY ),'%Y%m%d' ) AS UNSIGNED )) THEN quantity_ordered ELSE 0 END ) "saleNum180",
sum( CASE WHEN ( purchase_date_day >= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 6 - 2 - 364 DAY ), '%Y%m%d' ) AS UNSIGNED ) AND purchase_date_day <= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 2 - 364 DAY ),'%Y%m%d' ) AS UNSIGNED )) THEN quantity_ordered ELSE 0 END ) "yoySaleNum7",
sum( CASE WHEN ( purchase_date_day >= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 13 - 2 - 364 DAY ), '%Y%m%d' ) AS UNSIGNED ) AND purchase_date_day <= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 2 - 364 DAY ),'%Y%m%d' ) AS UNSIGNED )) THEN quantity_ordered ELSE 0 END ) "yoySaleNum14",
sum( CASE WHEN ( purchase_date_day >= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 29 - 2 - 364 DAY ), '%Y%m%d' ) AS UNSIGNED ) AND purchase_date_day <= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 2 - 364 DAY ),'%Y%m%d' ) AS UNSIGNED )) THEN quantity_ordered ELSE 0 END ) "yoySaleNum30",
sum( CASE WHEN ( purchase_date_day >= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 59 - 2 - 364 DAY ), '%Y%m%d' ) AS UNSIGNED ) AND purchase_date_day <= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 2 - 364 DAY ),'%Y%m%d' ) AS UNSIGNED )) THEN quantity_ordered ELSE 0 END ) "yoySaleNum60",
sum( CASE WHEN ( purchase_date_day >= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 89 - 2 - 364 DAY ), '%Y%m%d' ) AS UNSIGNED ) AND purchase_date_day <= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 2 - 364 DAY ),'%Y%m%d' ) AS UNSIGNED )) THEN quantity_ordered ELSE 0 END ) "yoySaleNum90"
FROM
t_amazon_sales_5 sale inner join tmp_monitor_sale tmp on sale.source_id = tmp.source_id and sale.msku = tmp.msku
where
sale.purchase_date_day >= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 364 - 2 - 365 DAY ), '%Y%m%d' ) AS UNSIGNED )
AND sale.order_status IN ( 1, 2, 3, 4 )
GROUP BY msku, source_id）
解决方案：建议分析一下业务，换一种方式实现，避免每次都几乎全量统计
历史记录  
  1. 2025-08-18 10:52:23, 由 李乐同 创建。
  2. 2025-08-18 10:59:59, 由 李乐同 编辑。
    修改了 所属模块，旧值为 "0"，新值为 "1842"。
    修改了 业务部门，旧值为 "0"，新值为 "2"。
    修改了 解决时效，旧值为 "0"，新值为 "-1"。
    修改了 重现步骤，区别为：
    001-
    001+ 反馈人 李冠 严重级别：严重 所属项目：亚马逊-百货 问题原因：慢SQL，严重影响公共库其它业务正常运行，SQL为：（ with tmp_monitor_sale as ( select 'OR-OR-WF-Pink' msku, 16332 source_id ) SELECT sale.msku, sale.source_id, sum( CASE WHEN ( purchase_date_day &gt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 6 - 2 DAY ), '%Y%m%d' ) AS UNSIGNED ) AND purchase_date_day &lt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 2 DAY ),'%Y%m%d' ) AS UNSIGNED )) THEN quantity_ordered ELSE 0 END ) "saleNum7", sum( CASE WHEN ( purchase_date_day &gt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 13 - 2 DAY ), '%Y%m%d' ) AS UNSIGNED ) AND purchase_date_day &lt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 2 DAY ),'%Y%m%d' ) AS UNSIGNED )) THEN quantity_ordered ELSE 0 END ) "saleNum14", sum( CASE WHEN ( purchase_date_day &gt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 27 - 2 DAY ), '%Y%m%d' ) AS UNSIGNED ) AND purchase_date_day &lt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 2 DAY ),'%Y%m%d' ) AS UNSIGNED )) THEN quantity_ordered ELSE 0 END ) "saleNum28", sum( CASE WHEN ( purchase_date_day &gt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 30 - 2 DAY ), '%Y%m%d' ) AS UNSIGNED ) AND purchase_date_day &lt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 2 DAY ),'%Y%m%d' ) AS UNSIGNED )) THEN quantity_ordered ELSE 0 END ) "saleNum30", sum( CASE WHEN ( purchase_date_day &gt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 59 - 2 DAY ), '%Y%m%d' ) AS UNSIGNED ) AND purchase_date_day &lt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 2 DAY ),'%Y%m%d' ) AS UNSIGNED )) THEN quantity_ordered ELSE 0 END ) "saleNum60", sum( CASE WHEN ( purchase_date_day &gt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 89 - 2 DAY ), '%Y%m%d' ) AS UNSIGNED ) AND purchase_date_day &lt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 2 DAY ),'%Y%m%d' ) AS UNSIGNED )) THEN quantity_ordered ELSE 0 END ) "saleNum90", sum( CASE WHEN ( purchase_date_day &gt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 119 - 2 DAY ), '%Y%m%d' ) AS UNSIGNED ) AND purchase_date_day &lt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 2 DAY ),'%Y%m%d' ) AS UNSIGNED )) THEN quantity_ordered ELSE 0 END ) "saleNum120", sum( CASE WHEN ( purchase_date_day &gt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 179 - 2 DAY ), '%Y%m%d' ) AS UNSIGNED ) AND purchase_date_day &lt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 2 DAY ),'%Y%m%d' ) AS UNSIGNED )) THEN quantity_ordered ELSE 0 END ) "saleNum180", sum( CASE WHEN ( purchase_date_day &gt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 6 - 2 - 364 DAY ), '%Y%m%d' ) AS UNSIGNED ) AND purchase_date_day &lt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 2 - 364 DAY ),'%Y%m%d' ) AS UNSIGNED )) THEN quantity_ordered ELSE 0 END ) "yoySaleNum7", sum( CASE WHEN ( purchase_date_day &gt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 13 - 2 - 364 DAY ), '%Y%m%d' ) AS UNSIGNED ) AND purchase_date_day &lt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 2 - 364 DAY ),'%Y%m%d' ) AS UNSIGNED )) THEN quantity_ordered ELSE 0 END ) "yoySaleNum14", sum( CASE WHEN ( purchase_date_day &gt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 29 - 2 - 364 DAY ), '%Y%m%d' ) AS UNSIGNED ) AND purchase_date_day &lt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 2 - 364 DAY ),'%Y%m%d' ) AS UNSIGNED )) THEN quantity_ordered ELSE 0 END ) "yoySaleNum30", sum( CASE WHEN ( purchase_date_day &gt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 59 - 2 - 364 DAY ), '%Y%m%d' ) AS UNSIGNED ) AND purchase_date_day &lt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 2 - 364 DAY ),'%Y%m%d' ) AS UNSIGNED )) THEN quantity_ordered ELSE 0 END ) "yoySaleNum60", sum( CASE WHEN ( purchase_date_day &gt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 89 - 2 - 364 DAY ), '%Y%m%d' ) AS UNSIGNED ) AND purchase_date_day &lt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 2 - 364 DAY ),'%Y%m%d' ) AS UNSIGNED )) THEN quantity_ordered ELSE 0 END ) "yoySaleNum90" FROM t_amazon_sales_5 sale inner join tmp_monitor_sale tmp on sale.source_id = tmp.source_id and sale.msku = tmp.msku where sale.purchase_date_day &gt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 364 - 2 - 365 DAY ), '%Y%m%d' ) AS UNSIGNED ) AND sale.order_status IN ( 1, 2, 3, 4 ) GROUP BY msku, source_id） 解决方案：建议分析一下业务，换一种方式实现，避免每次都几乎全量统计
    002- 严重级别：严重
    003- 所属项目：亚马逊-百货
    004- 问题原因：慢SQL，严重影响公共库其它业务正常运行，SQL为：（
    005- with tmp_monitor_sale as (
    006-
    007- select 'OR-OR-WF-Pink' msku, 16332 source_id
    008-
    009- )
    010- SELECT
    011- sale.msku,
    012- sale.source_id,
    013- sum( CASE WHEN ( purchase_date_day &gt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 6 - 2 DAY ), '%Y%m%d' ) AS UNSIGNED ) AND purchase_date_day &lt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 2 DAY ),'%Y%m%d' ) AS UNSIGNED )) THEN quantity_ordered ELSE 0 END ) "saleNum7",
    014- sum( CASE WHEN ( purchase_date_day &gt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 13 - 2 DAY ), '%Y%m%d' ) AS UNSIGNED ) AND purchase_date_day &lt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 2 DAY ),'%Y%m%d' ) AS UNSIGNED )) THEN quantity_ordered ELSE 0 END ) "saleNum14",
    015- sum( CASE WHEN ( purchase_date_day &gt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 27 - 2 DAY ), '%Y%m%d' ) AS UNSIGNED ) AND purchase_date_day &lt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 2 DAY ),'%Y%m%d' ) AS UNSIGNED )) THEN quantity_ordered ELSE 0 END ) "saleNum28",
    016- sum( CASE WHEN ( purchase_date_day &gt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 30 - 2 DAY ), '%Y%m%d' ) AS UNSIGNED ) AND purchase_date_day &lt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 2 DAY ),'%Y%m%d' ) AS UNSIGNED )) THEN quantity_ordered ELSE 0 END ) "saleNum30",
    017- sum( CASE WHEN ( purchase_date_day &gt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 59 - 2 DAY ), '%Y%m%d' ) AS UNSIGNED ) AND purchase_date_day &lt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 2 DAY ),'%Y%m%d' ) AS UNSIGNED )) THEN quantity_ordered ELSE 0 END ) "saleNum60",
    018- sum( CASE WHEN ( purchase_date_day &gt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 89 - 2 DAY ), '%Y%m%d' ) AS UNSIGNED ) AND purchase_date_day &lt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 2 DAY ),'%Y%m%d' ) AS UNSIGNED )) THEN quantity_ordered ELSE 0 END ) "saleNum90",
    019- sum( CASE WHEN ( purchase_date_day &gt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 119 - 2 DAY ), '%Y%m%d' ) AS UNSIGNED ) AND purchase_date_day &lt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 2 DAY ),'%Y%m%d' ) AS UNSIGNED )) THEN quantity_ordered ELSE 0 END ) "saleNum120",
    020- sum( CASE WHEN ( purchase_date_day &gt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 179 - 2 DAY ), '%Y%m%d' ) AS UNSIGNED ) AND purchase_date_day &lt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 2 DAY ),'%Y%m%d' ) AS UNSIGNED )) THEN quantity_ordered ELSE 0 END ) "saleNum180",
    021- sum( CASE WHEN ( purchase_date_day &gt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 6 - 2 - 364 DAY ), '%Y%m%d' ) AS UNSIGNED ) AND purchase_date_day &lt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 2 - 364 DAY ),'%Y%m%d' ) AS UNSIGNED )) THEN quantity_ordered ELSE 0 END ) "yoySaleNum7",
    022- sum( CASE WHEN ( purchase_date_day &gt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 13 - 2 - 364 DAY ), '%Y%m%d' ) AS UNSIGNED ) AND purchase_date_day &lt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 2 - 364 DAY ),'%Y%m%d' ) AS UNSIGNED )) THEN quantity_ordered ELSE 0 END ) "yoySaleNum14",
    023- sum( CASE WHEN ( purchase_date_day &gt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 29 - 2 - 364 DAY ), '%Y%m%d' ) AS UNSIGNED ) AND purchase_date_day &lt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 2 - 364 DAY ),'%Y%m%d' ) AS UNSIGNED )) THEN quantity_ordered ELSE 0 END ) "yoySaleNum30",
    024- sum( CASE WHEN ( purchase_date_day &gt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 59 - 2 - 364 DAY ), '%Y%m%d' ) AS UNSIGNED ) AND purchase_date_day &lt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 2 - 364 DAY ),'%Y%m%d' ) AS UNSIGNED )) THEN quantity_ordered ELSE 0 END ) "yoySaleNum60",
    025- sum( CASE WHEN ( purchase_date_day &gt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 89 - 2 - 364 DAY ), '%Y%m%d' ) AS UNSIGNED ) AND purchase_date_day &lt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 2 - 364 DAY ),'%Y%m%d' ) AS UNSIGNED )) THEN quantity_ordered ELSE 0 END ) "yoySaleNum90"
    026- FROM
    027- t_amazon_sales_5 sale inner join tmp_monitor_sale tmp on sale.source_id = tmp.source_id and sale.msku = tmp.msku
    028- where
    029- sale.purchase_date_day &gt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 364 - 2 - 365 DAY ), '%Y%m%d' ) AS UNSIGNED )
    030- AND sale.order_status IN ( 1, 2, 3, 4 )
    031- GROUP BY msku, source_id）
    032- 解决方案：建议分析一下业务，换一种方式实现，避免每次都几乎全量统计
    001- <br />
    001+ 反馈人 李冠<br /> 严重级别：严重<br /> 所属项目：亚马逊-百货<br /> 问题原因：慢SQL，严重影响公共库其它业务正常运行，SQL为：（<br /> with tmp_monitor_sale as (<br /><br /> select 'OR-OR-WF-Pink' msku, 16332 source_id<br /><br /> )<br /> SELECT<br /> sale.msku,<br /> sale.source_id,<br /> sum( CASE WHEN ( purchase_date_day &gt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 6 - 2 DAY ), '%Y%m%d' ) AS UNSIGNED ) AND purchase_date_day &lt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 2 DAY ),'%Y%m%d' ) AS UNSIGNED )) THEN quantity_ordered ELSE 0 END ) "saleNum7",<br /> sum( CASE WHEN ( purchase_date_day &gt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 13 - 2 DAY ), '%Y%m%d' ) AS UNSIGNED ) AND purchase_date_day &lt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 2 DAY ),'%Y%m%d' ) AS UNSIGNED )) THEN quantity_ordered ELSE 0 END ) "saleNum14",<br /> sum( CASE WHEN ( purchase_date_day &gt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 27 - 2 DAY ), '%Y%m%d' ) AS UNSIGNED ) AND purchase_date_day &lt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 2 DAY ),'%Y%m%d' ) AS UNSIGNED )) THEN quantity_ordered ELSE 0 END ) "saleNum28",<br /> sum( CASE WHEN ( purchase_date_day &gt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 30 - 2 DAY ), '%Y%m%d' ) AS UNSIGNED ) AND purchase_date_day &lt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 2 DAY ),'%Y%m%d' ) AS UNSIGNED )) THEN quantity_ordered ELSE 0 END ) "saleNum30",<br /> sum( CASE WHEN ( purchase_date_day &gt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 59 - 2 DAY ), '%Y%m%d' ) AS UNSIGNED ) AND purchase_date_day &lt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 2 DAY ),'%Y%m%d' ) AS UNSIGNED )) THEN quantity_ordered ELSE 0 END ) "saleNum60",<br /> sum( CASE WHEN ( purchase_date_day &gt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 89 - 2 DAY ), '%Y%m%d' ) AS UNSIGNED ) AND purchase_date_day &lt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 2 DAY ),'%Y%m%d' ) AS UNSIGNED )) THEN quantity_ordered ELSE 0 END ) "saleNum90",<br /> sum( CASE WHEN ( purchase_date_day &gt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 119 - 2 DAY ), '%Y%m%d' ) AS UNSIGNED ) AND purchase_date_day &lt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 2 DAY ),'%Y%m%d' ) AS UNSIGNED )) THEN quantity_ordered ELSE 0 END ) "saleNum120",<br /> sum( CASE WHEN ( purchase_date_day &gt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 179 - 2 DAY ), '%Y%m%d' ) AS UNSIGNED ) AND purchase_date_day &lt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 2 DAY ),'%Y%m%d' ) AS UNSIGNED )) THEN quantity_ordered ELSE 0 END ) "saleNum180",<br /> sum( CASE WHEN ( purchase_date_day &gt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 6 - 2 - 364 DAY ), '%Y%m%d' ) AS UNSIGNED ) AND purchase_date_day &lt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 2 - 364 DAY ),'%Y%m%d' ) AS UNSIGNED )) THEN quantity_ordered ELSE 0 END ) "yoySaleNum7",<br /> sum( CASE WHEN ( purchase_date_day &gt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 13 - 2 - 364 DAY ), '%Y%m%d' ) AS UNSIGNED ) AND purchase_date_day &lt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 2 - 364 DAY ),'%Y%m%d' ) AS UNSIGNED )) THEN quantity_ordered ELSE 0 END ) "yoySaleNum14",<br /> sum( CASE WHEN ( purchase_date_day &gt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 29 - 2 - 364 DAY ), '%Y%m%d' ) AS UNSIGNED ) AND purchase_date_day &lt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 2 - 364 DAY ),'%Y%m%d' ) AS UNSIGNED )) THEN quantity_ordered ELSE 0 END ) "yoySaleNum30",<br /> sum( CASE WHEN ( purchase_date_day &gt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 59 - 2 - 364 DAY ), '%Y%m%d' ) AS UNSIGNED ) AND purchase_date_day &lt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 2 - 364 DAY ),'%Y%m%d' ) AS UNSIGNED )) THEN quantity_ordered ELSE 0 END ) "yoySaleNum60",<br /> sum( CASE WHEN ( purchase_date_day &gt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 89 - 2 - 364 DAY ), '%Y%m%d' ) AS UNSIGNED ) AND purchase_date_day &lt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 2 - 364 DAY ),'%Y%m%d' ) AS UNSIGNED )) THEN quantity_ordered ELSE 0 END ) "yoySaleNum90"<br /> FROM<br /> t_amazon_sales_5 sale inner join tmp_monitor_sale tmp on sale.source_id = tmp.source_id and sale.msku = tmp.msku<br /> where<br /> sale.purchase_date_day &gt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 364 - 2 - 365 DAY ), '%Y%m%d' ) AS UNSIGNED )<br /> AND sale.order_status IN ( 1, 2, 3, 4 )<br /> GROUP BY msku, source_id）<br /> 解决方案：建议分析一下业务，换一种方式实现，避免每次都几乎全量统计
    002- 严重级别：严重<br />
    003- 所属项目：亚马逊-百货<br />
    004- 问题原因：慢SQL，严重影响公共库其它业务正常运行，SQL为：（<br />
    005- with tmp_monitor_sale as (<br />
    006- <br />
    007- select 'OR-OR-WF-Pink' msku, 16332 source_id<br />
    008- <br />
    009- )<br />
    010- SELECT<br />
    011- sale.msku,<br />
    012- sale.source_id,<br />
    013- sum( CASE WHEN ( purchase_date_day &gt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 6 - 2 DAY ), '%Y%m%d' ) AS UNSIGNED ) AND purchase_date_day &lt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 2 DAY ),'%Y%m%d' ) AS UNSIGNED )) THEN quantity_ordered ELSE 0 END ) "saleNum7",<br />
    014- sum( CASE WHEN ( purchase_date_day &gt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 13 - 2 DAY ), '%Y%m%d' ) AS UNSIGNED ) AND purchase_date_day &lt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 2 DAY ),'%Y%m%d' ) AS UNSIGNED )) THEN quantity_ordered ELSE 0 END ) "saleNum14",<br />
    015- sum( CASE WHEN ( purchase_date_day &gt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 27 - 2 DAY ), '%Y%m%d' ) AS UNSIGNED ) AND purchase_date_day &lt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 2 DAY ),'%Y%m%d' ) AS UNSIGNED )) THEN quantity_ordered ELSE 0 END ) "saleNum28",<br />
    016- sum( CASE WHEN ( purchase_date_day &gt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 30 - 2 DAY ), '%Y%m%d' ) AS UNSIGNED ) AND purchase_date_day &lt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 2 DAY ),'%Y%m%d' ) AS UNSIGNED )) THEN quantity_ordered ELSE 0 END ) "saleNum30",<br />
    017- sum( CASE WHEN ( purchase_date_day &gt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 59 - 2 DAY ), '%Y%m%d' ) AS UNSIGNED ) AND purchase_date_day &lt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 2 DAY ),'%Y%m%d' ) AS UNSIGNED )) THEN quantity_ordered ELSE 0 END ) "saleNum60",<br />
    018- sum( CASE WHEN ( purchase_date_day &gt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 89 - 2 DAY ), '%Y%m%d' ) AS UNSIGNED ) AND purchase_date_day &lt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 2 DAY ),'%Y%m%d' ) AS UNSIGNED )) THEN quantity_ordered ELSE 0 END ) "saleNum90",<br />
    019- sum( CASE WHEN ( purchase_date_day &gt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 119 - 2 DAY ), '%Y%m%d' ) AS UNSIGNED ) AND purchase_date_day &lt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 2 DAY ),'%Y%m%d' ) AS UNSIGNED )) THEN quantity_ordered ELSE 0 END ) "saleNum120",<br />
    020- sum( CASE WHEN ( purchase_date_day &gt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 179 - 2 DAY ), '%Y%m%d' ) AS UNSIGNED ) AND purchase_date_day &lt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 2 DAY ),'%Y%m%d' ) AS UNSIGNED )) THEN quantity_ordered ELSE 0 END ) "saleNum180",<br />
    021- sum( CASE WHEN ( purchase_date_day &gt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 6 - 2 - 364 DAY ), '%Y%m%d' ) AS UNSIGNED ) AND purchase_date_day &lt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 2 - 364 DAY ),'%Y%m%d' ) AS UNSIGNED )) THEN quantity_ordered ELSE 0 END ) "yoySaleNum7",<br />
    022- sum( CASE WHEN ( purchase_date_day &gt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 13 - 2 - 364 DAY ), '%Y%m%d' ) AS UNSIGNED ) AND purchase_date_day &lt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 2 - 364 DAY ),'%Y%m%d' ) AS UNSIGNED )) THEN quantity_ordered ELSE 0 END ) "yoySaleNum14",<br />
    023- sum( CASE WHEN ( purchase_date_day &gt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 29 - 2 - 364 DAY ), '%Y%m%d' ) AS UNSIGNED ) AND purchase_date_day &lt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 2 - 364 DAY ),'%Y%m%d' ) AS UNSIGNED )) THEN quantity_ordered ELSE 0 END ) "yoySaleNum30",<br />
    024- sum( CASE WHEN ( purchase_date_day &gt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 59 - 2 - 364 DAY ), '%Y%m%d' ) AS UNSIGNED ) AND purchase_date_day &lt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 2 - 364 DAY ),'%Y%m%d' ) AS UNSIGNED )) THEN quantity_ordered ELSE 0 END ) "yoySaleNum60",<br />
    025- sum( CASE WHEN ( purchase_date_day &gt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 89 - 2 - 364 DAY ), '%Y%m%d' ) AS UNSIGNED ) AND purchase_date_day &lt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 2 - 364 DAY ),'%Y%m%d' ) AS UNSIGNED )) THEN quantity_ordered ELSE 0 END ) "yoySaleNum90"<br />
    026- FROM<br />
    027- t_amazon_sales_5 sale inner join tmp_monitor_sale tmp on sale.source_id = tmp.source_id and sale.msku = tmp.msku<br />
    028- where<br />
    029- sale.purchase_date_day &gt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 364 - 2 - 365 DAY ), '%Y%m%d' ) AS UNSIGNED )<br />
    030- AND sale.order_status IN ( 1, 2, 3, 4 )<br />
    031- GROUP BY msku, source_id）<br />
    032- 解决方案：建议分析一下业务，换一种方式实现，避免每次都几乎全量统计
  3. 2025-08-18 15:38:29, 由 关依林 指派给 宋宁。
    修改了 指派给，旧值为 "guanyilin"，新值为 "songning"。

    目前已经指定单个source_id，msku维度做销量汇总，但是有品的listing订单量比较大，存在扫描的行数200W+行

  4. 2025-08-18 15:41:44, 由 宋宁 指派给 刘嘉威。
    修改了 指派给，旧值为 "songning"，新值为 "liujiawei"。
  5. 2025-08-18 15:53:51, 由 刘嘉威 指派给 宋宁。
    修改了 指派给，旧值为 "liujiawei"，新值为 "songning"。
    是fba监控策略调用，命中特别高销量的listing时，存在扫描大量行的情况，可能需要调整方案，预先聚合近两年的销量
  6. 2025-08-18 17:28:25, 由 宋宁 指派给 关依林。
    修改了 指派给，旧值为 "songning"，新值为 "guanyilin"。
    随着销量数据增长，之前设计的方案需要调整
  7. 2025-08-20 15:54:10, 由 关依林 编辑。
    修改了 Bug状态 ，旧值为 "active"，新值为 "closed"。
    修改了 解决时效，旧值为 "0"，新值为 "53.01"。
    修改了 指派给 ，旧值为 "guanyilin"，新值为 "closed"。
    修改了 上线时效，旧值为 "-1"，新值为 "1"。
    修改了 解决者 ，旧值为 ""，新值为 "guanyilin"。
    修改了 解决日期，旧值为 "0000-00-00 00:00:00"，新值为 "2025-08-20 15:53"。
    修改了 解决版本，旧值为 ""，新值为 "pro627"。
    修改了 解决方案，旧值为 ""，新值为 "performance"。
    修改了 由谁关闭，旧值为 ""，新值为 "guanyilin"。
    修改了 关闭日期，旧值为 "0000-00-00 00:00:00"，新值为 "2025-08-20 15:54:10"。
    修改了 是否确认，旧值为 "0"，新值为 "1"。
  8. 2025-08-21 09:14:46, 由 陈莉珍 编辑。
    修改了 所属项目，旧值为 "51"，新值为 "52"。
    修改了 所属模块，旧值为 "1842"，新值为 "1955"。
    修改了 上线时效，旧值为 "1"，新值为 "-1"。
所属项目 智能中心-需求池
所属模块 ERP系统（销售管理）   AMZ百货
所属计划
所属迭代
Bug类型
严重程度 一般
优先级 P2
Bug状态 已关闭
业务部门 总经办
影响功能
开发责任人 关依林
测试责任人
解决时效 53.01
激活次数 0
激活日期
是否确认 是
当前指派 Closed 于 2025-08-21 09:14:46
截止日期 0000-00-00
上线时间 0000-00-00 00:00:00
上线时效 -1
操作系统
浏览器
关键词
抄送给
所属迭代
相关需求
相关任务
由谁创建 李乐同 于 2025-08-18 10:52:23
影响环境 生产环境-业务反馈
由谁解决 关依林 于 2025-08-20 15:53:00
解决版本 生产环境-业务反馈
解决方案 性能问题
由谁关闭 关依林 于 2025-08-20 15:54:10
最后修改 陈莉珍 于 2025-08-21 09:14:46
相关Bug