首页 > 代码库 > 12.162s 1805.867s

12.162s 1805.867s

 

 

 

 

技术分享

 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;

 

12.162s 1805.867s