hive统计每日的活跃用户和新用户sql开发(附shell脚本)

简介: hive统计每日的活跃用户和新用户sql开发(附shell脚本)

假如有一个web系统,每天生成以下日志文件:

2020年12月21日数据
192.228.33.6,hunter,2020-12-21 10:30:20,/a
192.228.33.7,hunter,2020-12-21 10:30:26,/b
192.228.33.6,jack,2020-12-21 10:30:27,/a
192.228.33.8,tom,2020-12-21 10:30:28,/b
192.228.33.9,rose,2020-12-21 10:30:30,/b
192.228.33.10,julia,2020-12-21 10:30:40,/c
2020年12月22日数据
192.228.33.22,hunter,2020-12-22 10:30:20,/a
192.228.33.18,jerry,2020-12-22 10:30:30,/b
192.228.33.26,jack,2020-12-22 10:30:40,/a
192.228.33.18,polo,2020-12-22 10:30:50,/b
192.228.33.39,nissan,2020-12-22 10:30:53,/b
192.228.33.39,nissan,2020-12-22 10:30:55,/a
192.228.33.39,nissan,2020-12-22 10:30:58,/c
192.228.33.20,ford,2020-12-22 10:30:54,/c
2020年12月23日数据
192.228.33.46,hunter,2020-12-23 10:30:21,/a
192.228.43.18,jerry,2020-12-23 10:30:22,/b
192.228.43.26,tom,2020-12-23 10:30:23,/a
192.228.53.18,bmw,2020-12-23 10:30:24,/b
192.228.63.39,benz,2020-12-23 10:30:25,/b
192.228.33.25,haval,2020-12-23 10:30:30,/c
192.228.33.10,julia,2020-12-23 10:30:40,/c

需求

1.建立一个表,来存储每天新增的数据(分区表)

      2.统计每天的活跃用户(日活)(需要用户的ip,用户的账号,用户访问时间最早的一条url和时间)

      3.统计每天的新增用户(日新)

实现

建表(存储数据的分区表)

create table t_web_log(ip string,uid string,access_time string,url string)
partitioned by (day string)
row format delimited fields terminated by ',';

导数据

load data local inpath '/root/hivetest/log.21' into table t_web_log partition (day='2020-12-21');
load data local inpath '/root/hivetest/log.22' into table t_web_log partition (day='2020-12-22');
load data local inpath '/root/hivetest/log.23' into table t_web_log partition (day='2020-12-23');

show partitions t_web_log;(查看分区表)

指标统计

每日活跃用户统计

1.建一个保存日活数据的表

create table t_user_active_day(ip string,uid string,first_access string,url string) partitioned by (day string);

2.从日志表中查出日活数据并插入日活数据表

插入21日数据
insert into table t_user_active_day partition(day='2020-12-21')
select ip,uid,access_time,url
from
(
select ip,uid,access_time,url,
row_number() over(partition by uid order by access_time) as rn
from t_web_log
where day='2020-12-21') tmp
where rn=1;
插入22日数据
insert into table t_user_active_day partition(day='2020-12-22')
select ip,uid,access_time,url
from
(
select ip,uid,access_time,url,
row_number() over(partition by uid order by access_time) as rn
from t_web_log
where day='2020-12-22') tmp
where rn=1;
插入23日数据
insert into table t_user_active_day partition(day='2020-12-23')
select ip,uid,access_time,url
from
(
select ip,uid,access_time,url,
row_number() over(partition by uid order by access_time) as rn
from t_web_log
where day='2020-12-23') tmp
where rn=1;

每日新用户统计

思路——将当日活跃用户跟历史用户表关联,找出那些在历史用户表中尚不存在的用户

image.png

1.建历史用户表

create table t_user_history(uid string);

2.建一个存放新用户的表

create table t_user_new_day like t_user_active_day;

3.求出每日的新用户并把数据插入新用户表

