设计一个网站,株洲seo优化哪家好,c 网站开发需要学什么软件有哪些,wordpress添加新建标签在实现一个留存业务需求时#xff0c;碰到了一个难题#xff0c;我需要提供展示一个按照如下图格式的数据#xff0c; day 1 ~ day n的第一行是留存用户数量#xff0c;第二行是一个由多个事件组合执行四则算术运算得到的复合数值#xff0c;这里碰到的难点主要是第二行的…在实现一个留存业务需求时碰到了一个难题我需要提供展示一个按照如下图格式的数据 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 distinctuid,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.uidand 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_aentrance_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
LOCATIONhdfs://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
LOCATIONhdfs://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
LOCATIONhdfs://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没有效果就只能重新登录了
