相关导航
Hive SQL 案例：按 UTM 串统计访问情况
更新时间：2021-02-25 07:22:24 标签：hive
sql
utm
提示
Hive SQL 教程 编写中，使用过程中有任何建议，提供意见、建议、纠错、催更加微信 gairuo123。欢迎关注本教程作者新书《深入浅出Pandas：利用Python进行数据处理与分析》 。作者开办 Python 数据分析培训，详情 Python 数据分析培训。
UTM （全称是 Urchin Traffic Monitor），是一个流量监控方法，它通过 UTM 编码对流量的来源进行数据统计。本需求是通过查询库中的 UTM 串来看哪个业务访问量高。
源数据
数据表位于 dwd.pv_log，以下为其中涉及的部分：
| uuid | ctime | utm | p_day |
|---|---|---|---|
| 8675 | 2020-05-08 11:09:19.0 | play.c.get.clk.32.56.66 | 20200508 |
| 9726 | 2020-05-08 12:09:19.0 | buy.c.get.clk.32.56.66 | 20200508 |
| 3587 | 2020-05-08 13:09:19.0 | share.t.get.clk.32.56.66 | 20200508 |
| 9432 | 2020-05-08 09:09:19.0 | buy.c.get.clk.87.56.66 | 20200508 |
各字段说明：
- p_day：hive 库以日期分片，事件发生的日期
- uuid：用户访问的设备编号
- ctime：访问浏览时间
- utm： utm 串，用
.隔开，分 7 部分
需求分析
最终需要给出的数据如下：
| 业务 | pv | uv | last_time |
|---|---|---|---|
| play.c | 424 | 21 | 09:09:19 |
| buy.c | 342 | 12 | 09:09:19 |
字段定义：
- 业务：utm 前两位为维度，如表中所示
- pv：本周的订单数
- uv：本周的去重用户数
- last_time：最后一次访问时间，格式要求 09:09:19
同时时间要求 20200508 那一天的数据。
思路分析：
- 拆分 utm, 取前两位进行分组
- 然后聚合，时间取最大，并转换为指定格式
编写步骤
先进行 utm 拆分：
substring_index(utm, '.', 2) AS utm_name
substring_index 可以指定分隔符，然后取多少位。
然后再分组出基础数据：
SELECT substring_index(utm, '.', 2) AS utm_name,
uuid AS uuid,
date_format(ctime,'HH:mm:ss') AS c_time
FROM dwd.pv_log
WHERE p_day = 20200508
AND utm <> ''
AND utm IS NOT NULL
最终加工出数据。
最终代码
SELECT by_utm.utm_name AS name, -- 业务名称
count(by_utm.uuid) AS pv, -- PV
count(DISTINCT by_utm.uuid) AS uv, -- UV
max(by_utm.c_time) AS last_time -- 最后时间
FROM
(SELECT substring_index(utm, '.', 2) AS utm_name,
uuid AS uuid,
date_format(ctime,'HH:mm:ss') AS c_time
FROM dwd.pv_log
WHERE p_day = 20200508
AND utm <> ''
AND utm IS NOT NULL) AS by_utm
GROUP BY by_utm.utm_name
ORDER BY last_time DESC
总结
这里的核心点是掌握 substring_index() 和 date_format() 的使用方法。
