![]()
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;
浙公网安备 33010602011771号