Q0j451xP

1/*
2******************************************
3* rollup / summary tables example by f00 *
4******************************************
5*/
6
7-- sales table
8
9drop table if exists sales;
10
11create table sales
12(
13sale_id int unsigned not null auto_increment primary key,
14emp_id smallint unsigned not null,
15sale_date datetime not null,
16sale_amount decimal(10,2) not null default 0,
17key sale_date_emp_idx (sale_date, emp_id)
18)engine=innodb;
19
20
21-- some sales data
22
23insert into sales (emp_id, sale_date, sale_amount) values
24(1,now() - interval 3 month, 300.00),
25(1,now() - interval 3 month, 300.00),
26(2,now() - interval 3 month, 400.00),
27(2,now() - interval 3 month, 400.00),
28(3,now() - interval 3 month, 500.00),
29(3,now() - interval 3 month, 500.00),
30(4,now() - interval 3 month, 600.00),
31(4,now() - interval 3 month, 600.00),
32
33(1,now() - interval 2 month, 200.00),
34(1,now() - interval 2 month, 200.00),
35(2,now() - interval 2 month, 300.00),
36(2,now() - interval 2 month, 300.00),
37(3,now() - interval 2 month, 400.00),
38(3,now() - interval 2 month, 400.00),
39(4,now() - interval 2 month, 500.00),
40(4,now() - interval 2 month, 500.00),
41
42(1,now() - interval 1 month, 100.00),
43(1,now() - interval 1 month, 100.00),
44(2,now() - interval 1 month, 200.00),
45(2,now() - interval 1 month, 200.00),
46(3,now() - interval 1 month, 300.00),
47(3,now() - interval 1 month, 300.00),
48(4,now() - interval 1 month, 400.00),
49(4,now() - interval 1 month, 400.00),
50
51(1,now(), 100.00),
52(2,now(), 200.00),
53(3,now(), 300.00),
54(4,now(), 400.00);
55
56/*
57give me the sum of sales for each employee for a given period
58
59typical aggregation query
60*/
61
62select
63 emp_id,
64 sum(sale_amount) as monthly_sales
65from
66 sales
67where
68 sale_date between '2010-02-01 00:00:00' and '2010-02-28 00:00:00'
69group by
70 emp_id;
71 
72/*
73+----+-------------+-------+------+-------------------+------+---------+------+------+----------------------------------------------+
74| id | select_type | table | type | possible_keys     | key  | key_len | ref  |rows  | Extra                                        |
75+----+-------------+-------+------+-------------------+------+---------+------+------+----------------------------------------------+
76|  1 | SIMPLE      | sales | ALL  | sale_date_emp_idx | NULL | NULL    | NULL |  28  | Using where; Using temporary; Using filesort | <-- ouch
77+----+-------------+-------+------+-------------------+------+---------+------+------+----------------------------------------------+
781 row in set (0.00 sec)
79*/
80
81
82
83/*
84 now let's create a rollup table instead which has all of the sales summarised by year, month and employee. 
85 note the clustered primary key order
86*/
87
88
89drop table if exists sales_rollup;
90
91create table sales_rollup
92(
93year_id smallint unsigned not null,
94month_id tinyint unsigned not null,
95emp_id smallint unsigned not null,
96sale_amount decimal(10,2) not null default 0,
97primary key (year_id, month_id, emp_id)
98)engine=innodb;
99
100-- let's populate the rollup table
101
102insert ignore into sales_rollup
103select
104 year(sale_date) as year_id,
105 month(sale_date) as month_id,
106 emp_id,
107 sum(sale_amount) as sale_amount
108from
109 sales
110group by
111 year_id, month_id, emp_id;
112
113/*
114now let's do the same query as before 
115give me the sum of sales for each employee for a given year and month
116*/
117
118select * from sales_rollup where year_id = 2010 and month_id = 2;
119
120/*
121
122+----+-------------+--------------+------+---------------+---------+---------+-------------+------+-------+
123| id | select_type | table        | type | possible_keys | key     | key_len | ref         | rows | Extra |
124+----+-------------+--------------+------+---------------+---------+---------+-------------+------+-------+
125|  1 | SIMPLE      | sales_rollup | ref  | PRIMARY       | PRIMARY | 3       | const,const |    4 |       | <-- nice
126+----+-------------+--------------+------+---------------+---------+---------+-------------+------+-------+
1271 row in set (0.00 sec)
128*/
129
130/*
131problem - what happens if you add more sales data to the sales table ? 
132answer - the sales_rollup table will be out of date or stale !
133
134so you'll need to refresh the sales_rollup table every night or dynamically update it using triggers as data
135is inserted/updated in the sales table.
136
137whatever option is best for you !
138
139thanks for listening 
140
141f00
142*/