001- 反馈人 袁慧斌
001+ 反馈人 袁慧斌 数据库：zhcxkj_center_order 查询时间：22.3793 扫描行数：2576.22 发送行数：0 指纹SQL: INSERT INTO t_amazon_sales_aggregate ( msku , source_id , customer_id , three_day_ago_sale_num , yesterday_before_sale_num , today_sale_num , yesterday_sale_num , three_day_sale_num , six_day_sale_num , seven_day_sale_num , fourteen_day_sale_num , seven_tendency , thirty_day_sale_num , sixty_day_sale_num , thirty_tendency , ninety_day_sale_num , one_year_sale_num , create_time , three_day_ago_sale_num_beijing , yesterday_before_sale_num_beijing , today_sale_num_beijing , yesterday_sale_num_beijing , three_day_sale_num_beijing , six_day_sale_num_beijing , seven_day_sale_num_beijing , fourteen_day_sale_num_beijing , seven_tendency_beijing , thirty_day_sale_num_beijing , sixty_day_sale_num_beijing , thirty_tendency_beijing , ninety_day_sale_num_beijing , one_year_sale_num_beijing ) select t . msku , t . source_id , ? , t . threeDayAgoSaleNum , t . yesterdayBeforeSaleNum , t . todaySaleNum , t . yesterdaySaleNum , t . threeDaySaleNum , t . sixDaySaleNum , t . sevenDaySaleNum , t . fourteenDaySaleNum , ( t . sevenDaySaleNum * ? - t . fourteenDaySaleNum ) sevenTendency , t . thirtyDaySaleNum , t . sixtyDaySaleNum , ( t . thirtyDaySaleNum * ? - t . sixtyDaySaleNum ) thirtyTendency , t . ninetyDaySaleNum , t . oneYearSaleNum , now ( ) , t . threeDayAgoSaleNumBeijing , t . yesterdayBeforeSaleNumBeijing , t . todaySaleNumBeijing , t . yesterdaySaleNumBeijing , t . threeDaySaleNumBeijing , t . sixDaySaleNumBeijing , t . sevenDaySaleNumBeijing , t . fourteenDaySaleNumBeijing , ( t . sevenDaySaleNumBeijing * ? - t . fourteenDaySaleNumBeijing ) sevenTendencyBeijing , t . thirtyDaySaleNumBeijing , t . sixtyDaySaleNumBeijing , ( t . thirtyDaySaleNumBeijing * ? - t . sixtyDaySaleNumBeijing ) thirtyTendencyBeijing , t . ninetyDaySaleNumBeijing , t . oneYearSaleNumBeijing from ( SELECT sale . msku , sale . source_id , sum ( case when purchase_date_day = cast ( DATE_FORMAT ( DATE_ADD ( CURDATE ( ) , INTERVAL ? DAY ) , ? ) as UNSIGNED ) then quantity_ordered else ? END ) ? , sum ( c 真实SQL：INSERT INTO t_amazon_sales_aggregate ( msku, source_id, customer_id, three_day_ago_sale_num, yesterday_before_sale_num, today_sale_num, yesterday_sale_num, three_day_sale_num, six_day_sale_num, seven_day_sale_num, fourteen_day_sale_num, seven_tendency, thirty_day_sale_num, sixty_day_sale_num, thirty_tendency, ninety_day_sale_num, one_year_sale_num, create_time, three_day_ago_sale_num_beijing, yesterday_before_sale_num_beijing, today_sale_num_beijing, yesterday_sale_num_beijing, three_day_sale_num_beijing, six_day_sale_num_beijing, seven_day_sale_num_beijing, fourteen_day_sale_num_beijing, seven_tendency_beijing, thirty_day_sale_num_beijing, sixty_day_sale_num_beijing, thirty_tendency_beijing, ninety_day_sale_num_beijing, one_year_sale_num_beijing ) select t.msku, t.source_id, 1, t.threeDayAgoSaleNum, t.yesterdayBeforeSaleNum, t.todaySaleNum, t.yesterdaySaleNum, t.threeDaySaleNum, t.sixDaySaleNum, t.sevenDaySaleNum, t.fourteenDaySaleNum, (t.sevenDaySaleNum * 2 - t.fourteenDaySaleNum) sevenTendency, t.thirtyDaySaleNum, t.sixtyDaySaleNum, (t.thirtyDaySaleNum * 2 - t.sixtyDaySaleNum ) thirtyTendency, t.ninetyDaySaleNum, t.oneYearSaleNum, now(), t.threeDayAgoSaleNumBeijing, t.yesterdayBeforeSaleNumBeijing, t.todaySaleNumBeijing, t.yesterdaySaleNumBeijing, t.threeDaySaleNumBeijing, t.sixDaySaleNumBeijing, t.sevenDaySaleNumBeijing, t.fourteenDaySaleNumBeijing, (t.sevenDaySaleNumBeijing * 2 - t.fourteenDaySaleNumBeijing) sevenTendencyBeijing, t.thirtyDaySaleNumBeijing, t.sixtyDaySaleNumBeijing, (t.thirtyDaySaleNumBeijing * 2 - t.sixtyDaySaleNumBeijing ) thirtyTendencyBeijing, t.ninetyDaySaleNumBeijing, t.oneYearSaleNumBeijing from ( SELECT sale.msku, sale.source_id, sum( case when purchase_date_day = cast(DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL -3 DAY), %Y%m%d) as UNSIGNED) then quantity_ordered else 0 END) threeDayAgoSaleNum, sum( case when purchase_date_day = cast(DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL -2 DAY), %Y%m%d) as UNSIGNED) then quantity_ordered else 0 END) yesterdayBeforeSaleNum, sum( CASE WHEN purchase_date_day = cast( DATE_FORMAT( CURDATE(), %Y%m%d ) AS UNSIGNED ) THEN quantity_ordered ELSE 0 END ) todaySaleNum, sum( CASE WHEN purchase_date_day = cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 1 DAY ), %Y%m%d ) AS UNSIGNED ) THENquantity_ordered ELSE 0 END ) yesterdaySaleNum, sum( CASE WHEN ( purchase_date_day >= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 2 DAY ), %Y%m%d ) AS UNSIGNED ) AND purchase_date_day <= cast( DATE_FORMAT( now(), %Y%m%d ) AS UNSIGNED )) THEN quantity_ordered ELSE 0 END ) threeDaySaleNum, sum( CASE WHEN ( purchase_date_day >= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 5 - 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 ) sixDaySaleNum, 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 ) sevenDaySaleNum, 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 ) fourteenDaySaleNum, sum( CASE WHEN ( purchase_date_day >= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 29 - 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 ) thirtyDaySaleNum, 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 ) sixtyDaySaleNum, 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 ) ninetyDaySaleNum, sum( CASE WHEN ( purchase_date_day >= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 364 - 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 ) oneYearSaleNum, sum( CASE WHEN ( purchase_date_beijing >= DATE_FORMAT( CURDATE(), %Y-%m-%d 00:00:00 ) ) THEN quantity_ordered ELSE 0 END ) todaySaleNumBeijing, sum( CASE WHEN ( purchase_date_beijing >= DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 1 DAY ), %Y-%m-%d 00:00:00 ) and purchase_date_beijing < DATE_FORMAT(CURDATE(), %Y-%m-%d 00:00:00 ) ) THEN quantity_ordered ELSE 0 END ) yesterdaySaleNumBeijing, sum( case when ( purchase_date_beijing >= DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 2 DAY), %Y-%m-%d 00:00:00) and purchase_date_beijing < DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL -1 DAY), %Y-%m-%d 00:00:00) ) then quantity_ordered else 0 END) yesterdayBeforeSaleNumBeijing, sum( case when ( purchase_date_beijing >= DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 3 DAY), %Y-%m-%d 00:00:00) and purchase_date_beijing < DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL -2 DAY), %Y-%m-%d 00:00:00) ) then quantity_ordered else 0 END) threeDayAgoSaleNumBeijing, sum( CASE WHEN ( purchase_date_beijing >= DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 2 DAY ), %Y-%m-%d 00:00:00 )) THEN quantity_ordered ELSE 0 END ) threeDaySaleNumBeijing, sum( CASE WHEN ( purchase_date_beijing >= DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 5 - 1 DAY ), %Y-%m-%d 00:00:00 ) and purchase_date_beijing < DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 0 DAY ), %Y-%m-%d 00:00:00 ) ) THEN quantity_ordered ELSE 0 END ) sixDaySaleNumBeijing, sum( CASE WHEN ( purchase_date_beijing >= DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 6 - 1 DAY ), %Y-%m-%d 00:00:00 ) and purchase_date_beijing < DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 0 DAY ), %Y-%m-%d 00:00:00 ) ) THEN quantity_ordered ELSE 0 END ) sevenDaySaleNumBeijing, sum( CASE WHEN ( purchase_date_beijing >= DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 13 -1 DAY ), %Y-%m-%d 00:00:00 ) and purchase_date_beijing < DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 0 DAY ), %Y-%m-%d 00:00:00 ) ) THEN quantity_ordered ELSE 0 END ) fourteenDaySaleNumBeijing, sum( CASE WHEN ( purchase_date_beijing >= DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 29 -1 DAY ), %Y-%m-%d 00:00:00 ) and purchase_date_beijing < DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 0 DAY ), %Y-%m-%d 00:00:00 ) ) THEN quantity_ordered ELSE 0 END ) thirtyDaySaleNumBeijing, sum( CASE WHEN ( purchase_date_beijing >= DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 59 -1 DAY ), %Y-%m-%d 00:00:00 ) and purchase_date_beijing < DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 0 DAY ), %Y-%m-%d 00:00:00 ) ) THEN quantity_ordered ELSE 0 END ) sixtyDaySaleNumBeijing, sum( CASE WHEN ( purchase_date_beijing >= DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 89 -1 DAY ), %Y-%m-%d 00:00:00 ) and purchase_date_beijing < DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 0 DAY ), %Y-%m-%d 00:00:00 ) ) THEN quantity_ordered ELSE 0 END ) ninetyDaySaleNumBeijing, sum( CASE WHEN ( purchase_date_beijing >= DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 364 -1 DAY ), %Y-%m-%d 00:00:00 ) and purchase_date_beijing < DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 0 DAY ), %Y-%m-%d 00:00:00 ) ) THEN quantity_ordered ELSE 0 END ) oneYearSaleNumBeijing FROM t_amazon_sales_4 sale inner join ( select 21947 as source_id, 001NR173T5H42QPLRKTD as msku union select 16875 as source_id, 009JY8YSJJMY5033FVPB55 as msku union select 14427 as source_id, 00D21229491J as msku union select 8131 as source_id, 010415546VXBT0V as msku union select 16171 as source_id, 0112251WXUQBF0I as msku union select 15411 as source_id, 01H7J315HO6067K8JW9 as msku union select 16283 as source_id, 0235B311P21OUHVB00N1 as msku union select 9523 as source_id, 02...; 袁慧斌,人工修改过 AISQL修改建议：SQL 语句 过大，建议分批次 组合成SQL explain表： 额外建议： 索引建议：
002- 数据库：zhcxkj_center_order
003- 查询时间：22.3793
004- 扫描行数：2576.22
005- 发送行数：0
006- 指纹SQL: INSERT INTO t_amazon_sales_aggregate ( msku , source_id , customer_id , three_day_ago_sale_num , yesterday_before_sale_num , today_sale_num , yesterday_sale_num , three_day_sale_num , six_day_sale_num , seven_day_sale_num , fourteen_day_sale_num , seven_tendency , thirty_day_sale_num , sixty_day_sale_num , thirty_tendency , ninety_day_sale_num , one_year_sale_num , create_time , three_day_ago_sale_num_beijing , yesterday_before_sale_num_beijing , today_sale_num_beijing , yesterday_sale_num_beijing , three_day_sale_num_beijing , six_day_sale_num_beijing , seven_day_sale_num_beijing , fourteen_day_sale_num_beijing , seven_tendency_beijing , thirty_day_sale_num_beijing , sixty_day_sale_num_beijing , thirty_tendency_beijing , ninety_day_sale_num_beijing , one_year_sale_num_beijing ) select t . msku , t . source_id , ? , t . threeDayAgoSaleNum , t . yesterdayBeforeSaleNum , t . todaySaleNum , t . yesterdaySaleNum , t . threeDaySaleNum , t . sixDaySaleNum , t . sevenDaySaleNum , t . fourteenDaySaleNum , ( t . sevenDaySaleNum * ? - t . fourteenDaySaleNum ) sevenTendency , t . thirtyDaySaleNum , t . sixtyDaySaleNum , ( t . thirtyDaySaleNum * ? - t . sixtyDaySaleNum ) thirtyTendency , t . ninetyDaySaleNum , t . oneYearSaleNum , now ( ) , t . threeDayAgoSaleNumBeijing , t . yesterdayBeforeSaleNumBeijing , t . todaySaleNumBeijing , t . yesterdaySaleNumBeijing , t . threeDaySaleNumBeijing , t . sixDaySaleNumBeijing , t . sevenDaySaleNumBeijing , t . fourteenDaySaleNumBeijing , ( t . sevenDaySaleNumBeijing * ? - t . fourteenDaySaleNumBeijing ) sevenTendencyBeijing , t . thirtyDaySaleNumBeijing , t . sixtyDaySaleNumBeijing , ( t . thirtyDaySaleNumBeijing * ? - t . sixtyDaySaleNumBeijing ) thirtyTendencyBeijing , t . ninetyDaySaleNumBeijing , t . oneYearSaleNumBeijing from ( SELECT sale . msku , sale . source_id , sum ( case when purchase_date_day = cast ( DATE_FORMAT ( DATE_ADD ( CURDATE ( ) , INTERVAL ? DAY ) , ? ) as UNSIGNED ) then quantity_ordered else ? END ) ? , sum ( c
007- 真实SQL：INSERT INTO t_amazon_sales_aggregate ( msku, source_id, customer_id, three_day_ago_sale_num, yesterday_before_sale_num, today_sale_num, yesterday_sale_num, three_day_sale_num, six_day_sale_num, seven_day_sale_num, fourteen_day_sale_num, seven_tendency, thirty_day_sale_num, sixty_day_sale_num, thirty_tendency, ninety_day_sale_num, one_year_sale_num, create_time, three_day_ago_sale_num_beijing, yesterday_before_sale_num_beijing, today_sale_num_beijing, yesterday_sale_num_beijing, three_day_sale_num_beijing, six_day_sale_num_beijing, seven_day_sale_num_beijing, fourteen_day_sale_num_beijing, seven_tendency_beijing, thirty_day_sale_num_beijing, sixty_day_sale_num_beijing, thirty_tendency_beijing, ninety_day_sale_num_beijing, one_year_sale_num_beijing ) select t.msku, t.source_id, 1, t.threeDayAgoSaleNum, t.yesterdayBeforeSaleNum, t.todaySaleNum, t.yesterdaySaleNum, t.threeDaySaleNum, t.sixDaySaleNum, t.sevenDaySaleNum, t.fourteenDaySaleNum, (t.sevenDaySaleNum * 2 - t.fourteenDaySaleNum) sevenTendency, t.thirtyDaySaleNum, t.sixtyDaySaleNum, (t.thirtyDaySaleNum * 2 - t.sixtyDaySaleNum ) thirtyTendency, t.ninetyDaySaleNum, t.oneYearSaleNum, now(), t.threeDayAgoSaleNumBeijing, t.yesterdayBeforeSaleNumBeijing, t.todaySaleNumBeijing, t.yesterdaySaleNumBeijing, t.threeDaySaleNumBeijing, t.sixDaySaleNumBeijing, t.sevenDaySaleNumBeijing, t.fourteenDaySaleNumBeijing, (t.sevenDaySaleNumBeijing * 2 - t.fourteenDaySaleNumBeijing) sevenTendencyBeijing, t.thirtyDaySaleNumBeijing, t.sixtyDaySaleNumBeijing, (t.thirtyDaySaleNumBeijing * 2 - t.sixtyDaySaleNumBeijing ) thirtyTendencyBeijing, t.ninetyDaySaleNumBeijing, t.oneYearSaleNumBeijing from ( SELECT sale.msku, sale.source_id, sum( case when purchase_date_day = cast(DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL -3 DAY), %Y%m%d) as UNSIGNED) then quantity_ordered else 0 END) threeDayAgoSaleNum, sum( case when purchase_date_day = cast(DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL -2 DAY), %Y%m%d) as UNSIGNED) then quantity_ordered else 0 END) yesterdayBeforeSaleNum, sum( CASE WHEN purchase_date_day = cast( DATE_FORMAT( CURDATE(), %Y%m%d ) AS UNSIGNED ) THEN quantity_ordered ELSE 0 END ) todaySaleNum, sum( CASE WHEN purchase_date_day = cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 1 DAY ), %Y%m%d ) AS UNSIGNED ) THENquantity_ordered ELSE 0 END ) yesterdaySaleNum, sum( CASE WHEN ( purchase_date_day >= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 2 DAY ), %Y%m%d ) AS UNSIGNED ) AND purchase_date_day <= cast( DATE_FORMAT( now(), %Y%m%d ) AS UNSIGNED )) THEN quantity_ordered ELSE 0 END ) threeDaySaleNum, sum( CASE WHEN ( purchase_date_day >= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 5 - 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 ) sixDaySaleNum, 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 ) sevenDaySaleNum, 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 ) fourteenDaySaleNum, sum( CASE WHEN ( purchase_date_day >= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 29 - 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 ) thirtyDaySaleNum, 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 ) sixtyDaySaleNum, 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 ) ninetyDaySaleNum, sum( CASE WHEN ( purchase_date_day >= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 364 - 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 ) oneYearSaleNum, sum( CASE WHEN ( purchase_date_beijing >= DATE_FORMAT( CURDATE(), %Y-%m-%d 00:00:00 ) ) THEN quantity_ordered ELSE 0 END ) todaySaleNumBeijing, sum( CASE WHEN ( purchase_date_beijing >= DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 1 DAY ), %Y-%m-%d 00:00:00 ) and purchase_date_beijing < DATE_FORMAT(CURDATE(), %Y-%m-%d 00:00:00 ) ) THEN quantity_ordered ELSE 0 END ) yesterdaySaleNumBeijing, sum( case when ( purchase_date_beijing >= DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 2 DAY), %Y-%m-%d 00:00:00) and purchase_date_beijing < DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL -1 DAY), %Y-%m-%d 00:00:00) ) then quantity_ordered else 0 END) yesterdayBeforeSaleNumBeijing, sum( case when ( purchase_date_beijing >= DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 3 DAY), %Y-%m-%d 00:00:00) and purchase_date_beijing < DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL -2 DAY), %Y-%m-%d 00:00:00) ) then quantity_ordered else 0 END) threeDayAgoSaleNumBeijing, sum( CASE WHEN ( purchase_date_beijing >= DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 2 DAY ), %Y-%m-%d 00:00:00 )) THEN quantity_ordered ELSE 0 END ) threeDaySaleNumBeijing, sum( CASE WHEN ( purchase_date_beijing >= DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 5 - 1 DAY ), %Y-%m-%d 00:00:00 ) and purchase_date_beijing < DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 0 DAY ), %Y-%m-%d 00:00:00 ) ) THEN quantity_ordered ELSE 0 END ) sixDaySaleNumBeijing, sum( CASE WHEN ( purchase_date_beijing >= DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 6 - 1 DAY ), %Y-%m-%d 00:00:00 ) and purchase_date_beijing < DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 0 DAY ), %Y-%m-%d 00:00:00 ) ) THEN quantity_ordered ELSE 0 END ) sevenDaySaleNumBeijing, sum( CASE WHEN ( purchase_date_beijing >= DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 13 -1 DAY ), %Y-%m-%d 00:00:00 ) and purchase_date_beijing < DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 0 DAY ), %Y-%m-%d 00:00:00 ) ) THEN quantity_ordered ELSE 0 END ) fourteenDaySaleNumBeijing, sum( CASE WHEN ( purchase_date_beijing >= DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 29 -1 DAY ), %Y-%m-%d 00:00:00 ) and purchase_date_beijing < DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 0 DAY ), %Y-%m-%d 00:00:00 ) ) THEN quantity_ordered ELSE 0 END ) thirtyDaySaleNumBeijing, sum( CASE WHEN ( purchase_date_beijing >= DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 59 -1 DAY ), %Y-%m-%d 00:00:00 ) and purchase_date_beijing < DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 0 DAY ), %Y-%m-%d 00:00:00 ) ) THEN quantity_ordered ELSE 0 END ) sixtyDaySaleNumBeijing, sum( CASE WHEN ( purchase_date_beijing >= DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 89 -1 DAY ), %Y-%m-%d 00:00:00 ) and purchase_date_beijing < DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 0 DAY ), %Y-%m-%d 00:00:00 ) ) THEN quantity_ordered ELSE 0 END ) ninetyDaySaleNumBeijing, sum( CASE WHEN ( purchase_date_beijing >= DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 364 -1 DAY ), %Y-%m-%d 00:00:00 ) and purchase_date_beijing < DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 0 DAY ), %Y-%m-%d 00:00:00 ) ) THEN quantity_ordered ELSE 0 END ) oneYearSaleNumBeijing FROM t_amazon_sales_4 sale inner join ( select 21947 as source_id, 001NR173T5H42QPLRKTD as msku union select 16875 as source_id, 009JY8YSJJMY5033FVPB55 as msku union select 14427 as source_id, 00D21229491J as msku union select 8131 as source_id, 010415546VXBT0V as msku union select 16171 as source_id, 0112251WXUQBF0I as msku union select 15411 as source_id, 01H7J315HO6067K8JW9 as msku union select 16283 as source_id, 0235B311P21OUHVB00N1 as msku union select 9523 as source_id, 02...;
008- 袁慧斌,人工修改过
009- AISQL修改建议：SQL 语句 过大，建议分批次 组合成SQL
010- explain表：
011- 额外建议：
012- 索引建议：
013-

001- 反馈人 袁慧斌<br />
001+ 反馈人 袁慧斌<br /> 数据库：zhcxkj_center_order<br /> 查询时间：22.3793<br /> 扫描行数：2576.22<br /> 发送行数：0<br /> 指纹SQL: INSERT INTO t_amazon_sales_aggregate ( msku , source_id , customer_id , three_day_ago_sale_num , yesterday_before_sale_num , today_sale_num , yesterday_sale_num , three_day_sale_num , six_day_sale_num , seven_day_sale_num , fourteen_day_sale_num , seven_tendency , thirty_day_sale_num , sixty_day_sale_num , thirty_tendency , ninety_day_sale_num , one_year_sale_num , create_time , three_day_ago_sale_num_beijing , yesterday_before_sale_num_beijing , today_sale_num_beijing , yesterday_sale_num_beijing , three_day_sale_num_beijing , six_day_sale_num_beijing , seven_day_sale_num_beijing , fourteen_day_sale_num_beijing , seven_tendency_beijing , thirty_day_sale_num_beijing , sixty_day_sale_num_beijing , thirty_tendency_beijing , ninety_day_sale_num_beijing , one_year_sale_num_beijing ) select t . msku , t . source_id , ? , t . threeDayAgoSaleNum , t . yesterdayBeforeSaleNum , t . todaySaleNum , t . yesterdaySaleNum , t . threeDaySaleNum , t . sixDaySaleNum , t . sevenDaySaleNum , t . fourteenDaySaleNum , ( t . sevenDaySaleNum * ? - t . fourteenDaySaleNum ) sevenTendency , t . thirtyDaySaleNum , t . sixtyDaySaleNum , ( t . thirtyDaySaleNum * ? - t . sixtyDaySaleNum ) thirtyTendency , t . ninetyDaySaleNum , t . oneYearSaleNum , now ( ) , t . threeDayAgoSaleNumBeijing , t . yesterdayBeforeSaleNumBeijing , t . todaySaleNumBeijing , t . yesterdaySaleNumBeijing , t . threeDaySaleNumBeijing , t . sixDaySaleNumBeijing , t . sevenDaySaleNumBeijing , t . fourteenDaySaleNumBeijing , ( t . sevenDaySaleNumBeijing * ? - t . fourteenDaySaleNumBeijing ) sevenTendencyBeijing , t . thirtyDaySaleNumBeijing , t . sixtyDaySaleNumBeijing , ( t . thirtyDaySaleNumBeijing * ? - t . sixtyDaySaleNumBeijing ) thirtyTendencyBeijing , t . ninetyDaySaleNumBeijing , t . oneYearSaleNumBeijing from ( SELECT sale . msku , sale . source_id , sum ( case when purchase_date_day = cast ( DATE_FORMAT ( DATE_ADD ( CURDATE ( ) , INTERVAL ? DAY ) , ? ) as UNSIGNED ) then quantity_ordered else ? END ) ? , sum ( c<br /> 真实SQL：INSERT INTO t_amazon_sales_aggregate ( msku, source_id, customer_id, three_day_ago_sale_num, yesterday_before_sale_num, today_sale_num, yesterday_sale_num, three_day_sale_num, six_day_sale_num, seven_day_sale_num, fourteen_day_sale_num, seven_tendency, thirty_day_sale_num, sixty_day_sale_num, thirty_tendency, ninety_day_sale_num, one_year_sale_num, create_time, three_day_ago_sale_num_beijing, yesterday_before_sale_num_beijing, today_sale_num_beijing, yesterday_sale_num_beijing, three_day_sale_num_beijing, six_day_sale_num_beijing, seven_day_sale_num_beijing, fourteen_day_sale_num_beijing, seven_tendency_beijing, thirty_day_sale_num_beijing, sixty_day_sale_num_beijing, thirty_tendency_beijing, ninety_day_sale_num_beijing, one_year_sale_num_beijing ) select t.msku, t.source_id, 1, t.threeDayAgoSaleNum, t.yesterdayBeforeSaleNum, t.todaySaleNum, t.yesterdaySaleNum, t.threeDaySaleNum, t.sixDaySaleNum, t.sevenDaySaleNum, t.fourteenDaySaleNum, (t.sevenDaySaleNum * 2 - t.fourteenDaySaleNum) sevenTendency, t.thirtyDaySaleNum, t.sixtyDaySaleNum, (t.thirtyDaySaleNum * 2 - t.sixtyDaySaleNum ) thirtyTendency, t.ninetyDaySaleNum, t.oneYearSaleNum, now(), t.threeDayAgoSaleNumBeijing, t.yesterdayBeforeSaleNumBeijing, t.todaySaleNumBeijing, t.yesterdaySaleNumBeijing, t.threeDaySaleNumBeijing, t.sixDaySaleNumBeijing, t.sevenDaySaleNumBeijing, t.fourteenDaySaleNumBeijing, (t.sevenDaySaleNumBeijing * 2 - t.fourteenDaySaleNumBeijing) sevenTendencyBeijing, t.thirtyDaySaleNumBeijing, t.sixtyDaySaleNumBeijing, (t.thirtyDaySaleNumBeijing * 2 - t.sixtyDaySaleNumBeijing ) thirtyTendencyBeijing, t.ninetyDaySaleNumBeijing, t.oneYearSaleNumBeijing from ( SELECT sale.msku, sale.source_id, sum( case when purchase_date_day = cast(DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL -3 DAY), %Y%m%d) as UNSIGNED) then quantity_ordered else 0 END) threeDayAgoSaleNum, sum( case when purchase_date_day = cast(DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL -2 DAY), %Y%m%d) as UNSIGNED) then quantity_ordered else 0 END) yesterdayBeforeSaleNum, sum( CASE WHEN purchase_date_day = cast( DATE_FORMAT( CURDATE(), %Y%m%d ) AS UNSIGNED ) THEN quantity_ordered ELSE 0 END ) todaySaleNum, sum( CASE WHEN purchase_date_day = cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 1 DAY ), %Y%m%d ) AS UNSIGNED ) THENquantity_ordered ELSE 0 END ) yesterdaySaleNum, sum( CASE WHEN ( purchase_date_day &gt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 2 DAY ), %Y%m%d ) AS UNSIGNED ) AND purchase_date_day &lt;= cast( DATE_FORMAT( now(), %Y%m%d ) AS UNSIGNED )) THEN quantity_ordered ELSE 0 END ) threeDaySaleNum, sum( CASE WHEN ( purchase_date_day &gt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 5 - 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 ) sixDaySaleNum, 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 ) sevenDaySaleNum, 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 ) fourteenDaySaleNum, sum( CASE WHEN ( purchase_date_day &gt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 29 - 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 ) thirtyDaySaleNum, 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 ) sixtyDaySaleNum, 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 ) ninetyDaySaleNum, sum( CASE WHEN ( purchase_date_day &gt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 364 - 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 ) oneYearSaleNum, sum( CASE WHEN ( purchase_date_beijing &gt;= DATE_FORMAT( CURDATE(), %Y-%m-%d 00:00:00 ) ) THEN quantity_ordered ELSE 0 END ) todaySaleNumBeijing, sum( CASE WHEN ( purchase_date_beijing &gt;= DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 1 DAY ), %Y-%m-%d 00:00:00 ) and purchase_date_beijing &lt; DATE_FORMAT(CURDATE(), %Y-%m-%d 00:00:00 ) ) THEN quantity_ordered ELSE 0 END ) yesterdaySaleNumBeijing, sum( case when ( purchase_date_beijing &gt;= DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 2 DAY), %Y-%m-%d 00:00:00) and purchase_date_beijing &lt; DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL -1 DAY), %Y-%m-%d 00:00:00) ) then quantity_ordered else 0 END) yesterdayBeforeSaleNumBeijing, sum( case when ( purchase_date_beijing &gt;= DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 3 DAY), %Y-%m-%d 00:00:00) and purchase_date_beijing &lt; DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL -2 DAY), %Y-%m-%d 00:00:00) ) then quantity_ordered else 0 END) threeDayAgoSaleNumBeijing, sum( CASE WHEN ( purchase_date_beijing &gt;= DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 2 DAY ), %Y-%m-%d 00:00:00 )) THEN quantity_ordered ELSE 0 END ) threeDaySaleNumBeijing, sum( CASE WHEN ( purchase_date_beijing &gt;= DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 5 - 1 DAY ), %Y-%m-%d 00:00:00 ) and purchase_date_beijing &lt; DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 0 DAY ), %Y-%m-%d 00:00:00 ) ) THEN quantity_ordered ELSE 0 END ) sixDaySaleNumBeijing, sum( CASE WHEN ( purchase_date_beijing &gt;= DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 6 - 1 DAY ), %Y-%m-%d 00:00:00 ) and purchase_date_beijing &lt; DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 0 DAY ), %Y-%m-%d 00:00:00 ) ) THEN quantity_ordered ELSE 0 END ) sevenDaySaleNumBeijing, sum( CASE WHEN ( purchase_date_beijing &gt;= DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 13 -1 DAY ), %Y-%m-%d 00:00:00 ) and purchase_date_beijing &lt; DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 0 DAY ), %Y-%m-%d 00:00:00 ) ) THEN quantity_ordered ELSE 0 END ) fourteenDaySaleNumBeijing, sum( CASE WHEN ( purchase_date_beijing &gt;= DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 29 -1 DAY ), %Y-%m-%d 00:00:00 ) and purchase_date_beijing &lt; DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 0 DAY ), %Y-%m-%d 00:00:00 ) ) THEN quantity_ordered ELSE 0 END ) thirtyDaySaleNumBeijing, sum( CASE WHEN ( purchase_date_beijing &gt;= DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 59 -1 DAY ), %Y-%m-%d 00:00:00 ) and purchase_date_beijing &lt; DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 0 DAY ), %Y-%m-%d 00:00:00 ) ) THEN quantity_ordered ELSE 0 END ) sixtyDaySaleNumBeijing, sum( CASE WHEN ( purchase_date_beijing &gt;= DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 89 -1 DAY ), %Y-%m-%d 00:00:00 ) and purchase_date_beijing &lt; DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 0 DAY ), %Y-%m-%d 00:00:00 ) ) THEN quantity_ordered ELSE 0 END ) ninetyDaySaleNumBeijing, sum( CASE WHEN ( purchase_date_beijing &gt;= DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 364 -1 DAY ), %Y-%m-%d 00:00:00 ) and purchase_date_beijing &lt; DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 0 DAY ), %Y-%m-%d 00:00:00 ) ) THEN quantity_ordered ELSE 0 END ) oneYearSaleNumBeijing FROM t_amazon_sales_4 sale inner join ( select 21947 as source_id, 001NR173T5H42QPLRKTD as msku union select 16875 as source_id, 009JY8YSJJMY5033FVPB55 as msku union select 14427 as source_id, 00D21229491J as msku union select 8131 as source_id, 010415546VXBT0V as msku union select 16171 as source_id, 0112251WXUQBF0I as msku union select 15411 as source_id, 01H7J315HO6067K8JW9 as msku union select 16283 as source_id, 0235B311P21OUHVB00N1 as msku union select 9523 as source_id, 02...;<br /> 袁慧斌,人工修改过<br /> AISQL修改建议：SQL 语句 过大，建议分批次 组合成SQL <br /> explain表：<br /> 额外建议：<br /> 索引建议：<br /><br />
002- 数据库：zhcxkj_center_order<br />
003- 查询时间：22.3793<br />
004- 扫描行数：2576.22<br />
005- 发送行数：0<br />
006- 指纹SQL: INSERT INTO t_amazon_sales_aggregate ( msku , source_id , customer_id , three_day_ago_sale_num , yesterday_before_sale_num , today_sale_num , yesterday_sale_num , three_day_sale_num , six_day_sale_num , seven_day_sale_num , fourteen_day_sale_num , seven_tendency , thirty_day_sale_num , sixty_day_sale_num , thirty_tendency , ninety_day_sale_num , one_year_sale_num , create_time , three_day_ago_sale_num_beijing , yesterday_before_sale_num_beijing , today_sale_num_beijing , yesterday_sale_num_beijing , three_day_sale_num_beijing , six_day_sale_num_beijing , seven_day_sale_num_beijing , fourteen_day_sale_num_beijing , seven_tendency_beijing , thirty_day_sale_num_beijing , sixty_day_sale_num_beijing , thirty_tendency_beijing , ninety_day_sale_num_beijing , one_year_sale_num_beijing ) select t . msku , t . source_id , ? , t . threeDayAgoSaleNum , t . yesterdayBeforeSaleNum , t . todaySaleNum , t . yesterdaySaleNum , t . threeDaySaleNum , t . sixDaySaleNum , t . sevenDaySaleNum , t . fourteenDaySaleNum , ( t . sevenDaySaleNum * ? - t . fourteenDaySaleNum ) sevenTendency , t . thirtyDaySaleNum , t . sixtyDaySaleNum , ( t . thirtyDaySaleNum * ? - t . sixtyDaySaleNum ) thirtyTendency , t . ninetyDaySaleNum , t . oneYearSaleNum , now ( ) , t . threeDayAgoSaleNumBeijing , t . yesterdayBeforeSaleNumBeijing , t . todaySaleNumBeijing , t . yesterdaySaleNumBeijing , t . threeDaySaleNumBeijing , t . sixDaySaleNumBeijing , t . sevenDaySaleNumBeijing , t . fourteenDaySaleNumBeijing , ( t . sevenDaySaleNumBeijing * ? - t . fourteenDaySaleNumBeijing ) sevenTendencyBeijing , t . thirtyDaySaleNumBeijing , t . sixtyDaySaleNumBeijing , ( t . thirtyDaySaleNumBeijing * ? - t . sixtyDaySaleNumBeijing ) thirtyTendencyBeijing , t . ninetyDaySaleNumBeijing , t . oneYearSaleNumBeijing from ( SELECT sale . msku , sale . source_id , sum ( case when purchase_date_day = cast ( DATE_FORMAT ( DATE_ADD ( CURDATE ( ) , INTERVAL ? DAY ) , ? ) as UNSIGNED ) then quantity_ordered else ? END ) ? , sum ( c<br />
007- 真实SQL：INSERT INTO t_amazon_sales_aggregate ( msku, source_id, customer_id, three_day_ago_sale_num, yesterday_before_sale_num, today_sale_num, yesterday_sale_num, three_day_sale_num, six_day_sale_num, seven_day_sale_num, fourteen_day_sale_num, seven_tendency, thirty_day_sale_num, sixty_day_sale_num, thirty_tendency, ninety_day_sale_num, one_year_sale_num, create_time, three_day_ago_sale_num_beijing, yesterday_before_sale_num_beijing, today_sale_num_beijing, yesterday_sale_num_beijing, three_day_sale_num_beijing, six_day_sale_num_beijing, seven_day_sale_num_beijing, fourteen_day_sale_num_beijing, seven_tendency_beijing, thirty_day_sale_num_beijing, sixty_day_sale_num_beijing, thirty_tendency_beijing, ninety_day_sale_num_beijing, one_year_sale_num_beijing ) select t.msku, t.source_id, 1, t.threeDayAgoSaleNum, t.yesterdayBeforeSaleNum, t.todaySaleNum, t.yesterdaySaleNum, t.threeDaySaleNum, t.sixDaySaleNum, t.sevenDaySaleNum, t.fourteenDaySaleNum, (t.sevenDaySaleNum * 2 - t.fourteenDaySaleNum) sevenTendency, t.thirtyDaySaleNum, t.sixtyDaySaleNum, (t.thirtyDaySaleNum * 2 - t.sixtyDaySaleNum ) thirtyTendency, t.ninetyDaySaleNum, t.oneYearSaleNum, now(), t.threeDayAgoSaleNumBeijing, t.yesterdayBeforeSaleNumBeijing, t.todaySaleNumBeijing, t.yesterdaySaleNumBeijing, t.threeDaySaleNumBeijing, t.sixDaySaleNumBeijing, t.sevenDaySaleNumBeijing, t.fourteenDaySaleNumBeijing, (t.sevenDaySaleNumBeijing * 2 - t.fourteenDaySaleNumBeijing) sevenTendencyBeijing, t.thirtyDaySaleNumBeijing, t.sixtyDaySaleNumBeijing, (t.thirtyDaySaleNumBeijing * 2 - t.sixtyDaySaleNumBeijing ) thirtyTendencyBeijing, t.ninetyDaySaleNumBeijing, t.oneYearSaleNumBeijing from ( SELECT sale.msku, sale.source_id, sum( case when purchase_date_day = cast(DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL -3 DAY), %Y%m%d) as UNSIGNED) then quantity_ordered else 0 END) threeDayAgoSaleNum, sum( case when purchase_date_day = cast(DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL -2 DAY), %Y%m%d) as UNSIGNED) then quantity_ordered else 0 END) yesterdayBeforeSaleNum, sum( CASE WHEN purchase_date_day = cast( DATE_FORMAT( CURDATE(), %Y%m%d ) AS UNSIGNED ) THEN quantity_ordered ELSE 0 END ) todaySaleNum, sum( CASE WHEN purchase_date_day = cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 1 DAY ), %Y%m%d ) AS UNSIGNED ) THENquantity_ordered ELSE 0 END ) yesterdaySaleNum, sum( CASE WHEN ( purchase_date_day &gt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 2 DAY ), %Y%m%d ) AS UNSIGNED ) AND purchase_date_day &lt;= cast( DATE_FORMAT( now(), %Y%m%d ) AS UNSIGNED )) THEN quantity_ordered ELSE 0 END ) threeDaySaleNum, sum( CASE WHEN ( purchase_date_day &gt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 5 - 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 ) sixDaySaleNum, 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 ) sevenDaySaleNum, 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 ) fourteenDaySaleNum, sum( CASE WHEN ( purchase_date_day &gt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 29 - 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 ) thirtyDaySaleNum, 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 ) sixtyDaySaleNum, 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 ) ninetyDaySaleNum, sum( CASE WHEN ( purchase_date_day &gt;= cast( DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 364 - 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 ) oneYearSaleNum, sum( CASE WHEN ( purchase_date_beijing &gt;= DATE_FORMAT( CURDATE(), %Y-%m-%d 00:00:00 ) ) THEN quantity_ordered ELSE 0 END ) todaySaleNumBeijing, sum( CASE WHEN ( purchase_date_beijing &gt;= DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 1 DAY ), %Y-%m-%d 00:00:00 ) and purchase_date_beijing &lt; DATE_FORMAT(CURDATE(), %Y-%m-%d 00:00:00 ) ) THEN quantity_ordered ELSE 0 END ) yesterdaySaleNumBeijing, sum( case when ( purchase_date_beijing &gt;= DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 2 DAY), %Y-%m-%d 00:00:00) and purchase_date_beijing &lt; DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL -1 DAY), %Y-%m-%d 00:00:00) ) then quantity_ordered else 0 END) yesterdayBeforeSaleNumBeijing, sum( case when ( purchase_date_beijing &gt;= DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 3 DAY), %Y-%m-%d 00:00:00) and purchase_date_beijing &lt; DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL -2 DAY), %Y-%m-%d 00:00:00) ) then quantity_ordered else 0 END) threeDayAgoSaleNumBeijing, sum( CASE WHEN ( purchase_date_beijing &gt;= DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 2 DAY ), %Y-%m-%d 00:00:00 )) THEN quantity_ordered ELSE 0 END ) threeDaySaleNumBeijing, sum( CASE WHEN ( purchase_date_beijing &gt;= DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 5 - 1 DAY ), %Y-%m-%d 00:00:00 ) and purchase_date_beijing &lt; DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 0 DAY ), %Y-%m-%d 00:00:00 ) ) THEN quantity_ordered ELSE 0 END ) sixDaySaleNumBeijing, sum( CASE WHEN ( purchase_date_beijing &gt;= DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 6 - 1 DAY ), %Y-%m-%d 00:00:00 ) and purchase_date_beijing &lt; DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 0 DAY ), %Y-%m-%d 00:00:00 ) ) THEN quantity_ordered ELSE 0 END ) sevenDaySaleNumBeijing, sum( CASE WHEN ( purchase_date_beijing &gt;= DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 13 -1 DAY ), %Y-%m-%d 00:00:00 ) and purchase_date_beijing &lt; DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 0 DAY ), %Y-%m-%d 00:00:00 ) ) THEN quantity_ordered ELSE 0 END ) fourteenDaySaleNumBeijing, sum( CASE WHEN ( purchase_date_beijing &gt;= DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 29 -1 DAY ), %Y-%m-%d 00:00:00 ) and purchase_date_beijing &lt; DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 0 DAY ), %Y-%m-%d 00:00:00 ) ) THEN quantity_ordered ELSE 0 END ) thirtyDaySaleNumBeijing, sum( CASE WHEN ( purchase_date_beijing &gt;= DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 59 -1 DAY ), %Y-%m-%d 00:00:00 ) and purchase_date_beijing &lt; DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 0 DAY ), %Y-%m-%d 00:00:00 ) ) THEN quantity_ordered ELSE 0 END ) sixtyDaySaleNumBeijing, sum( CASE WHEN ( purchase_date_beijing &gt;= DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 89 -1 DAY ), %Y-%m-%d 00:00:00 ) and purchase_date_beijing &lt; DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 0 DAY ), %Y-%m-%d 00:00:00 ) ) THEN quantity_ordered ELSE 0 END ) ninetyDaySaleNumBeijing, sum( CASE WHEN ( purchase_date_beijing &gt;= DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 364 -1 DAY ), %Y-%m-%d 00:00:00 ) and purchase_date_beijing &lt; DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL - 0 DAY ), %Y-%m-%d 00:00:00 ) ) THEN quantity_ordered ELSE 0 END ) oneYearSaleNumBeijing FROM t_amazon_sales_4 sale inner join ( select 21947 as source_id, 001NR173T5H42QPLRKTD as msku union select 16875 as source_id, 009JY8YSJJMY5033FVPB55 as msku union select 14427 as source_id, 00D21229491J as msku union select 8131 as source_id, 010415546VXBT0V as msku union select 16171 as source_id, 0112251WXUQBF0I as msku union select 15411 as source_id, 01H7J315HO6067K8JW9 as msku union select 16283 as source_id, 0235B311P21OUHVB00N1 as msku union select 9523 as source_id, 02...;<br />
008- 袁慧斌,人工修改过<br />
009- AISQL修改建议：SQL 语句 过大，建议分批次 组合成SQL <br />
010- explain表：<br />
011- 额外建议：<br />
012- 索引建议：<br />
013- <br />