力扣高频SQL 50题（基础版）第四十七题之1321.餐馆营业额变化增长

力扣高频SQL 50题（基础版）第四十七题

1321.餐馆营业额变化增长

题目说明

表: Customer

±--------------±--------+

| Column Name | Type |

±--------------±--------+

| customer_id | int |

| name | varchar |

| visited_on | date |

| amount | int |

±--------------±--------+

在 SQL 中，(customer_id, visited_on) 是该表的主键。

该表包含一家餐馆的顾客交易数据。

visited_on 表示 (customer_id) 的顾客在 visited_on 那天访问了餐馆。

amount 是一个顾客某一天的消费总额。

你是餐馆的老板，现在你想分析一下可能的营业额变化增长（每天至少有一位顾客）。

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

结果按 visited_on 升序排序。

实现过程

准备数据

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

实现方式

with t1 as (
     select distinct  # 因为窗口函数是按照日期计算的。所以相同日期的结果也是相同的，直接去重即可
            visited_on,
            sum(amount) over(order by visited_on range interval 6 day preceding) amount, # 按照日期排序，范围是当前日期和当前日期的前六天
            round(sum(amount) over(order by visited_on range interval 6 day preceding)/7, 2) average_amount
     from Customer)
select visited_on,
       amount,
       average_amount
from t1
where datediff(visited_on,(select min(visited_on) from Customer))>=6 #去除日期不足7日的结果
order by visited_on;

结果截图