相关导航
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() 的使用方法。