21日新用户
insert into table t_user_new_day partition (day='2020-12-21')
select ip,uid,first_access,url
from
(
select a.ip,a.uid,a.first_access,a.url,b.uid as b_uid
from t_user_active_day a
left join t_user_history b on a.uid=b.uid
where a.day='2020-12-21') tmp
where tmp.b_uid is null;
22日新用户
insert into table t_user_new_day partition (day='2020-12-22')
select ip,uid,first_access,url
from
(
select a.ip,a.uid,a.first_access,a.url,b.uid as b_uid
from t_user_active_day a
left join t_user_history b on a.uid=b.uid
where a.day='2020-12-22') tmp
where tmp.b_uid is null;
23日新用户
insert into table t_user_new_day partition (day='2020-12-23')
select ip,uid,first_access,url
from
(
select a.ip,a.uid,a.first_access,a.url,b.uid as b_uid
from t_user_active_day a
left join t_user_history b on a.uid=b.uid
where a.day='2020-12-23') tmp
where tmp.b_uid is null;

4.将每日的新用户插入历史表

21日数据
insert into table t_user_history
select uid
from t_user_new_day
where day='2020-12-21';
22日数据
insert into table t_user_history
select uid
from t_user_new_day
where day='2020-12-22';
23日数据
insert into table t_user_history
select uid
from t_user_new_day
where day='2020-12-23';

加餐

清空表

truncate table t_user_active_day;

修改Linux系统时间

date -s '2020-12-20 00:20:00'

统计日新、日活的shell脚本

vi user_etl.sh

