- 学习:知识的初次邂逅
- 复习:知识的温故知新
练习:知识的实践应用
目录
一,原题力扣链接
二,题干
表:
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
升序排序。返回结果格式的例子如下。
示例 1:
输入: 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 | +-------------+--------------+--------------+-------------+ 输出: +--------------+--------------+----------------+ | 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
三,建表语句
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');
四,分析
题解:
表:客户表
字段: 客户id,客户名称,就餐天数,数量
要求:7天之后的 顾客消费平均值 按照日期升序,平均值保留2位小数
--
7天之后的顾客消费平均值
需要用到范围开窗 rang
平均值 sum(7天内的数量和)/7
sum(数量) over(order by 日期 rang 包含自己的往前6天) /7
条件 :7天之后的
那么大于 最小的天数+6
升序我们在开窗里面已经做了;
保留两位小数 round
--
解法二
思路 第一步 拿到一个只有日期的列 连接主表 条件 非等值连接 我的日期如果和主表的日期相差 7天以内 条件2 我的日期 大于 主表日期最小的天数 范围+6 以日期分组 求主表sum 和主表sum/7
五,SQL解答
解法一,窗口函数 范围窗口 range
with t1 as (
select visited_on,
sum(amount) over ( order by visited_on range interval 6 day preceding) as amount
from Customer
),t2 as (
select *,
round(amount/7,2) as average_amount
from t1 where visited_on>= date_add((select min(visited_on) from customer),interval 6 day)
)
select distinct * from t2;
解法二,自联结,主动判断天数
select distinct c1.visited_on,
sum( c2.amount) as amount ,
round(sum( c2.amount)/7,2) as average_amount
from (select distinct visited_on from customer) c1,customer c2
where datediff(c1.visited_on,c2.visited_on) between 0 and 6
and c1.visited_on >= date_add((select min(visited_on) from customer),interval 6 day)
group by c1.visited_on;
六,验证
七,知识点总结
- 范围开窗 rang 指定天数范围
- sum配合开窗 统计不同窗口大小的累计值;
- 7天之后,大于:最小的天数+6天
- 开窗行数联系
- round函数联系
- 开窗+排序+指定窗口范围联系
- 学习:知识的初次邂逅
- 复习:知识的温故知新
练习:知识的实践应用