删除某一时间段内出现的重复项

-- 创建示例表
CREATE TABLE events (
    id INT PRIMARY KEY,
    user_id INT,
    event_type VARCHAR(50),
    timestamp DATETIME
);

-- 插入示例数据
INSERT INTO events (id, user_id, event_type, timestamp) VALUES
(1, 101, 'login', '2023-01-15 10:00:00'),
(2, 101, 'login', '2023-01-15 10:00:01'),
(3, 102, 'purchase', '2023-01-20 15:30:00'),
(4, 102, 'purchase', '2023-01-20 15:30:01');

-- 删除重复项的SQL语句
WITH duplicates AS (
    SELECT user_id, event_type, MIN(id) as min_id
    FROM events
    WHERE timestamp BETWEEN '2023-01-01' AND '2023-01-31'
    GROUP BY user_id, event_type
    HAVING COUNT(*) > 1
)
DELETE FROM events
WHERE id NOT IN (SELECT min_id FROM duplicates)
AND timestamp BETWEEN '2023-01-01' AND '2023-01-31';