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

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

源数据

数据表位于 dwd.order_detail：

各字段说明：

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

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

需求分析

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

字段定义：

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

思路分析：

• 将订单下单时间转化为当年的周数
• 然后按周数进行 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() 的使用方法。

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