深圳智汇创想科技有限公司

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

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