首页 > 代码库 > w-BIG TABLE-view+where-small table

w-BIG TABLE-view+where-small table

w-BIG TABLE

 1 DROP PROCEDURE IF EXISTS truncate_insert_mine_category_trend_asinrank_month;
 2 DELIMITER /w/
 3 CREATE PROCEDURE truncate_insert_mine_category_trend_asinrank_month ()
 4 BEGIN
 5 TRUNCATE mine_category_trend_asinrank_month ;
 6 INSERT INTO mine_category_trend_asinrank_month (
 7     fk_countrycode,
 8     fk_categoryid,
 9     history_year,
10     history_month
11 ) SELECT
12     CountryCode,
13     CategoryId,
14     YearValue,
15     MonthValue
16 FROM
17     mt_month
18 GROUP BY
19     CountryCode,
20     CategoryId,
21     YearValue,
22     MonthValue ;
23 END/w/
24 DELIMITER;
25 CALL truncate_insert_mine_category_trend_asinrank_month;
26 
27 DROP PROCEDURE IF EXISTS insert_update_mine_category_trend_asinrank_month;
28 DELIMITER /w/
29 CREATE PROCEDURE insert_update_mine_category_trend_asinrank_month ()
30 BEGIN
31 SET @wtab =  mine_category_trend_asinrank_month  ;
32 SET @wtab_src = mt_month;
33 SET @w = 10;
34 SET @wyear_max=(SELECT MAX(YearValue) FROM mt_month)+1;
35 SET @wyear = (SELECT MIN(YearValue) FROM mt_month);
36 
37 WHILE @w < 101 DO
38     WHILE @wyear < @wyear_max DO
39         SET @wmonth=(SELECT MIN(MonthValue) FROM mt_month WHERE YearValue=@wyear);
40         SET @wmonth_max=(SELECT MAX(MonthValue) FROM mt_month WHERE YearValue=@wyear)+1;
41         WHILE @wmonth < @wmonth_max DO
42 
43                 DROP VIEW IF EXISTS    wview;
44                 SET @wview_where = CONCAT( WHERE YearValue=http://www.mamicode.com/,@wyear, AND MonthValue=http://www.mamicode.com/,@wmonth, AND TopX=,@w);
45                 SET @wview =  CONCAT(CREATE VIEW wview AS SELECT * FROM ,@wtab_src,@wview_where,;);
46                 PREPARE  stmt0 FROM @wview ;
47                 EXECUTE  stmt0  ;
48                 DROP PREPARE stmt0;
49 
50                 DROP VIEW IF EXISTS    wview1;
51                 SET @wview1_where =  CONCAT( WHERE history_year=,@wyear, AND history_month=,@wmonth,;);
52                 SET @wview1 =  CONCAT(CREATE VIEW wview1 AS SELECT * FROM ,@wtab,@wview1_where,;);
53                 PREPARE  stmt2 FROM @wview1 ;
54                 EXECUTE  stmt2  ;
55                 DROP PREPARE stmt2;
56 
57                 SET @wfield = CONCAT(unit,@w);
58                 SET @wnewvalue = CONCAT((SELECT SumUnit FROM wview da  WHERE  wview1.fk_countrycode = da.CountryCode  AND wview1.fk_categoryid = da.CategoryId AND wview1.history_year = da.YearValue AND wview1.history_month = da.MonthValue  AND da.TopX=,@w,  ));
59                 SET @wfieldb = CONCAT(gms,@w);
60                 SET @wnewvalueb = CONCAT((SELECT SumGms FROM wview da  WHERE  wview1.fk_countrycode = da.CountryCode  AND wview1.fk_categoryid = da.CategoryId AND wview1.history_year = da.YearValue AND wview1.history_month = da.MonthValue  AND da.TopX=,@w,  ));
61                                 
62                 SET @wpre = CONCAT(UPDATE , wview1 , SET ,@wfield,=,@wnewvalue,,,@wfieldb,=,@wnewvalueb);
63                 PREPARE  stmt1 FROM @wpre ;
64                 EXECUTE  stmt1  ;
65                 DROP PREPARE stmt1;
66 
67         SET @wmonth=@wmonth+1;
68         END WHILE ;
69     SET @wyear=@wyear+1;
70     END WHILE ;
71 SET @wyear = (SELECT MIN(YearValue) FROM mt_month);
72 SET @w=@w+10;
73 END WHILE ;
74 END/w/
75 DELIMITER ;
76 CALL insert_update_mine_category_trend_asinrank_month;

 

w-BIG TABLE-view+where-small table