SQL面试题练习 —— 各用户最长的连续登录天数-可间断

目录

  • 1 题目
  • 2 建表语句
  • 3 题解

1 题目


现有各用户的登录记录表t_login_events如下，表中每行数据表达的信息是一个用户何时登录了平台。现要求统计各用户最长的连续登录天数，间断一天也算作连续，例如：一个用户在1,3,5,6登录，则视为连续6天登录。

样例数据

+----------+----------------------+
| user_id  |    login_datetime    |
+----------+----------------------+
| 100      | 2021-12-01 19:00:00  |
| 100      | 2021-12-01 19:30:00  |
| 100      | 2021-12-02 21:01:00  |
| 100      | 2021-12-03 11:01:00  |
| 101      | 2021-12-01 19:05:00  |
| 101      | 2021-12-01 21:05:00  |
| 101      | 2021-12-03 21:05:00  |
| 101      | 2021-12-05 15:05:00  |
| 101      | 2021-12-06 19:05:00  |
| 102      | 2021-12-01 19:55:00  |
| 102      | 2021-12-01 21:05:00  |
| 102      | 2021-12-02 21:57:00  |
| 102      | 2021-12-03 19:10:00  |
| 104      | 2021-12-04 21:57:00  |
| 104      | 2021-12-02 22:57:00  |
| 105      | 2021-12-01 10:01:00  |
+----------+----------------------+

期望结果

+----------+---------------+
| user_id  | max_log_days  |
+----------+---------------+
| 100      | 3             |
| 101      | 6             |
| 102      | 3             |
| 104      | 3             |
| 105      | 1             |
+----------+---------------+

2 建表语句


--建表语句create table if not exists t_login_events
(user_id        int comment '用户id',login_datetime string comment '登录时间'
)comment '直播间访问记录';
--数据插入
INSERT overwrite table t_login_events
VALUES (100, '2021-12-01 19:00:00'),(100, '2021-12-01 19:30:00'),(100, '2021-12-02 21:01:00'),(100, '2021-12-03 11:01:00'),(101, '2021-12-01 19:05:00'),(101, '2021-12-01 21:05:00'),(101, '2021-12-03 21:05:00'),(101, '2021-12-05 15:05:00'),(101, '2021-12-06 19:05:00'),(102, '2021-12-01 19:55:00'),(102, '2021-12-01 21:05:00'),(102, '2021-12-02 21:57:00'),(102, '2021-12-03 19:10:00'),(104, '2021-12-04 21:57:00'),(104, '2021-12-02 22:57:00'),(105, '2021-12-01 10:01:00');

3 题解


  1. 数据去重
select user_id,to_date(login_datetime) as login_date
from t_login_events
group by user_id, to_date(login_datetime)

执行结果

+----------+-------------+
| user_id  | login_date  |
+----------+-------------+
| 100      | 2021-12-01  |
| 100      | 2021-12-02  |
| 100      | 2021-12-03  |
| 101      | 2021-12-01  |
| 101      | 2021-12-03  |
| 101      | 2021-12-05  |
| 101      | 2021-12-06  |
| 102      | 2021-12-01  |
| 102      | 2021-12-02  |
| 102      | 2021-12-03  |
| 104      | 2021-12-02  |
| 104      | 2021-12-04  |
| 105      | 2021-12-01  |
+----------+-------------+
  1. 计算日期差

根据用户分组，使用lag函数获得当前行的上一行数据中的日期，使用datediff函数判断日期当期日期与上一行日期的时间差。

select user_id,login_date,lag(login_date, 1, null) over (partition by user_id order by login_date asc)                       as lag_log_date,datediff(login_date, lag(login_date, 1, null) over (partition by user_id order by login_date asc)) as date_diff
from (select user_id,to_date(login_datetime) as login_datefrom t_login_eventsgroup by user_id, to_date(login_datetime)) t1

执行结果

+----------+-------------+---------------+------------+
| user_id  | login_date  | lag_log_date  | date_diff  |
+----------+-------------+---------------+------------+
| 100      | 2021-12-01  | NULL          | NULL       |
| 100      | 2021-12-02  | 2021-12-01    | 1          |
| 100      | 2021-12-03  | 2021-12-02    | 1          |
| 101      | 2021-12-01  | NULL          | NULL       |
| 101      | 2021-12-03  | 2021-12-01    | 2          |
| 101      | 2021-12-05  | 2021-12-03    | 2          |
| 101      | 2021-12-06  | 2021-12-05    | 1          |
| 102      | 2021-12-01  | NULL          | NULL       |
| 102      | 2021-12-02  | 2021-12-01    | 1          |
| 102      | 2021-12-03  | 2021-12-02    | 1          |
| 104      | 2021-12-02  | NULL          | NULL       |
| 104      | 2021-12-04  | 2021-12-02    | 2          |
| 105      | 2021-12-01  | NULL          | NULL       |
+----------+-------------+---------------+------------+
  1. 判断是否连续，累积求和得到分组id

