当前位置: 首页 > news >正文

请写sql满足业务:找到连续登录3天以上的用户

为了找到连续登录超过 3 天的用户,我们可以使用 SQL 窗口函数和递归查询来实现。假设有一个 user_logins 表,包含以下字段:

  • user_id(用户ID)
  • login_date(登录日期)

假设 login_date 是 DATE 类型,下面是实现该需求的 SQL 查询:

1. 数据库表结构与样本数据

CREATE TABLE user_logins (user_id INT,login_date DATE
);INSERT INTO user_logins (user_id, login_date) VALUES
(1, '2023-07-01'), (1, '2023-07-02'), (1, '2023-07-03'),(1, '2023-07-05'),
(2, '2023-07-01'), (2, '2023-07-03'), (2, '2023-07-04'),(2, '2023-07-05');

2. SQL 查询

WITH login_streaks AS (SELECTuser_id,login_date,login_date - INTERVAL ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) DAY AS streak_idFROMuser_logins
),
streak_groups AS (SELECTuser_id,COUNT(*) AS streak_lengthFROMlogin_streaksGROUP BYuser_id, streak_id
)
SELECT DISTINCTuser_id
FROMstreak_groups
WHEREstreak_length > 3;

解析:

这段SQL代码通过使用公用表表达式(CTE)计算用户的连续登录天数,并筛选出连续登录天数大于等于3天的用户。我们将逐步解析这段SQL代码的每个部分。

1. WITH子句和CTE

SQL代码使用了两个CTE:login_streaks 和 streak_groups

CTE 1:login_streaks
WITH login_streaks AS (SELECTuser_id,login_date,login_date - INTERVAL ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) DAY AS streak_idFROMuser_logins
),

目的:计算每个用户的登录日期,并为每个用户生成一个“连续登录标识符”(streak_id)。

关键点

  • ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date):为每个用户按login_date排序的每一行生成一个唯一的行号。
  • login_date - INTERVAL ROW_NUMBER() DAY:通过减去行号的天数来生成一个“连续登录标识符”。如果用户在连续的日期登录,减去行号后得到的结果将是相同的。

例如:

  • 假设用户在 2024-07-01 和 2024-07-02 登录,那么:
    • 2024-07-01 - INTERVAL 1 DAY = 2024-06-30
    • 2024-07-02 - INTERVAL 2 DAY = 2024-06-30
    • 这两个记录的“连续登录标识符”将是相同的 2024-06-30,表明它们是连续登录的。
CTE 2:streak_groups
streak_groups AS (SELECTuser_id,COUNT(*) AS streak_lengthFROMlogin_streaksGROUP BYuser_id, streak_id
)

目的:计算每个用户的每个“连续登录标识符”对应的连续登录天数。

关键点

  • GROUP BY user_id, streak_id:按用户和“连续登录标识符”分组。
  • COUNT(*) AS streak_length:计算每个分组的记录数量,即连续登录的天数。

最终查询

SELECTuser_id
FROMstreak_groups
WHEREstreak_length >= 3;

目的:筛选出连续登录天数大于等于3天的用户。

关键点

  • WHERE streak_length >= 3:只选择连续登录天数(streak_length)大于等于3的用户。

完整解析

  1. login_streaks CTE

    • 为每个用户生成一个包含user_idlogin_datestreak_id的临时表。
    • streak_id 标识用户的连续登录,具有相同streak_id的记录表示用户的连续登录序列。
  2. streak_groups CTE

    • 计算每个用户的每个streak_id对应的连续登录天数。
    • 结果表包含user_idstreak_length
  3. 最终查询

    • streak_groups中筛选出streak_length大于等于3的用户。

示例数据和结果

假设user_logins表包含以下数据:

user_idlogin_date
12024-07-01
12024-07-02
12024-07-03
12024-07-05
22024-07-01
22024-07-03
22024-07-04
2

2024-07-05

Step 1login_streaks CTE结果:

