题43:
根据下表写一条SQL查询计算以7天(某日期+该日期前的6天)为一个时间段的顾客消费平均值。
要求:
- 查询结果按visited_on排序
- average_amount要保留两位小数,日期数据的格式为(YYYY-MM-DD)
解题思路:本题要求按日期来计算连续7天顾客消费平均值
(1)使用窗口函数的话,即使前边的数据不够窗口函数也会将范围内的数据框住并计算,因此需要手动的只要能够完整框住7天就可以;
(2)数据中存在着同一用户在某日多次消费的情况,这样一来即使窗口照旧向前取6天就无法覆盖被挤出去的数据了,因此,需要构建一个小表格用来存放每天的金额总量。
select distinct visited_on,
sum_amount as amount,
round(sum_amount/7,2) as average_amount
from(
select visited_on,
sum(amount) over (order by visited_on rows 6 preceding) as sum_amount --计算连续7的累计金额
from(
select visited_on,
sum(amount) as amount
from Customer
group by visited_on-- -- 计算每天的金额总量
) tt
)ll
where datediff(visited_on,(
select min(visited_on)
from Customer
)
) >=6;