HiveSql面试题56：即时订单比例问题

目录

0 题目需求

1 数据准备

2 数据分析

3 小结

---

0 题目需求

订单配送中，顾客有时候希望自定配送日期，如果期望配送日期和下单日期相同，称为即时订单，如果配送日期和下单日期不同，称为计划订单。

每个用户最早下单的时间被称为首单。

请求出每个用户的首单为即时订单的比例，保留两位小数，以小数形式显示。

表结构

配送信息表

字段名	字段类型	字段含义
delivery_id	String	配送订单id
user_id	String	用户id
order_date	date	下单日期
custom_date	date	顾客希望的配送日期

1 数据准备

（1）建表

DROP TABLE IF EXISTS delivery_info;
CREATE TABLE delivery_info (
  `delivery_id` string ,
  `user_id` string ,
  `order_date` string ,
  `custom_date` string 
) COMMENT '邮寄信息表';

（2）插入数据

INSERT INTO delivery_info 
(
delivery_id,
user_id,
order_date,
custom_date
)
VALUES ('1','1','2021-08-01','2021-08-02')
,('2','2','2021-08-02','2021-08-02')
,('3','1','2021-08-11','2021-08-12')
,('4','3','2021-08-24','2021-08-24')
,('5','3','2021-08-21','2021-08-22')
,('6','2','2021-08-11','2021-08-13')
,('7','4','2021-08-09','2021-08-09');

1       1       2021-08-01      2021-08-02
2       2       2021-08-02      2021-08-02
3       1       2021-08-11      2021-08-12
4       3       2021-08-24      2021-08-24
5       3       2021-08-21      2021-08-22
6       2       2021-08-11      2021-08-13
7       4       2021-08-09      2021-08-09
Time taken: 0.146 seconds, Fetched: 7 row(s)

2 数据分析

第一步：需求明确

目标：每个用户的首单中即时订单的比例，保留两位小数

（1）首单的定义：每个用户最早下单的时间。min(time)

  (2)  即时订单的定义：配送日期和下单日期相同

（3）落脚点：最终求的是订单的比例，且保留两位小数

（4）维度：用户

需求疑问点：这里的比例指的是按条件所生成的订单数占全部订单的比例还是，按条件所生成的订单数占用户订单数的比例，如果是后者的话，题目限定了为首单，那么满足条件的话要么数量为1，要么为0,,1比上用户所有订单数，这样好像分析的意义不大，本题暂且按前者去求。

方法：窗口函数进行辅助计算，根据条件生成条件标签

第二步：求首单日期

select *
     ,min(order_date) over(partition by user_id) as first_order_date --打标签,过滤出首单的信息
from delivery_info

delivery_info.delivery_id       delivery_info.user_id   delivery_info.order_date        delivery_info.custom_date       first_order_date
1       1       2021-08-01      2021-08-02      2021-08-01
3       1       2021-08-11      2021-08-12      2021-08-01
2       2       2021-08-02      2021-08-02      2021-08-02
6       2       2021-08-11      2021-08-13      2021-08-02
4       3       2021-08-24      2021-08-24      2021-08-21
5       3       2021-08-21      2021-08-22      2021-08-21
7       4       2021-08-09      2021-08-09      2021-08-09

第三步：求首单中即时订单的标签值，是为‘1’，不是为0

select *
    ,case when first_order_date = order_date and order_date=custom_date then '1' else '0' end as flg
from  
(select *
     ,min(order_date) over(partition by user_id) as first_order_date --打标签,过滤出首单的信息
from delivery_info
) t

t.delivery_id   t.user_id       t.order_date    t.custom_date   t.first_order_date      flg
1               1               2021-08-01      2021-08-02      2021-08-01              0
3               1               2021-08-11      2021-08-12      2021-08-01              0
2               2               2021-08-02      2021-08-02      2021-08-02              1
6               2               2021-08-11      2021-08-13      2021-08-02              0
4               3               2021-08-24      2021-08-24      2021-08-21              0
5               3               2021-08-21      2021-08-22      2021-08-21              0
7               4               2021-08-09      2021-08-09      2021-08-09              1

第三步：根据分组条件flg求出满足条件的订单数及全部的订单数

select flg
       ,count(case when flg=1 then delivery_id else null end) over(partition by flg) as flg_cnt
       ,count(delivery_id) over()  as all_cnt
from
(select *
    ,case when first_order_date = order_date and order_date=custom_date then '1' else '0' end as flg
from  
(select *
     ,min(order_date) over(partition by user_id) as first_order_date --打标签,过滤出首单的信息
from delivery_info
) t
) t

flg     flg_cnt all_cnt
0       0       7
0       0       7
0       0       7
0       0       7
0       0       7
1       2       7
1       2       7
Time taken: 2.245 seconds, Fetched: 7 row(s)

第四步：求出比例，并去重。最终SQL如下：

select
  max(cast(flg_cnt / all_cnt as decimal(18, 2))) as ratio
from
  (
    select
      flg,
      count(
        case
          when flg = 1 then delivery_id
          else null
        end
      ) over(partition by flg) as flg_cnt,
      count(delivery_id) over() as all_cnt
    from
      (
        select
          *,
          case
            when first_order_date = order_date
            and order_date = custom_date then '1'
            else '0'
          end as flg
        from
          (
            select
              *,
              min(order_date) over(partition by user_id) as first_order_date --打标签,过滤出首单的信息
            from
              delivery_info
          ) t
      ) t
  ) t
where
  flg = 1
group by
  flg

OK
ratio
0.29
Time taken: 3.263 seconds, Fetched: 1 row(s)

3 小结

本文分析一种即时订单比例问题分析方法，采用按照条件生成标签的形式求解，当然本题也可以用关联的形式求解，读者可自行尝试