#!/bin/bash
day_str=`date -d '-1 day'+'%Y-%m-%d'`
echo "准备处理$day_str的数据......"
hive_exec=/root/apps/hive-1.2.1/bin/hive
HQL_user_active_day="
insert into table exercise.t_user_active_day partition(day=\"$day_str\")
select ip,uid,access_time,url
from
(
select ip,uid,access_time,url,
row_number() over(partition by uid order by access_time) as rn
from t_web_log
where day=\"$day_str\") tmp
where rn=1;
"
$hive_exec "$HQL_user_active_day"
HQL_user_new_day="
insert into table t_user_new_day partition (day=\"$day_str\")
select ip,uid,first_access,url
from
(
select a.ip,a.uid,a.first_access,a.url,b.uid as b_uid
from t_user_active_day a
left join t_user_history b on a.uid=b.uid
where a.day=\"$day_str\") tmp
where tmp.b_uid is null;
"
$hive_exec "$HQL_user_new_day"
HQL_new_to_history="
insert into table t_user_history
select uid
from t_user_new_day
where day=\"$day_str\;
"
$hive_exec "$HQL_new_to_history"

运行:sh user_etl.sh

相关文章
|
3天前
|
SQL HIVE
【Hive SQL 每日一题】统计用户连续下单的日期区间
该SQL代码用于统计用户连续下单的日期区间。首先按`user_id`和`order_date`分组并去除重复,然后使用`row_number()`标记行号,并通过`date_sub`与行号计算潜在的连续日期。接着按用户ID和计算后的日期分组,排除连续订单数少于2的情况,最后提取连续下单的起始和结束日期。输出结果展示了用户连续下单的日期范围。
|
3天前
|
SQL HIVE
【Hive SQL 每日一题】环比增长率、环比增长率、复合增长率
该文介绍了环比增长率、同比增长率和复合增长率的概念及计算公式,并提供了SQL代码示例来计算商品的月度增长率。环比增长率是相邻两期数据的增长率,同比增长率是与去年同期相比的增长率,复合增长率则是连续时间段内平均增长的速率。文章还包含了一组销售数据用于演示如何运用这些增长率进行计算。
|
3天前
|
SQL HIVE
【Hive SQL】字符串操作函数你真的会用吗?
本文介绍了SQL中判断字符串是否包含子串的几种方法。`IN`函数判断元素是否完全等于给定元素组中的某项,而非包含关系。`INSTR`和`LOCATE`函数返回子串在字符串中首次出现的位置,用于检测是否存在子串。`SUBSTR`则用于提取字符串的子串。`LIKE`用于模糊匹配,常与通配符配合使用。注意`IN`并非用于判断子串包含。
|
3天前
|
SQL 关系型数据库 HIVE
【Hive SQL 每日一题】统计最近1天/7天/30天商品的销量
这段内容是关于SQL查询的示例,目标是统计`sales`表中最近1天、7天和30天的商品销量和销售次数。表结构包含`id`、`product_id`、`quantity`和`sale_date`字段。初始查询方法通过三个独立的子查询完成,但效率较低。优化后的查询使用了`lateral view explode`将数据炸裂,通过一次查询同时获取所有所需时间段的数据,提高了效率。示例中展示了优化前后的SQL代码及结果对比。
|
3天前
|
SQL HIVE
【Hive SQL 每日一题】统计最近7天内连续下单3日的用户量
创建了一个名为`sales`的测试表,包含`user_id`、`product_id`、`quantity`和`sale_date`字段,插入了多条销售数据。需求是找出最近7天内连续下单3天的用户数量。SQL查询通过分组和窗口函数`row_number()`检查日期连续性,最终计算满足条件的唯一用户数。示例结果显示有3名用户符合条件。
|
3天前
|
SQL BI HIVE
【Hive SQL 每日一题】统计用户留存率
用户留存率是衡量产品成功的关键指标,表示用户在特定时间内持续使用产品的比例。计算公式为留存用户数除以初始用户数。例如,游戏发行后第一天有10000玩家,第七天剩5000人,第一周留存率为50%。提供的SQL代码展示了如何根据用户活动数据统计每天的留存率。需求包括计算系统上线后的每日留存率,以及从第一天开始的累计N日留存率。通过窗口函数`LAG`和`COUNT(DISTINCT user_id)`,可以有效地分析用户留存趋势。
|
3天前
|
SQL HIVE 索引
【Hive SQL 每日一题】行列转换
该文介绍了如何使用SQL进行数据的行列转换。首先展示了行转列的例子,通过创建一个学生成绩表,利用`IF`和`SUM`函数按学生ID分组,将每个学生的各科成绩转换为独立列。然后,文章讲述了列转行的需求,利用`LATERAL VIEW`和`POSEXPLODE`将已转换的表格恢复为原始行格式,通过索引匹配过滤笛卡尔积避免错误结果。此外,还提到了使用`UNION ALL`的另一种列转行方法。
|
3天前
|
SQL HIVE
【Hive SQL 每日一题】分组排名取值
创建了一个名为`sales_data`的测试表,包含商品ID、销售额和销售日期。展示了部分示例数据。接着,提供了三个SQL查询:1) 查找每个商品销售额最高的记录;2) 获取每个商品最近和最远的销售记录;3) 求每个商品距今第二近的销售记录。每个查询都利用了窗口函数来处理数据,并给出了相应的查询结果图。
|
3天前
|
SQL 数据挖掘 HIVE
【Hive SQL 每日一题】在线课程学生行为数据分析
该数据分析师任务是分析在线学习平台的学生行为,以优化课程内容和学习体验。提供的数据包括`students`表(含学生ID、姓名、年龄和性别)和`course_activity`表(含活动ID、学生ID、课程ID、活动日期和学习时长)。分析涉及:1) 学生参加的课程数量,2) 课程总学习时长,3) 按性别分组的平均学习时长,4) 学生首次参加的课程及日期,5) 学生最近一次学习的时长,以及6) 参与学生最多的课程。所有查询都使用了SQL,部分涉及窗口函数和分组统计。数据集可在给定链接下载。
|
3天前
|
SQL HIVE UED
【Hive SQL 每日一题】分析电商平台的用户行为和订单数据
作为一名数据分析师,你需要分析电商平台的用户行为和订单数据。你有三张表:`users`(用户信息),`orders`(订单信息)和`order_items`(订单商品信息)。任务包括计算用户总订单金额和数量,按月统计订单,找出最常购买的商品,找到平均每月最高订单金额和数量的用户,以及分析高消费用户群体的年龄和性别分布。通过SQL查询,你可以实现这些分析,例如使用`GROUP BY`、`JOIN`和窗口函数来排序和排名。