写一个包含多个事件四则运算的留存SQL ——impala hive

已于 2022-05-29 10:15:29 修改
阅读量1.7k 收藏
点赞数
分类专栏： SQL
于 2022-02-26 16:16:12 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/moelimoe/article/details/123140525
版权

SQL查询 留存模型 四则运算 事件分析 数据聚合
关键词由CSDN通过智能技术生成

在实现一个留存业务需求时，碰到了一个难题，我需要提供展示一个按照如下图格式的数据，
在这里插入图片描述
day 1 ~ day n的第一行是留存用户数量，第二行是一个由多个事件组合执行四则算术运算得到的复合数值，这里碰到的难点主要是第二行的计算，如果只想查看第二行的解决方法可以点击这里

由于数据传输速率受限，我不能使用先查询出所有数据然后在代码里处理数据的方法，因此我需要在sql查询中尽量完成所有聚合计算以减少查询返回的行数

留存模型采用的是经典模型（Classic retention）留存用户的数量都是在各天day n独立计算的

这里day 1~day n第一行计算新用户留存数量，第二行的小数计算留存的新用户中某个混合事件的表现，混合事件可以是由某一事件计算得到的值或者由多个事件进行四则运算得到的组合事件的值，第二行的值计算是这篇文章要讲的重点

例如求某个活动事件有两个入口entrance_a和entrance_b，结束通关标识事件为event_over

假设事件名称event_name为"activety_1"，用户表为t_user，事件表为t_event
t_user表的数据是这样的
在这里插入图片描述
t_event表的数据是这样的
在这里插入图片描述

1

计算第一行, 也就是计算经典留存模型day 1~day n的留存用户量，可以用t_user和t_event的join和case when语句实现：

with temp_user as (
select distinct 
		`uid`, 
		to_date(`firstday`) as `firstday`
from `t_user` 
where firstday >= "2022-02-01" and firstday < "2022-02-04"	-- 02-01 ~ 02-03的新用户
),
temp_event as (
select distinct
	`uid`,
	to_date(`event_date`) as `event_date`
from `t_event` 
where event_name = "activity_1" 
and event_date >= "2022-02-01" 
and event_date < "2022-02-07"   -- 02-03往后推3天-->day 3是02-06
)
select  
`firstday`, 
Count(distinct a.uid) as `new_user`,
Count(distinct case when event_date = date_add(firstday, 1) then a.uid end) as `day 1`,
Count(distinct case when event_date = date_add(firstday, 2) then a.uid end) as `day 2`,
Count(distinct case when event_date = date_add(firstday, 3) then a.uid end) as `day 3`
from temp_user a left join temp_event b on a.uid = b.uid
group by firstday

用以上sql语句查询得到的结果：
在这里插入图片描述
格式跟开头的图中的表格保持了一致，数值稍微验证一下可知没有问题，求第一行的留存用户数相对较简单

2

计算第二行的值，我想计算事件activity_1在day 1 ~ day n的通关表现，具体来说就是要计算出day 1~day n各留存用户的(entrance_a + entrance_b)/event_over值

现在假设事件表t_event_1的内容是这样的
在这里插入图片描述
用户表t_user同之前的不变
在这里插入图片描述
要计算出day 1~day n各留存用户的(entrance_a + entrance_b)/event_over的表现，结果展示格式类似下面这张图
在这里插入图片描述
除了要按day 1 ~ day n展示数据外还涉及到属性entrance_a, entrance_b等的聚合计算，使用分析函数（Analytics Function）并不能降低得到的行数，这里我采用了先把要统计的数据（entrance_a, entrance_b, event_over）先分别计算出来按firstday和event_date分组成行，然后再利用case when和求和语句把算出来的结果聚合到对应的day n，写出来的sql如下

