RedShift で重複レコードのうち新しい方を採用する定石

RedShift では、プライマリーキーやユニーク制約を無視してデータを投入することができます。 select 時は制約を加味した最適化が行われるようですが、書き込みにおいては制約は無視されます。また、解析用のデータベースでは、データの正規化はあまり重要ではありません。

最近まで関わっていたセミリアルタイム KPI システム(delay, cycle 共に五分)では、高速化のためにプライマリキーが重複してしまうことを許容し、後から入って来たレコード (batched_at が新しい方) を有効レコードとする設計を採用しました。

create table sometable (
    id bigint,
    created_at timestamp with time zone,
    sales bigint,
    batched_at timestamp with time zone
);

RedShift は Window関数が使えます。そこで、同じ ID のレコードに batched_at の降順に番号を振り、最初のレコードだけを抜き出します。

select id, created_at, sales
from (
    select *, row_number() over (partition by id order by batched_at desc) rank
    from sometable
)
where rank = 1;

通常のトランザクション管理システムでは使わないと思いますが、解析用のデータベースではなかなか使えるテクニックです (いろんなところからデータを集めてくると、綺麗なデータばかりとは限りませんしね…苦笑)。ご参考まで。

links

social