SmartHR Tech Blog

SmartHR 開発者ブログ

複雑な要件を1クエリで完結させる、PostgreSQL活用術

PdE

こんにちは、SmartHR のプロダクトエンジニアの横山です！ 今日は、とあるプロダクト開発で実装した巨大な SQL において、一般的な構文（UNIONやJOINなど）の影に隠れがちですが、とても助けになった PostgreSQL の機能を紹介します。

巨大な SQL と聞くと、複雑性や可読性の観点から敬遠されることも多いです。しかし、パフォーマンス要件的に必要に迫られたり、アプリケーション層の複雑さを解消するために、あえて DB 側でロジックを解決し、1 クエリで完結させる選択が必要な場面も、稀にですが確実に存在します。

本記事では、実際のプロダクトコードで活用したテクニックの中から、痒いところに手が届いた機能をピックアップしました。ORM だけでは解決できない課題に直面したとき、あるいはパフォーマンスチューニングをするときの一助となれば幸いです。

目次

複雑なクエリを保守しやすくするための基本

本題のテクニックに入る前に、前提となる複雑なクエリを書く（読み解く）ための基礎体力について軽く触れておきます。なぜならば複雑な要件を 1 クエリで解決しようとすると、SQL もまた複雑になりがちだからです。

以下に挙げる 2 つの知識（CTE とウィンドウ関数）は現代的な SQL を書く上で必須です。もし初めて聞いたという方は、技術書や公式ドキュメント（あるいは AI）で深掘りしてみることをおすすめします。ここの理解度で、SQL の自由度は劇的に変わります。

WITH句（CTE）でクエリを構造化する

複雑な SQL と戦うための最大の武器、それが CTE（Common Table Expressions：共通テーブル式） です。 簡単に言えば、SQL 内で一時的なテーブルに名前をつけて定義できる機能です。本記事では「CTE の WITH」と呼ぶことがあるため、WITH 句という言葉だけを知っている方も、お手数ですが CTE という単語もセットで覚えてください。

古いスタイルの SQL では、サブクエリ（副問合せ）をネストして書くことが一般的でした。しかし、ネストが深くなると内側から外側へ読み解く必要があり、脳のメモリを消費するコードになりがちです。

WITH句を使うと、処理を上から下へ流れるように記述できます。アプリケーションコードでいう変数や関数に切り出して名前をつける感覚に近いです。

以下にサンプルの SQL を示します。本来はもっとコンパクトにまとめられる SQL ですが、あくまでサブクエリのネストやWITH句の例として、SELECT を分けています。

-- 😭 読みづらい：ロジックが増えるたびにインデントが深くなる
SELECT
  u.name,
  high_scorers.avg_score
FROM
  (
    SELECT -- 3. さらにその中から平均80点以上のみ抽出（フィルタリング）
      user_id,
      avg_score
    FROM
      (
        SELECT -- 2. 抽出したデータでユーザーごとに平均点を算出（集計）
          user_id,
          AVG(score) as avg_score
        FROM
          (
            SELECT -- 1. まず直近の対象データのみを抽出（絞り込み）
              user_id,
              score
            FROM
              test_scores
            WHERE
              created_at >= '2024-01-01'
          ) AS recent_scores
        GROUP BY
          user_id
      ) AS avg_scores
    WHERE
      avg_score >= 80
  ) AS high_scorers
  JOIN users u ON u.id = high_scorers.user_id;

-- 😍 読みやすい：WITH句ならロジックが上から下へ流れる
WITH recent_scores AS (
  -- 1. まず直近の対象データのみを抽出
  SELECT
    user_id,
    score
  FROM
    test_scores
  WHERE
    created_at >= '2024-01-01'
),
avg_scores AS (
  -- 2. 抽出したデータでユーザーごとに平均点を算出
  SELECT
    user_id,
    AVG(score) as avg_score
  FROM
    recent_scores
  GROUP BY
    user_id
),
high_scorers AS (
  -- 3. 平均80点以上のみ抽出
  SELECT
    user_id,
    avg_score
  FROM
    avg_scores
  WHERE
    avg_score >= 80
)
SELECT
  u.name,
  high_scorers.avg_score
