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

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

在实现一个留存业务需求时，碰到了一个难题，我需要提供展示一个按照如下图格式的数据，
在这里插入图片描述
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
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
hive sql用户留存计算
ViaNnAljj的博客
04-22 377
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 7893
第一种方式 第二种方式
hive支持sql大全（1-3:关系运算/数学运算/逻辑运算）
Octemull的博客
07-27 8387
一、关系运算：1. 等值比较: =          语法：A=B          操作类型：所有基本类型          描述: 如果表达式A与表达式B相等，则为TRUE；否则为FALSE          举例：          hive>select 1 from lxw_dual where 1=1;          1 2. 不等值比较:
Hive留存计算
colby_chenlun的博客
09-06 1004
准备表 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 300
日常写代码中发现，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 ...
php-thrift-sql：一个通过Thrift连接到Hive或ImpalaPHP库
01-30
PHP ThriftSQL ThriftSQL.phar存档旨在提供对PHPSQL-on-Hadoop框架的访问。 它将Thrift和各种服务包捆绑在... 这允许一次处理一个大型记录的大型结果数据集，从而最大限度地减少了PHP的内存消耗。 // Load this librequ
1、通过亿级数据量在hive和impala中查询比较text、orc和parquet性能表现（一）
最新发布
05-29
1、通过亿级数据量在hive和impala中查询比较text、orc和parquet性能表现（一） 网址：https://blog.csdn.net/chenwewi520feng/article/details/130465139 本文通过在hdfs中三种不同数据格式文件存储相同数量的数据，...
Impala和Hive的关系（详解）
01-27
Impala与Hive都是构建在Hadoop之上的数据查询工具各有不同的侧重适应面，但从客户端使用来看Impala与Hive有很多的共同之处，如数据表元数 据、ODBC/JDBC驱动、SQL语法、灵活的文件格式、存储资源池等。Impala与Hive...
Impala与Hive的比较
03-01
Impala是Cloudera在受到Google的Dremel启发下开发的实时交互SQL大数据查询工具，Impala没有再使用缓慢的Hive+MapReduce批处理，而是通过使用与商用并行关系数据库中类似的分布式查询引擎（由QueryPlanner、Query...
Impala 3.4 SQL查询之重写（二）
skyyws的博客
12-29 705
在上一篇文章中，我们介绍了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 3384
impala字符串函数与时间函数
NULLIF()、NULLIFZERO()、 ZEROIFNULL()用法
SunWuKong_Hadoop的博客
12-16 9087
原文： 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 | +-----+ --得到-...
HiveSQL用户留存率
asd623444055的博客
06-17 1441
用户留存率
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; 实际场景：   拼接成各种类型
Impala必要知识总结
isea_you's blog
12-10 1699
目录 impala的架构： impala的内外部操作： 相对于Hive，impala的不同： impala的查询： impala的压缩和存储： impala的架构： Impala自身包含三个模块：Impalad、Statestore和Catalog，除此之外它还依赖Hive Metastore和HDFS impala的内外部操作： impala分为外部操作和内部操作： 外...
impala mysq的不等于查询
cheney20121的专栏
08-03 2674
sql中的等于和不等于， ‘=’ ,’!=’,’&lt;&gt;’,’is null’…. 不等于：&lt;&gt; ,!=,~= ,^= 这四个符号据说都可以在oracle中表示不等于，但是试了之后发现&lt;&gt; ,!= ,^=是可以的，~=不行,需要注意的是，只有&lt;&gt;是标准的sql语法，可以移植，其他的都是oracle平台的特性，移植性较差，所以开发中，尽量使用&lt;&g...
Impala 时间戳相减
kuangzhi9124的博客
08-30 1万+
统计用户在线时长时，需用下线时间减去上线时间，数据库显示时间类型为TIMESTAMP，实际显示为2018-08-29 12:50:36.000000000，使用datadiff函数，失败，首先将将这个转换为时间戳，再相减，即可得到 select id,(unix_timestamp(offline_time)-unix_timestamp(online_time))`在线时长/秒` From ...
impala数据库 写sql 查找一个字符串中出现某个字符的次数
05-22
可以使用Impala内置的字符串函数 `regexp_count` 来实现查询一个字符串中某个字符出现的次数。例如，假设有一个名为 `mytable` 的表，其中包含一个名为 `mycolumn` 的字符串类型列，可以使用以下SQL查询语句来获取某个字符在 `mycolumn` 中出现的次数： ``` SELECT regexp_count(mycolumn, 'your_char') as count FROM mytable; ``` 其中，`your_char` 是你要查找的字符。该查询将返回一个名为 `count` 的列，其中包含 `mycolumn` 中 `your_char` 出现的次数。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值