user_idlogin_datestreak_id
12024-07-012024-06-30
12024-07-022024-06-30
12024-07-032024-06-30
12024-07-052024-07-02
22024-07-012024-06-30
22024-07-032024-07-01
22024-07-042024-07-01
22024-07-052024-07-01

Step 2streak_groups CTE结果:

user_idstreak_length
13
11
21
23

Step 3: 最终查询结果:

user_id
1
2

总结

这段SQL代码通过使用两个CTE,先计算每个用户的连续登录天数,然后筛选出连续登录天数大于等于3天的用户,非常适合于分析用户的活跃度和粘性。

相关文章:

请写sql满足业务:找到连续登录3天以上的用户

为了找到连续登录超过 3 天的用户,我们可以使用 SQL 窗口函数和递归查询来实现。假设有一个 user_logins 表,包含以下字段: user_id(用户ID)login_date(登录日期) 假设 login_date 是 DATE 类…...

fatal error: apriltag/apriltag.h: No such file or directory 的 参考解决方法

文章目录 写在前面一、问题描述二、解决方法参考链接 写在前面 自己的测试环境: Ubuntu20.04,ROS-Noteic 一、问题描述 自己编译ROS程序的时候遇到如下问题: fatal error: apriltag/apriltag.h: No such file or directory9 | #include &…...

C++继承(一文说懂)

目录 一: 🔥继承的概念及定义1.1 继承的概念1.2 继承定义1.2.1 定义格式1.2.2 继承关系和访问限定符1.2.3 继承基类成员访问方式的变化 二:🔥基类和派生类对象赋值转换三:🔥继承中的作用域四:&a…...

卷积神经网络可视化的探索

文章目录 训练LeNet模型下载FashionMNIST数据训练保存模型 卷积神经网络可视化加载模型一个测试图像不同层对图像处理的可视化第一个卷积层的处理第二个卷积层的处理 卷积神经网络是利用图像空间结构的一种深度学习网络架构,图像在经过卷积层、激活层、池化层、全连…...

RxJava学习记录

