relation 一つで「再利用率」を測る — project_assets の実装と検証クエリ

前回、assets と project_assets の 2 表だけで始めると書いた(前回)。骨格だけ出して、中身は書かずに終わっている。今回はその relation 列を実際に置いて、再利用率をどう SQL で出すかを書く。

結論から言えば、この 2 表と relation・instances の 2 列があれば、算数の 3 本柱(再利用率・資産あたりの平均再利用・時間当たり収益差)はすべて素の SQL で組める。逆に、この 2 列の意味を最初に詰めておかないと、あとから数字が全部ズレる。ここが一番のハマりどころだった。

まず、2 表を確定させる

assets は「作ったもの」の台帳。列は最小に絞る。

create table assets (
  id          bigint primary key generated always as identity,
  name        text not null,
  kind        text not null,
  created_at  timestamptz not null default now()
);

kind は自由テキストだ。前回書いたとおり、分類体系は作らない。'newsletter_template' でも 'shopify_liquid_snippet' でも 'sql_view' でも、その時に自分が呼んでいる名前をそのまま入れる。あとで揺れが気になったら手で寄せればいい。最初から enum にすると増やすたびに migration が要る割に、分類の質は上がらない。

project_assets は「プロジェクトと資産の関係」を持つ junction 表だが、junction にしては列が多い。relation と instances が乗っているからだ。

create table project_assets (
  id                bigint primary key generated always as identity,
  project_id        bigint not null references projects(id),
  asset_id          bigint not null references assets(id),
  relation          text   not null check (relation in ('created','reused')),
  instances         int    not null default 1,
  unit_price_local  numeric(14,2),
  currency          text   not null check (currency in ('JPY','KRW')),
  created_at        timestamptz not null default now()
);
create index on project_assets (asset_id, relation);
create index on project_assets (project_id);

一番言いたいのは relation と instances を分けたことだ。relation は「このプロジェクトで、この資産は新しく作ったのか、それとも既存を持ってきたのか」の二値。instances は「その資産を、このプロジェクト内で何回インスタンス化したか」の整数。

例えばニュースレターの HTML テンプレートを 1 本作って、3 店舗ぶんに使い回して 6 ヶ月配信した場合。assets に 1 行、project_assets に relation='created', instances=18 の 1 行が入る。翌年別のクライアントで同じテンプレを流用したら、その案件では relation='reused', instances=N。「作った回数」と「実際に走らせた回数」を 1 列で潰さないための分離だ。

算数の 1 本目 — 再利用率

期間を切って、その期間内の全 instances のうち、reused がどれだけを占めるか。素直に書ける。

select
  date_trunc('month', pa.created_at) as month,
  sum(case when relation = 'reused' then instances else 0 end) as reused_inst,
  sum(instances) as total_inst,
  round(
    100.0 *
    sum(case when relation = 'reused' then instances else 0 end)::numeric
    / nullif(sum(instances), 0),
    1
  ) as reuse_rate_pct
from project_assets pa
where pa.created_at >= '2026-01-01'
group by 1
order by 1;

返ってくるのはこういう形だ(数値は説明用のダミー):

   month    | reused_inst | total_inst | reuse_rate_pct
------------+-------------+------------+----------------
 2026-01-01 |          42 |         88 |           47.7
 2026-02-01 |          61 |        104 |           58.7
 2026-03-01 |          77 |        115 |           67.0

nullif を挟んでいるのは、対象月に project_assets が 1 行も無いときにゼロ除算で落ちないため。地味だが、月次ダッシュボードに載せると絶対に踏む。

算数の 2 本目 — 資産あたりの平均再利用回数

「作ったもののうち、どれだけの資産が繰り返し使われているか」。ここは 2 通りの見せ方がある。集約だけで済ませるパターンと、資産ごとの分布まで欲しいパターンだ。

集約だけならこう。

select
  count(distinct a.id) as total_assets,
  sum(case when pa.relation = 'reused' then pa.instances end) as total_reused_inst,
  round(
    sum(case when pa.relation = 'reused' then pa.instances end)::numeric
    / nullif(count(distinct a.id), 0),
    2
  ) as avg_reuse_per_asset
from assets a
left join project_assets pa on pa.asset_id = a.id;

ただ、この平均はほぼ嘘に近い。実際には少数の資産が突出して再利用され、大半は 1 回で終わる、というロングテール分布になる。window function で資産ごとに見ておいた方が話が早い。

select
  a.id,
  a.name,
  a.kind,
  sum(case when pa.relation = 'reused' then pa.instances else 0 end)
    over (partition by a.id) as reused_inst_per_asset,
  rank() over (
    order by sum(case when pa.relation = 'reused' then pa.instances else 0 end)
    desc
  ) as rank_by_reuse
from assets a
left join project_assets pa on pa.asset_id = a.id;

この形にしておくと、「上位数%の資産が全再利用の大半を占める」といった話が同じテーブルから出せる。筆者の環境では、この rank をずっと眺めていると「次に何を作れば効きそうか」の直感が付く。平均値からは絶対に得られない感覚だ。

算数の 3 本目 — 再利用有無での時間当たり収益差

これが一番効いた。プロジェクトを「再利用資産を 1 つ以上含む」「まったく含まない」の 2 群に分けて、時間当たり単価の分布を比べる。実数値は出さないが、クエリの形はそのまま貼る。

