MySQL基础练习题17-餐馆营业额变化增长

题目

准备数据

分析数据

第一步：求每天的营业额

第二步：求最近7天的总营销额

第三步：求平均营销额

第四步：求从第一个7天的平均营销额

---

题目

计算以 7 天（某日期 + 该日期前的 6 天）为一个时间段的顾客消费平均值。average_amount 要 保留两位小数。结果按 visited_on 升序排序

准备数据

## 创建库
create database db;
use db;

## 创建表
Create table If Not Exists Customer (customer_id int, name varchar(20), visited_on date, amount int);

## 向表中插入数据
Truncate table Customer;
insert into Customer (customer_id, name, visited_on, amount) values ('1', 'Jhon', '2019-01-01', '100');
insert into Customer (customer_id, name, visited_on, amount) values ('2', 'Daniel', '2019-01-02', '110');
insert into Customer (customer_id, name, visited_on, amount) values ('3', 'Jade', '2019-01-03', '120');
insert into Customer (customer_id, name, visited_on, amount) values ('4', 'Khaled', '2019-01-04', '130');
insert into Customer (customer_id, name, visited_on, amount) values ('5', 'Winston', '2019-01-05', '110');
insert into Customer (customer_id, name, visited_on, amount) values ('6', 'Elvis', '2019-01-06', '140');
insert into Customer (customer_id, name, visited_on, amount) values ('7', 'Anna', '2019-01-07', '150');
insert into Customer (customer_id, name, visited_on, amount) values ('8', 'Maria', '2019-01-08', '80');
insert into Customer (customer_id, name, visited_on, amount) values ('9', 'Jaze', '2019-01-09', '110');
insert into Customer (customer_id, name, visited_on, amount) values ('1', 'Jhon', '2019-01-10', '130');
insert into Customer (customer_id, name, visited_on, amount) values ('3', 'Jade', '2019-01-10', '150');

输入：

customer表

分析数据

第一个七天消费平均值从 2019-01-01 到 2019-01-07 是 (100 + 110 + 120 + 130 + 110 + 140 + 150)/7 = 122.86
第二个七天消费平均值从 2019-01-02 到 2019-01-08 是 (110 + 120 + 130 + 110 + 140 + 150 + 80)/7 = 120
第三个七天消费平均值从 2019-01-03 到 2019-01-09 是 (120 + 130 + 110 + 140 + 150 + 80 + 110)/7 = 120
第四个七天消费平均值从 2019-01-04 到 2019-01-10 是 (130 + 110 + 140 + 150 + 80 + 110 + 130 + 150)/7 = 142.86

思路:

1. 求每天的营业额
2. 求最近7天的总营销额
3. 求平均营销额
4. 求从第一个7天的平均营销额

 第一步：求每天的营业额

select visited_on,
       sum(amount) as cn
       from customer
group by visited_on;

说明：因为10号有两天，目的是求每天的平均销售额，所以求个每天的总销售额。 

第二步：求最近7天的总营销额

with t1 as (
    select visited_on,
       sum(amount) as cn
       from customer
group by visited_on
) select visited_on,
         cn,
         sum(cn) over(order by visited_on
             range between interval 6 day preceding and current row ) as amount
         from t1;

说明：利用从窗口函数和窗口范围对近7天求个总销售额。根据日期排序和计算，所以用range而不是rows，而且在MySQL中‘6 preceding’ 要改成‘interval 6 day preceding’

第三步：求平均营销额

with t2 as (
    with t1 as (
    select visited_on,
       sum(amount) as cn
       from customer
group by visited_on
) select visited_on,
         cn,
         sum(cn) over(order by visited_on
             range between interval 6 day preceding and current row ) as amount
         from t1
)select visited_on, cn, amount,
        round(amount / 7 ,2) as avg,
        (select min(visited_on) from customer) min_visited,
        datediff(visited_on,(select min(visited_on) from customer)) average_amount
        from t2;

说明：求连续几天需要一列层差数列差 ，可以利用datediff()函数求日期差。

第四步：求从第一个7天的平均营销额

with t3 as (
    with t2 as (
    with t1 as (
    select visited_on,
       sum(amount) as cn
       from customer
group by visited_on
) select visited_on,
         cn,
         sum(cn) over(order by visited_on
             range between interval 6 day preceding and current row ) as amount
         from t1
)select visited_on, cn, amount,
        round(amount / 7 ,2) as avg,
        (select min(visited_on) from customer) min_visited,
        datediff(visited_on,(select min(visited_on) from customer)) average_amount
        from t2
)select visited_on,amount,
        average_amount
        from t3
where average_amount >=6;