根据date_diff结果判断是否连续，如果date_diff <= 2则认为连续 我们给赋值为0，否则不连续，赋值为1。

select user_id,login_date,lag_log_date,date_diff,sum(if(date_diff <= 2, 0, 1)) over (partition by user_id order by login_date asc) as group_id
from (select user_id,login_date,lag(login_date, 1, null) over (partition by user_id order by login_date asc)  as lag_log_date,datediff(login_date, lag(login_date, 1, null)over (partition by user_id order by login_date asc)) as date_difffrom (select user_id,to_date(login_datetime) as login_datefrom t_login_eventsgroup by user_id, to_date(login_datetime)) t1) t2

执行结果

+----------+-------------+---------------+------------+-----------+
| user_id  | login_date  | lag_log_date  | date_diff  | group_id  |
+----------+-------------+---------------+------------+-----------+
| 100      | 2021-12-01  | NULL          | NULL       | 1         |
| 100      | 2021-12-02  | 2021-12-01    | 1          | 1         |
| 100      | 2021-12-03  | 2021-12-02    | 1          | 1         |
| 101      | 2021-12-01  | NULL          | NULL       | 1         |
| 101      | 2021-12-03  | 2021-12-01    | 2          | 1         |
| 101      | 2021-12-05  | 2021-12-03    | 2          | 1         |
| 101      | 2021-12-06  | 2021-12-05    | 1          | 1         |
| 102      | 2021-12-01  | NULL          | NULL       | 1         |
| 102      | 2021-12-02  | 2021-12-01    | 1          | 1         |
| 102      | 2021-12-03  | 2021-12-02    | 1          | 1         |
| 104      | 2021-12-02  | NULL          | NULL       | 1         |
| 104      | 2021-12-04  | 2021-12-02    | 2          | 1         |
| 105      | 2021-12-01  | NULL          | NULL       | 1         |
+----------+-------------+---------------+------------+-----------+
  1. 按照用户和group_id 分组，计算每次连续登陆的天数，再根据用户分组计算最大连续天数

首先根据user_id和group_id分组，用datediff计算出出最大登陆日期和最小登陆日期，两者做差+1 得到每次连续登陆的天数。然后按照用户分组，使用max()计算每个用户最大连续天数。

select user_id,max(log_days) as max_log_days
from (select user_id,group_id,datediff(max(login_date), min(login_date)) + 1 as log_daysfrom (select user_id,login_date,lag_log_date,date_diff,sum(if(date_diff <= 2, 0, 1)) over (partition by user_id order by login_date asc) as group_idfrom (select user_id,login_date,lag(login_date, 1, null) over (partition by user_id order by login_date asc)  as lag_log_date,datediff(login_date, lag(login_date, 1, null)over (partition by user_id order by login_date asc)) as date_difffrom (select user_id,to_date(login_datetime) as login_datefrom t_login_eventsgroup by user_id, to_date(login_datetime)) t1) t2) t3group by user_id,group_id) t4
group by user_id

执行结果

+----------+---------------+
| user_id  | max_log_days  |
+----------+---------------+
| 100      | 3             |
| 101      | 6             |
| 102      | 3             |
| 104      | 3             |
| 105      | 1             |
+----------+---------------+

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：http://www.rhkb.cn/news/374343.html

如若内容造成侵权/违法违规/事实不符，请联系长河编程网进行投诉反馈email:809451989@qq.com，一经查实，立即删除！

相关文章

SpringBoot项目架构实战之“网关zuul搭建“

SpringBoot项目架构实战之“网关zuul搭建“

第三章 网关zuul搭建 前言&#xff1a; 1、主要功能 zuul主要提供动态路由&#xff08;内置ribbon实现&#xff09;和过滤&#xff08;可以做统一鉴权过滤器、灰度发布过滤器、黑白名单IP过滤器、服务限流过滤器&#xff08;可以配合Sentinel实现&#xff09;&#xff09;功能…
阅读更多...
css简单易懂的加载动画，看不会算我输好吧

css简单易懂的加载动画，看不会算我输好吧

效果展示 步骤 第一阶段 先准备结构&#xff0c;并且放置12个div&#xff0c;每一个div旋转30*n度&#xff0c; 做一个圆圈 dom <div class"modal"><div class"loading"><div class"item1"></div><div class&quo…
阅读更多...
Vue 项目中 history 路由模式的使用

