Postgres 11.9中用CTE实现日期转指定格式且重复值纳秒自增的DBT查询

核心实现逻辑 #

• 用窗口函数给同一生效日期的行按稳定排序规则生成从0开始的行号，作为时间偏移量
• 基础日期转换为timestamp类型后，叠加对应行号的微秒偏移，保证同组日期的时间值唯一
• 最终按要求格式化输出为YYYY-MM-DD.HH.MM.SS.nnnnnn格式的字符串，可直接作为复合主键组成字段

注意事项 #

• 必须指定稳定的排序规则：排序字段需能唯一确定同组内的行顺序，避免每次执行生成的偏移量不一致导致主键冲突，示例中使用源表唯一业务键排序，可根据实际业务调整
• 单组重复日期最多支持100万行，完全覆盖常规业务场景，若超量可调整偏移单位适配

适配DBT的CTE查询代码 #

WITH source_data AS (
    -- DBT环境下替换为你的源表引用即可
    SELECT 
        生效日期列,
        业务字段1,
        业务字段2,
        -- 替换为源表中可稳定排序的唯一字段，保证同日期行排序固定
        源表唯一标识字段
    FROM {{ ref('你的源表名称') }}
),
ranked_date_group AS (
    SELECT
        *,
        -- 同生效日期分组，按指定字段排序，生成从0开始的偏移量
        (ROW_NUMBER() OVER (PARTITION BY 生效日期列 ORDER BY 源表唯一标识字段) - 1) AS time_offset
    FROM source_data
),
unique_datetime_gen AS (
    SELECT
        *,
        -- 基础时间叠加微秒偏移，生成唯一时间戳
        (生效日期列::TIMESTAMP + (time_offset || ' microseconds')::INTERVAL) AS unique_timestamp
    FROM ranked_date_group
)
SELECT
    -- 格式化为要求的目标格式
    TO_CHAR(unique_timestamp, 'YYYY-MM-DD.HH24.MI.SS.US') AS target_primary_key_datetime,
    业务字段1,
    业务字段2
    -- 其余需要输出的业务字段
FROM unique_datetime_gen

效果验证 #

举例：若基础转换后的时间为2021-07-30 00:00:00.000000，同日期第2行生成的目标值为2021-07-30.00.00.00.000001，第3行为2021-07-30.00.00.00.000002，以此类推，完全满足主键唯一性要求。

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