You need to enable JavaScript to run this app. SQL如何批量插入用户每日最后活动对应的次日0点记录
优惠活动
大模型
产品
解决方案
定价
更多

SQL如何批量插入用户每日最后活动对应的次日0点记录

用户活动表自动插入每日末尾记录SQL实现

核心逻辑

按「用户ID + 活动自然日」维度,定位每个用户当天时间最晚的活动记录,再生成对应日期次日00:00:00的新增数据,不同数据库的语法实现如下:


各数据库实现代码

1. MySQL 8.0+ 版本

-- 先查询校验待插入数据,确认结果符合预期再执行写入
SELECT 
    user_id, 
    activity,
    DATE_ADD(DATE(dt), INTERVAL 1 DAY) AS dt
FROM (
    SELECT 
        user_id,
        activity,
        dt,
        -- 按用户、活动自然日分组,给当日所有活动按时间倒序排名
        ROW_NUMBER() OVER (
            PARTITION BY user_id, DATE(dt) 
            ORDER BY dt DESC
        ) AS rn
    FROM user_activity
    -- 增量执行时可加过滤条件,排除已插入的零点记录,避免重复计算
    -- WHERE TIME(dt) != '00:00:00'
) t
WHERE rn = 1;

-- 校验无误后执行插入,可加LEFT JOIN判断避免重复写入
INSERT INTO user_activity (user_id, activity, dt)
SELECT t.user_id, t.activity, t.dt
FROM (
    SELECT 
        user_id, 
        activity,
        DATE_ADD(DATE(dt), INTERVAL 1 DAY) AS dt
    FROM (
        SELECT 
            user_id,
            activity,
            dt,
            ROW_NUMBER() OVER (
                PARTITION BY user_id, DATE(dt) 
                ORDER BY dt DESC
            ) AS rn
        FROM user_activity
        -- WHERE TIME(dt) != '00:00:00'
    ) sub
    WHERE rn = 1
) t
LEFT JOIN user_activity u 
    ON t.user_id = u.user_id 
    AND t.dt = u.dt 
    AND t.activity = u.activity
WHERE u.user_id IS NULL;

2. MySQL 5.x 兼容版本(无窗口函数)

-- 校验待插入数据
SELECT a.user_id, a.activity, DATE_ADD(DATE(a.dt), INTERVAL 1 DAY) AS dt
FROM user_activity a
INNER JOIN (
    -- 找到每个用户每天的最大活动时间
    SELECT user_id, DATE(dt) AS act_day, MAX(dt) AS max_dt
    FROM user_activity
    -- WHERE TIME(dt) != '00:00:00'
    GROUP BY user_id, DATE(dt)
) b 
    ON a.user_id = b.user_id 
    AND a.dt = b.max_dt;

-- 插入逻辑同8.0版本,替换对应子查询即可

3. Hive / Spark SQL 版本

-- 校验待插入数据
SELECT 
    user_id, 
    activity,
    DATE_FORMAT(DATE_ADD(CAST(dt AS DATE), 1), 'yyyy-MM-dd 00:00:00') AS dt
FROM (
    SELECT 
        user_id,
        activity,
        dt,
        ROW_NUMBER() OVER (
            PARTITION BY user_id, TO_DATE(dt) 
            ORDER BY dt DESC
        ) AS rn
    FROM user_activity
    -- WHERE HOUR(dt) != 0 AND MINUTE(dt) !=0 AND SECOND(dt) !=0
) t
WHERE rn = 1;

4. PostgreSQL 版本

-- 校验待插入数据
SELECT 
    user_id, 
    activity,
    (DATE(dt) + INTERVAL '1 day')::timestamp AS dt
FROM (
    SELECT 
        user_id,
        activity,
        dt,
        ROW_NUMBER() OVER (
            PARTITION BY user_id, DATE(dt) 
            ORDER BY dt DESC
        ) AS rn
    FROM user_activity
    -- WHERE dt::time != '00:00:00'
) t
WHERE rn = 1;

注意事项

  • 首次全量初始化时,可直接执行不带去重的插入逻辑;后续增量同步新活动时,必须加上注释里的过滤条件,排除已插入的零点记录,避免把历史插入数据误判为当日普通活动,生成无效重复记录
  • 所有插入操作前必须先运行校验查询,核对返回的待插入数据和业务预期一致,避免误写脏数据
  • 如果表数据量较大,可提前在user_id、dt字段上建联合索引,提升窗口函数的计算效率

内容的提问来源于stack exchange,提问作者OctavianWR

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级,最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分), 限时 9.9 元起

最近更新时间:2026.11.06 03:54:02