Hive SQL必刷练习题:留存率问题

embedded/2025/1/20 14:05:33/

首次登录算作当天新增,第二天也登录了算作一日留存。可以理解为,在10月1号登陆了。在10月2号也登陆了,那这个人就可以算是在1号留存

今日留存率 = (今日登录且明天也登录的用户数) / 今日登录的总用户数 * 100%

在这里插入图片描述

解决思路:

​ 这类问题主要借助left join,根据原表的数据,先去找到每个用户最先登录的时间,这个就是通过对用户group by,然后搜索date_format(min(login_ts),‘yyyy-MM-dd’)。这样就得到一个表,第一列是用户id,第二列就是每一个用户第一次登录的日期。

​ 之后用这个新表,left join原表数据,但是这个条件不仅是id相等,还要有个t1.user_id=t2.user_id and datediff(date_format(t2.login_ts,‘yyyy-MM-dd’),t1.first_login)=1

说实话,这个多条件join的还确实没遇到过。这样符合条件的就会被增加到后面,不符合的赋空值。【当然也可以不多条件join,直接就是两个表的user_id一样,那这样再计算新增人数和留存人数的时候,就不能直接通过count(列名字)来计算了,还需要去重和判断天数关系】比如下图这样

在这里插入图片描述

​ 这样就可以根据first_login分组group by计算count(t1.id),就是每日新增人数,然后count(连接上的表的列信息),就是后续一天也登陆的人数。

代码:

sql">selectt3.first_login,t3.register,t3.remain_1/t3.register retention
from(selectt1.first_login,count(t1.user_id) register,count(t2.user_id) remain_1from(selectuser_id,date_format(min(login_ts),'yyyy-MM-dd')   first_loginfromuser_login_detailgroup byuser_id)t1left joinuser_login_detail t2ont1.user_id=t2.user_id and datediff(date_format(t2.login_ts,'yyyy-MM-dd'),t1.first_login)=1group byt1.first_login
)t3

总结一下:

其实这类问题的关键在于,你要想办法将每个用户的最初登录时间和第二天登录时间这两个信息,放到一行中。这就是先求出来初次登陆时间后,然后借助这个表进行left join,之后再此基础上以最初登录时间进行分组group by,再用聚合函数即可。


http://www.ppmy.cn/embedded/155504.html

相关文章

电机驱动-标准库和HAL库

一、标准库 Motor.c-标准库 #include "stm32f10x.h" // Device header #include "PWM.h"/*** 函 数:直流电机初始化* 参 数:无* 返 回 值:无*/ void Motor_Init(void) {RCC_APB2PeriphClockCmd…

报告分享 | 大语言模型安全和隐私研究综述

大语言模型的强大能力和广泛应用引发了大量的相关研究,尤其是其在安全和隐私方面所带来的问题。本报告以 2024 年发表在《High-Confidence Computing》期刊上的一篇综述论文为核心,介绍大语言模型安全和隐私研究概况。 1 背景和动机 大型语言模型&#…

Three.js+Vue3+Vite应用lil-GUI调试开发3D效果(三)

前期文章中我们完成了创建第一个场景、添加轨道控制器的功能,接下来我们继续阐述其他的功能,本篇文章中主要讲述如何应用lil-GUI调试开发3D效果,在开始具体流程和步骤之前,请先查看之前的内容,因为该功能必须在前期内容…

vue2配置跨域后请求的是本机

这个我来说明一下,因为我们公司的后端设置解决了跨域问题,所以我有很久没有看相关的内容了,然后昨天请求了需要跨域的接口,请求半天一直不对,浏览器显示的是本机地址,我以为是自己配置错了,后面…

使用Pytorch完成图像分类任务

使用Pytorch完成图像分类任务_pytorch_Benron-GitCode 开源社区

python实现pdf转word和excel

一、引言   在办公中,我们经常遇收到pdf文件格式,因为pdf格式文件不易修改,当我们需要编辑这些pdf文件时,经常需要开通会员或收费功能才能使用编辑功能。今天,我要和大家分享的,是如何使用python编程实现…

AUTOSAR从入门到精通-自动驾驶测试技术

目录 前言 算法原理 测试场景定义与作用 测试场景要素 测试场景分类 场景信息提取与挖掘方法 自动驾驶感知测试分类 自动驾驶图像系统测试 自动驾驶激光雷达系统测试 自动驾驶融合感知系统测试 自动驾驶仿真测试 1. 功能安全 2. 预期功能安全 3. 软件测试 4.敏捷…

matlab实现了一个完整的语音通信系统的模拟,包括语音信号的读取、编码(PCM 和汉明码)、调制

% step 1: 读入语音信号,并进行归一化处理 [audio, fs] = audioread(D:\txyp4.m4a); len_speech = length(audio); % 计算语音信号的采样点个数