FROM
  high_scorers
  JOIN users u ON u.id = high_scorers.user_id;

私が実装した大きなクエリも、この WITH を複数連ねることで、ロジックをステップごとに分解して記述しています。

行を集約せずに集計できるウィンドウ関数

もう一つのカギが ウィンドウ関数（Window Functions） です。これは WINDOW() という特定の関数があるわけではなく、SUM や RANK などの関数に OVER 句を組み合わせることで実現する機能の総称です。

SQL には SUM や COUNT などの集約関数がありますが、通常の GROUP BY とともにこれらを使うと、行がグループごとに 1 行に圧縮（集約）されてしまいます。

「個別の明細行（誰が、何を、いつ）は残したいけど、全体の集計値（合計、平均、順位など）も横に添えたい」

そんなワガママな要望を叶えるのがウィンドウ関数です。OVER (...) というキーワードが出てきたら、その手前にある関数がウィンドウ関数というグループに属しているんだなと理解すれば大丈夫です。

構文のイメージと、実際の挙動を見てみましょう。

-- ↓↓↓ ※OVERが後ろにあるSUMはウィンドウ関数として振る舞う！
SUM(amount) OVER (PARTITION BY user_id)

例えば、「各ユーザーの購入明細（商品名・金額）を出しつつ、その横に『そのユーザーの合計購入額』も出したい」というケースで考えてみます。

SELECT
  user_id,
  product_name,
  amount,
  -- OVER (PARTITION BY user_id) でユーザーごとの合計を計算して、行を集約せずに付与
  SUM(amount) OVER (PARTITION BY user_id) as user_total_amount
FROM
  sales
ORDER BY
  user_id;

結果は以下のようになるはずです。

user_id product_name amount user_total_amount
A さん キーボード 10,000 13,000
A さん マウス 3,000 13,000
B さん モニター 20,000 20,000
C さん ケーブル 1,000 6,000
C さん 充電器 5,000 6,000

このように、明細行（キーボード、マウス...）を残したまま、集計結果（A さんなら合計 13,000）をすべての行に持たせることができます。通常の GROUP BY では明細行が消えてしまうため、これはウィンドウ関数ならではの機能です。

SUM は通常は集約関数ですが、OVER をつけるとウィンドウ関数として振る舞います。ウィンドウ関数としてのみ振る舞うDENSE_RANK などもあります。DENSE_RANK はこの記事で紹介しています。

ウィンドウ関数の行を集約しない特性が、アプリケーションが必要とする複雑なデータ構造を 1 回のクエリで作る上で非常に重要になります。

1 クエリの幅を広げる活用術

ここからは、実際にプロダクトのクエリの中で活躍した、PostgreSQL の知っておくと嬉しいテクニックを紹介します。

任意の ID リスト順にソートしたい！ unnest(ARRAY[...]) + WITH ORDINALITY + CTE（WITH句）

とてもややこしいのですが、ここでいう WITH ORDINALITY の WITH は、先ほど述べた CTE（WITH 句）とは全く別物です。

さて、「すでにある ID のリスト順（[id_B, id_A, id_C]）の通りに検索結果を返したい」要件は、しばしば発生します。 通常、WHERE id IN (...)のように検索すると、返却されるレコードの順序はバラバラになります。

その並び替えを事前に定義した ID 順にしたい場合は、もしそのリストが並び替えでしか使われていないのであれば、array_position という関数を使う選択肢があります。 これは array_position(並び替え済みの値の配列, 今の行の値) という形式で、「その値が配列の何番目にあるか（インデックス）」を返してくれる関数です。これを利用してORDER BYを書くことも可能です。

-- 絞り込みは別途必要なので、絞り込みをする場合はIDのリストがクエリに2回登場する
SELECT * FROM users
WHERE id IN ('id_B', 'id_A', 'id_C' ...)
ORDER BY array_position(ARRAY['id_B', 'id_A', 'id_C', ...], id);

