2021-06-08

餐馆营业额变化增长

写一条 SQL 查询计算以 7 天（某日期 + 该日期前的 6 天）为一个时间段的顾客消费平均值

查询结果格式的例子如下：

• 查询结果按 visited_on 排序
• average_amount 要 保留两位小数，日期数据的格式为 (‘YYYY-MM-DD’)

展示效果：

+--------------+--------------+----------------+
| visited_on   | amount       | average_amount |
+--------------+--------------+----------------+
| 2019-01-07   | 860          | 122.86         |
| 2019-01-08   | 840          | 120.00         |
| 2019-01-09   | 840          | 120.00         |
| 2019-01-10   | 1000         | 142.86         |
+--------------+--------------+----------------+

Create table If Not Exists 73_Customer (customer_id int, name varchar(20), visited_on date, amount int);
Truncate table 73_Customer;
insert into 73_Customer (customer_id, name, visited_on, amount) values ('1', 'Jhon', '2019-01-01', '100');
insert into 73_Customer (customer_id, name, visited_on, amount) values ('2', 'Daniel', '2019-01-02', '110');
insert into 73_Customer (customer_id, name, visited_on, amount) values ('3', 'Jade', '2019-01-03', '120');
insert into 73_Customer (customer_id, name, visited_on, amount) values ('4', 'Khaled', '2019-01-04', '130');
insert into 73_Customer (customer_id, name, visited_on, amount) values ('5', 'Winston', '2019-01-05', '110');
insert into 73_Customer (customer_id, name, visited_on, amount) values ('6', 'Elvis', '2019-01-06', '140');
insert into 73_Customer (customer_id, name, visited_on, amount) values ('7', 'Anna', '2019-01-07', '150');
insert into 73_Customer (customer_id, name, visited_on, amount) values ('8', 'Maria', '2019-01-08', '80');
insert into 73_Customer (customer_id, name, visited_on, amount) values ('9', 'Jaze', '2019-01-09', '110');
insert into 73_Customer (customer_id, name, visited_on, amount) values ('1', 'Jhon', '2019-01-10', '130');
insert into 73_Customer (customer_id, name, visited_on, amount) values ('3', 'Jade', '2019-01-10', '150');

最终sql：

select
    s.visited_on,
    sum(c.amount) as "amount",
    round(sum(c.amount) / 7, 2) as "average_amount"
from 
    73_Customer c 
inner join 
   (select
         distinct visited_on
    from
         73_Customer c 
    where 
         visited_on >= (select
                              distinct visited_on
                        from
                              73_customer 
                        order by
                              visited_on asc
                        limit 1 offset 6)
   ) s 
on 
   datediff(s.visited_on, c.visited_on) >= 0 
   and
   datediff(s.visited_on, c.visited_on) < 7
group by
   s.visited_on;