SQL 刷题累计订单金额问题-526互联

需求描述

现有订单信息表如下：

create table t_simple_order
(
    uid    int,    --用户id
    day_id date,--订单日期
    ant    numeric --订单金额
);
insert into t_simple_order values (1,'2023-02-01'),(1,'2023-03-02'),(1,'2023-04-02');
insert into t_simple_order values (2,'2023-03-11'),(2,'2023-03-15'),(2,'2023-04-01');

要求计算每个用户首次订单日期后30天内累计订单金额，要求输出，用户ID，首次订单日期，30天内累计订单累计金额

解题思路

题目其实很简单，第一步找出每个用户首次下单时间，此处可以使用聚合函数min对下单时间求最小值，就是首次下单，然后对每个用户每天的下单金额进行汇总，然后进行关联即可

参考实现

select t.uid,
       t.fisrst_day,
       sum(sum_ant) filter ( where t1.day_id between t.fisrst_day and (t.fisrst_day + 30) ) total_ant
from (select uid, min(day_id) fisrst_day
      from t_simple_order
      group by uid) t
         left join
     (select uid, day_id, sum(ant) sum_ant
      from t_simple_order
      group by uid, day_id) t1 on t.uid = t1.uid
group by t.uid, t.fisrst_day;

此处使用了 post过热SQL 为聚合函数的一个扩展项 filter 实现，也可以使用 case when 对时间进行过滤

select t.uid,
       t.fisrst_day,
       sum(case when t1.day_id between t.fisrst_day and (t.fisrst_day + 30) then sum_ant end) total_ant
from (select uid, min(day_id) fisrst_day
      from t_simple_order
      group by uid) t
         left join
     (select uid, day_id, sum(ant) sum_ant
      from t_simple_order
      group by uid, day_id) t1 on t.uid = t1.uid
group by t.uid, t.fisrst_day;

最后给出本题的Hive解法

select t.uid,
       t.fisrst_day,
       sum(if(t1.day_id between t.fisrst_day and  date_add(t.fisrst_day,30), sum_ant, 0)) total_ant
from (select uid, min(day_id) fisrst_day
      from t_simple_order
      group by uid) t
         left join
     (select uid, day_id, sum(ant) sum_ant
      from t_simple_order
      group by uid, day_id) t1 on t.uid = t1.uid
group by t.uid, t.fisrst_day;