【精选面试题】mysql统计用户连续签到天数

问题

统计用户连续签到天数

有一个类似于这样的表结构，其中sign_in_date字段在比较时需要注意处理。

CREATE TABLE sign_ins (  
    id INT AUTO_INCREMENT PRIMARY KEY,  
    user_id INT NOT NULL,  
    sign_in_date DATE NOT NULL  
);

解题思路

1，我们将按user_id,sign_in_date进行排序，就会发现用户的签到数据将在查询中连续。排序顺序为user_id，再排sign_in_date。

ORDER BY user_id,sign_in_date;

2，此时我们一个能需要记录连续天数的变量。记录上一条记录user_id用于发现用户是否改变，记录上一条记录的sign_in_date来发现时间是否连续

SET @prev_user_id = NULL;
SET @prev_date = NULL;
SET @rank = 0;

补充知识点

 在 MySQL 中，变量可以分为用户定义的变量（User-Defined Variables）和系统变量（System Variables）。这些变量在数据库管理和查询中扮演着重要的角色，尤其是在处理复杂逻辑、存储过程、函数以及优化查询性能时。

用户定义的变量

用户定义的变量是用户自己创建的变量，用于存储临时值，这些值可以在 SQL 语句的执行过程中被引用和修改。用户定义的变量以 @ 符号开始。

-- 定义一个用户变量并赋值 
SET @myVar = 100; 
-- 或者在 SELECT 语句中直接赋值 
SELECT @myVar := 100; 
-- 使用变量 
SELECT @myVar; 
-- 在 WHERE 子句中使用 
SELECT * FROM my_table WHERE id = @myVar; 
-- 在更新操作中递增变量 
UPDATE my_table SET value = value + 1 WHERE id = (@myVar := @myVar + 1);

注意：用户定义的变量在会话（session）级别是可见的，并且它们的值会一直保留到会话结束，除非被显式地修改或删除。

系统变量

系统变量是 MySQL 服务器维护的变量，用于控制服务器的操作。这些变量可以是全局的（影响整个服务器）或会话级别的（仅影响当前会话）。

-- 查看所有全局系统变量 
SHOW GLOBAL VARIABLES; 
-- 查看特定全局系统变量 
SHOW GLOBAL VARIABLES LIKE 'max_connections'; 
-- 查看所有会话系统变量 
SHOW SESSION VARIABLES; 
-- 查看特定会话系统变量 
SHOW SESSION VARIABLES LIKE 'character_set_client';
-- 设置全局系统变量（需要 SUPER 权限） 
SET GLOBAL max_connections = 1000; 
-- 设置会话系统变量 
SET SESSION character_set_client = utf8mb4; 
-- 或者简写为（默认为 SESSION） 
SET character_set_client = utf8mb4;

解答

SET @prev_user_id = NULL;  
SET @prev_date = NULL;  
SET @rank = 0;  
  
SELECT   
    user_id,   
    sign_in_date,  
    @rank := IF(@prev_user_id = user_id AND DATEDIFF(sign_in_date, @prev_date) = 1, @rank + 1, 1) AS consecutive_days,  
    @prev_user_id := user_id,  
    @prev_date := sign_in_date  
FROM   
    sign_ins  
ORDER BY   
    user_id,   
    sign_in_date;