w-BIG TABLE-view+where-small table

w-BIG TABLE

 

 1 DROP PROCEDURE IF EXISTS truncate_insert_sales_rank_toparow_month;
 2 DELIMITER /w/
 3 CREATE PROCEDURE truncate_insert_sales_rank_toparow_month ()
 4 BEGIN
 5 TRUNCATE sales_rank_toparow_month ;
 6 INSERT INTO sales_rank_toparow_month (
 7     fk_countrycode,
 8     fk_categoryid,
 9     history_year,
10     history_month
11 ) SELECT
12     country,
13     categoryid,
14     grab_year,
15     grab_month
16 FROM
17     grab_sales_rank_month
18 GROUP BY
19     country,
20     categoryid,
21     grab_year,
22     grab_month ;
23 END/w/
24 DELIMITER;
25 CALL truncate_insert_sales_rank_toparow_month;
26 
27 DROP PROCEDURE IF EXISTS insert_update_sales_rank_toparow_month;
28 DELIMITER /w/
29 CREATE PROCEDURE insert_update_sales_rank_toparow_month ()
30 BEGIN
31 SET @wtab = ' sales_rank_toparow_month  ';
32 SET @wtab_src = 'grab_sales_rank_month';
33 SET @w = 10;
34 SET @wyear_max=(SELECT MAX(grab_year) FROM grab_sales_rank_month)+1;
35 SET @wyear = (SELECT MIN(grab_year) FROM grab_sales_rank_month);
36 
37 WHILE @w < 101 DO
38     WHILE @wyear < @wyear_max DO
39         SET @wmonth=(SELECT MIN(grab_month) FROM grab_sales_rank_month WHERE grab_year=@wyear);
40         SET @wmonth_max=(SELECT MAX(grab_month) FROM grab_sales_rank_month WHERE grab_year=@wyear)+1;
41         WHILE @wmonth < @wmonth_max DO
42 
43                 DROP VIEW IF EXISTS    wview;
44                 SET @wview_where = CONCAT(' WHERE grab_year=',@wyear,' AND grab_month=',@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('coin',@w);
58                 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_month = da.grab_month  AND da.topx=',@w,'  )');
59                 SET @wfieldb = CONCAT('amount',@w);
60                 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_month = da.grab_month  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(grab_year) FROM grab_sales_rank_month);
72 SET @w=@w+10;
73 END WHILE ;
74 END/w/
75 DELIMITER ;
76 CALL insert_update_sales_rank_toparow_month;

 

CREATE INDEX w1 ON grab_sales_rank_month (country);
CREATE INDEX w11 ON grab_sales_rank_month (categoryid);
CREATE INDEX w12 ON grab_sales_rank_month (grab_year);
CREATE INDEX w13 ON grab_sales_rank_month (topx);

USING BTREE  --->BETTER--->USING HASH

posted @ 2016-12-29 15:59  papering  阅读(216)  评论(0)  编辑  收藏  举报