Vue 项目中 history 路由模式的使用

在最近帮客户开发的一个项目中&#xff0c;由于项目的特殊性&#xff0c;需要用到 Vue 中的 history路由模式。该模式使用时会涉及到“上传白屏”和“刷新 404 问题”。在帮助客户解决这两个问题的过程中&#xff0c;总结问题的解决方案并记录下来&#xff0c;希望能够保留这篇…
阅读更多...
分布式训练

分布式训练

一、分布式计算 跟多GPU不同是&#xff1a;数据不是从主存拿的&#xff0c;是在分布式文件系统拿的&#xff0c;有多个工作站&#xff0c;工作站中有多个GPU&#xff0c;通过网络读取数据到GPU中&#xff0c;GPU通过网络接收到来自参数服务器的参数进行运算计算梯度&#xff0c…
阅读更多...
怎样免费在线文字转语音？5个配音工具一键包揽

怎样免费在线文字转语音？5个配音工具一键包揽

日常在享受有声读物的乐趣时&#xff0c;不知道大家是否也曾渴望将手中的精彩文本以生动的声音演绎出来&#xff1f; 无论是为了自我沉浸&#xff0c;还是为家人朋友创造独特的听觉盛宴&#xff0c;一款支持文本转语音的配音软件都能成为你的得力助手。它不仅能让文字跃然耳边…
阅读更多...
【C++深度探索】全面解析多态性机制(一)

【C++深度探索】全面解析多态性机制(一)

hello hello~ &#xff0c;这里是大耳朵土土垚~&#x1f496;&#x1f496; &#xff0c;欢迎大家点赞&#x1f973;&#x1f973;关注&#x1f4a5;&#x1f4a5;收藏&#x1f339;&#x1f339;&#x1f339; &#x1f4a5;个人主页&#xff1a;大耳朵土土垚的博客 &#x1…
阅读更多...
演唱会售票系统（Springboot+MySQL+Mybatis+BootStrap）

演唱会售票系统（Springboot+MySQL+Mybatis+BootStrap）

本演唱会售票系统结合了多个流行的技术栈&#xff0c;提供了全面的功能模块&#xff0c;包括用户和管理员两个角色。前端采用Bootstrap框架设计响应式界面&#xff0c;后端采用Spring Boot和MyBatis Plus实现业务逻辑和数据库操作&#xff0c;Sa-Token确保系统的安全性。通过这…
阅读更多...
深入分析与解决4.3问题：iOS应用版本更新审核被拒原因解析

深入分析与解决4.3问题：iOS应用版本更新审核被拒原因解析

深入分析与解决4.3问题&#xff1a;iOS应用版本更新审核被拒原因解析 在iOS应用开发和发布过程中&#xff0c;遇到4.3问题&#xff08;设计 - 垃圾邮件&#xff09;是一个常见且令人头疼的情况。即使您的应用已成功发布其第一个版本&#xff0c;但在进行版本更新时&#xff0c…
阅读更多...
【机器学习】初学者经典案例（随记）

【机器学习】初学者经典案例（随记）

&#x1f388;边走、边悟&#x1f388;迟早会好 一、概念 机器学习是一种利用数据来改进模型性能的计算方法&#xff0c;属于人工智能的一个分支。它旨在让计算机系统通过经验自动改进&#xff0c;而不需要明确编程。 类型 监督学习&#xff1a;使用带标签的数据进行训练&…
阅读更多...
队列+二叉树广度优先

队列+二叉树广度优先

题目出自力扣-n叉树的层序遍历 我是原始人&#xff0c;递归写出一道题就只有递归思路&#xff0c;开始的想法是写深搜函数&#xff0c;传一个随着层数递增的int参数q&#xff0c;节点空就return&#xff0c;否则遍历所有节点&#xff0c;每个子节点又以q1为层数递归&#xff…
阅读更多...
C++ | Leetcode C++题解之第226题翻转二叉树

C++ | Leetcode C++题解之第226题翻转二叉树

题目&#xff1a; 题解&#xff1a; class Solution { public:TreeNode* invertTree(TreeNode* root) {if (root nullptr) {return nullptr;}TreeNode* left invertTree(root->left);TreeNode* right invertTree(root->right);root->left right;root->right …
阅读更多...
js字符串文字添加不同颜色，replace的妙用$1...$9

js字符串文字添加不同颜色，replace的妙用$1...$9

