数据准备
drop table Customer;
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');
需求
写一条 SQL 查询计算以 7 天(某日期 + 该日期前的 6 天)为一个时间段的顾客消费平均值。average_amount 要 保留两位小数。
查询结果按 visited_on 排序。
输入
输出
- 以visited_on分组,求出从当天起,前面六个数据的日期,并求出当天的总消费额
select visited_on,
lag(visited_on,6) over () rn1,
sum(amount) as sum_amount
from Customer
group by visited_on;
- 获得七个日期时间内的销售额
-- 获得七个日期时间内的销售额
with t1 as (
select visited_on,
lag(visited_on,6) over () rn1,
sum(amount) as sum_amount
from Customer
group by visited_on
)
select *,
sum(sum_amount) over (rows between 6 preceding and current row ) total_amount
from t1
;
- 获得七个日期是连续七天的数据,并求出七天内的平均销售额(保留两位小数)
-- 获得七个日期是连续七天的数据,并求出七天内的平均销售额(保留两位小数)
with t1 as (
select visited_on,
lag(visited_on,6) over () rn1,
sum(amount) as sum_amount
from Customer
group by visited_on
),t2 as (
select *,
sum(sum_amount) over (rows between 6 preceding and current row ) total_amount
from t1
)
select visited_on,total_amount as amount,
round(total_amount/7 , 2) as average_amount
from t2
where datediff(visited_on,rn1)=6
order by visited_on;