PostgreSQL 基礎講座 #8 ビュー・CTE・ウィンドウ関数 — 複雑なクエリを読みやすくする

インフラ PostgreSQL データベース
読了 4分

第 3 回の結合・集計が組み合わさり始めると、クエリはすぐに画面 1 つを超えます。今回はその複雑さを扱う 3 つの道具です。ビューはクエリに名前を付け、CTE はクエリを段階に分け、ウィンドウ関数は「グループごとの順位・比較」という、集計ではできない計算を開いてくれます。

ビュー: 繰り返されるクエリに名前を #

ビュー作成 
CREATE VIEW paid_order_stats AS
SELECT user_id, count(*) AS order_count, sum(amount_jpy) AS total_jpy
FROM orders
WHERE status = 'paid'
GROUP BY user_id;

SELECT * FROM paid_order_stats WHERE total_jpy >= 100000;

ビューは保存された SELECT です。データのコピーではなく、参照するたびに元のクエリが実行される名札なので、常に最新でストレージも使いません。コードのあちこちで繰り返される結合・フィルタの組み合わせ(「決済完了の注文」「アクティブユーザー」)に名前を付けて、チームの共通語彙にするのがビューの居場所です。

集計が重くて毎回実行するのが負担なら、マテリアライズドビューがあります。CREATE MATERIALIZED VIEW は結果を物理的に保存するので参照は速いですが、元が変わっても REFRESH MATERIALIZED VIEW をするまでは古い値のままです。「正確に今」が必要なら普通のビュー、「5 分前の値でいいから速く」ならマテリアライズドビュー + 定期 REFRESH が判断基準です。

CTE: クエリを上から下へ記述する #

第 3 回では重複集計を避けるためにサブクエリを結合しましたが、サブクエリが増えるほど、クエリは内側から読まなければならないパズルになります。WITH 句(CTE、Common Table Expression)は同じことを上から下へ記述させてくれます。

CTEで段階的に記述 
WITH order_stats AS (
    SELECT user_id, sum(amount_jpy) AS total_jpy
    FROM orders GROUP BY user_id
),
review_stats AS (
    SELECT user_id, count(*) AS review_count
    FROM reviews GROUP BY user_id
)
SELECT u.name, coalesce(os.total_jpy, 0) AS total_jpy, coalesce(rs.review_count, 0) AS review_count
FROM users u
LEFT JOIN order_stats os ON os.user_id = u.id
LEFT JOIN review_stats rs ON rs.user_id = u.id;

「注文の集計を作り、レビューの集計を作り、ユーザーに付ける」という思考の順序が、そのままクエリの順序になります。性能面では、プランナが CTE を本文に展開して(インライン化)サブクエリと同等に扱うのが現在の既定の動作なので、読みやすさのために性能を犠牲にしているのではという心配は不要です。 確認が必要なら、いつもどおり EXPLAIN です。

ウィンドウ関数: 行を減らさない集計 #

GROUP BY はグループごとに 1 行へ畳みます。ところが「各注文の行に、そのユーザーの注文合計も一緒に」のような要求は、行を畳んではいけません。ウィンドウ関数は行をそのままにして、各行の視界(ウィンドウ)の中で計算します。

ウィンドウ関数の基本形 
SELECT id, user_id, amount_jpy,
       sum(amount_jpy) OVER (PARTITION BY user_id) AS user_total_jpy,
       rank() OVER (ORDER BY amount_jpy DESC)      AS overall_rank
FROM orders;

OVER がウィンドウ関数の目印で、PARTITION BY は「誰同士をまとめて見るか」、その中の ORDER BY は「どの順序で数えるか」です。GROUP BY と違い、結果の行数はそのままです。

代表パターンが、第 3 回で予告したグループごとの上位 N 件です。

ユーザーごとの最新注文3件 
-- ユーザーごとの最新の注文 3 件
SELECT *
FROM (
    SELECT o.*,
           ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
    FROM orders o
) t
WHERE rn <= 3;

ROW_NUMBER() がユーザーごとに最新から 1、2、3……と番号を振り、外側で 3 以下だけを残します。1 件なら第 3 回の DISTINCT ON が短く、N 件からはこのパターンが標準です。ウィンドウ関数の結果は WHERE に直接書けないので(計算順序上 WHERE が先です)、サブクエリか CTE で一度包む形までがセットです。

時系列比較の定番 LAG も同じ構造です。

LAGで前月比 
-- 月別売上と前月比
SELECT month, revenue,
       revenue - LAG(revenue) OVER (ORDER BY month) AS diff_from_prev
FROM monthly_revenue;

LAG はウィンドウの順序で 1 つ前の行の値を持ってきます(最初の行は NULL)。前月比、直前のイベントとの間隔のように、「隣の行との比較」がすべてこの 1 つの形で解けます。

まとめ #

  • ビューは保存された SELECT で常に最新です。重い集計を先に計算しておくならマテリアライズドビュー + REFRESH です。
  • CTE はクエリを上から下へ記述させてくれます。プランナがインライン処理するので、読みやすさの代償に性能を払うことはありません。
  • ウィンドウ関数は行を減らさない集計です。OVER + PARTITION BY + ORDER BY の 3 部品がすべてです。
  • グループごとの上位 N 件は ROW_NUMBER + サブクエリのフィルタが標準パターンです。1 件なら DISTINCT ON が近道です。
  • LAG/LEAD で隣の行との比較(前月比など)が 1 行になります。次回は基礎の締めくくり、ロール・権限・バックアップです。
X