更改字符串第一个数字为红色显示&#xff0c;第二个数字为黄色显示 $1匹配的是正则第一个括号选中的字符串&#xff0c;可以使用正则不断用括号匹配然后更改样式 const testStr "剩余12个名额&#xff0c;截止时间12月25日" testStr this.testStr.replace(/(\d)(\D…
阅读更多...
简单状压dp(以力扣464为例)

简单状压dp(以力扣464为例)

目录 1.状态压缩dp是啥&#xff1f; 2.题目分析 3.解题思路 4.算法分析 5.代码分析 6.代码一览 7.结语 1.状态压缩dp是啥&#xff1f; 顾名思义&#xff0c;状态压缩dp就是将原本会超出内存限制的存储改用更加有效的存储方式。简而言之&#xff0c;就是压缩dp的空间。 …
阅读更多...
设计模式探索：建造者模式

设计模式探索：建造者模式

1. 什么是建造者模式 建造者模式 (Builder Pattern)&#xff0c;也被称为生成器模式&#xff0c;是一种创建型设计模式。 定义&#xff1a;将一个复杂对象的构建与表示分离&#xff0c;使得同样的构建过程可以创建不同的表示。 建造者模式要解决的问题&#xff1a; 建造者模…
阅读更多...
谷粒商城学习-10-docker安装mysql

谷粒商城学习-10-docker安装mysql

文章目录 一&#xff0c;拉取MySQL镜像1&#xff0c;搜索MySQL的Docker镜像2&#xff0c;拉取MySQL镜像3&#xff0c;查看已经拉取的镜像 二&#xff0c;创建、启动MySQL容器1&#xff0c;使用docker run创建启动容器2&#xff0c;使用docker ps查看运行状态的容器3&#xff0c…
阅读更多...
力扣-dfs

力扣-dfs

何为深度优先搜索算法&#xff1f; 深度优先搜索算法&#xff0c;即DFS。就是找一个点&#xff0c;往下搜索&#xff0c;搜索到尽头再折回&#xff0c;走下一个路口。 695.岛屿的最大面积 695. 岛屿的最大面积 题目 给你一个大小为 m x n 的二进制矩阵 grid 。 岛屿 是由一些相…
阅读更多...
Qt：12.输入类控件(QSpinBox-整数值输入的小部件、QDateEdit、QTimeEdit、QDateTimeEdit- 日期和时间输入的控件)

Qt：12.输入类控件(QSpinBox-整数值输入的小部件、QDateEdit、QTimeEdit、QDateTimeEdit- 日期和时间输入的控件)

目录 一、QSpinBox-整数值输入的小部件&#xff1a; 1.1QSpinBox介绍&#xff1a; 1.2属性介绍&#xff1a; 1.3通用属性介绍&#xff1a; 1.4信号介绍&#xff1a; 二、QDateEdit、QTimeEdit、QDateTimeEdit- 日期和时间输入的控件&#xff1a; 2.1QDateEdit、QTimeEdit…
阅读更多...
测试面试宝典（一）——你觉得测试和开发需要怎么结合才能使软件的质量得到更好的保障？

测试面试宝典（一）——你觉得测试和开发需要怎么结合才能使软件的质量得到更好的保障？

“在我看来&#xff0c;测试和开发的有效结合对于保障软件质量至关重要。 首先&#xff0c;在需求分析阶段&#xff0c;测试人员就应该参与进来&#xff0c;与开发人员一起理解软件的需求和功能。这样测试人员可以提前制定测试计划和策略&#xff0c;明确测试的重点和范围。 在…
阅读更多...
springboot零食盒子-计算机毕业设计源码50658

springboot零食盒子-计算机毕业设计源码50658

目 录 1 绪论 1.1 研究背景 1.2研究意义 1.3论文结构与章节安排 2 微信小程序的零食盒子系统分析 2.1 可行性分析 2.2 系统流程分析 2.2.1 数据流程 3.3.2 业务流程 2.3 系统功能分析 2.3.1 功能性分析 2.3.2 非功能性分析 2.4 系统用例分析 2.5本章小结 3 微信…
阅读更多...
人工智能算法工程师(中级)课程3-sklearn机器学习之数据处理与代码详解

人工智能算法工程师(中级)课程3-sklearn机器学习之数据处理与代码详解

大家好&#xff0c;我是微学AI,今天给大家分享一下人工智能算法工程师(中级)课程3-sklearn机器学习之数据处理与代码详解。 Sklearn&#xff08;Scikit-learn&#xff09;是一个基于Python的开源机器学习库&#xff0c;它提供了简单有效的数据挖掘和数据分析工具。Sklearn包含了…
阅读更多...
最新文章
推荐文章

Copyright @ 2022~2023

友情链接： 我的编程经验分享 一条长河网 尧图网站开发