-- 查询新用户
with temp_user as (
select distinct 
		`uid`, 
		to_date(`firstday`) as `firstday`
from `t_user` 
where firstday >= "2022-02-01" and firstday < "2022-02-04"	-- 02-01 ~ 02-03的新用户
),
-- 按firstday和event_date分组统计各个要查询的值
temp_event_1 as (
select 
  a.uid,
  to_date(a.firstday) `firstday`,
  to_date(b.event_date) `event_date`,
  count(case when b.entrance = "a" then 1 end) as entrance_a,
  count(case when b.entrance = "b" then 1 end) as entrance_b,
  count(case when b.event_status = "event_over" then 1 end) as event_over
from t_user a left join t_event_1 b on a.uid = b.uid
  and to_date(b.event_date) between date_add(a.firstday, 1) and date_add(a.firstday, 3)
  and (b.entrance in ("a", "b") OR b.event_status = "event_over")    -- 注意不同的列必须要用OR分开查！因为在同一张表里面的限定了event_over就会有一部分的entrance事件查不到！
  and b.event_date >= "2022-02-01"  
  and b.event_date < "2022-02-07" -- 02-03往后推3天 --> 02-06
group by firstday, event_date, a.uid
)
-- 行转列减少返回的数据行数
select  
  evt.firstday,
  count(distinct evt.uid) `new user`,
  (sum(case when date_add(evt.firstday, 1) = evt.event_date then evt.entrance_a else 0 end ) + 
  sum(case when date_add(evt.firstday, 1) = evt.event_date then evt.entrance_b else 0 end )) / 
  sum(case when date_add(evt.firstday, 1) = evt.event_date then evt.event_over else 0 end ) `day 1`,
  (sum(case when date_add(evt.firstday, 2) = evt.event_date then evt.entrance_a else 0 end ) + 
  sum(case when date_add(evt.firstday, 2) = evt.event_date then evt.entrance_b else 0 end )) / 
  sum(case when date_add(evt.firstday, 2) = evt.event_date then evt.event_over else 0 end ) `day 2`,
  (sum(case when date_add(evt.firstday, 3) = evt.event_date then evt.entrance_a else 0 end ) + 
  sum(case when date_add(evt.firstday, 3) = evt.event_date then evt.entrance_b else 0 end )) / 
  sum(case when date_add(evt.firstday, 3) = evt.event_date then evt.event_over else 0 end ) `day 3`
from temp_event_1 evt
group by firstday

查询的结果：
在这里插入图片描述
这里出现的null如果你想在sql中把它们变成0也可以使用zeroifnull()函数将每一列计算结果包住

验证正确性
根据用户表t_user和事件表t_event_1对比查询结果的正确性：
用户表
在这里插入图片描述
先看firstday在02-01的用户，只有0和1两个，对应的day 1就是他们在02-02的(entrance_a+entrance_b)/event_over表现值：
在这里插入图片描述
再检查02-02的用户2和3在day 1, day 2, day3的表现数值，也就是他们分别再02-03, 02-04和02-05的表现数值：
在这里插入图片描述
根据验证结果查询是没有问题的

day 1 ~ day n第二行的解决sql相对来说比较复杂，但是我目前没有想到更好的sql，如果有更好的方法查出结果，欢迎评论告诉我，感谢~

