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_monthly (product, ym, amount) VALUES ('苹果', '201904', 11341.00);
INSERT INTO sales_monthly (product, ym, amount) VALUES ('苹果', '201905', 11459.00);
INSERT INTO sales_monthly (product, ym, amount) VALUES ('苹果', '201906', 11560.00);

INSERT INTO sales_monthly (product, ym, amount) VALUES ('香蕉', '201801', 10138.00);
INSERT INTO sales_monthly (product, ym, amount) VALUES ('香蕉', '201802', 10194.00);
INSERT INTO sales_monthly (product, ym, amount) VALUES ('香蕉', '201803', 10328.00);
INSERT INTO sales_monthly (product, ym, amount) VALUES ('香蕉', '201804', 10322.00);
INSERT INTO sales_monthly (product, ym, amount) VALUES ('香蕉', '201805', 10481.00);
INSERT INTO sales_monthly (product, ym, amount) VALUES ('香蕉', '201806', 10502.00);
INSERT INTO sales_monthly (product, ym, amount) VALUES ('香蕉', '201807', 10589.00);
INSERT INTO sales_monthly (product, ym, amount) VALUES ('香蕉', '201808', 10681.00);
INSERT INTO sales_monthly (product, ym, amount) VALUES ('香蕉', '201809', 10798.00);
INSERT INTO sales_monthly (product, ym, amount) VALUES ('香蕉', '201810', 10829.00);
INSERT INTO sales_monthly (product, ym, amount) VALUES ('香蕉', '201811', 10913.00);
INSERT INTO sales_monthly (product, ym, amount) VALUES ('香蕉', '201812', 11056.00);
INSERT INTO sales_monthly (product, ym, amount) VALUES ('香蕉', '201901', 11161.00);
INSERT INTO sales_monthly (product, ym, amount) VALUES ('香蕉', '201902', 11173.00);
INSERT INTO sales_monthly (product, ym, amount) VALUES ('香蕉', '201903', 11288.00);
INSERT INTO sales_monthly (product, ym, amount) VALUES ('香蕉', '201904', 11408.00);
INSERT INTO sales_monthly (product, ym, amount) VALUES ('香蕉', '201905', 11469.00);
INSERT INTO sales_monthly (product, ym, amount) VALUES ('香蕉', '201906', 11528.00);

INSERT INTO sales_monthly (product, ym, amount) VALUES ('桔子', '201801', 10154.00);
INSERT INTO sales_monthly (product, ym, amount) VALUES ('桔子', '201802', 10183.00);
INSERT INTO sales_monthly (product, ym, amount) VALUES ('桔子', '201803', 10245.00);
INSERT INTO sales_monthly (product, ym, amount) VALUES ('桔子', '201804', 10325.00);
INSERT INTO sales_monthly (product, ym, amount) VALUES ('桔子', '201805', 10465.00);
INSERT INTO sales_monthly (product, ym, amount) VALUES ('桔子', '201806', 10505.00);
INSERT INTO sales_monthly (product, ym, amount) VALUES ('桔子', '201807', 10578.00);
INSERT INTO sales_monthly (product, ym, amount) VALUES ('桔子', '201808', 10680.00);
INSERT INTO sales_monthly (product, ym, amount) VALUES ('桔子', '201809', 10788.00);
INSERT INTO sales_monthly (product, ym, amount) VALUES ('桔子', '201810', 10838.00);
INSERT INTO sales_monthly (product, ym, amount) VALUES ('桔子', '201811', 10942.00);
INSERT INTO sales_monthly (product, ym, amount) VALUES ('桔子', '201812', 10988.00);
INSERT INTO sales_monthly (product, ym, amount) VALUES ('桔子', '201901', 11099.00);
INSERT INTO sales_monthly (product, ym, amount) VALUES ('桔子', '201902', 11181.00);
INSERT INTO sales_monthly (product, ym, amount) VALUES ('桔子', '201903', 11302.00);
INSERT INTO sales_monthly (product, ym, amount) VALUES ('桔子', '201904', 11327.00);
INSERT INTO sales_monthly (product, ym, amount) VALUES ('桔子', '201905', 11423.00);
INSERT INTO sales_monthly (product, ym, amount) VALUES ('桔子', '201906', 11524.00);

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

select *,
       round(avg(amount) over(partition by product order by ym
           ROWS BETWEEN 2 PRECEDING AND CURRENT ROW),2) as sum
from sales_monthly;

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

select *,
       round(sum(amount) over(partition by product order by ym
           ROWS BETWEEN unbounded preceding and current row),2) as sum
from sales_monthly;

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

环比增长率：是与上个月相比

同比增长率：与去年同期比较（本期数-同期数）/同期数 * 100%

select product, ym, amount,
       substr(ym,1,4) y,
       substr(ym,5,2) m
       from sales_monthly;

with t1 as (
    select product, ym, amount,
           substr(ym,1,4) y,
           substr(ym,5,2) m
    from sales_monthly
), t2 as (
    -- 求环比
    select product, ym, amount,
           case
               when lag(amount) over(partition by product order by ym) is null then null
               else round((amount - lag(amount) over(partition by product order by ym)) / (lag(amount) over(partition by product order by ym)) * 100,2)
               end '环比',
           -- 求同比
           case
               when y = '2018' then null
               else round((amount - lag(amount, 12) over (partition by product order by y)) / lag(amount, 12) over (partition by product order by y) * 100,2)
               end '同比'
    from t1
)select *
from t2;