001- 反馈人：袁慧斌
001+ 反馈人：袁慧斌 数据库：zhcxkj_center_order 查询时间：21.5635 执行次数: 37 扫描行数：152491 发送行数：1 指纹SQL: with tmp_monitor_sale as ( select ? msku , ? source_id ) SELECT sale . msku , sale . source_id , sum ( CASE WHEN ( purchase_date_day >= cast ( DATE_FORMAT ( DATE_ADD ( CURDATE ( ) , INTERVAL - ? - ? DAY ) , ? ) AS UNSIGNED ) AND purchase_date_day <= cast ( DATE_FORMAT ( DATE_ADD ( CURDATE ( ) , INTERVAL - ? DAY ) , ? ) AS UNSIGNED ) ) THEN quantity_ordered ELSE ? END ) ? , sum ( CASE WHEN ( purchase_date_day >= cast ( DATE_FORMAT ( DATE_ADD ( CURDATE ( ) , INTERVAL - ? - ? DAY ) , ? ) AS UNSIGNED ) AND purchase_date_day <= cast ( DATE_FORMAT ( DATE_ADD ( CURDATE ( ) , INTERVAL - ? DAY ) , ? ) AS UNSIGNED ) ) THEN quantity_ordered ELSE ? END ) ? , sum ( CASE WHEN ( purchase_date_day >= cast ( DATE_FORMAT ( DATE_ADD ( CURDATE ( ) , INTERVAL - ? - ? DAY ) , ? ) AS UNSIGNED ) AND purchase_date_day <= cast ( DATE_FORMAT ( DATE_ADD ( CURDATE ( ) , INTERVAL - ? DAY ) , ? ) AS UNSIGNED ) ) THEN quantity_ordered ELSE ? END ) ? , sum ( CASE WHEN ( purchase_date_day >= cast ( DATE_FORMAT ( DATE_ADD ( CURDATE ( ) , INTERVAL - ? - ? DAY ) , ? ) AS UNSIGNED ) AND purchase_date_day <= cast ( DATE_FORMAT ( DATE_ADD ( CURDATE ( ) , INTERVAL - ? DAY ) , ? ) AS UNSIGNED ) ) THEN quantity_ordered ELSE ? END ) ? , sum ( CASE WHEN ( purchase_date_day >= cast ( DATE_FORMAT ( DATE_ADD ( CURDATE ( ) , INTERVAL - ? - ? DAY ) , ? ) AS UNSIGNED ) AND purchase_date_day <= cast ( DATE_FORMAT ( DATE_ADD ( CURDATE ( ) , INTERVAL - ? DAY ) , ? ) AS UNSIGNED ) ) THEN quantity_ordered ELSE ? END ) ? , sum ( CASE WHEN ( purchase_date_day >= cast ( DATE_FORMAT ( DATE_ADD ( CURDATE ( ) , INTERVAL - ? - ? DAY ) , ? ) AS UNSIGNED ) AND purchase_date_day <= cast ( DATE_FORMAT ( DATE_ADD ( CURDATE ( ) , INTERVAL - ? DAY ) , ? ) AS UNSIGNED ) ) THEN quantity_ordered ELSE ? END ) ? , sum ( CASE WHEN ( purchase_date_day >= cast ( DATE_FORMAT ( DATE_ADD ( CURDATE ( ) , INTERVAL - ? - ? DAY ) , ? ) AS UNSIGNED ) AND purchase_date_day <= cast ( DATE_FORMAT ( DATE_ADD ( CURD 真实SQL：with tmp_monitor_sale as ( select 2157458-6605-1014076531 msku, 6605 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_6 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; 袁慧斌,人工修改过 AISQL修改建议：WITH tmp_monitor_sale AS ( SELECT 2157458-6605-1014076531 AS msku, 6605 AS source_id ) SELECT sale.msku, sale.source_id, SUM(CASE WHEN purchase_date_day BETWEEN DATE_FORMAT(CURDATE() - INTERVAL 8 DAY, %Y%m%d) AND DATE_FORMAT(CURDATE() - INTERVAL 2 DAY, %Y%m%d) THEN quantity_ordered ELSE 0 END) AS saleNum7, SUM(CASE WHEN purchase_date_day BETWEEN DATE_FORMAT(CURDATE() - INTERVAL 15 DAY, %Y%m%d) AND DATE_FORMAT(CURDATE() - INTERVAL 2 DAY, %Y%m%d) THEN quantity_ordered ELSE 0 END) AS saleNum14, SUM(CASE WHEN purchase_date_day BETWEEN DATE_FORMAT(CURDATE() - INTERVAL 29 DAY, %Y%m%d) AND DATE_FORMAT(CURDATE() - INTERVAL 2 DAY, %Y%m%d) THEN quantity_ordered ELSE 0 END) AS saleNum28, SUM(CASE WHEN purchase_date_day BETWEEN DATE_FORMAT(CURDATE() - INTERVAL 32 DAY, %Y%m%d) AND DATE_FORMAT(CURDATE() - INTERVAL 2 DAY, %Y%m%d) THEN quantity_ordered ELSE 0 END) AS saleNum30, SUM(CASE WHEN purchase_date_day BETWEEN DATE_FORMAT(CURDATE() - INTERVAL 61 DAY, %Y%m%d) AND DATE_FORMAT(CURDATE() - INTERVAL 2 DAY, %Y%m%d) THEN quantity_ordered ELSE 0 END) AS saleNum60, SUM(CASE WHEN purchase_date_day BETWEEN DATE_FORMAT(CURDATE() - INTERVAL 91 DAY, %Y%m%d) AND DATE_FORMAT(CURDATE() - INTERVAL 2 DAY, %Y%m%d) THEN quantity_ordered ELSE 0 END) AS saleNum90, SUM(CASE WHEN purchase_date_day BETWEEN DATE_FORMAT(CURDATE() - INTERVAL 121 DAY, %Y%m%d) AND DATE_FORMAT(CURDATE() - INTERVAL 2 DAY, %Y%m%d) THEN quantity_ordered ELSE 0 END) AS saleNum120, SUM(CASE WHEN purchase_date_day BETWEEN DATE_FORMAT(CURDATE() - INTERVAL 181 DAY, %Y%m%d) AND DATE_FORMAT(CURDATE() - INTERVAL 2 DAY, %Y%m%d) THEN quantity_ordered ELSE 0 END) AS saleNum180, SUM(CASE WHEN purchase_date_day BETWEEN DATE_FORMAT(CURDATE() - INTERVAL 372 DAY, %Y%m%d) AND DATE_FORMAT(CURDATE() - INTERVAL 366 DAY, %Y%m%d) THEN quantity_ordered ELSE 0 END) AS yoySaleNum7, SUM(CASE WHEN purchase_date_day BETWEEN DATE_FORMAT(CURDATE() - INTERVAL 379 DAY, %Y%m%d) AND DATE_FORMAT(CURDATE() - INTERVAL 366 DAY, %Y%m%d) THEN quantity_ordered ELSE 0 END) AS yoySaleNum14, SUM(CASE WHEN purchase_date_day BETWEEN DATE_FORMAT(CURDATE() - INTERVAL 395 DAY, %Y%m%d) AND DATE_FORMAT(CURDATE() - INTERVAL 366 DAY, %Y%m%d) THEN quantity_ordered ELSE 0 END) AS yoySaleNum30, SUM(CASE WHEN purchase_date_day BETWEEN DATE_FORMAT(CURDATE() - INTERVAL 425 DAY, %Y%m%d) AND DATE_FORMAT(CURDATE() - INTERVAL 366 DAY, %Y%m%d) THEN quantity_ordered ELSE 0 END) AS yoySaleNum60, SUM(CASE WHEN purchase_date_day BETWEEN DATE_FORMAT(CURDATE() - INTERVAL 455 DAY, %Y%m%d) AND DATE_FORMAT(CURDATE() - INTERVAL 366 DAY, %Y%m%d) THEN quantity_ordered ELSE 0 END) AS yoySaleNum90 FROM t_amazon_sales_6 sale INNER JOIN tmp_monitor_sale tmp ON sale.source_id = tmp.source_id AND sale.msku = tmp.msku WHERE sale.purchase_date_day >= DATE_FORMAT(CURDATE() - INTERVAL 731 DAY, %Y%m%d) AND sale.order_status IN (1, 2, 3, 4) GROUP BY sale.msku, sale.source_id; explain表：id
002- 数据库：zhcxkj_center_order
002+ select_type
003- 查询时间：21.5635
003+ table
004- 执行次数: 37
004+ partitions
005- 扫描行数：152491
005+ type
006- 发送行数：1
006+ possible_keys
007- 指纹SQL: with tmp_monitor_sale as ( select ? msku , ? source_id ) SELECT sale . msku , sale . source_id , sum ( CASE WHEN ( purchase_date_day >= cast ( DATE_FORMAT ( DATE_ADD ( CURDATE ( ) , INTERVAL - ? - ? DAY ) , ? ) AS UNSIGNED ) AND purchase_date_day <= cast ( DATE_FORMAT ( DATE_ADD ( CURDATE ( ) , INTERVAL - ? DAY ) , ? ) AS UNSIGNED ) ) THEN quantity_ordered ELSE ? END ) ? , sum ( CASE WHEN ( purchase_date_day >= cast ( DATE_FORMAT ( DATE_ADD ( CURDATE ( ) , INTERVAL - ? - ? DAY ) , ? ) AS UNSIGNED ) AND purchase_date_day <= cast ( DATE_FORMAT ( DATE_ADD ( CURDATE ( ) , INTERVAL - ? DAY ) , ? ) AS UNSIGNED ) ) THEN quantity_ordered ELSE ? END ) ? , sum ( CASE WHEN ( purchase_date_day >= cast ( DATE_FORMAT ( DATE_ADD ( CURDATE ( ) , INTERVAL - ? - ? DAY ) , ? ) AS UNSIGNED ) AND purchase_date_day <= cast ( DATE_FORMAT ( DATE_ADD ( CURDATE ( ) , INTERVAL - ? DAY ) , ? ) AS UNSIGNED ) ) THEN quantity_ordered ELSE ? END ) ? , sum ( CASE WHEN ( purchase_date_day >= cast ( DATE_FORMAT ( DATE_ADD ( CURDATE ( ) , INTERVAL - ? - ? DAY ) , ? ) AS UNSIGNED ) AND purchase_date_day <= cast ( DATE_FORMAT ( DATE_ADD ( CURDATE ( ) , INTERVAL - ? DAY ) , ? ) AS UNSIGNED ) ) THEN quantity_ordered ELSE ? END ) ? , sum ( CASE WHEN ( purchase_date_day >= cast ( DATE_FORMAT ( DATE_ADD ( CURDATE ( ) , INTERVAL - ? - ? DAY ) , ? ) AS UNSIGNED ) AND purchase_date_day <= cast ( DATE_FORMAT ( DATE_ADD ( CURDATE ( ) , INTERVAL - ? DAY ) , ? ) AS UNSIGNED ) ) THEN quantity_ordered ELSE ? END ) ? , sum ( CASE WHEN ( purchase_date_day >= cast ( DATE_FORMAT ( DATE_ADD ( CURDATE ( ) , INTERVAL - ? - ? DAY ) , ? ) AS UNSIGNED ) AND purchase_date_day <= cast ( DATE_FORMAT ( DATE_ADD ( CURDATE ( ) , INTERVAL - ? DAY ) , ? ) AS UNSIGNED ) ) THEN quantity_ordered ELSE ? END ) ? , sum ( CASE WHEN ( purchase_date_day >= cast ( DATE_FORMAT ( DATE_ADD ( CURDATE ( ) , INTERVAL - ? - ? DAY ) , ? ) AS UNSIGNED ) AND purchase_date_day <= cast ( DATE_FORMAT ( DATE_ADD ( CURD
007+ key
008- 真实SQL：with tmp_monitor_sale as ( select 2157458-6605-1014076531 msku, 6605 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_6 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;
008+ key_len
009- 袁慧斌,人工修改过
009+ ref
010- AISQL修改建议：WITH tmp_monitor_sale AS (
010+ rows
011- SELECT 2157458-6605-1014076531 AS msku, 6605 AS source_id
011+ filtered
012- )
012+ Extra
013- SELECT
013+ 1
014- sale.msku,
014+ PRIMARY
015- sale.source_id,
015+ <derived2>
016- SUM(CASE WHEN purchase_date_day BETWEEN DATE_FORMAT(CURDATE() - INTERVAL 8 DAY, %Y%m%d) AND DATE_FORMAT(CURDATE() - INTERVAL 2 DAY, %Y%m%d) THEN quantity_ordered ELSE 0 END) AS saleNum7,
016+ None
017- SUM(CASE WHEN purchase_date_day BETWEEN DATE_FORMAT(CURDATE() - INTERVAL 15 DAY, %Y%m%d) AND DATE_FORMAT(CURDATE() - INTERVAL 2 DAY, %Y%m%d) THEN quantity_ordered ELSE 0 END) AS saleNum14,
017+ system
018- SUM(CASE WHEN purchase_date_day BETWEEN DATE_FORMAT(CURDATE() - INTERVAL 29 DAY, %Y%m%d) AND DATE_FORMAT(CURDATE() - INTERVAL 2 DAY, %Y%m%d) THEN quantity_ordered ELSE 0 END) AS saleNum28,
018+ None
019- SUM(CASE WHEN purchase_date_day BETWEEN DATE_FORMAT(CURDATE() - INTERVAL 32 DAY, %Y%m%d) AND DATE_FORMAT(CURDATE() - INTERVAL 2 DAY, %Y%m%d) THEN quantity_ordered ELSE 0 END) AS saleNum30,
019+ None
020- SUM(CASE WHEN purchase_date_day BETWEEN DATE_FORMAT(CURDATE() - INTERVAL 61 DAY, %Y%m%d) AND DATE_FORMAT(CURDATE() - INTERVAL 2 DAY, %Y%m%d) THEN quantity_ordered ELSE 0 END) AS saleNum60,
020+ None
021- SUM(CASE WHEN purchase_date_day BETWEEN DATE_FORMAT(CURDATE() - INTERVAL 91 DAY, %Y%m%d) AND DATE_FORMAT(CURDATE() - INTERVAL 2 DAY, %Y%m%d) THEN quantity_ordered ELSE 0 END) AS saleNum90,
021+ None
022- SUM(CASE WHEN purchase_date_day BETWEEN DATE_FORMAT(CURDATE() - INTERVAL 121 DAY, %Y%m%d) AND DATE_FORMAT(CURDATE() - INTERVAL 2 DAY, %Y%m%d) THEN quantity_ordered ELSE 0 END) AS saleNum120,
022+ 1
023- SUM(CASE WHEN purchase_date_day BETWEEN DATE_FORMAT(CURDATE() - INTERVAL 181 DAY, %Y%m%d) AND DATE_FORMAT(CURDATE() - INTERVAL 2 DAY, %Y%m%d) THEN quantity_ordered ELSE 0 END) AS saleNum180,
023+ 100.0
024- SUM(CASE WHEN purchase_date_day BETWEEN DATE_FORMAT(CURDATE() - INTERVAL 372 DAY, %Y%m%d) AND DATE_FORMAT(CURDATE() - INTERVAL 366 DAY, %Y%m%d) THEN quantity_ordered ELSE 0 END) AS yoySaleNum7,
024+ None
025- SUM(CASE WHEN purchase_date_day BETWEEN DATE_FORMAT(CURDATE() - INTERVAL 379 DAY, %Y%m%d) AND DATE_FORMAT(CURDATE() - INTERVAL 366 DAY, %Y%m%d) THEN quantity_ordered ELSE 0 END) AS yoySaleNum14,
025+ 1
026- SUM(CASE WHEN purchase_date_day BETWEEN DATE_FORMAT(CURDATE() - INTERVAL 395 DAY, %Y%m%d) AND DATE_FORMAT(CURDATE() - INTERVAL 366 DAY, %Y%m%d) THEN quantity_ordered ELSE 0 END) AS yoySaleNum30,
026+ PRIMARY
027- SUM(CASE WHEN purchase_date_day BETWEEN DATE_FORMAT(CURDATE() - INTERVAL 425 DAY, %Y%m%d) AND DATE_FORMAT(CURDATE() - INTERVAL 366 DAY, %Y%m%d) THEN quantity_ordered ELSE 0 END) AS yoySaleNum60,
027+ sale
028- SUM(CASE WHEN purchase_date_day BETWEEN DATE_FORMAT(CURDATE() - INTERVAL 455 DAY, %Y%m%d) AND DATE_FORMAT(CURDATE() - INTERVAL 366 DAY, %Y%m%d) THEN quantity_ordered ELSE 0 END) AS yoySaleNum90
028+ None
029- FROM t_amazon_sales_6 sale
029+ ref
030- INNER JOIN tmp_monitor_sale tmp ON sale.source_id = tmp.source_id AND sale.msku = tmp.msku
030+ idx_source_id_msku,idx_purchase_date_day,idx_source_id_purchase_date_beijing_msku,idx_source_id_country_code_purchase_date_asin
031- WHERE sale.purchase_date_day >= DATE_FORMAT(CURDATE() - INTERVAL 731 DAY, %Y%m%d)
031+ idx_source_id_msku
032- AND sale.order_status IN (1, 2, 3, 4)
032+ 518
033- GROUP BY sale.msku, sale.source_id;
033+ const,const
034- explain表：
034+ 396434
035-
035+ 20.0
036-
036+ Using index condition; Using where
037-
037+ 2
038-
038+ DERIVED
039-
039+ None
040-
040+ None
041-
041+ None
042-
042+ None
043-
043+ None
044-
044+ None
045-
045+ None
046-
046+ None
047- id
047+ None
048-
048+ No tables used
049-
049+
050- select_type
050+ 额外建议：排查是否缺少索引 索引建议：join查询分析出错: (1146, "Table zhcxkj_center_order.tmp doesnt exist")join查询分析出错: (1146, "Table zhcxkj_center_order.tmp doesnt exist") 取出表 【t_amazon_sales_6】 where条件表达式 【order_status IN (1, 2, 3, 4)】 100000 条记录，重复的数据有：【98240】 条，没有必要为该字段创建索引。→ 【t_amazon_sales_6】表 【purchase_date_day】字段，索引已经存在，无需添加任何索引。 【t_amazon_sales_6】表 【purchase_date_day】字段，索引分析： +------------------+-----------------------+-------------------+---------------+ | TABLE_NAME | INDEX_NAME | COLUMN_NAME | CARDINALITY | +==================+=======================+===================+===============+ | t_amazon_sales_6 | idx_purchase_date_day | purchase_date_day | 41778 | +------------------+-----------------------+-------------------+---------------+
051-
052-
053- table
054-
055-
056- partitions
057-
058-
059- type
060-
061-
062- possible_keys
063-
064-
065- key
066-
067-
068- key_len
069-
070-
071- ref
072-
073-
074- rows
075-
076-
077- filtered
078-
079-
080- Extra
081-
082-
083-
084-
085-
086-
087- 1
088-
089-
090- PRIMARY
091-
092-
093- <derived2>
094-
095-
096- None
097-
098-
099- system
100-
101-
102- None
103-
104-
105- None
106-
107-
108- None
109-
110-
111- None
112-
113-
114- 1
115-
116-
117- 100.0
118-
119-
120- None
121-
122-
123-
124-
125- 1
126-
127-
128- PRIMARY
129-
130-
131- sale
132-
133-
134- None
135-
136-
137- ref
138-
139-
140- idx_source_id_msku,idx_purchase_date_day,idx_source_id_purchase_date_beijing_msku,idx_source_id_country_code_purchase_date_asin
141-
142-
143- idx_source_id_msku
144-
145-
146- 518
147-
148-
149- const,const
150-
151-
152- 396434
153-
154-
155- 20.0
156-
157-
158- Using index condition; Using where
159-
160-
161-
162-
163- 2
164-
165-
166- DERIVED
167-
168-
169- None
170-
171-
172- None
173-
174-
175- None
176-
177-
178- None
179-
180-
181- None
182-
183-
184- None
185-
186-
187- None
188-
189-
190- None
191-
192-
193- None
194-
195-
196- No tables used
197-
198-
199-
200-
201-
202- 额外建议：排查是否缺少索引
203- 索引建议：join查询分析出错: (1146, "Table zhcxkj_center_order.tmp doesnt exist")join查询分析出错: (1146, "Table zhcxkj_center_order.tmp doesnt exist")
204- 取出表 【t_amazon_sales_6】 where条件表达式 【order_status IN (1, 2, 3, 4)】 100000 条记录，重复的数据有：【98240】 条，没有必要为该字段创建索引。
205-
206- → 【t_amazon_sales_6】表 【purchase_date_day】字段，索引已经存在，无需添加任何索引。
207-
208- 【t_amazon_sales_6】表 【purchase_date_day】字段，索引分析：
209- +------------------+-----------------------+-------------------+---------------+
210- | TABLE_NAME | INDEX_NAME | COLUMN_NAME | CARDINALITY |
211- +==================+=======================+===================+===============+
212- | t_amazon_sales_6 | idx_purchase_date_day | purchase_date_day | 41778 |
213- +------------------+-----------------------+-------------------+---------------+
214-
215-

001- 反馈人：袁慧斌<br />
001+ 反馈人：袁慧斌<br /> 数据库：zhcxkj_center_order<br /> 查询时间：21.5635<br /> 执行次数: 37<br /> 扫描行数：152491<br /> 发送行数：1<br /> 指纹SQL: with tmp_monitor_sale as ( select ? msku , ? source_id ) SELECT sale . msku , sale . source_id , sum ( CASE WHEN ( purchase_date_day &gt;= cast ( DATE_FORMAT ( DATE_ADD ( CURDATE ( ) , INTERVAL - ? - ? DAY ) , ? ) AS UNSIGNED ) AND purchase_date_day &lt;= cast ( DATE_FORMAT ( DATE_ADD ( CURDATE ( ) , INTERVAL - ? DAY ) , ? ) AS UNSIGNED ) ) THEN quantity_ordered ELSE ? END ) ? , sum ( CASE WHEN ( purchase_date_day &gt;= cast ( DATE_FORMAT ( DATE_ADD ( CURDATE ( ) , INTERVAL - ? - ? DAY ) , ? ) AS UNSIGNED ) AND purchase_date_day &lt;= cast ( DATE_FORMAT ( DATE_ADD ( CURDATE ( ) , INTERVAL - ? DAY ) , ? ) AS UNSIGNED ) ) THEN quantity_ordered ELSE ? END ) ? , sum ( CASE WHEN ( purchase_date_day &gt;= cast ( DATE_FORMAT ( DATE_ADD ( CURDATE ( ) , INTERVAL - ? - ? DAY ) , ? ) AS UNSIGNED ) AND purchase_date_day &lt;= cast ( DATE_FORMAT ( DATE_ADD ( CURDATE ( ) , INTERVAL - ? DAY ) , ? ) AS UNSIGNED ) ) THEN quantity_ordered ELSE ? END ) ? , sum ( CASE WHEN ( purchase_date_day &gt;= cast ( DATE_FORMAT ( DATE_ADD ( CURDATE ( ) , INTERVAL - ? - ? DAY ) , ? ) AS UNSIGNED ) AND purchase_date_day &lt;= cast ( DATE_FORMAT ( DATE_ADD ( CURDATE ( ) , INTERVAL - ? DAY ) , ? ) AS UNSIGNED ) ) THEN quantity_ordered ELSE ? END ) ? , sum ( CASE WHEN ( purchase_date_day &gt;= cast ( DATE_FORMAT ( DATE_ADD ( CURDATE ( ) , INTERVAL - ? - ? DAY ) , ? ) AS UNSIGNED ) AND purchase_date_day &lt;= cast ( DATE_FORMAT ( DATE_ADD ( CURDATE ( ) , INTERVAL - ? DAY ) , ? ) AS UNSIGNED ) ) THEN quantity_ordered ELSE ? END ) ? , sum ( CASE WHEN ( purchase_date_day &gt;= cast ( DATE_FORMAT ( DATE_ADD ( CURDATE ( ) , INTERVAL - ? - ? DAY ) , ? ) AS UNSIGNED ) AND purchase_date_day &lt;= cast ( DATE_FORMAT ( DATE_ADD ( CURDATE ( ) , INTERVAL - ? DAY ) , ? ) AS UNSIGNED ) ) THEN quantity_ordered ELSE ? END ) ? , sum ( CASE WHEN ( purchase_date_day &gt;= cast ( DATE_FORMAT ( DATE_ADD ( CURDATE ( ) , INTERVAL - ? - ? DAY ) , ? ) AS UNSIGNED ) AND purchase_date_day &lt;= cast ( DATE_FORMAT ( DATE_ADD ( CURD<br /> 真实SQL：with tmp_monitor_sale as ( select 2157458-6605-1014076531 msku, 6605 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_6 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;<br /> 袁慧斌,人工修改过<br /> AISQL修改建议：WITH tmp_monitor_sale AS (<br /> SELECT 2157458-6605-1014076531 AS msku, 6605 AS source_id<br /> )<br /> SELECT <br /> sale.msku,<br /> sale.source_id,<br /> SUM(CASE WHEN purchase_date_day BETWEEN DATE_FORMAT(CURDATE() - INTERVAL 8 DAY, %Y%m%d) AND DATE_FORMAT(CURDATE() - INTERVAL 2 DAY, %Y%m%d) THEN quantity_ordered ELSE 0 END) AS saleNum7,<br /> SUM(CASE WHEN purchase_date_day BETWEEN DATE_FORMAT(CURDATE() - INTERVAL 15 DAY, %Y%m%d) AND DATE_FORMAT(CURDATE() - INTERVAL 2 DAY, %Y%m%d) THEN quantity_ordered ELSE 0 END) AS saleNum14,<br /> SUM(CASE WHEN purchase_date_day BETWEEN DATE_FORMAT(CURDATE() - INTERVAL 29 DAY, %Y%m%d) AND DATE_FORMAT(CURDATE() - INTERVAL 2 DAY, %Y%m%d) THEN quantity_ordered ELSE 0 END) AS saleNum28,<br /> SUM(CASE WHEN purchase_date_day BETWEEN DATE_FORMAT(CURDATE() - INTERVAL 32 DAY, %Y%m%d) AND DATE_FORMAT(CURDATE() - INTERVAL 2 DAY, %Y%m%d) THEN quantity_ordered ELSE 0 END) AS saleNum30,<br /> SUM(CASE WHEN purchase_date_day BETWEEN DATE_FORMAT(CURDATE() - INTERVAL 61 DAY, %Y%m%d) AND DATE_FORMAT(CURDATE() - INTERVAL 2 DAY, %Y%m%d) THEN quantity_ordered ELSE 0 END) AS saleNum60,<br /> SUM(CASE WHEN purchase_date_day BETWEEN DATE_FORMAT(CURDATE() - INTERVAL 91 DAY, %Y%m%d) AND DATE_FORMAT(CURDATE() - INTERVAL 2 DAY, %Y%m%d) THEN quantity_ordered ELSE 0 END) AS saleNum90,<br /> SUM(CASE WHEN purchase_date_day BETWEEN DATE_FORMAT(CURDATE() - INTERVAL 121 DAY, %Y%m%d) AND DATE_FORMAT(CURDATE() - INTERVAL 2 DAY, %Y%m%d) THEN quantity_ordered ELSE 0 END) AS saleNum120,<br /> SUM(CASE WHEN purchase_date_day BETWEEN DATE_FORMAT(CURDATE() - INTERVAL 181 DAY, %Y%m%d) AND DATE_FORMAT(CURDATE() - INTERVAL 2 DAY, %Y%m%d) THEN quantity_ordered ELSE 0 END) AS saleNum180,<br /> SUM(CASE WHEN purchase_date_day BETWEEN DATE_FORMAT(CURDATE() - INTERVAL 372 DAY, %Y%m%d) AND DATE_FORMAT(CURDATE() - INTERVAL 366 DAY, %Y%m%d) THEN quantity_ordered ELSE 0 END) AS yoySaleNum7,<br /> SUM(CASE WHEN purchase_date_day BETWEEN DATE_FORMAT(CURDATE() - INTERVAL 379 DAY, %Y%m%d) AND DATE_FORMAT(CURDATE() - INTERVAL 366 DAY, %Y%m%d) THEN quantity_ordered ELSE 0 END) AS yoySaleNum14,<br /> SUM(CASE WHEN purchase_date_day BETWEEN DATE_FORMAT(CURDATE() - INTERVAL 395 DAY, %Y%m%d) AND DATE_FORMAT(CURDATE() - INTERVAL 366 DAY, %Y%m%d) THEN quantity_ordered ELSE 0 END) AS yoySaleNum30,<br /> SUM(CASE WHEN purchase_date_day BETWEEN DATE_FORMAT(CURDATE() - INTERVAL 425 DAY, %Y%m%d) AND DATE_FORMAT(CURDATE() - INTERVAL 366 DAY, %Y%m%d) THEN quantity_ordered ELSE 0 END) AS yoySaleNum60,<br /> SUM(CASE WHEN purchase_date_day BETWEEN DATE_FORMAT(CURDATE() - INTERVAL 455 DAY, %Y%m%d) AND DATE_FORMAT(CURDATE() - INTERVAL 366 DAY, %Y%m%d) THEN quantity_ordered ELSE 0 END) AS yoySaleNum90<br /> FROM t_amazon_sales_6 sale<br /> INNER JOIN tmp_monitor_sale tmp ON sale.source_id = tmp.source_id AND sale.msku = tmp.msku<br /> WHERE sale.purchase_date_day &gt;= DATE_FORMAT(CURDATE() - INTERVAL 731 DAY, %Y%m%d)<br /> AND sale.order_status IN (1, 2, 3, 4)<br /> GROUP BY sale.msku, sale.source_id;<br /> explain表：<br /><br /><br /><br /><br /><br /><br /><br /><br /><table><tbody><tr><th>id</th>
002- 数据库：zhcxkj_center_order<br />
002+ <th>select_type</th>
003- 查询时间：21.5635<br />
003+ <th>table</th>
004- 执行次数: 37<br />
004+ <th>partitions</th>
005- 扫描行数：152491<br />
005+ <th>type</th>
006- 发送行数：1<br />
006+ <th>possible_keys</th>
007- 指纹SQL: with tmp_monitor_sale as ( select ? msku , ? source_id ) SELECT sale . msku , sale . source_id , sum ( CASE WHEN ( purchase_date_day &gt;= cast ( DATE_FORMAT ( DATE_ADD ( CURDATE ( ) , INTERVAL - ? - ? DAY ) , ? ) AS UNSIGNED ) AND purchase_date_day &lt;= cast ( DATE_FORMAT ( DATE_ADD ( CURDATE ( ) , INTERVAL - ? DAY ) , ? ) AS UNSIGNED ) ) THEN quantity_ordered ELSE ? END ) ? , sum ( CASE WHEN ( purchase_date_day &gt;= cast ( DATE_FORMAT ( DATE_ADD ( CURDATE ( ) , INTERVAL - ? - ? DAY ) , ? ) AS UNSIGNED ) AND purchase_date_day &lt;= cast ( DATE_FORMAT ( DATE_ADD ( CURDATE ( ) , INTERVAL - ? DAY ) , ? ) AS UNSIGNED ) ) THEN quantity_ordered ELSE ? END ) ? , sum ( CASE WHEN ( purchase_date_day &gt;= cast ( DATE_FORMAT ( DATE_ADD ( CURDATE ( ) , INTERVAL - ? - ? DAY ) , ? ) AS UNSIGNED ) AND purchase_date_day &lt;= cast ( DATE_FORMAT ( DATE_ADD ( CURDATE ( ) , INTERVAL - ? DAY ) , ? ) AS UNSIGNED ) ) THEN quantity_ordered ELSE ? END ) ? , sum ( CASE WHEN ( purchase_date_day &gt;= cast ( DATE_FORMAT ( DATE_ADD ( CURDATE ( ) , INTERVAL - ? - ? DAY ) , ? ) AS UNSIGNED ) AND purchase_date_day &lt;= cast ( DATE_FORMAT ( DATE_ADD ( CURDATE ( ) , INTERVAL - ? DAY ) , ? ) AS UNSIGNED ) ) THEN quantity_ordered ELSE ? END ) ? , sum ( CASE WHEN ( purchase_date_day &gt;= cast ( DATE_FORMAT ( DATE_ADD ( CURDATE ( ) , INTERVAL - ? - ? DAY ) , ? ) AS UNSIGNED ) AND purchase_date_day &lt;= cast ( DATE_FORMAT ( DATE_ADD ( CURDATE ( ) , INTERVAL - ? DAY ) , ? ) AS UNSIGNED ) ) THEN quantity_ordered ELSE ? END ) ? , sum ( CASE WHEN ( purchase_date_day &gt;= cast ( DATE_FORMAT ( DATE_ADD ( CURDATE ( ) , INTERVAL - ? - ? DAY ) , ? ) AS UNSIGNED ) AND purchase_date_day &lt;= cast ( DATE_FORMAT ( DATE_ADD ( CURDATE ( ) , INTERVAL - ? DAY ) , ? ) AS UNSIGNED ) ) THEN quantity_ordered ELSE ? END ) ? , sum ( CASE WHEN ( purchase_date_day &gt;= cast ( DATE_FORMAT ( DATE_ADD ( CURDATE ( ) , INTERVAL - ? - ? DAY ) , ? ) AS UNSIGNED ) AND purchase_date_day &lt;= cast ( DATE_FORMAT ( DATE_ADD ( CURD<br />
007+ <th>key</th>
008- 真实SQL：with tmp_monitor_sale as ( select 2157458-6605-1014076531 msku, 6605 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_6 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;<br />
008+ <th>key_len</th>
009- 袁慧斌,人工修改过<br />
009+ <th>ref</th>
010- AISQL修改建议：WITH tmp_monitor_sale AS (<br />
010+ <th>rows</th>
011- SELECT 2157458-6605-1014076531 AS msku, 6605 AS source_id<br />
011+ <th>filtered</th>
012- )<br />
012+ <th>Extra</th>
013- SELECT <br />
013+ </tr></tbody><tbody><tr><td>1</td>
014- sale.msku,<br />
014+ <td>PRIMARY</td>
015- sale.source_id,<br />
015+ <td>&lt;derived2&gt;</td>
016- SUM(CASE WHEN purchase_date_day BETWEEN DATE_FORMAT(CURDATE() - INTERVAL 8 DAY, %Y%m%d) AND DATE_FORMAT(CURDATE() - INTERVAL 2 DAY, %Y%m%d) THEN quantity_ordered ELSE 0 END) AS saleNum7,<br />
016+ <td>None</td>
017- SUM(CASE WHEN purchase_date_day BETWEEN DATE_FORMAT(CURDATE() - INTERVAL 15 DAY, %Y%m%d) AND DATE_FORMAT(CURDATE() - INTERVAL 2 DAY, %Y%m%d) THEN quantity_ordered ELSE 0 END) AS saleNum14,<br />
017+ <td>system</td>
018- SUM(CASE WHEN purchase_date_day BETWEEN DATE_FORMAT(CURDATE() - INTERVAL 29 DAY, %Y%m%d) AND DATE_FORMAT(CURDATE() - INTERVAL 2 DAY, %Y%m%d) THEN quantity_ordered ELSE 0 END) AS saleNum28,<br />
018+ <td>None</td>
019- SUM(CASE WHEN purchase_date_day BETWEEN DATE_FORMAT(CURDATE() - INTERVAL 32 DAY, %Y%m%d) AND DATE_FORMAT(CURDATE() - INTERVAL 2 DAY, %Y%m%d) THEN quantity_ordered ELSE 0 END) AS saleNum30,<br />
019+ <td>None</td>
020- SUM(CASE WHEN purchase_date_day BETWEEN DATE_FORMAT(CURDATE() - INTERVAL 61 DAY, %Y%m%d) AND DATE_FORMAT(CURDATE() - INTERVAL 2 DAY, %Y%m%d) THEN quantity_ordered ELSE 0 END) AS saleNum60,<br />
020+ <td>None</td>
021- SUM(CASE WHEN purchase_date_day BETWEEN DATE_FORMAT(CURDATE() - INTERVAL 91 DAY, %Y%m%d) AND DATE_FORMAT(CURDATE() - INTERVAL 2 DAY, %Y%m%d) THEN quantity_ordered ELSE 0 END) AS saleNum90,<br />
021+ <td>None</td>
022- SUM(CASE WHEN purchase_date_day BETWEEN DATE_FORMAT(CURDATE() - INTERVAL 121 DAY, %Y%m%d) AND DATE_FORMAT(CURDATE() - INTERVAL 2 DAY, %Y%m%d) THEN quantity_ordered ELSE 0 END) AS saleNum120,<br />
022+ <td>1</td>
023- SUM(CASE WHEN purchase_date_day BETWEEN DATE_FORMAT(CURDATE() - INTERVAL 181 DAY, %Y%m%d) AND DATE_FORMAT(CURDATE() - INTERVAL 2 DAY, %Y%m%d) THEN quantity_ordered ELSE 0 END) AS saleNum180,<br />
023+ <td>100.0</td>
024- SUM(CASE WHEN purchase_date_day BETWEEN DATE_FORMAT(CURDATE() - INTERVAL 372 DAY, %Y%m%d) AND DATE_FORMAT(CURDATE() - INTERVAL 366 DAY, %Y%m%d) THEN quantity_ordered ELSE 0 END) AS yoySaleNum7,<br />
024+ <td>None</td>
025- SUM(CASE WHEN purchase_date_day BETWEEN DATE_FORMAT(CURDATE() - INTERVAL 379 DAY, %Y%m%d) AND DATE_FORMAT(CURDATE() - INTERVAL 366 DAY, %Y%m%d) THEN quantity_ordered ELSE 0 END) AS yoySaleNum14,<br />
025+ </tr><tr><td>1</td>
026- SUM(CASE WHEN purchase_date_day BETWEEN DATE_FORMAT(CURDATE() - INTERVAL 395 DAY, %Y%m%d) AND DATE_FORMAT(CURDATE() - INTERVAL 366 DAY, %Y%m%d) THEN quantity_ordered ELSE 0 END) AS yoySaleNum30,<br />
026+ <td>PRIMARY</td>
027- SUM(CASE WHEN purchase_date_day BETWEEN DATE_FORMAT(CURDATE() - INTERVAL 425 DAY, %Y%m%d) AND DATE_FORMAT(CURDATE() - INTERVAL 366 DAY, %Y%m%d) THEN quantity_ordered ELSE 0 END) AS yoySaleNum60,<br />
027+ <td>sale</td>
028- SUM(CASE WHEN purchase_date_day BETWEEN DATE_FORMAT(CURDATE() - INTERVAL 455 DAY, %Y%m%d) AND DATE_FORMAT(CURDATE() - INTERVAL 366 DAY, %Y%m%d) THEN quantity_ordered ELSE 0 END) AS yoySaleNum90<br />
028+ <td>None</td>
029- FROM t_amazon_sales_6 sale<br />
029+ <td>ref</td>
030- INNER JOIN tmp_monitor_sale tmp ON sale.source_id = tmp.source_id AND sale.msku = tmp.msku<br />
030+ <td>idx_source_id_msku,idx_purchase_date_day,idx_source_id_purchase_date_beijing_msku,idx_source_id_country_code_purchase_date_asin</td>
031- WHERE sale.purchase_date_day &gt;= DATE_FORMAT(CURDATE() - INTERVAL 731 DAY, %Y%m%d)<br />
031+ <td>idx_source_id_msku</td>
032- AND sale.order_status IN (1, 2, 3, 4)<br />
032+ <td>518</td>
033- GROUP BY sale.msku, sale.source_id;<br />
033+ <td>const,const</td>
034- explain表：<br />
034+ <td>396434</td>
035- <br />
035+ <td>20.0</td>
036- <br />
036+ <td>Using index condition; Using where</td>
037- <br />
037+ </tr><tr><td>2</td>
038- <br />
038+ <td>DERIVED</td>
039- <br />
039+ <td>None</td>
040- <br />
040+ <td>None</td>
041- <br />
041+ <td>None</td>
042- <br />
042+ <td>None</td>
043- <table>
043+ <td>None</td>
044- <tbody>
044+ <td>None</td>
045- <tr>
045+ <td>None</td>
046- <th>
046+ <td>None</td>
047- id
047+ <td>None</td>
048- </th>
048+ <td>No tables used</td>
049- <th>
049+ </tr></tbody></table><br />
050- select_type
050+ 额外建议：排查是否缺少索引<br /> 索引建议：join查询分析出错: (1146, "Table zhcxkj_center_order.tmp doesnt exist")join查询分析出错: (1146, "Table zhcxkj_center_order.tmp doesnt exist")<br /> 取出表 【t_amazon_sales_6】 where条件表达式 【order_status IN (1, 2, 3, 4)】 100000 条记录，重复的数据有：【98240】 条，没有必要为该字段创建索引。<br /><br /><span style="color:#008000;font-weight:bold;">→ 【t_amazon_sales_6】表 【purchase_date_day】字段，索引已经存在，无需添加任何索引。</span><br /><br /> 【t_amazon_sales_6】表 【purchase_date_day】字段，索引分析：<br /> +------------------+-----------------------+-------------------+---------------+<br /> | TABLE_NAME | INDEX_NAME | COLUMN_NAME | CARDINALITY |<br /> +==================+=======================+===================+===============+<br /> | t_amazon_sales_6 | idx_purchase_date_day | purchase_date_day | 41778 |<br /> +------------------+-----------------------+-------------------+---------------+<br /><br /><br />
051- </th>
052- <th>
053- table
054- </th>
055- <th>
056- partitions
057- </th>
058- <th>
059- type
060- </th>
061- <th>
062- possible_keys
063- </th>
064- <th>
065- key
066- </th>
067- <th>
068- key_len
069- </th>
070- <th>
071- ref
072- </th>
073- <th>
074- rows
075- </th>
076- <th>
077- filtered
078- </th>
079- <th>
080- Extra
081- </th>
082- </tr>
083- </tbody>
084- <tbody>
085- <tr>
086- <td>
087- 1
088- </td>
089- <td>
090- PRIMARY
091- </td>
092- <td>
093- &lt;derived2&gt;
094- </td>
095- <td>
096- None
097- </td>
098- <td>
099- system
100- </td>
101- <td>
102- None
103- </td>
104- <td>
105- None
106- </td>
107- <td>
108- None
109- </td>
110- <td>
111- None
112- </td>
113- <td>
114- 1
115- </td>
116- <td>
117- 100.0
118- </td>
119- <td>
120- None
121- </td>
122- </tr>
123- <tr>
124- <td>
125- 1
126- </td>
127- <td>
128- PRIMARY
129- </td>
130- <td>
131- sale
132- </td>
133- <td>
134- None
135- </td>
136- <td>
137- ref
138- </td>
139- <td>
140- idx_source_id_msku,idx_purchase_date_day,idx_source_id_purchase_date_beijing_msku,idx_source_id_country_code_purchase_date_asin
141- </td>
142- <td>
143- idx_source_id_msku
144- </td>
145- <td>
146- 518
147- </td>
148- <td>
149- const,const
150- </td>
151- <td>
152- 396434
153- </td>
154- <td>
155- 20.0
156- </td>
157- <td>
158- Using index condition; Using where
159- </td>
160- </tr>
161- <tr>
162- <td>
163- 2
164- </td>
165- <td>
166- DERIVED
167- </td>
168- <td>
169- None
170- </td>
171- <td>
172- None
173- </td>
174- <td>
175- None
176- </td>
177- <td>
178- None
179- </td>
180- <td>
181- None
182- </td>
183- <td>
184- None
185- </td>
186- <td>
187- None
188- </td>
189- <td>
190- None
191- </td>
192- <td>
193- None
194- </td>
195- <td>
196- No tables used
197- </td>
198- </tr>
199- </tbody>
200- </table>
201- <br />
202- 额外建议：排查是否缺少索引<br />
203- 索引建议：join查询分析出错: (1146, "Table zhcxkj_center_order.tmp doesnt exist")join查询分析出错: (1146, "Table zhcxkj_center_order.tmp doesnt exist")<br />
204- 取出表 【t_amazon_sales_6】 where条件表达式 【order_status IN (1, 2, 3, 4)】 100000 条记录，重复的数据有：【98240】 条，没有必要为该字段创建索引。<br />
205- <br />
206- <span style="color:green;font-weight:bold;">→ 【t_amazon_sales_6】表 【purchase_date_day】字段，索引已经存在，无需添加任何索引。</span><br />
207- <br />
208- 【t_amazon_sales_6】表 【purchase_date_day】字段，索引分析：<br />
209- +------------------+-----------------------+-------------------+---------------+<br />
210- | TABLE_NAME | INDEX_NAME | COLUMN_NAME | CARDINALITY |<br />
211- +==================+=======================+===================+===============+<br />
212- | t_amazon_sales_6 | idx_purchase_date_day | purchase_date_day | 41778 |<br />
213- +------------------+-----------------------+-------------------+---------------+<br />
214- <br />
215- <br />