计算全量数据分析全年单月的月订单金额结果存入视图table1。 字段要求：month月订单金额2 结合上题结果可知自动售货机下半年的总订单金额远高于上半年对结果中月订单金额最高的月份进行分析求该月单日订单金额结果存入视图table2中。 字段要求：day日订单金额3 结合第一题的数据结果对订单金额较高的四个月9101112进行单日销量走势分析计算这四个月中1-30日单日订单金额均值结果存入

1.创建视图table1:

CREATE VIEW table1 AS SELECT DATE_FORMAT(time_of_payment,'%m') AS month, SUM(Actual_amount) AS 月订单金额 FROM sales_data GROUP BY month;

2.创建视图table2:

CREATE VIEW table2 AS SELECT DATE_FORMAT(time_of_payment,'%d') AS day, SUM(Actual_amount) AS 日订单金额 FROM sales_data WHERE DATE_FORMAT(time_of_payment,'%m') = 'xx' --xx代表月份为上一步骤中月订单金额最高的月份 GROUP BY day;

3.创建视图table3:

CREATE VIEW table3 AS SELECT DATE_FORMAT(time_of_payment,'%d') AS day, AVG(Actual_amount) AS 日订单均额 FROM sales_data WHERE DATE_FORMAT(time_of_payment,'%m') IN ('09','10','11','12') AND DATE_FORMAT(time_of_payment,'%d') BETWEEN 1 AND 30 GROUP BY day;

4.创建视图table4:

CREATE VIEW table4 AS SELECT categorize, COUNT(*) AS 年订单量, SUM(Actual_amount) AS 年订单金额 FROM sales_data s JOIN product_info p ON s.commodity = p.commodity GROUP BY categorize;

5.创建视图table5:

CREATE VIEW table5 AS SELECT location, SUM(Actual_amount) AS 年订单金额 FROM sales_data GROUP BY location;

6.创建视图table6:

CREATE VIEW table6 AS SELECT p.categorize, COUNT(*) AS 年订单量 FROM sales_data s JOIN product_info p ON s.commodity = p.commodity WHERE s.location = 'xx' --xx代表年订单金额最高的区域 GROUP BY p.categorize;

7.创建视图table7:

CREATE VIEW table7 AS SELECT p.categorize, SUM(s.Actual_amount) AS 年订单金额 FROM sales_data s JOIN product_info p ON s.commodity = p.commodity WHERE s.location = 'xx' --xx代表年订单金额最高的区域 GROUP BY p.categorize;

8.创建视图table8:

CREATE VIEW table8 AS SELECT p.categorize, COUNT(*) AS 年订单量, SUM(s.Actual_amount) AS 年订单金额 FROM sales_data s JOIN product_info p ON s.commodity = p.commodity WHERE p.categorize = '饮料' GROUP BY p.sec_categorize;

9.创建视图table9:

CREATE VIEW table9 AS SELECT DATE_FORMAT(time_of_payment,'%m') AS month, COUNT(*) AS 茶饮料 FROM sales_data s JOIN product_info p ON s.commodity = p.commodity WHERE p.sec_categorize = '茶饮料' AND s.location = 'xx' --xx代表销量最高的地区 AND DATE_FORMAT(time_of_payment,'%m') BETWEEN 9 AND 12 GROUP BY month;

10.创建视图table10:

CREATE VIEW table10 AS SELECT p.sec_categorize, COUNT(*) AS 年订单量, SUM(s.Actual_amount) AS 年订单金额 FROM sales_data s JOIN product_info p ON s.commodity = p.commodity WHERE s.location = 'xx' --xx代表年订单金额最低的地区 AND p.sec_categorize = '功能饮料' GROUP BY p.sec_categorize