高效获取同UID、VID且满足半小时时间范围条件的最小记录创建日期的优化方案问询

针对你遇到的自连接查询速度慢的问题，其实可以用窗口函数（推荐）或者变量跟踪的方案来替代，性能会提升很多，下面分场景给你详细说明：

需求回顾 #

对表t1中每个(uid, vid)分组的记录，为每条记录找到早于当前date_created且时间差不超过30分钟的最小date_created；如果没有符合条件的记录，就用当前记录的date_created作为dc。

方案一：使用窗口函数（推荐，支持MySQL 8+/PostgreSQL/SQL Server等现代数据库） #

窗口函数是数据库针对这类分组计算场景高度优化的特性，完全避免了自连接带来的笛卡尔积问题，性能提升非常明显。

实现代码 #

SELECT 
    uid,
    vid,
    date_created,
    COALESCE(
        MIN(date_created) OVER (
            PARTITION BY uid, vid 
            ORDER BY date_created 
            RANGE BETWEEN INTERVAL '30 MINUTE' PRECEDING AND 1 PRECEDING
        ),
        date_created
    ) AS dc
FROM t1
ORDER BY uid, vid, date_created;

逻辑说明 #

• PARTITION BY uid, vid：限定仅在同一个uid和vid的组内进行计算，不会跨组干扰
• ORDER BY date_created：按时间顺序排列组内记录，确保窗口范围的正确性
• RANGE BETWEEN INTERVAL '30 MINUTE' PRECEDING AND 1 PRECEDING：窗口范围设置为当前记录之前30分钟到当前记录的前一条，只筛选符合时间条件的前置记录
• COALESCE：如果窗口内没有符合条件的记录（MIN返回NULL），则用当前记录的date_created填充dc

方案二：使用变量跟踪（适配MySQL 5.x等不支持窗口函数的老版本） #

如果你的数据库版本不支持窗口函数，可以用用户变量来跟踪组内的时间状态，避免自连接：

实现代码 #

SELECT 
    uid,
    vid,
    date_created,
    CASE 
        WHEN TIMESTAMPDIFF(MINUTE, prev_date, date_created) <= 30 THEN first_valid_date
        ELSE date_created
    END AS dc
FROM (
    SELECT 
        uid,
        vid,
        date_created,
        -- 跟踪同组上一条记录的日期
        @prev_date := CASE 
            WHEN uid = @prev_uid AND vid = @prev_vid THEN @prev_date
            ELSE NULL
        END AS prev_date,
        -- 跟踪同组内符合条件的最早日期
        @first_valid_date := CASE 
            WHEN uid = @prev_uid AND vid = @prev_vid AND TIMESTAMPDIFF(MINUTE, @prev_date, date_created) <= 30 THEN @first_valid_date
            ELSE date_created
        END AS first_valid_date,
        -- 更新变量为当前记录的uid和vid
        @prev_uid := uid,
        @prev_vid := vid
    FROM t1
    -- 初始化变量
    CROSS JOIN (SELECT @prev_uid := NULL, @prev_vid := NULL, @prev_date := NULL, @first_valid_date := NULL) vars
    -- 必须按uid、vid、date_created排序，保证变量跟踪的顺序正确
    ORDER BY uid, vid, date_created
) AS sub_query;

关键优化：添加复合索引 #

不管用哪种方案，都一定要给表t1添加以下复合索引，能让数据库快速定位分组和排序，大幅提升查询效率：

CREATE INDEX idx_uid_vid_date ON t1(uid, vid, date_created);

结果验证 #

用你提供的示例数据测试，两种方案都会得到你期望的结果：

内容的提问来源于stack exchange，提问作者AngelStrongX12