题目描述
(通过次数8,972 | 提交次数14,294,通过率62.77%)
表: 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 天)为一个时间段的顾客消费平均值。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 来源:力扣(LeetCode) 链接:https://leetcode.cn/problems/restaurant-growth
//测试数据 Create table If Not Exists Customer (customer_id int, name varchar(20), visited_on date, amount int); 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');
解题思路
Customer表中保存了每个客户每天的消费金额。
题目要求,查询每一天的最近7天的消费总额。
那么,可以先计算出每一天的消费总额(按天分组汇总即可),再根据时间区间,汇总每天的近7天消费总额。
这里的近7天,指的是当天+当天之前的6天。
这是典型的开窗汇总场景,只是开窗窗口的开始点是滚动的。在这里,开窗窗口的开始点,是当天-6天,结束点是当天。
这类滚动开窗的语法如下:
sum(汇总字段) over([partition by 开窗字段] [order by 排序字段 [rows N preceding]])
上面语法中的“rows N preceding”,就是滚动开窗的写法,含义为从当前行的前N行到当前行。
也可以使用“range between interval N day preceding and current row”的写法,功能是一样。
上面的写法,适合排序字段为DATE类型。当然,如果ORDER BY的排序字段为数值类型,也可以使用“range between…”的写法,如“order by datediff(visited_on, ‘2019-01-01’) range between 6 preceding and current row”。
参考SQL
未特别说明的情况下,参考SQL为基于MySQL8.0实现。
with tmp as ( select visited_on, sum(amount) amount from Customer group by visited_on ) select visited_on, amount, average_amount from ( select visited_on, round(sum(amount) over(order by visited_on rows 6 preceding),2) amount, round(avg(amount) over(order by visited_on rows 6 preceding),2) average_amount, row_number() over(order by visited_on) rn from tmp )a where rn>=7;
本站所有内容均为原创,本站保留所有权利。仅允许非商业用途的转载,但必须注明来源网站、作者、来源链接!否则,由此造成的一切后果,由转载方承担!
干货分享、技术提升、面试笔试、学习交流,欢迎关注公众号:xuesql。QQ学习交流群:209942678。