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 |
各字段说明:
.
隔开,分 7 部分最终需要给出的数据如下:
业务 | pv | uv | last_time |
---|---|---|---|
play.c | 424 | 21 | 09:09:19 |
buy.c | 342 | 12 | 09:09:19 |
字段定义:
同时时间要求 20200508 那一天的数据。
思路分析:
先进行 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()
的使用方法。