LeetCode 1321.餐馆营业额变化增长

数据准备

drop table Customer;

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');

需求

写一条 SQL 查询计算以 7 天（某日期 + 该日期前的 6 天）为一个时间段的顾客消费平均值。average_amount 要 保留两位小数。

查询结果按 visited_on 排序。

输入

输出

1. 以visited_on分组,求出从当天起,前面六个数据的日期,并求出当天的总消费额

select visited_on,
       lag(visited_on,6) over () rn1,
       sum(amount) as sum_amount
from Customer
group by visited_on;

2. 获得七个日期时间内的销售额

-- 获得七个日期时间内的销售额
with t1 as (
    select visited_on,
        lag(visited_on,6) over () rn1,
        sum(amount) as sum_amount
    from Customer
    group by visited_on
)
select *,
       sum(sum_amount) over (rows between 6 preceding and current row ) total_amount
from t1
;

3. 获得七个日期是连续七天的数据,并求出七天内的平均销售额(保留两位小数)

-- 获得七个日期是连续七天的数据,并求出七天内的平均销售额(保留两位小数)
with t1 as (
    select visited_on,
        lag(visited_on,6) over () rn1,
        sum(amount) as sum_amount
    from Customer
    group by visited_on
),t2 as (
    select *,
       sum(sum_amount) over (rows between 6 preceding and current row ) total_amount
    from t1
)
select visited_on,total_amount as amount,
       round(total_amount/7 , 2) as average_amount
from t2
where datediff(visited_on,rn1)=6
order by visited_on;