1 [SQL]DROP PROCEDURE IF EXISTS truncate_insert_sales_rank_toparow_week; 2 受影响的行: 0 3 时间: 0.001s 4 5 [SQL] 6 7 CREATE PROCEDURE truncate_insert_sales_rank_toparow_week () 8 BEGIN 9 TRUNCATE sales_rank_toparow_week ; 10 INSERT INTO sales_rank_toparow_week ( 11 fk_countrycode, 12 fk_categoryid, 13 history_year, 14 history_week 15 ) SELECT 16 country, 17 categoryid, 18 grab_year, 19 grab_week 20 FROM 21 grab_sales_rank_week 22 GROUP BY 23 country, 24 categoryid, 25 grab_year, 26 grab_week ; 27 END; 28 受影响的行: 0 29 时间: 0.001s 30 31 [SQL] 32 33 CALL truncate_insert_sales_rank_toparow_week; 34 受影响的行: 11281 35 时间: 0.299s 36 37 [SQL] 38 39 DROP PROCEDURE IF EXISTS insert_update_sales_rank_toparow_week; 40 受影响的行: 0 41 时间: 0.000s 42 43 [SQL] 44 45 CREATE PROCEDURE insert_update_sales_rank_toparow_week () 46 BEGIN 47 SET @wtab = ' sales_rank_toparow_week '; 48 SET @wtab_src = 'grab_sales_rank_week'; 49 SET @w = 10; 50 SET @wyear_max=(SELECT MAX(grab_year) FROM grab_sales_rank_week)+1; 51 SET @wyear = (SELECT MIN(grab_year) FROM grab_sales_rank_week); 52 53 WHILE @w < 101 DO 54 WHILE @wyear < @wyear_max DO 55 SET @wweek=(SELECT MIN(grab_week) FROM grab_sales_rank_week WHERE grab_year=@wyear); 56 SET @wweek_max=(SELECT MAX(grab_week) FROM grab_sales_rank_week WHERE grab_year=@wyear)+1; 57 WHILE @wweek < @wweek_max DO 58 59 DROP TEMPORARY TABLE IF EXISTS wtmp; 60 SET @wtmp_where = CONCAT(' WHERE src.grab_year=',@wyear,' AND src.grab_week=',@wweek,' AND src.topx=',@w); 61 SET @wimp = ' src.country,src.categoryid,src.grab_year,src.grab_week,src.topx,src.sum_coin,src.sum_amount,tab.autoid,tab.fk_countrycode,tab.fk_categoryid,tab.history_year,tab.history_week '; 62 SET @wjoin = CONCAT(' src LEFT JOIN ',@wtab,' tab ON tab.fk_countrycode = src.country AND tab.fk_categoryid = src.categoryid AND tab.history_year = src.grab_year AND tab.history_week = src.grab_week '); 63 SET @wtmp = CONCAT('CREATE TEMPORARY TABLE wtmp AS SELECT ',@wimp,' FROM ',@wtab_src,@wjoin,@wtmp_where,';'); 64 PREPARE stmt0 FROM @wtmp ; 65 EXECUTE stmt0 ; 66 DROP PREPARE stmt0; 67 68 SET @wfield = CONCAT('coin',@w); 69 SET @wfieldb = CONCAT('amount',@w); 70 SET @wpre = CONCAT('UPDATE ',@wtab,' wf LEFT JOIN wtmp ON wf.autoid=wtmp.autoid SET wf.',@wfield,'=wtmp.sum_coin, wf.',@wfieldb,'=wtmp.sum_amount WHERE wf.autoid=wtmp.autoid'); 71 PREPARE stmt1 FROM @wpre ; 72 EXECUTE stmt1 ; 73 DROP PREPARE stmt1; 74 75 SET @wweek=@wweek+1; 76 END WHILE ; 77 SET @wyear=@wyear+1; 78 END WHILE ; 79 SET @wyear = (SELECT MIN(grab_year) FROM grab_sales_rank_week); 80 SET @w=@w+10; 81 END WHILE ; 82 END; 83 受影响的行: 0 84 时间: 0.001s 85 86 [SQL] 87 88 CALL insert_update_sales_rank_toparow_week; 89 受影响的行: 0 90 时间: 12.162s
1 DROP PROCEDURE IF EXISTS truncate_insert_sales_rank_toparow_week; 2 DELIMITER /w/ 3 CREATE PROCEDURE truncate_insert_sales_rank_toparow_week () 4 BEGIN 5 TRUNCATE sales_rank_toparow_week ; 6 INSERT INTO sales_rank_toparow_week ( 7 fk_countrycode, 8 fk_categoryid, 9 history_year, 10 history_week 11 ) SELECT 12 country, 13 categoryid, 14 grab_year, 15 grab_week 16 FROM 17 grab_sales_rank_week 18 GROUP BY 19 country, 20 categoryid, 21 grab_year, 22 grab_week ; 23 END/w/ 24 DELIMITER; 25 CALL truncate_insert_sales_rank_toparow_week; 26 27 DROP PROCEDURE IF EXISTS insert_update_sales_rank_toparow_week; 28 DELIMITER /w/ 29 CREATE PROCEDURE insert_update_sales_rank_toparow_week () 30 BEGIN 31 SET @wtab = ' sales_rank_toparow_week '; 32 SET @wtab_src = 'grab_sales_rank_week'; 33 SET @w = 10; 34 SET @wyear_max=(SELECT MAX(grab_year) FROM grab_sales_rank_week)+1; 35 SET @wyear = (SELECT MIN(grab_year) FROM grab_sales_rank_week); 36 37 WHILE @w < 101 DO 38 WHILE @wyear < @wyear_max DO 39 SET @wweek=(SELECT MIN(grab_week) FROM grab_sales_rank_week WHERE grab_year=@wyear); 40 SET @wweek_max=(SELECT MAX(grab_week) FROM grab_sales_rank_week WHERE grab_year=@wyear)+1; 41 WHILE @wweek < @wweek_max DO 42 43 DROP VIEW IF EXISTS wview; 44 SET @wview_where = CONCAT(' WHERE grab_year=',@wyear,' AND grab_week=',@wweek,' AND topx=',@w); 45 SET @wimp = ' country,categoryid,grab_year,grab_week,topx,sum_coin,sum_amount '; 46 SET @wview = CONCAT('CREATE VIEW wview AS SELECT ',@wimp,' FROM ',@wtab_src,@wview_where,';'); 47 PREPARE stmt0 FROM @wview ; 48 EXECUTE stmt0 ; 49 DROP PREPARE stmt0; 50 51 DROP VIEW IF EXISTS wview1; 52 SET @wview1_where = CONCAT(' WHERE history_year=',@wyear,' AND history_week=',@wweek,';'); 53 54 SET @wfield = CONCAT('coin',@w); 55 SET @wfieldb = CONCAT('amount',@w); 56 57 SET @wimp = CONCAT(' fk_countrycode,fk_categoryid,history_year,history_week,',@wfield,',',@wfieldb); 58 SET @wview1 = CONCAT('CREATE VIEW wview1 AS SELECT ',@wimp,' FROM ',@wtab,@wview1_where,';'); 59 PREPARE stmt2 FROM @wview1 ; 60 EXECUTE stmt2 ; 61 DROP PREPARE stmt2; 62 63 64 SET @wnewvalue = CONCAT('(SELECT sum_coin FROM wview da WHERE wview1.fk_countrycode = da.country AND wview1.fk_categoryid = da.categoryid AND wview1.history_year = da.grab_year AND wview1.history_week = da.grab_week AND da.topx=',@w,' )'); 65 66 SET @wnewvalueb = CONCAT('(SELECT sum_amount FROM wview da WHERE wview1.fk_countrycode = da.country AND wview1.fk_categoryid = da.categoryid AND wview1.history_year = da.grab_year AND wview1.history_week = da.grab_week AND da.topx=',@w,' )'); 67 68 SET @wpre = CONCAT('UPDATE ',' wview1 ',' SET ',@wfield,'=',@wnewvalue,',',@wfieldb,'=',@wnewvalueb); 69 PREPARE stmt1 FROM @wpre ; 70 EXECUTE stmt1 ; 71 DROP PREPARE stmt1; 72 73 SET @wweek=@wweek+1; 74 END WHILE ; 75 SET @wyear=@wyear+1; 76 END WHILE ; 77 SET @wyear = (SELECT MIN(grab_year) FROM grab_sales_rank_week); 78 SET @w=@w+10; 79 END WHILE ; 80 END/w/ 81 DELIMITER ; 82 CALL insert_update_sales_rank_toparow_week;