以上的模拟数据，都可以在这个链接(https://demo.gethue.com/hue/home)找到，账号密码登录的时候都是demo，找到Hive下的数据库选择default子库
在这里插入图片描述
Editor选择Hive就可以查询了
在这里插入图片描述
你也可以选择自己创建新表和插入数据模拟，以下是我的建表和插入数据的sql：

-- 用户表创建和值插入
CREATE TABLE `t_user`(
  `uid` int, 
  `firstday` timestamp)
ROW FORMAT SERDE 
  'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  'hdfs://namenode:8020/user/hive/warehouse/t_user'
  ;

INSERT INTO t_user (uid, firstday)
VALUES
  (0, '2022-02-01 00:01:00'),
  (1,  '2022-02-01 00:04:30'),
  (2, '2022-02-02 10:00:00'),
  (3,  '2022-02-02 14:30:00')
;
-- 事件表t_event创建和插入值
CREATE TABLE `t_event`(
  `uid` int,
  `event_name` STRING,
  `event_date` TIMESTAMP
  )
ROW FORMAT SERDE 
  'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  'hdfs://namenode:8020/user/hive/warehouse/t_event'
  ;

INSERT INTO t_event (uid, event_name, event_date)
VALUES
  (1, "activity_1",  '2022-02-01 00:01:00'),
  (1, "activity_1",  '2022-02-01 00:04:30'),
  (1, "activity_1",  '2022-02-02 10:00:00'),
  (1, "activity_1",  '2022-02-02 14:30:00'),
  (2, "activity_1",  '2022-02-02 00:04:30'),
  (2, "activity_1",  '2022-02-03 10:00:00'),
  (2, "activity_1",  '2022-02-04 14:30:00'),
  (3, "activity_1",  '2022-02-05 09:00:00'),
  (3, "activity_1",  '2022-02-05 12:30:00'),
  (0, "activity_1",  '2022-02-06 14:30:00')
;

-- 用户表t_event_1创建和值插入
CREATE TABLE `t_event_1`(
  `uid` int,
  `entrance` string,
  `event_status` STRING,
  `event_name` STRING,
  `event_date` TIMESTAMP
  )
ROW FORMAT SERDE 
  'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  'hdfs://namenode:8020/user/hive/warehouse/t_event_1'
  ;

INSERT INTO `t_event_1` (uid, entrance, event_status, event_name, event_date)
VALUES
  (1, "a", "event_over", "activity_1",  '2022-02-01 00:01:00'),
  (1, "b", "event_over", "activity_1",  '2022-02-01 00:04:30'),
  (1, "a", "event_failed", "activity_1",  '2022-02-02 10:00:00'),
  (1, "b", "event_failed", "activity_1",  '2022-02-02 14:30:00'),
  (1, "a", "event_over", "activity_1",  '2022-02-06 14:30:00'),
  (2, "a", "event_over", "activity_1",  '2022-02-02 14:30:00'),  
  (2, "a", "event_failed", "activity_1",  '2022-02-02 00:04:30'),
  (2, "b", "event_over", "activity_1",  '2022-02-03 10:00:00'),
  (2, "a", "event_over", "activity_1",  '2022-02-04 14:30:00'),
  (2, "b", "event_failed", "activity_1",  '2022-02-05 16:30:00'),
  (3, "a", "event_over", "activity_1",  '2022-02-05 09:00:00'),
  (3, "b", "event_over", "activity_1",  '2022-02-02 00:04:30'),
  (3, "b", "event_over", "activity_1",  '2022-02-05 12:30:00'),
  (0, "a", "event_over", "activity_1",  '2022-02-06 14:30:00'),
  (0, "a", "event_over", "activity_1",  '2022-02-02 14:30:00'),  
  (0, "b", "event_over", "activity_1",  '2022-02-02 00:04:30')
;

注意你登录的session只会保持一段时间，大概十几分钟或更多？，一般你可以通过re-create session来重新打开并继续执行sql查询，如果re-create没有效果就只能重新登录了
在这里插入图片描述

Moelimoe
关注
专栏目录
HiveSQL用户留存率
asd623444055的博客
06-17 1557
用户留存率
数仓面试|四个在工作后才知道的SQL密技
jmx_bigdata的博客
08-07 337
SQL是大数据从业者的必备技能，大部分的大数据技术框架也都提供了SQL的解决方案。可以说SQL是一种经久不衰、历久弥新的编程语言。尤其是在数仓领域，使用SQL更是家常便饭。本文会分享四个在面试和工作中常用的几个使用技巧，具体包括： 日期与期间的使用 临时表与Common Table Expression (WITH) Aggregation 与CASE WHEN的结合使用 Window Function的其他用途 数仓？不就是写写SQL吗… [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来
hive sql用户留存计算
ViaNnAljj的博客
04-22 412
select first_day, by_day, count(orderid) as total_num_of_order from (select uuid, first_day, activate_day, orderid, datediff(activate_day,first_day) as by_day from (select t1.uuid, from_unixtime(unix_timestamp(t1.dt,'yyyymmdd'),'yyyy-mm-dd.
impala 中的case when then 语法
zy103118的博客
09-19 8037
第一种方式 第二种方式
hive-sql-常用函数-留存分析-行列转换-连续登录
wenwen1542的博客
02-28 390
hive-sql-常用函数-留存分析-行列转换-连续登录。
hive支持sql大全（1-3:关系运算/数学运算/逻辑运算）
Octemull的博客
07-27 8470
一、关系运算：1. 等值比较: =          语法：A=B          操作类型：所有基本类型          描述: 如果表达式A与表达式B相等，则为TRUE；否则为FALSE          举例：          hive>select 1 from lxw_dual where 1=1;          1 2. 不等值比较:
大数据 hive 实战数据
04-07
在大数据处理领域，Hive是一个极其重要的工具，它被广泛应用于大数据分析和数据仓库操作。本实战数据集主要涉及两个核心部分：`video`数据和`user`数据，这些都是构建大数据分析模型的基础元素。让我们深入探讨一下...
数仓中几个SQL技巧
weixin_49067219的博客
11-29 304
SQL是大数据从业者的必备技能，大部分的大数据技术框架也都提供了SQL的解决方案。可以说SQL是一种经久不衰、历久弥新的编程语言。尤其是在数仓领域，使用SQL更是家常便饭。本文会分享四个在面试和工作中常用的几个使用技巧，具体包括： 日期与期间的使用 临时表与Common Table Expression (WITH) Aggregation 与CASE WHEN的结合使用 Window Function的其他用途 数仓？不就是写写SQL吗… 第一：日期.
大数据Hadoop、Hive、Kafka、Hbase、Spark等框架面经
m0_46689661的博客
12-05 9897
大数据Hadoop、Hive、Kafka、Hbase、Spark等框架面经
Hive存储格式的坑~
阿尼古
03-26 2842
首先介绍一下我遇到的坑 今天平台巡检过程中，发现了同步任务报错，出现脏数据的问题，后来定位到是因为数据为空值，导致脏数据的问题，那么问题来了，怎么会出现空值呢？ 寻求根源，表的数据是从哪里来的，数据格式是怎样的呢？ SQL任务 CREATE TABLE IF NOT EXISTS xxxx ( stat_date STRING COMMENT...
Hive留存计算
colby_chenlun的博客
09-06 1044
准备表 CREATE EXTERNAL TABLE ods_app_log ( sdk_ver string ,time_zone string ,commit_id string ,commit_time string ,pid string ,app_token string ,app_id string ,device_id...
hive不能连续比较运算
IKnowNothinglee的博客
09-23 331
日常写代码中发现，hive不能连续比较运算，记录一下： 错误写法： hive> select 1<2<3; OK _c0 true Time taken: 0.142 seconds, Fetched: 1 row(s) hive> select 1<7<3; OK _c0 true Time taken: 0.131 seconds, Fetched: 1 ...
Impala 3.4 SQL查询之重写（二）
skyyws的博客
12-29 732
在上一篇文章中，我们介绍了Impala基本的SQL解析流程。本文我们将跟大家一起看下Impala中的一些SQL重写规则。
impala常用数值函数大全（超详细）
hua_ge_zui_liang的博客
09-29 1万+
本文目录数值函数函数列表 数值函数 Impala中数学函数用来执行数值计算，比如基本加法，减法，乘法和除法及更复杂的运算 函数列表 abs(numeric_type a) ==>返回参数的绝对值 --得到-12的绝对值 [master:21000] > select abs(-12) as abs; +-----+ | abs | +-----+ | 12 | +-----+ --得到-12.5的绝对值 [master:21000] > select abs(-12.5) as a
Impala常用函数
ZYN7979的博客
01-30 3773
impala字符串函数与时间函数
NULLIF()、NULLIFZERO()、 ZEROIFNULL()用法
SunWuKong_Hadoop的博客
12-16 9345
原文： Purpose: Returns 0 if the numeric expression evaluates to NULL, otherwise returns the result of the expression. Usage notes: Used to avoid unexpected results due to unexpected propagation of
Impala 数值函数大全
热门推荐
柯西柯西Cauchy
05-02 2万+
Impala数学函数 Impala中数学函数用来执行数值计算，比如基本加法，减法，乘法和除法及更复杂的运算 函数列表 abs(numeric_type a) 返回参数的绝对值 --得到-12的绝对值 [master:21000] &amp;gt; select abs(-12) as abs; +-----+ | abs | +-----+ | 12 | +-----+ --得到-...
impala常用语法
12-25 1万+
1、语法：group_concat select city_id, group_concat(concat(type,”,”,cast(count as string)),”#”) from tmp_park_site_device_count_type_day where p_date=20170926 group by city_id; 实际场景：   拼接成各种类型
HiveSQL题——用户留存数
最新发布
爱吃辣条的博客
07-28 761
HiveSQL题——用户留存数
Hadoop生态：Hive与Impala SQL查询系统对比分析
"基于Hadoop的各大类SQL查询系统对比，包括Hive和Impala的详细介绍" 在大数据处理领域，Hadoop已经成为事实上的标准框架，而Hive和Impala是两个非常重要的类SQL查询系统，它们提供了对大规模数据的便捷查询和分析...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值