Medium之1321.餐馆营业额变化增长***

表: Customer

Column Name	Type
customer_id	int
name	varchar
visited_on	date
amount	int
(customer_id, visited_on) 是该表的主键
该表包含一家餐馆的顾客交易数据
visited_on 表示 (customer_id) 的顾客在 visited_on 那天访问了餐馆
amount 是一个顾客某一天的消费总额

问题

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

写一条 SQL 查询计算以 7 天（某日期 + 该日期前的 6 天）为一个时间段的顾客消费平均值
查询结果按 visited_on 排序
average_amount 要 保留两位小数，日期数据的格式为 (‘YYYY-MM-DD’)

示例

Customer 表:

customer_id	name	visited_on	amount
1	Jhon	2019-01-01	100
2	Daniel	2019-01-02	110
3	Jade	2019-01-03	120
4	Khaled	2019-01-04	130
5	Winston	2019-01-05	110
6	Elvis	2019-01-06	140
7	Anna	2019-01-07	150
8	Maria	2019-01-08	80
9	Jaze	2019-01-09	110
1	Jhon	2019-01-10	130
3	Jade	2019-01-10	150

Result表:

visited_on	amount	average_amount
2019-01-07	860	122.86
2019-01-08	840	120
2019-01-09	840	120
2019-01-10	1000	142.86

第一个七天消费平均值从 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

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/restaurant-growth
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

解答

select visited_on,
    sum_amount amount,
    round(sum_amount/7,2) average_amount
from(
    select visited_on,
            sum(amount) over(order by visited_on rows 6 preceding) sum_amount
    from (
        select customer_id,visited_on,sum(amount) amount
        from customer
        group by visited_on
        ) t
    ) tt 
order by visited_on
WHERE DATEDIFF(visited_on, (SELECT MIN(visited_on) FROM Customer)) >= 6  #限制时间从第7天开始

知识点

1.ROWS 6 PRECEDING等价于rows between 6 proceding and current row
:表示取当前行和前六行；

2.表t用来解决一天中客户有多次消费，这样向前滑动6行就会有误，所以要先整理好每个客户在每天的总消费。