5h7yfRSx

1-- MySql Materialized View Hack
2
3
4-- 1. Define a regular view to store the query.
5
6CREATE IF NOT EXISTS VIEW V_CustomerStats
7AS
8SELECT
9  c.id,
10  round(avg(os.subtotal + os.freight), 2) AS avgOrderSpend,
11  round(sum(os.subtotal + os.freight), 2) AS totalOrderSpend,
12  round(sum(os.totalDiscount), 2) AS lifetimeDiscount,
13  count(os.id) AS numOrders
14FROM
15  Customer AS c
16  JOIN (
17    SELECT
18      o.id,
19      o.customerid,
20      o.freight,
21      sum((od.unitprice * (1 - od.discount) * od.quantity)) AS subtotal,
22      sum((od.unitprice * od.discount) * od.quantity) AS totalDiscount
23    FROM
24      customerOrder AS o
25      JOIN orderdetail AS od ON o.id = od.orerid
26    GROUP BY
27      o.id) AS os ON os.customerid = c.id
28  GROUP BY
29    c.id;
30
31
32-- 2. Create a new table based on the view's result set
33
34CREATE TABLE MV_CustomerStats
35AS
36SELECT
37  *
38FROM
39  V_CustomerStats;
40
41CREATE INDEX MV_CustomerStatsId ON MV_CustomerStats (id);
42
43
44-- 3. Get the table's result set
45
46SELECT * FROM MV_CustomerStats;
47
48
49-- 4. Refresh the data in the table
50
51-- Create a new table for the updated result set
52CREATE TABLE MV_CustomerStats_new
53AS
54SELECT
55  *
56FROM
57  V_CustomerStats;
58
59CREATE INDEX MV_CustomerStatsId ON MV_CustomerStats_new (id);
60
61-- Move the new table into position and the old one out of position in the same RENAME query
62RENAME TABLE MV_CustomerStats TO MV_CustomerStats_old,
63MV_CustomerStats_new TO MV_CustomerStats;
64
65-- Get rid of the old data
66DROP TABLE MV_CustomerStats_old;