with project_rev as (
  select
    p.id as project_id,
    p.hours,
    sum(pa.unit_price_local * pa.instances / r.rate) as revenue_jpy,
    bool_or(pa.relation = 'reused') as has_reuse
  from projects p
  join project_assets pa on pa.project_id = p.id
  join fx_daily_rates r
    on r.base_ccy = pa.currency
   and r.quote_ccy = 'JPY'
   and r.rate_date = date_trunc('day', pa.created_at)::date
  group by p.id, p.hours
)
select
  has_reuse,
  count(*) as n_projects,
  round(avg(revenue_jpy / nullif(hours, 0))::numeric, 0) as avg_rev_per_hour_jpy,
  round(
    percentile_cont(0.5) within group (order by revenue_jpy / nullif(hours, 0))::numeric,
    0
  ) as median_rev_per_hour_jpy
from project_rev
group by has_reuse;

返る形はこう(数値はダミー、傾向だけ):

 has_reuse | n_projects | avg_rev_per_hour_jpy | median_rev_per_hour_jpy
-----------+------------+----------------------+-------------------------
 false     |         14 |                7,800 |                   7,200
 true      |         21 |               13,400 |                  11,900

平均と中央値の両方を出しているのは、片方だけだと外れ値の話ができないためだ。差の大きさそのものより、「差が安定して出るか」の方が意思決定には効く。

粒度 — 数字を振り回す一番の原因

ここまでのクエリは、instances をどう数えたかで結果が全部変わる。「作る単位」の定義が曖昧なまま SQL を書くと、後で必ず揉める。

具体例を出す。ニュースレター配信テンプレートを 1 本作った。3 店舗 × 6 ヶ月で回した。これを 1 asset × 18 instances と数えるか、18 assets と数えるかで、再利用率は 0% と 94% の間を振れる。前者は「テンプレそのものは 1 個しかなく、そのうち 18 回インスタンス化された」、後者は「配信 1 回 1 回を作ったものと見なす」だ。

筆者はほぼ常に前者を採る。理由は 1 つで、「再利用によって工数がどれだけ減ったか」を測りたいからだ。18 回配信するのに 18 回作り直したなら再利用ではない。1 回作って 17 回コピーしたなら、それは 17 回ぶんの再利用だ。この観点だと、asset は「作る労力が発生する最小単位」で、instances は「その労力を割り当てた先の数」になる。

ややこしいのは、この粒度が資産の kind ごとに変わることだ。SQL view なら 1 view = 1 asset で自然だが、Shopify の Liquid スニペットは「1 個作って 10 テーマに埋めた」時に 1 asset × 10 instances と数えるのか、10 assets × 1 instance と数えるのか、案件によって揺れる。正直、まだ筆者も答えを一本化できていない。今のところ「後から SQL で書き換えたときに、過去の数字が変にならない方」を優先している。

通貨 — 薄い正規化テーブル 1 つで済ませる

JPY と KRW が同じ表に入る。project_assets の 1 行 1 行に為替を持たせる案は最初考えたが、やめた。理由は 2 つ。履歴データが線形に膨張することと、後日レートを補正したくなったときに UPDATE の範囲が広すぎることだ。

代わりに fx_daily_rates を薄く 1 表持って、view で JPY に寄せる。

create table fx_daily_rates (
  base_ccy   text not null,
  quote_ccy  text not null,
  rate_date  date not null,
  rate       numeric(18,8) not null,
  primary key (base_ccy, quote_ccy, rate_date)
);
create view v_project_assets_jpy as
select
pa.*,
pa.unit_price_local / r.rate as unit_price_jpy
from project_assets pa
join fx_daily_rates r
on r.base_ccy = pa.currency
and r.quote_ccy = 'JPY'
and r.rate_date = date_trunc('day', pa.created_at)::date;

レートが無い日は行が消えるので、実運用では lateral で「その日以前の最新レート」を引く形に直すことが多い。ただ最初は単純な等式 join で始めて、抜けが目に見えるようにしておいた方が安全だと感じる。抜けを LEFT JOIN で隠すと、後で「なぜ売上が過小に見えるのか」を追う羽目になる。

ハマったところ 2 つ

1 つ目。instances 列を後付けで足した。既存行は default で 1 が入る。ここで過去の「1 回作って 18 回使い回した」プロジェクトが、全部 instances=1 のまま残り、再利用率がその期間だけ極端に低く見える現象が出た。ALTER で列を足すときは、既存データの意味を先に整理してから足すべきだった。default 1 は「無難」に見えて、意味的には嘘だ。

2 つ目。relation の 'created'/'reused' 判定を今のところ手で入れている。プロジェクト立ち上げ時に「これは新規、これは前の案件から持ってきた」を人が決める。この判定にバイアスが入るのは避けられない。「持ってきた」ものを心情的に「新しく作った」ことにしたくなる瞬間が、正直ある。ここを LLM を混ぜて判定させる話は、次回に回す。

次回

次回は書き手が複数いる DB — 人間と複数の LLM エージェントが同じ表に書き込む状況で、推測と確定をどう隔離するかを書く。written_by と status の 2 列で、上の「手動判定のバイアス」を含めて処理する話になる予定だ。筆者は普段、受託開発で AI・自動化系の案件をやっていて、この設計は現場のログを整理する過程で自然と落ち着いた形になった。