如何在Presto SQL中为表添加当前日期以留存历史数据

现有代码问题说明 #

1. 目标表QQQ未定义date字段，插入日期值会触发列不匹配错误
2. INSERT语句中SELECT*, current_date as date, member_id, segment_id重复选取了member_id和segment_id，导致列数与表结构不符

正确实现步骤 #

1. 创建带日期字段的目标表

先确保存储历史数据的QQQ表包含日期字段：

CREATE TABLE IF NOT EXISTS QQQ (
    member_id bigint,
    segment_id int,
    date date  -- 用于存储每日刷新的日期，格式为YYYY-MM-DD
);

2. 每日刷新数据的逻辑

如果要保留中间表ABC来降低查询负载，每日执行以下脚本：

-- 刷新中间表ABC，获取当日最新的member和segment数据
DROP TABLE IF EXISTS ABC;
CREATE TABLE ABC AS 
SELECT member_id, segment_id 
FROM XYZ;

-- 将当日数据追加到QQQ，同时带上当前日期
INSERT INTO QQQ (member_id, segment_id, date)
SELECT member_id, segment_id, current_date
FROM ABC;

如果无需中间表，直接从XYZ取数插入更简洁：

INSERT INTO QQQ (member_id, segment_id, date)
SELECT member_id, segment_id, current_date
FROM XYZ;

3. 查询过去5天历史数据

用以下语句筛选最近5天的记录：

SELECT member_id, segment_id, date
FROM QQQ
WHERE date >= current_date - INTERVAL '5' DAY;

关键说明 #

• Presto中的current_date本身就是标准date类型（YYYY-MM-DD格式），无需额外格式转换
• 必须使用INSERT追加数据，而非CREATE TABLE AS覆盖，这样才能保留每日的历史记录

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