書き味はシンプルです。シンプルさはとても大切です。ただ、もしクエリの複数箇所でその ID リストを使いたい場合（例：WHERE句での絞り込みと、ORDER BYでのソートの両方など）、そしてそのリストが長大になる場合は、共通化した方が良いでしょう。巨大な配列をクエリ内に何度も登場させたくありません。

もし ID リストが数百〜数千件と長大であった場合、その配列リテラルをクエリのあちこちに埋め込むと、SQL 全体のサイズが肥大化します。これは単に人間にとって読みにくいだけでなく、DB のパーサー（SQL の構文解析機能）に無駄な負荷をかけ、通信量も増大させてしまうため、パフォーマンスの観点でも好ましくありません。

unnest(ARRAY[...]) + WITH ORDINALITYを使うと、CTE の WITH と組み合わせて以下のように書けます。

WITH sorted_ids AS (
  SELECT
    *
  FROM
    -- ここでIDリストを一度だけ定義し、順序番号(sort_num)を振る
    unnest(ARRAY['id_B', 'id_A', 'id_C', ...]) WITH ORDINALITY AS t(id, sort_num)
)
SELECT
  users.*
FROM
  users
  -- IDリストと結合することで絞り込みと順序情報の付与を同時に行う
  JOIN sorted_ids ON users.id = sorted_ids.id
ORDER BY
  sorted_ids.sort_num;

このように書くと、長大なリストの登場は一回だけで済ますことができます。 長いリストは CTE で一度だけ定義し、テーブルとして使い回す。複雑なクエリをスマートに保つテクニックの一つです。

ちなみに、コード内の AS t(id, sort_num) という部分は、テーブルの別名とカラムの別名を同時に定義する構文です。

WITH ORDINALITY を使うと、元のデータの横に連番（1, 2, 3...）が自動的に付与されます。ここでは以下のように名前をつけています。

  • t: 一時的なテーブル名
  • id: 1 つ目のカラム（unnest した ID そのもの）
  • sort_num: 2 つ目のカラム（自動付与された連番）

こうして名前を定義しておくことで、後続の処理でORDER BY sorted_ids.sort_numのように指定し、リストの定義順通りにソートできるようになります。

最新の履歴だけ欲しい！ JOIN で増えた行へマークする DENSE_RANK

ランキング関数といえば売上ランキングのような用途が真っ先に思い浮かびますが、実務、特に履歴管理を含む複雑なデータを扱う場面では、少し違った用途で活躍します。

具体的には、「1 対多（1:N）の JOIN を含む履歴データから、最新の 1 件（N 行すべて）を取得したい」ケースです。

例として、エンジニアの評価履歴（Evaluations）を取得する機能を考えてみましょう。1 回の評価には複数の担当プロジェクト（Projects）が紐付いています。これらを結合すると、データは以下のようになります。

社員名 評価完了日 担当プロジェクト
A さん 2025-04-01 基盤刷新 PJ
A さん 2025-04-01 新卒メンター
A さん 2024-10-01 管理画面改修
A さん 2024-10-01 決済機能追加
B さん 2025-03-30 基盤刷新 PJ
B さん 2025-03-30 新卒メンター

この状態から各社員の最新の評価データのみを抽出したいとき、どうしますか？

ここで DENSE_RANK の出番です。これは同率順位があった場合、番号を飛ばさずに詰めて採番します。評価完了日が同じであれば、JOINによって行が増えていても、それらは全て同率 1 位として扱われます。

SELECT
  *,
  DENSE_RANK() OVER (
    PARTITION BY employee_id
    ORDER BY evaluation_date DESC -- 日付が同じなら全部1位
  ) as rank_num
FROM
  evaluations
  JOIN projects ON ...

結果は以下のように、rank_num を取得できます。

社員名 評価完了日 担当プロジェクト rank_num
A さん 2025-04-01 基盤刷新 PJ 1
A さん 2025-04-01 新卒メンター 1
A さん 2024-10-01 管理画面改修 2
A さん 2024-10-01 決済機能追加 2
B さん 2025-03-30 基盤刷新 PJ 1
B さん 2025-03-30 新卒メンター 1

