MySQL练习题-求连续、累计、环比和同比问题

目录

准备数据

1）求不同产品每个月截止当月最近3个月的平均销售额 

2）求不同产品截止当月的累计销售额

3）求环比增长率和同比增长率

---

准备数据

-- product 表示产品名称，ym 表示年月，amount 表示销售金额（元）
CREATE TABLE sales_monthly (
                               product VARCHAR(20),  -- 使用 VARCHAR2 来表示字符串类型
                               ym VARCHAR(10),       -- 使用 VARCHAR2 表示年月
                               amount decimal(10, 2)   -- 使用 NUMBER 表示金额，保留两位小数
);

-- 插入测试数据
INSERT INTO sales_monthly (product, ym, amount) VALUES ('苹果', '201801', 10159.00);
INSERT INTO sales_monthly (product, ym, amount) VALUES ('苹果', '201802', 10211.00);
INSERT INTO sales_monthly (product, ym, amount) VALUES ('苹果', '201803', 10247.00);
INSERT INTO sales_monthly (product, ym, amount) VALUES ('苹果', '201804', 10376.00);
INSERT INTO sales_monthly (product, ym, amount) VALUES ('苹果', '201805', 10400.00);
INSERT INTO sales_monthly (product, ym, amount) VALUES ('苹果', '201806', 10565.00);
INSERT INTO sales_monthly (product, ym, amount) VALUES ('苹果', '201807', 10613.00);
INSERT INTO sales_monthly (product, ym, amount) VALUES ('苹果', '201808', 10696.00);
INSERT INTO sales_monthly (product, ym, amount) VALUES ('苹果', '201809', 10751.00);
INSERT INTO sales_monthly (product, ym, amount) VALUES ('苹果', '201810', 10842.00);
INSERT INTO sales_monthly (product, ym, amount) VALUES ('苹果', '201811', 10900.00);
INSERT INTO sales_monthly (product, ym, amount) VALUES ('苹果', '201812', 10972.00);
INSERT INTO sales_monthly (product, ym, amount) VALUES ('苹果', '201901', 11155.00);
INSERT INTO sales_monthly (product, ym, amount) VALUES ('苹果', '201902', 11202.00);
INSERT INTO sales_monthly (product, ym, amount) VALUES ('苹果', '201903', 11260.00);
INSERT INTO sales_mont