17. 订单金额趋势分析

题目需求

查询截止每天的最近3天内的订单金额总和以及订单金额日平均值，保留两位小数，四舍五入。

最近三天 的业务逻辑通常是基于当天往前推2天

期望结果如下：

create_date (日期)	total_3d <decimal(16,2)> (最近3日订单金额总和)	avg_3d <decimal(16,2)> (最近3日订单金额日平均值)
2020-10-08	75970.00	75970.00
2021-09-27	104970.00	52485.00
2021-09-28	175470.00	58490.00
…	…	…

需要用到的表：

订单信息表：order_info

order_id (订单id)	user_id (用户id)	create_date (下单日期)	total_amount (订单金额)
1	101	2021-09-30	29000.00
10	103	2020-10-02	28000.00

思路一

实现一

select t2.create_date,
       -- 计算出最近3天中，共有几天有订单记录
       -- count(*),
       -- 计算出最近3天中，订单金额的总和
       round(sum(t3.total_amount_for_everyday), 2)            as total_3d,
       round(sum(t3.total_amount_for_everyday) / count(*), 2) as avg_3d
from (
         -- 3）使用 explode 炸开 list，形成 create_date, create_date
         --                              create_date, create_date - 1
         --                              create_date, create_date - 2

         -- 2）统计每个 create_date 最近3天内 的date，并放到list中
         select create_date,
                -- 注: 该方式计算的不是最近3日(是物理级别上的最近3条记录)
                -- collect_list(create_date) over(order by create_date rows between 2 preceding and current row)
                last_3_days
         from (
                  -- 1） 按 create_date 去重，并升序排序
                  select distinct create_date
                  from order_info
                  order by create_date
              ) t1
                  lateral view explode(
                          array(create_date, date_add(create_date, -1), date_add(create_date, -2))) tmp as last_3_days
     ) t2

         join

     (
         -- 3）统计每个日期的销售总额
         select create_date,
                sum(total_amount) as total_amount_for_everyday
         from order_info
         group by create_date
     ) t3
     on t2.last_3_days = t3.create_date
group by t2.create_date;

实现二：使用 over(range)

-- 方法二(节选SQL): 使用 over(range)

-- 2）统计每个 create_date 最近3天内 的date，并放到list中
select create_date,
       -- 注: 在对 yyyy-MM-dd 型 string 数据做加减法时，需要做一个类型转换 cast(xxx as date)
       collect_list(create_date) over (order by cast(create_date as date) range between 2 preceding and current row)
from (
         -- 1） 按 create_date 去重，并升序排序
         select distinct create_date
         from order_info
         order by create_date
     ) t1

学习链接

hive开窗函数中range和rows的区别

题目来源

http://practice.atguigu.cn/#/question/17/desc?qType=SQL