最後に自分で名付けた rank_num で絞り込みをすれば、各社員の最新のプロジェクトを取得することができます。この絞り込みは、ウィンドウ関数と同じ階層では WHERE に書けません。別の CTE などで書かなくてはいけないため注意してください。

DENSE_RANK に類似した関数はいくつかあり、例えば ROW_NUMBER や RANK があります。いずれも採番のルールが違うため、今回のケースでは適しません。

BOOL_OR（ウィンドウ関数）で「一つでも〇〇なら true」を直感的に

「あるユーザーの宿題一覧の中に、一つでも『未提出』が含まれているか？」
「このプロジェクトのタスク一覧の中に、一つでも『緊急』があるか？」

このように、グループ内のデータ全体を見渡して、「どれか一つでも条件を満たせばフラグを立てたい」「どれか一つでも条件を満たしているグループのみ絞り込みたい」ケースはよくあります。

PostgreSQL なら、BOOL_OR をウィンドウ関数として使うことで、これを自然言語に近い形で記述できます。

SELECT
  *,
  -- プロジェクト内に緊急ステータスのタスクが1つでもあれば、そのタスクは緊急プロジェクトのタスクとみなす
  BOOL_OR(status = '緊急')
    OVER (PARTITION BY project_id) as is_urgent_project
FROM tasks;

結果の is_urgent_project は以下のようになります。

task_name project_id status is_urgent_project
タスク A pj_1 通常 true
タスク B pj_1 緊急 true
タスク C pj_2 通常 false
タスク D pj_2 通常 false

BOOL_OR（論理和）という名前の通り、パーティション内のどれか一つでも（OR）true であれば、結果は true になります。

逆に、「グループ内の すべてが true のときだけ true」を返したい場合は、BOOL_AND（標準 SQL だと EVERY）が使えます。

ウィンドウ関数に空の OVER () を使って全体集計

通常、ウィンドウ関数といえば PARTITION BY で区切って使うものですが、あえて何も指定しないことで実現できる便利な挙動があります。

空の OVER () とその使い道

Web アプリケーションで一覧画面を作る際、必ずと言っていいほど実装するのがページネーションです。「現在のページの 20 件」と「全ヒット件数（1530 件など）」の両方が必要になります。

通常、これを実装するには 2 回のクエリを発行するのが定石です。

  • データ取得用: LIMIT 20 OFFSET 0 をつけて検索
  • カウント用: LIMIT を外して COUNT(*) で全件数を取得

しかし、ウィンドウ関数を使えば 1 回のクエリで完結できます。 OVER の中に PARTITION BY を書かずに 空っぽ () にすると、検索結果全体を一つの大きなグループとして扱えます。

SELECT
  id,
  name,
  -- WHERE句を通過した全行数をカウントして、各行にくっつける
  COUNT(*) OVER () as total_count
FROM
  users
WHERE
  name LIKE '%sato%'
LIMIT 20;

このようなクエリを書けば、以下のように、画面に出す「全 N 件」のようなテキストで使えそうな全体の件数を横に添えることができます。

id name total_count
101 sato takashi 1530
102 sato hanako 1530
... ... ...
120 sato ken 1530

⚠️ パフォーマンス上の注意点

このテクニックは「クエリの回数を減らせる」というメリットがありますが、常に最適解とは限りません。むしろ最適解となることは少ないはずです。

単純な COUNT(*) であればインデックススキャンのみで高速に完了するケースでも、ウィンドウ関数COUNT(*) OVER ()を使うと、テーブル本体へのアクセスが必要になりパフォーマンスが落ちる可能性があります。

  • インデックスが効く単純な検索の場合は、クエリを 2 回に分けたほうが速い場合が多い
  • 複雑な検索でフルスキャンが避けられない場合は、1 回で済ませるこのテクニックが有効なことがある
    • ※常にとも言い切れないので計測が大事

と覚えておくとよいと思います。

また、COUNT(*) OVER () は JOIN で膨れ上がった重複レコードもすべてカウントしてしまいます。「JOIN で生まれた重複を除いた純粋な母数が知りたい」という場合には、次に紹介する方法が有効です。

応用:ページネーション用に DENSE_RANK の値を使っていたとき、ついでに JOIN で膨れる前の件数を出す

