订单金额趋势分析
题目需求
查询截止每天的最近3天内的订单金额总和以及订单金额日平均值,保留两位小数,四舍五入。期望结果如下:
create_date(日期) | total_3d(最近3日订单金额总和) | avg_ad(最近3日订单金额日平均值) |
---|---|---|
2020-10-08 | 75970.00 | 75970.00 |
2021-09-27 | 104970.00 | 52485.00 |
2021-09-28 | 175470.00 | 58490.00 |
… | … | … |
代码实现
select
create_date,
round(sum(total_amount_by_day) over (order by create_date rows between 2 preceding and current row), 2) total_3d,
round(avg(total_amount_by_day) over (order by create_date rows between 2 preceding and current row), 2) avg_ad
from
(
select
create_date,
sum(total_amount) total_amount_by_day
from order_info
group by create_date
)t1;