MySQL 8.1中如何从指定月份随机提取两天的全量用户登录数据

我已将MySQL数据库升级至8.1版本，需要从_tbl_login表中提取指定年月的随机两天全量用户登录数据。该表每行对应一条用户登录记录，存储全年各日期的数据，需求为随机选出指定月份的两天，返回这两天的所有登录行，排除该月其他日期的数据。

尝试的查询及问题 #

第一次查询：返回全月数据 #

尝试以下查询后，返回的是10月所有日期的全部数据：

WITH rando AS 
( SELECT ` _Date`, 
  ROW_NUMBER() 
  OVER ( PARTITION BY ` _Date` ORDER BY RAND() ) AS rn 
  FROM _tbl_login WHERE MONTH ( _Date ) = '10' ) 
SELECT * FROM
    _tbl_login 
WHERE
    ` _Date` IN ( SELECT ` _Date` FROM rando WHERE rn <= 2 ) 
    AND MONTH ( _Date ) = '10';

问题原因：PARTITION BY _Date``会为每个日期的每一行生成独立行号，导致每个日期都存在rn <= 2的记录，最终IN子句包含所有日期，返回全月数据。

修改后的查询：仅返回日期 #

修改后的查询仅返回两个日期，而非对应日期的全量登录记录：

WITH rando AS 
( SELECT _Date, 
  ROW_NUMBER()  
    OVER ( ORDER BY RAND() ) AS rn 
  FROM _tbl_login WHERE MONTH ( _Date ) = '10' ) 
SELECT _Date FROM
    _tbl_login 
WHERE
    _Date IN ( SELECT _Date FROM rando WHERE rn <= 2 ) 
    AND MONTH (_Date ) = '10';

查询结果：

+------------+
| _Date      |
+------------+
| 2023-10-02 |
| 2023-10-06 |
+------------+
2 rows in set (1.75 sec)

问题原因：查询字段仅指定了_Date，且CTE直接从全表行取随机行号，无法确保选中不同日期，也未返回全量记录。

期望结果格式 #

需要返回选中日期的全量登录记录，示例如下：

+-----+------------+------------+
| id  | _Date      | _login     |
+-----+------------+------------+
|  1  | 2023-10-07 | Kevin      |
|  2  | 2023-10-07 | Joe        |
|  3  | 2023-10-07 | Lydia      |
|  4  | 2023-10-07 | Homer      |
|  5  | 2023-10-07 | Ada        |
|  6  | 2023-10-07 | Lucy       |
|  7  | 2023-10-07 | Kyara      |
|  8  | 2023-10-07 | Lucas      |
|  9  | 2023-10-07 | Steve      |
| 10 | 2023-10-24 | Steve      |
| 11 | 2023-10-24 | Ada        |
| 12 | 2023-10-24 | Frank      |
| 13 | 2023-10-24 | Clint      |
| 14 | 2023-10-24 | Kyara      |
| 15 | 2023-10-24 | Maddy      |
| 16 | 2023-10-24 | Peter      |
| 17 | 2023-10-24 | Lucas      |
+-----+------------+------------+

正确解决方案 #

方案1：DISTINCT去重日期后随机选取 #

先获取指定月份的唯一日期，再随机选2个，最后关联原表返回全量记录：

WITH random_dates AS (
    SELECT DISTINCT _Date
    FROM _tbl_login
    WHERE MONTH(_Date) = 10 AND YEAR(_Date) = 2023 -- 增加年份过滤避免跨年度同月份数据
    ORDER BY RAND()
    LIMIT 2
)
SELECT t.*
FROM _tbl_login t
JOIN random_dates rd ON t._Date = rd._Date;

方案2：ROW_NUMBER生成随机行号选取日期 #

通过CTE先去重日期，再给日期生成随机行号，筛选出前2个日期后关联原表：

WITH date_list AS (
    SELECT DISTINCT _Date
    FROM _tbl_login
    WHERE MONTH(_Date) = 10 AND YEAR(_Date) = 2023
),
random_dates AS (
    SELECT _Date,
           ROW_NUMBER() OVER (ORDER BY RAND()) AS rn
    FROM date_list
)
SELECT t.*
FROM _tbl_login t
JOIN random_dates rd ON t._Date = rd._Date
WHERE rd.rn <= 2;

关键说明 #

• 增加YEAR(_Date) = 2023可确保查询仅针对目标年份的指定月份，避免数据混淆。
• 先去重日期再随机选取，保证选中的是两个不同的日期，而非同一日期的不同行。
• 通过JOIN关联原表，最终返回选中日期的所有登录记录。

内容的提问来源于stack exchange，提问作者George A. Custer