1.每日SQL----2024/11/7

news/2024/11/8 12:46:20/

题目:

计算用户次日留存率,即用户第二天继续登录的概率

表:

iddevice_iddate
121382024-05-03
232142024-05-09
332142024-06-15
465432024-08-13
523152024-08-13
623152024-08-14
723152024-08-15
832142024-05-09
932142024-08-15
1065432024-08-13
1123152024-08-13
1223152024-08-14
1323152024-08-15
1432142024-08-16
1532142024-08-18
1665432024-08-13
  • id 用户唯一标识符
  • device_id 用户登录使用的设备标识符
  • date 用户登录日期

你应该返回的结果:

avg_ret
0.3000

请认真思考后作答


解题思路

  1. 去重数据:首先,我们需要从原始数据中选择每个设备ID (device_id) 和对应的登录日期 (date),并去除重复项。这一步是为了确保每个设备在每个日期只有一条记录。

  2. 查找下一个登录日期:使用窗口函数 LEAD(),我们可以找到每个设备的下一个登录日期。LEAD() 函数会返回当前行之后的指定偏移量的行的值。这里我们将 LEAD() 应用在 date 列上,按 device_id 分区,并按 date 排序。

  3. 计算日期差:对于每一对连续的登录日期(当前日期 date1 和下一个日期 date2),我们使用 DATEDIFF() 函数计算它们之间的天数差。如果天数差为1,则表示用户在次日登录了。

  4. 计算次日留存率:使用 IF 语句,如果 DATEDIFF(date2, date1) = 1,则返回1,否则返回0。然后,对所有的这些0和1取平均值,得到次日留存率。

代码如下

sql">select avg(if(datediff(date2, date1)=1, 1, 0)) as avg_ret
from (selectdistinct device_id,date as date1,lead(date) over (partition by device_id order by date) as date2from (select distinct device_id, datefrom user_detail) as uniq_id_date
) as id_last_next_date

运行结果如下

在这里插入图片描述

本题的重点是lead开窗函数的使用,你学会了吗?下期见~

后附建表语句

sql">drop table if  exists `user_detail`;
CREATE TABLE `user_detail` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`date` date NOT NULL
);INSERT INTO user_detail VALUES(1,2138,'2024-05-03');
INSERT INTO user_detail VALUES(2,3214,'2024-05-09');
INSERT INTO user_detail VALUES(3,3214,'2024-06-15');
INSERT INTO user_detail VALUES(4,6543,'2024-08-13');
INSERT INTO user_detail VALUES(5,2315,'2024-08-13');
INSERT INTO user_detail VALUES(6,2315,'2024-08-14');
INSERT INTO user_detail VALUES(7,2315,'2024-08-15');
INSERT INTO user_detail VALUES(8,3214,'2024-05-09');
INSERT INTO user_detail VALUES(9,3214,'2024-08-15');
INSERT INTO user_detail VALUES(10,6543,'2024-08-13');
INSERT INTO user_detail VALUES(11,2315,'2024-08-13');
INSERT INTO user_detail VALUES(12,2315,'2024-08-14');
INSERT INTO user_detail VALUES(13,2315,'2024-08-15');
INSERT INTO user_detail VALUES(14,3214,'2024-08-16');
INSERT INTO user_detail VALUES(15,3214,'2024-08-18');
INSERT INTO user_detail VALUES(16,6543,'2024-08-13');

http://www.ppmy.cn/news/1545356.html

相关文章

ChatGPT的多面手:日常办公、论文写作与深度学习的结合

ChatGPT,由OpenAI精心打造的大型语言模型,依托于先进的人工神经网络技术,展现了在理解和生成自然语言文本方面的强大能力。该模型的核心设计宗旨是通过对话式交互,为用户带来前所未有的体验,无论是提供信息、答疑解惑&…

k8s图形化显示(KRM)

在master节点 kubectl get po -n kube-system 这个命令会列出 kube-system 命名空间中的所有 Pod 的状态和相关信息,比如名称、状态、重启次数等。 systemctl status kubelet #查看kubelet状态 yum install git #下载git命令 git clone https://gitee.com/duk…

网络安全知识见闻终章 ?

安全知识没有终点!!! 一、软件和硬件设备的类型 1.软件程序的类型 2.硬件设备的类型 二、网络类型、协议、设备、安全 1.网络类型 2.网络协议 常见的网络协议 3、网络设备 a. 网络设备的分类 b. 网络设备的功能 4.网络安全 三、w…

HTML5和CSS3 介绍

HTML5 (HyperText Markup Language 5) 定义 HTML5 是 HTML 的第五个主要版本,它是对前一版本(HTML4 和 XHTML)的重大改进。HTML5 引入了许多新特性,旨在简化网页开发,提高用户体验,并支持更丰富的多媒体和…

Cesium使用flyToBoundingSphere实现倾斜相机视角观察物体

之前有一篇文章介绍如何使用Cesium倾斜相机视角观察物体,后面发现了一个API viewer.camera.flyToBoundingSphere,能直接实现我想要的效果。 所以我封装了一个函数,通过使用 Cesium.Camera.flyToBoundingSphere API,自动调整相机的…

实现自动化数据抓取:使用Node.js操控鼠标点击与位置坐标

在当今信息爆炸的时代,自动化数据抓取技术(也称为“网络爬虫”)对于数据分析与信息挖掘具有重要的作用。本文将介绍如何利用Node.js实现自动化数据抓取,并通过控制鼠标点击与位置坐标的方式,采集页面上指定的新闻数据。…

Jenkins找不到maven构建项目

有的可能没有出现maven这个选项 解决办法:需要安装Maven项目插件 输入​Maven Integration plugin​

Android 音视频合成经验总结

刚刚实现了音视频合成的需求,趁热打铁记录一下遇到的问题 需求描述: 将给定的音频及视频合成一个视频,如果音频时长小于视频,则重复播放;如果大于视频时长则截取到视频结束。 采用的是MediaMuxer、MediaCodec原生方…