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
相关产品推荐
相关产品推荐

