题目
题目链接：餐馆营业额变动增长
你是餐馆的老板，当初你想剖析一下可能的营业额变动增长（每天至多有一位顾客）。
计算以 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');-- 工夫不间断的例子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 ('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 表:+-------------+--------------+--------------+-------------+| 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 |+-------------+--------------+--------------+-------------+在 SQL 中，(customer_id, visited_on) 是该表的主键。该表蕴含一家餐馆的顾客交易数据。visited_on 示意 (customer_id) 的顾客在 visited_on 那天拜访了餐馆。amount 是一个顾客某一天的生产总额。输入：+--------------+--------------+----------------+| 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 是restaurant-growth/restaurant-growth/ (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本题考查的常识是如何累加一段时间区间内的值
有两种实现形式：
- 应用窗口函数，窗口函数比拟好了解应用
6 PRECEDING AND current ROW就能查找进去了（计划一） - 应用自连，连贯条件不太容易想到，须要应用
DATEDIFF函数，这个函数能够计算两个日期之间的天数，而后应用BETWEEN条件（计划二和计划三）
解析
要晓得过来
7天的均匀消费额，须要先晓得每天的总消费额，作为长期表tmp1select visited_on, sum(amount) sum_amount from Customer group by visited_on+-------------+--------------+| visited_on | sum_amount |+-------------+--------------+| 2019-01-01 | 100 || 2019-01-02 | 110 || 2019-01-03 | 120 || 2019-01-04 | 130 || 2019-01-05 | 110 || 2019-01-06 | 140 || 2019-01-07 | 150 || 2019-01-08 | 80 || 2019-01-09 | 110 || 2019-01-10 | 280 |+-------------+--------------+应用窗口函数，计算过来
7天的总的消费额，作为长期表tmp2select sum(sum_amount) sum_amount over (order by to_days(visited_on) range between 6 preceding and current row) as sum_amount from tmp1| visited_on | sum_amount |+-------------+--------------+| 2019-01-01 | 100 || 2019-01-02 | 210 || 2019-01-03 | 330 || 2019-01-04 | 460 || 2019-01-05 | 570 || 2019-01-06 | 710 || 2019-01-07 | 860 || 2019-01-08 | 840 || 2019-01-09 | 840 || 2019-01-10 | 1000 |+-------------+--------------+计算过来
7天的均匀消费额，作为长期表tmp3select visited_on, sum_amount amount, sum_amount / 7 as average_amount from tmp2| visited_on | sum_amount | average_amount |+-------------+--------------+----------------+| 2019-01-01 | 100 | 14.2857 || 2019-01-02 | 210 | 30.0000 || 2019-01-03 | 330 | 47.1429 || 2019-01-04 | 460 | 65.7143 || 2019-01-05 | 570 | 81.4286 || 2019-01-06 | 710 | 101.4286 || 2019-01-07 | 860 | 122.8571 || 2019-01-08 | 840 | 120.0000 || 2019-01-09 | 840 | 120.0000 || 2019-01-10 | 1000 | 142.8571 |+-------------+-------------+----------------+- 筛选出计算数据大于等于七天的数据
- 须要晓得表中日期最小的一天，作为长期表 `tmp4` `select min(visited_on) min_visited_on from Customer` ``` | min_visited_on | +-----------------+ | 2019-01-01 | +-----------------+ ``` - 应用 `datediff(expr1, expr2)` 函数，计算两个日期之间的天数，这里须要大于等于 `6` 天 `select visited_on, amount, round(average_amount, 2) average_amount from tmp3 where datediff(visited_on, (select min(visited_on) from Customer)) >= 6` ``` | visited_on | amount | average_amount | +-------------+--------------+----------------+ | 2019-01-07 | 860 | 122.8571 | | 2019-01-08 | 840 | 120.0000 | | 2019-01-09 | 840 | 120.0000 | | 2019-01-10 | 1000 | 142.8571 | +-------------+--------------+----------------+ ```最终 sql 语句如下：
SELECT visited_on, sum_amount amount, ROUND( sum_amount / 7, 2 ) average_amountFROM ( SELECT visited_on, SUM( sum_amount ) OVER ( ORDER BY to_days(visited_on) RANGE BETWEEN 6 PRECEDING AND current ROW ) sum_amount FROM ( SELECT visited_on, SUM( amount ) sum_amount FROM Customer GROUP BY visited_on ) tmp1) tmp2WHERE DATEDIFF(visited_on, ( SELECT MIN( visited_on ) FROM Customer )) >= 6;下面 sql 能够简化一下，不过有问题，就是如果工夫不间断，排序不会跳过。
也就是说 rk > 7 只能筛选出间断 7 天的数据
SELECT visited_on, amount, SUM( amount / 7, 2 ) average_amountFROM ( SELECT visited_on, RANK() OVER ( ORDER BY visited_on ) AS rk, SUM(SUM( amount )) OVER ( ORDER BY visited_on RANGE INTERVAL 7-1 DAY PRECEDING ) AS amount FROM Customer GROUP BY visited_on) AS tep WHERE rk >= 7 ORDER BY 1办法二
此办法是应用自连，连贯的条件是工夫间断 7 天，这个办法如果工夫不间断，就会有问题
WITH t AS ( SELECT visited_on, SUM( amount ) amount FROM Customer GROUP BY visited_on)SELECT a.visited_on, SUM( b.amount ) amount, ROUND( AVG( b.amount ), 2 ) average_amountFROM t a, t bWHERE DATEDIFF( a.visited_on, b.visited_on ) BETWEEN 0 AND 6GROUP BY a.visited_on COUNT(*) = 7;办法三
SELECT a.visited_on, sum( b.amount ) AS amount, round( sum( b.amount ) / 7, 2 ) AS average_amountFROM ( SELECT DISTINCT visited_on FROM Customer ) a JOIN Customer b ON datediff( a.visited_on, b.visited_on ) BETWEEN 0 AND 6WHERE a.visited_on >= ( SELECT min( visited_on ) FROM Customer ) + 6GROUP BY a.visited_onORDER BY visited_on往期 MySQL 题目
- MySQL 题目
- LeetCode mysql 刷题一：计算非凡奖金 | 买下所有产品的客户
- LeetCode mysql 刷题二：电影评分——判断日期的五种办法
- LeetCode mysql 刷题三：确认率——MySQL 中的 null 解决 | 判断三角形的四种办法