在Redshift中按时间戳将8M数据集划分为1M规模的分桶

实现思路 #

先给数据集按时间戳升序分配行号，再按每100万条记录划分为一个分桶，最后调整分桶边界确保前后桶时间连续，生成符合要求的起止对照表。

完整SQL代码 #

WITH numbered_data AS (
    -- 给每条记录按时间戳升序分配行号
    SELECT 
        timestamp_column,
        ROW_NUMBER() OVER (ORDER BY timestamp_column) AS row_num
    FROM your_table_name  -- 替换为实际表名
),
bucket_boundaries AS (
    -- 按每100万条分组，计算初始起止时间
    SELECT
        ((row_num - 1) // 1000000) + 1 AS bucket_num,
        MIN(timestamp_column) AS bucket_start,
        MAX(timestamp_column) AS bucket_end
    FROM numbered_data
    GROUP BY ((row_num - 1) // 1000000) + 1
    ORDER BY bucket_num
),
final_buckets AS (
    -- 调整边界，让前一个桶的结束时间等于后一个桶的开始时间
    SELECT
        bucket_num,
        bucket_start,
        COALESCE(LEAD(bucket_start) OVER (ORDER BY bucket_num), bucket_end) AS bucket_end
    FROM bucket_boundaries
)
-- 生成最终的分桶起止表
SELECT
    CONCAT('Bucket', bucket_num) AS "BucketX",
    TO_CHAR(bucket_start, 'YYYY-MM-DD HH24:MI:SS') AS "Start",  -- 可按需调整时间格式
    TO_CHAR(bucket_end, 'YYYY-MM-DD HH24:MI:SS') AS "End"
FROM final_buckets;

代码说明 #

• numbered_data：通过ROW_NUMBER()窗口函数给每条记录按时间戳从早到晚分配唯一行号，保证分桶顺序正确。
• bucket_boundaries：用整数除法(row_num - 1) // 1000000将行号划分为每100万条一组，计算每组的最小和最大时间作为初始分桶边界。
• final_buckets：使用LEAD()窗口函数将每个桶的结束时间替换为下一个桶的开始时间，确保分桶时间连续（如Bucket1的End等于Bucket2的Start），最后一个桶的结束时间保留自身最大时间。
• 最终查询将桶编号格式化为BucketX样式，时间戳转为易读的字符串格式（可根据需求修改TO_CHAR的格式参数，比如只保留年份用'YYYY'）。

示例结果表 #

注意事项 #

• 请将代码中的your_table_name和timestamp_column替换为实际的表名和时间戳字段名。
• 如果总记录数不是100万的整数倍，最后一个分桶的记录数会少于100万。

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