文章目录 1. 总览1.1 基本原理1.2 导入包和依赖 2. 操作符2.1 创建操作符2.2 转换操作符2.3 组合操作符2.4 功能操作符 1. 总览 1.1 基本原理 参考文献 构建流:每一步操作都会生成一个新的Observable节点(没错,包括ObserveOn和SubscribeOn线程变换操作…...

Spring Boot Vue 毕设系统讲解 3

目录 项目配置类 项目中配置的相关代码 spring Boot 拦截器相关知识 一、基于URL实现的拦截器: 二、基于注解的拦截器 三、把拦截器添加到配置中,相当于SpringMVC时的配置文件干的事儿: 项目配置类 项目中配置的相关代码 首先定义项目认…...

Spring Boot对接大模型:实战价值与技巧

Spring Boot对接大模型:实战价值与技巧 随着大数据和人工智能技术的飞速发展,大模型(Large-scale Models)在各个行业中的应用越来越广泛。为了充分利用这些大模型的能力,我们需要将其与现有的应用框架进行对接。Sprin…...

完美解决NameError: name ‘file‘ is not defined的正确解决方法,亲测有效!!!

完美解决NameError: name ‘file’ is not defined的正确解决方法,亲测有效!!! 亲测有效 完美解决NameError: name file is not defined的正确解决方法,亲测有效!!!报错问题解决思路…...

Witness Table 的由来

“Witness Table” 是 Swift 中的一个术语,源于编译原理和类型系统的概念。它被用来表示一种机制,通过这个机制,编译器可以确保某个类型确实实现了它声明遵循的协议中的所有方法和属性。下面是对这个术语的详细解释: 1. 术语来源…...

Python 3 AI 编程助手

Python 3 AI 编程助手 Python 3 是当前最流行的编程语言之一,特别是在人工智能(AI)领域。Python 3 的语法简洁明了,拥有丰富的库和框架,使其成为开发 AI 应用程序的首选语言。本文将介绍 Python 3 在 AI 编程中的关键特性、常用库以及如何使用 Python 3 构建 AI 应用程序…...

【nginx】nginx的配置文件到底是什么结构,到底怎么写?

背景:我window中下载了一个nginx,想要通过nginx来对本地的两个项目做动态代理,但是没想到下载启动都没遇见什么问题,但是在配置nginx.conf配置文件时,遇见了很多问题,查了好久没查到什么特别有用的内容&…...

基于React 实现井字棋

一、简介 这篇文章会基于React 实现井字棋小游戏功能。 二、效果演示 三、技术实现 import {useEffect, useState} from "react";export default (props) > {return <Board/> }const Board () > {let initialState [[, , ], [, , ], [, , ]];const [s…...

文件的换行符,Windows 的 CRLF 和 Linux 的 LF

文件的换行符&#xff0c;Windows 的 CRLF 和 Linux 的 LF&#xff0c;在开发项目时用哪种比较合适&#xff1f; 在开发项目时选择文件的换行符&#xff08;Windows 的 CRLF 或 Linux 的 LF&#xff09;&#xff0c;通常取决于几个因素&#xff0c;包括项目的运行环境、项目的…...

怎样优化 PostgreSQL 中对日期时间范围的模糊查询?

文章目录 一、问题分析&#xff08;一&#xff09;索引未有效利用&#xff08;二&#xff09;日期时间格式不统一&#xff08;三&#xff09;复杂的查询条件 二、优化策略&#xff08;一&#xff09;使用合适的索引&#xff08;二&#xff09;规范日期时间格式&#xff08;三&a…...

B端设计:任何不顾及用户体验的设计,都是在装样子,花架子

B端设计是指面向企业客户的设计&#xff0c;通常涉及产品、服务或系统的界面和功能设计。与C端设计不同&#xff0c;B端设计更注重实用性和专业性&#xff0c;因为它直接影响企业的效率和利益。 在B端设计中&#xff0c;用户体验同样至关重要。不顾及用户体验的设计只是空洞的表…...

React@16.x(51)路由v5.x(16)- 手动实现文件目录参考

作为前面几篇文章的参考&#xff1a; 实现 Router实现 Route实现 Switch实现 withRouter实现 Link 和 NavLink 以上。...

从零开始读RocketMq源码(二)Message的发送详解

目录 前言 准备 消息发送方式 深入源码 消息发送模式 选择发送方式 同步发送消息 校验消息体 获取Topic订阅信息 高级特性-消息重投 选择消息队列-负载均衡 装载消息体发送消息 压缩消息内容 构造发送message的请求的Header 更新broker故障信息 异步发送消息 …...

怎样优化 PostgreSQL 中对布尔类型数据的查询?

文章目录 一、索引的合理使用1. 常规 B-tree 索引2. 部分索引 二、查询编写技巧1. 避免不必要的类型转换2. 逻辑表达式的优化 三、表结构设计1. 避免过度细分的布尔列2. 规范化与反规范化 四、数据分布与分区1. 数据分布的考虑2. 表分区 五、数据库参数调整1. 相关配置参数2. 定…...

mysql在linux系统下重置root密码

mysql在linux系统下重置root密码 登录服务器时候mysql密码忘记了&#xff0c;没办法只能重置&#xff0c;找了一圈&#xff0c;把行之有效的方法介绍在这里。 错误展示&#xff1a; 我还以为yes就可以了呢&#xff0c;这是不行的意思。 关掉mysql服务 sudo systemctl stop …...

设计模式探索:观察者模式

1. 观察者模式 1.1 什么是观察者模式 观察者模式用于建立一种对象与对象之间的依赖关系&#xff0c;当一个对象发生改变时将自动通知其他对象&#xff0c;其他对象会相应地作出反应。 在观察者模式中有如下角色&#xff1a; Subject&#xff08;抽象主题/被观察者&#xf…...

Spark 之 入门讲解详细版(1)

1、简介 1.1 Spark简介 Spark是加州大学伯克利分校AMP实验室&#xff08;Algorithms, Machines, and People Lab&#xff09;开发通用内存并行计算框架。Spark在2013年6月进入Apache成为孵化项目&#xff0c;8个月后成为Apache顶级项目&#xff0c;速度之快足见过人之处&…...

ubuntu搭建nfs服务centos挂载访问

在Ubuntu上设置NFS服务器 在Ubuntu上&#xff0c;你可以使用apt包管理器来安装NFS服务器。打开终端并运行&#xff1a; sudo apt update sudo apt install nfs-kernel-server创建共享目录 创建一个目录用于共享&#xff0c;例如/shared&#xff1a; sudo mkdir /shared sud…...

渗透实战PortSwigger靶场-XSS Lab 14:大多数标签和属性被阻止

<script>标签被拦截 我们需要把全部可用的 tag 和 event 进行暴力破解 XSS cheat sheet&#xff1a; https://portswigger.net/web-security/cross-site-scripting/cheat-sheet 通过爆破发现body可以用 再把全部 events 放进去爆破 这些 event 全部可用 <body onres…...

JVM垃圾回收机制全解析

Java虚拟机&#xff08;JVM&#xff09;中的垃圾收集器&#xff08;Garbage Collector&#xff0c;简称GC&#xff09;是用于自动管理内存的机制。它负责识别和清除不再被程序使用的对象&#xff0c;从而释放内存空间&#xff0c;避免内存泄漏和内存溢出等问题。垃圾收集器在Ja…...

Spring Cloud Gateway 中自定义验证码接口返回 404 的排查与解决

Spring Cloud Gateway 中自定义验证码接口返回 404 的排查与解决 问题背景 在一个基于 Spring Cloud Gateway WebFlux 构建的微服务项目中&#xff0c;新增了一个本地验证码接口 /code&#xff0c;使用函数式路由&#xff08;RouterFunction&#xff09;和 Hutool 的 Circle…...

Java线上CPU飙高问题排查全指南

一、引言 在Java应用的线上运行环境中&#xff0c;CPU飙高是一个常见且棘手的性能问题。当系统出现CPU飙高时&#xff0c;通常会导致应用响应缓慢&#xff0c;甚至服务不可用&#xff0c;严重影响用户体验和业务运行。因此&#xff0c;掌握一套科学有效的CPU飙高问题排查方法&…...

华硕a豆14 Air香氛版,美学与科技的馨香融合

在快节奏的现代生活中&#xff0c;我们渴望一个能激发创想、愉悦感官的工作与生活伙伴&#xff0c;它不仅是冰冷的科技工具&#xff0c;更能触动我们内心深处的细腻情感。正是在这样的期许下&#xff0c;华硕a豆14 Air香氛版翩然而至&#xff0c;它以一种前所未有的方式&#x…...

CRMEB 中 PHP 短信扩展开发:涵盖一号通、阿里云、腾讯云、创蓝

目前已有一号通短信、阿里云短信、腾讯云短信扩展 扩展入口文件 文件目录 crmeb\services\sms\Sms.php 默认驱动类型为&#xff1a;一号通 namespace crmeb\services\sms;use crmeb\basic\BaseManager; use crmeb\services\AccessTokenServeService; use crmeb\services\sms\…...

【LeetCode】3309. 连接二进制表示可形成的最大数值(递归|回溯|位运算)

LeetCode 3309. 连接二进制表示可形成的最大数值&#xff08;中等&#xff09; 题目描述解题思路Java代码 题目描述 题目链接&#xff1a;LeetCode 3309. 连接二进制表示可形成的最大数值&#xff08;中等&#xff09; 给你一个长度为 3 的整数数组 nums。 现以某种顺序 连接…...

脑机新手指南(七):OpenBCI_GUI:从环境搭建到数据可视化(上)

一、OpenBCI_GUI 项目概述 &#xff08;一&#xff09;项目背景与目标 OpenBCI 是一个开源的脑电信号采集硬件平台&#xff0c;其配套的 OpenBCI_GUI 则是专为该硬件设计的图形化界面工具。对于研究人员、开发者和学生而言&#xff0c;首次接触 OpenBCI 设备时&#xff0c;往…...