Hive SQL 案例：按周订单数及用户数

更新时间：2021-02-20 21:28:46 标签：hive sql 订单

提示

Hive SQL 教程编写中，使用过程中有任何建议，提供意见、建议、纠错、催更加微信 gairuo123。欢迎关注本教程作者新书《深入浅出Pandas：利用Python进行数据处理与分析》。作者开办 Python 数据分析培训，详情 Python 数据分析培训。

电商业务小周期一般为一周，这和人们的生活作息规律有关系。本需求是将订单表中的订单流水按自然周进行统计，给出订单数和用户数。

源数据

数据表位于 dwd.order_detail：

p_day	uid	order_id	create_time	sku_id
20190410	23424	3325264322	2019-04-10 14:55:37.300	1111
20190513	454121	9725372353	2019-05-13 13:54:17.300	23421
20190511	4234	5345433525	2019-05-11 10:15:31.300	21322
20190315	32546	5354378679	2019-03-15 08:01:41.667	14325
20190515	2525	6436438692	2019-05-15 19:05:55.000	1111

各字段说明：

p_day：hive 库以日期分片，下单日期
uid：用户的注册 id
order_id：下单订单号
create_time：订单创建时间，不重复
sku_id：购买商品 ID

数据表是一个以订单-商品为粒度的流水表。

需求分析

最终需要给出的数据如下：

周	开始日期	结束日期	订单数	用户数
1	20200101	20200104	324	23424
2	20200105	20200111	3242	23424

字段定义：

周：周数, 周日为本周第一天（索引）
订单数：本周的订单数
用户数：本周的去重用户数

思路分析：

将订单下单时间转化为当年的周数
然后按周数进行 GROUP BY

编写步骤

这儿重点的是周日为本周第一天这个需求：

weekofyear(date_add(create_time, 1)) AS week_id

代码解释：

weekofyear 是返回当前日期是本年的第几周，从周一开始
date_add 时间增加一天让周初的日期移到周日，即原本是周日，提前一天进入周一，新的一周

组合出新表：

SELECT p_day,
      weekofyear(date_add(create_time, 1)) AS week_id,
      uid,
      order_id
FROM dwd.order_detail
WHERE p_day >= 20200101
 AND p_day < 20210101

然后再按 week_id 分组给出最终代码。

最终代码

SELECT order_with_week.week_id AS week_id, -- 周数
       min(order_with_week.p_day) AS b_day, -- 当周开始日期
       max(order_with_week.p_day) AS e_day, -- 当周结束日期
       COUNT(DISTINCT order_with_week.uid) AS uid_qty, -- 用户数
       COUNT (DISTINCT order_with_week.order_id) AS order_qty -- 订单数
FROM
  (SELECT p_day,
          weekofyear(date_add(create_time, 1)) AS week_id,
          uid,
          order_id
   FROM dwd.order_detail
   WHERE p_day >= 20200101
     AND p_day < 20210101) AS order_with_week
GROUP BY order_with_week.week_id

其中可以注意的点有：

最外层 SELECT 均有 AS
字段命名见名知义：week_id、order_qty、b_day
虚表命名见名知义，order_with_week 或 o_week , 保留 as
虚表内自用字段不带别名
按逻辑缩进

总结

这里的核心点是掌握 weekofyear() 和 date_add() 的使用方法。

<< 指定商品带来的复购 Hive SQL 教程按 UTM 串统计访问情况 >>

<< 指定商品带来的复购 Hive SQL 案例按 UTM 串统计访问情况 >>