私の実装ではこの OVER () は、先ほど紹介した DENSE_RANK との組み合わせで利用しました。 ページネーションの判断基準（「1 ページ目＝ 1 人目〜10 人目」など）を作るために既に DENSE_RANK の数値を利用していたので、その数値から全体の件数も出してしまおうという考えです。

例えば、1 人のユーザーが複数の行（履歴）を持っているデータにおいて、全行数ではなく全ユーザー数をベースに、1 ページあたり 10 ユーザーのページネーションを実装したい場合を考えてみましょう。

単に COUNT(*) OVER () をすると行数分（例えば 100 行）が返ってきてしまいますし、LIMIT 10 をかけると「10 行（おそらくユーザーが重複し、 10 ユーザー未満の数しか含まれない）」しか取れません。

そこで、DENSE_RANK で振った番号を活用します。

WITH ranked_users AS (
  SELECT
    user_id,
    other_columns,
    -- ユーザーごとに連番を振る（1, 1, 2, 2, 2, 3...）
    DENSE_RANK() OVER (ORDER BY user_id) as user_num
  FROM
    user_histories
)
SELECT
  *,
  -- 連番の最大値を取れば、それがすなわち「全ユーザー数」になる
  MAX(user_num) OVER () as total_unique_users
FROM
  ranked_users
WHERE
  -- ここで「1ページ目の10人分」を指定。行数は10行以上になる可能性があるが、ユーザー数は10人
  user_num BETWEEN 1 AND 10;

このようにMAXを使うことで、JOIN 済みの結果セットが返ってくる状態でも、このロジックを使って実質のページネーションとともに、その母数がいくつかを導出できます。

もちろん、普通に COUNT(DISTINCT user_id) OVER () することでも全ユーザー数は出せますが、今回はページネーションの絞り込み用として既に DENSE_RANK を計算していたため、その計算結果を再利用するほうがクエリとして無駄がないと判断しました。

さよなら、アプリ側での整形ループ！ JOIN 済みの結果を JSON にする jsonb_agg

やむをえずアプリケーション層で（ほぼ）生 SQL を書く時にあるあるなのが、1 対多（Users : Tags など）のテーブルを JOIN してデータを取得した結果、行の増幅が発生してアプリ側での再整形が必要になることです。面倒くさいですよね。アプリケーション層では以下のような処理が必要になるはずです。

  1. DB から大量の行を取得する
  2. for や each でループを回す
  3. 1 つ前の ID と今の ID をチェック。値が違ったら、新しいユーザーオブジェクトを作る
  4. 値が同じなら、タグ配列に値を push する

このように「ID が変わったかチェックして何かしらを行う処理」は、コードが複雑になりがちです。素朴な一対多が一つだけあるのだったらまだ大丈夫でしょうが、二重三重に一対多の関係があって JOIN されたレコードを、頭に入れて再構築するのは大変です。

アプリケーションサーバーよりも DB サーバーの方がスケールしにくいため、この手法を嫌う人もいるかもしれませんが、DB 側で JSON に集約してから値を返してもらうのも一つの手です。

json 型と jsonb 型

PostgreSQL には JSON を扱うためのデータ型として、json 型と jsonb 型の 2 種類が存在します。そのことを知らずに JSON を扱おうとすると、直感的に json 型を使おうとしてしまうかもしれないので、先に 2 つの型を紹介しておきます。

json 型は入力されたテキストをそのまま文字列として扱う型です。書き込みは速いですが、検索や加工のたびに構文解析が必要なため、参照は遅いです。

もう一方の jsonb 型は入力されたテキストをバイナリ形式に分解・変換した型です。jsonb の b は binary の b です。書き込みは少し遅いですが、参照や検索が非常に高速で、もしカラムとして追加する場合はインデックス（GIN）を貼ることも可能です。

json 型を使うのはレアなケースで、基本的には jsonb 型を使うと覚えておけば大丈夫です。今回この記事でも json 型の値は利用せず、 jsonb 型の値を利用します。

jsonb_agg（集約関数）で行を配列に畳み込む

