オートインクリメントを頼りにレコード作成日を推定する

オートインクリメントの特性を用いて失われた情報をある程度復元するというハックが役に立ったので、事例として紹介してみます。

状況設定

users と user_details という2つのテーブルがあるとします。 users のプライマリキー (users.id) はオートインクリメントの代替キーで、user_detailsのプライマリキー (user_details.id) は users.id を参照しているとします。また、以下のような事情があるとします。 (過去の設計のよしあしに関わらず、今あるテーブルに対して可能な限りの情報を取り出す問題だと考えてください)

全ての users が user_details を随伴しているわけではない。
users のレコードには作成時刻は記録されていないが、かわりに user_details には作成時刻 (user_details.created_at) が記録されている。

CREATE TABLE users (
  id BIGSERIAL PRIMARY KEY,
  -- ...
);

CREATE TABLE user_details (
  id BIGINTEGER PRIMARY KEY,
  created_at TIMESTAMP NOT NULL,
  -- ...,
  FOREIGN KEY (id) REFERENCES users
);

また、以下のことを仮定します。

ユーザーは定常的に、十分な速度で増加している。
users レコードが作られるとほぼ同時に対応する user_details が作られることが多く、 user_details を持つユーザーの大部分はこれに該当する。ただし全部ではなく、あとから作られることもある。
user_details のレコードが users からのカスケード以外で削除されることはほぼない。

課題

「作成されてからおよそ24時間以上経過したユーザー」を抽出することを考えます。

解決方法

以下のような条件を書きました。なおサンプルコードの構文はBigQueryの構文に基づいています。

SELECT id FROM users
WHERE
  id < (
    SELECT MAX(user_details.id) FROM user_details
    WHERE
      created_at < TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 24 HOUR)
  )
  -- AND ...

ここでのポイントは以下の通りです:

オートインクリメントの値はおおむね挿入された時間順を保っている。
user_details.created_at は users の作成時刻の上からのよい近似である。

したがって、users レコードそのものの作成時刻がわからなくても、オートインクリメント順で後続するuser_detailsレコードよりは前に作られていることは確実です。そこで、それらの user_details のうちもっとも若いものを作成時刻の推定値として利用すれば、(十分なサンプルがあるという前提のもと) よい近似になることが期待されます。

今回はこの推定値を直接推定する必要はなく、その逆関数を求めることが目的です。ここで出てくる作成時刻の推定値はオートインクリメント順に対して単調なので、おおむねこの関数の逆関数としてふるまう関数を考えることができます。単調性を使うと、この計算はMAXをとる軸とフィルタリングを行う軸を反転するだけで得られることがわかります。