増幅された行を畳み込むのにはjsonb_aggを利用します。これはGROUP BYで集約された複数の行を、一つの jsonb 型の配列の値に変換する集約関数です。

ここで重要なテクニックが、集約関数内のORDER BYです。 これを書かないと、生成される jsonb 型の配列の値、例えば["タグA", "タグB", "タグC"]の中身の順序はランダムになってしまいます。配列内の順序を保証したい場合、以下のように関数の中でソート指定を行う必要があります。

SELECT
  user_id,
  jsonb_agg(
    tag_name
    ORDER BY created_at -- JSON配列の中身を付与された順にソート！
  ) as tags
FROM user_tags
GROUP BY user_id;

こうすれば、アプリケーション側で「ID が変わったかどうか」を判定するループ処理を書く必要はもうありません。DB から返ってきた JSON をそのまま利用するだけになり、ロジックが劇的にシンプルになります。

ただし、jsonb_agg は集約結果をすべてメモリ上で構築してから返却します。数万〜数十万行といった大量データを 1 つの値にまとめようとすると、DB サーバーのメモリを急激に圧迫する恐れがあるため、用法用量を守って正しくお使いください。

jsonb_build_array で jsonb の配列を作る

jsonb_agg が複数行をまとめて jsonb 型の配列の値にするのに対し、jsonb_build_arrayは指定したカラムや値をその場で jsonb 型の配列の値にする関数です。

-- カラムの値と固定値を混ぜて配列にする例
SELECT jsonb_build_array(id, 'active') FROM users;
-- 結果: [1, "active"]

私が実装したクエリでは、jsonb_build_arrayを型合わせのために利用しました。諸事情により「ある配列（jsonb 型）」と「別の ID（単一の数値）」を UNION ALL で結合したかったのですが、型が違うとエラーになります。そこで、単一の ID も jsonb_build_array(id) で [101] のような配列に変換することで、強引に型を揃えてUNION ALLしました。少しマニアックですが、データの形式を揃えたい時などに利用できます。

生成した jsonb 型の値に対する検索

生成した jsonb 型の値に対して、後から検索をかけることも可能です。 PostgreSQL の jsonb 型は非常に優秀で、@>（包含）演算子 などを使用することで、生成後の値に対しても検索できます。

-- 配列の中に、特定のIDや文字列が含まれているか検索
WHERE value @> '["緊急"]'::jsonb

ただし、ここでもパフォーマンスには注意が必要です。私のケースのように UNION された結果や CTE で動的に生成された JSON データに対しては、インデックスを効かせることができません。今回はすでに対象件数が十分絞り込まれているコンテキストだったため許容しましたが、大量データに対して無邪気にインデックスの効かない検索処理を行うと、事故につながるリスクがあるためご注意ください。

jsonb 型の値への検索方法に対しては、本記事ではこれ以上触れず、簡単に紹介するに留めます。 過去に他の人が紹介した記事が出ているため、ぜひ参考にしてみてください！

tech.smarthr.jp

おわりに

結構ボリュームのある記事になってしまいました。

今回紹介したテクニックはすべて、実際のプロダクト開発における、とある 1 つの巨大クエリに詰め込まれたエッセンスです。

もちろん、無闇に複雑で巨大な SQL を書くことが正義ではありません。もしこれがミッションクリティカルな機能であったり、多くの人が頻繁に触れる箇所であれば、要件の再検討、テーブルの非正規化、アプリ側での処理分割などを選んでいたでしょう。

しかし、プロダクトの開発背景、パフォーマンス要件、開発速度……それらを天秤にかけ、あえて DB 側で完結させることが最適解となる瞬間は存在します。その時のために、PostgreSQL には強力な機能が備わっていることを知っていただければ幸いです。

最終的に 9 つの CTE が連なることとなった私のクエリ。将来改修が必要になった時、この記事を読み返して頭を抱えているのは、他ならぬ私自身かもしれません。

We Are Hiring!

SmartHR では一緒に SmartHR を作りあげていく仲間を募集中です！

少しでも興味を持っていただけたら、カジュアル面談でざっくばらんにお話ししましょう！

hello-world.smarthr.co.jp