PostgreSQL CTE・ウィンドウ関数活用術 - 高度なクエリを書く

はじめに

複雑なデータ分析や集計処理を行う際、ネストしたサブクエリが深くなり、SQLの可読性が著しく低下した経験はありませんか。また、「各グループ内での順位付け」「前後の行との比較」「累計値の算出」といった処理を、アプリケーション側で実装せざるを得なかったことはないでしょうか。

PostgreSQLのCTE（Common Table Expression、共通テーブル式）とウィンドウ関数を使いこなすことで、これらの課題をSQL単体でエレガントに解決できます。本記事では、CTEによるクエリの構造化、再帰CTEによる階層データの処理、そしてROW_NUMBER、RANK、LAG、LEAD、SUM OVERといったウィンドウ関数を活用した分析クエリの書き方を、実践的なサンプルコードとともに解説します。

この記事を読むことで、以下のことができるようになります。

CTEを使って複雑なクエリを可読性の高い形で構造化できる
再帰CTEで階層構造やツリー構造のデータを効率的に処理できる
ROW_NUMBER、RANK、DENSE_RANKで順位付けや重複排除ができる
LAG、LEADで前後の行と比較した差分計算ができる
SUM OVER、AVG OVERで累計や移動平均を算出できる

前提条件

PostgreSQL 14以降がインストールされていること
psqlまたはGUIツール（pgAdmin、DBeaver等）でデータベースに接続できること
SELECT文の基本的な構文（WHERE句、GROUP BY、ORDER BY等）を理解していること

基本的なSELECT文やJOINについては、以下の関連記事を参照してください。

サンプルデータの準備

本記事で使用するサンプルテーブルとデータを準備します。売上分析や組織階層の処理を想定したデータ構成です。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56


-- 社員テーブル（組織階層用）
CREATE TABLE employees (
    employee_id   SERIAL PRIMARY KEY,
    name          VARCHAR(100) NOT NULL,
    department    VARCHAR(50) NOT NULL,
    manager_id    INTEGER REFERENCES employees(employee_id),
    salary        INTEGER NOT NULL,
    hire_date     DATE NOT NULL
);

-- 売上テーブル（分析クエリ用）
CREATE TABLE sales (
    sale_id       SERIAL PRIMARY KEY,
    employee_id   INTEGER REFERENCES employees(employee_id),
    sale_date     DATE NOT NULL,
    amount        INTEGER NOT NULL,
    product_category VARCHAR(50) NOT NULL
);

-- 社員データの挿入（階層構造）
INSERT INTO employees (name, department, manager_id, salary, hire_date) VALUES
('田中太郎', '経営', NULL, 1200000, '2015-04-01'),
('鈴木花子', '営業', 1, 800000, '2018-04-01'),
('佐藤次郎', '営業', 1, 750000, '2019-06-01'),
('高橋美咲', '開発', 1, 850000, '2017-10-01'),
('伊藤健一', '営業', 2, 550000, '2020-04-01'),
('渡辺さくら', '営業', 2, 520000, '2021-04-01'),
('山本大輔', '営業', 3, 480000, '2022-04-01'),
('中村優子', '開発', 4, 600000, '2019-04-01'),
('小林翔太', '開発', 4, 580000, '2020-10-01'),
('加藤理恵', '開発', 4, 620000, '2018-07-01');

-- 売上データの挿入（2025年のデータ）
INSERT INTO sales (employee_id, sale_date, amount, product_category) VALUES
(2, '2025-10-01', 150000, '電子機器'),
(2, '2025-10-05', 80000, '家具'),
(2, '2025-10-12', 200000, '電子機器'),
(2, '2025-11-03', 120000, '電子機器'),
(2, '2025-11-15', 95000, '文具'),
(2, '2025-12-01', 180000, '電子機器'),
(3, '2025-10-02', 90000, '家具'),
(3, '2025-10-20', 110000, '電子機器'),
(3, '2025-11-08', 75000, '文具'),
(3, '2025-11-25', 160000, '電子機器'),
(3, '2025-12-10', 140000, '家具'),
(5, '2025-10-08', 65000, '文具'),
(5, '2025-10-22', 85000, '電子機器'),
(5, '2025-11-12', 70000, '家具'),
(5, '2025-12-05', 95000, '電子機器'),
(6, '2025-10-15', 55000, '文具'),
(6, '2025-11-02', 120000, '電子機器'),
(6, '2025-11-28', 80000, '家具'),
(6, '2025-12-15', 110000, '電子機器'),
(7, '2025-10-25', 45000, '文具'),
(7, '2025-11-18', 70000, '電子機器'),
(7, '2025-12-08', 60000, '家具');

CTE（共通テーブル式）の基本

CTEとは何か

CTE（Common Table Expression、共通テーブル式）は、WITH句を使用してクエリ内で一時的な名前付き結果セットを定義する機能です。サブクエリを名前付きのブロックとして分離できるため、複雑なクエリの可読性と保守性が大幅に向上します。

CTEの基本構文は以下の通りです。

1
2
3
4
5


WITH cte_name AS (
    -- CTEの定義（SELECTクエリ）
    SELECT ...
)
SELECT * FROM cte_name;

サブクエリとの比較

同じ結果を得るクエリをサブクエリとCTEで比較してみましょう。「部門ごとの平均給与を超える社員を抽出する」という要件を考えます。

サブクエリを使った場合は以下のようになります。

1
2
3
4
5
6
7
8
9


-- サブクエリによる実装（可読性が低い）
SELECT e.name, e.department, e.salary
FROM employees e
WHERE e.salary > (
    SELECT AVG(e2.salary)
    FROM employees e2
    WHERE e2.department = e.department
)
ORDER BY e.department, e.salary DESC;

同じ処理をCTEで書き直すと、以下のように構造化できます。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11


-- CTEによる実装（可読性が高い）
WITH dept_avg AS (
    SELECT department, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
)
SELECT e.name, e.department, e.salary, d.avg_salary
FROM employees e
JOIN dept_avg d ON e.department = d.department
WHERE e.salary > d.avg_salary
ORDER BY e.department, e.salary DESC;

実行結果は以下の通りです。

1
2
3
4
5
6


   name    | department | salary  |      avg_salary
-----------+------------+---------+----------------------
 高橋美咲   | 開発       | 850000  | 662500.0000000000
 加藤理恵   | 開発       | 620000  | 662500.0000000000
 鈴木花子   | 営業       | 800000  | 596000.0000000000
 佐藤次郎   | 営業       | 750000  | 596000.0000000000

CTEを使うことで、「部門ごとの平均給与を計算する」という処理がdept_avgという名前で明確に分離され、メインクエリの意図が理解しやすくなります。

複数のCTEを連結する

CTEは複数定義でき、後続のCTEから前のCTEを参照できます。これにより、段階的にデータを加工する処理を明確に表現できます。

以下は「月別の売上を集計し、さらにその中から売上が10万円を超える月だけを抽出し、上位3件を取得する」という処理です。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23


WITH monthly_sales AS (
    -- ステップ1: 月別売上を集計
    SELECT 
        DATE_TRUNC('month', sale_date) AS month,
        SUM(amount) AS total_amount,
        COUNT(*) AS sale_count
    FROM sales
    GROUP BY DATE_TRUNC('month', sale_date)
),
high_sales_months AS (
    -- ステップ2: 10万円超の月を抽出
    SELECT month, total_amount, sale_count
    FROM monthly_sales
    WHERE total_amount > 100000
)
-- ステップ3: 上位3件を取得
SELECT 
    TO_CHAR(month, 'YYYY年MM月') AS 月,
    total_amount AS 売上合計,
    sale_count AS 取引件数
FROM high_sales_months
ORDER BY total_amount DESC
LIMIT 3;

実行結果は以下の通りです。

1
2
3
4
5


     月      | 売上合計 | 取引件数
-------------+----------+----------
 2025年10月  |  880000  |    9
 2025年11月  |  890000  |    8
 2025年12月  |  585000  |    5

再帰CTE - 階層データの処理

再帰CTEの基本構造

再帰CTE（Recursive CTE）は、自分自身を参照するCTEです。階層構造やツリー構造のデータを処理する際に威力を発揮します。WITH RECURSIVEキーワードを使用して定義します。

再帰CTEの基本構造は以下の通りです。

1
2
3
4
5
6
7
8


WITH RECURSIVE cte_name AS (
    -- 非再帰項（アンカーメンバー）: 開始点
    SELECT ...
    UNION ALL
    -- 再帰項: 自分自身を参照
    SELECT ... FROM cte_name WHERE ...
)
SELECT * FROM cte_name;

再帰CTEは以下の順序で実行されます。

非再帰項（アンカーメンバー）を実行し、初期結果を生成
再帰項を実行し、前のステップの結果を使って新しい行を生成
再帰項が行を返さなくなるまで2を繰り返す
すべての結果を結合して返す

組織階層の取得

社員テーブルにはmanager_idで上司と部下の関係が定義されています。再帰CTEを使って、特定の社員から始まる組織ツリーを取得してみましょう。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31


WITH RECURSIVE org_tree AS (
    -- 非再帰項: 経営者（田中太郎）を起点とする
    SELECT 
        employee_id,
        name,
        department,
        manager_id,
        1 AS level,
        name AS path
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- 再帰項: 部下を再帰的に取得
    SELECT 
        e.employee_id,
        e.name,
        e.department,
        e.manager_id,
        ot.level + 1,
        ot.path || ' → ' || e.name
    FROM employees e
    INNER JOIN org_tree ot ON e.manager_id = ot.employee_id
)
SELECT 
    REPEAT('  ', level - 1) || name AS 組織図,
    department AS 部門,
    level AS 階層
FROM org_tree
ORDER BY path;

実行結果は以下の通りです。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12


         組織図          | 部門 | 階層
-------------------------+------+------
 田中太郎                 | 経営 |    1
   鈴木花子               | 営業 |    2
     伊藤健一             | 営業 |    3
     渡辺さくら           | 営業 |    3
   佐藤次郎               | 営業 |    2
     山本大輔             | 営業 |    3
   高橋美咲               | 開発 |    2
     小林翔太             | 開発 |    3
     加藤理恵             | 開発 |    3
     中村優子             | 開発 |    3

特定のマネージャー配下の全社員を取得

特定のマネージャー（例：鈴木花子）の配下にいる全社員を取得する例です。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17


WITH RECURSIVE subordinates AS (
    -- 起点となるマネージャー
    SELECT employee_id, name, department, manager_id, salary
    FROM employees
    WHERE name = '鈴木花子'
    
    UNION ALL
    
    -- 部下を再帰的に取得
    SELECT e.employee_id, e.name, e.department, e.manager_id, e.salary
    FROM employees e
    INNER JOIN subordinates s ON e.manager_id = s.employee_id
)
SELECT name, department, salary
FROM subordinates
WHERE name != '鈴木花子'  -- マネージャー自身は除外
ORDER BY salary DESC;

実行結果は以下の通りです。

1
2
3
4


    name    | department | salary
------------+------------+--------
 伊藤健一    | 営業       | 550000
 渡辺さくら  | 営業       | 520000

再帰CTEの注意点

再帰CTEを使用する際は、以下の点に注意が必要です。

終了条件の確保: 再帰項が最終的に空の結果を返すように設計しないと、無限ループになります
UNION vs UNION ALL: UNIONは重複を排除するためサイクル検出に役立ちますが、UNION ALLより遅くなります
メモリ消費: 大きな階層を処理する場合、中間結果がメモリを消費します

サイクルが存在する可能性があるデータでは、PostgreSQL 14以降で導入されたCYCLE句を使用できます。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10


WITH RECURSIVE search_graph AS (
    SELECT id, link, data, 1 AS depth
    FROM graph
    WHERE id = 1
    UNION ALL
    SELECT g.id, g.link, g.data, sg.depth + 1
    FROM graph g
    INNER JOIN search_graph sg ON g.id = sg.link
) CYCLE id SET is_cycle USING path
SELECT * FROM search_graph WHERE NOT is_cycle;

ウィンドウ関数の基本

ウィンドウ関数とは

ウィンドウ関数は、現在の行に関連する一連の行（ウィンドウ）に対して計算を行う関数です。通常の集計関数（SUM、AVGなど）と異なり、行をグループ化して1行にまとめることなく、各行に対して結果を返します。

ウィンドウ関数の基本構文は以下の通りです。

1
2
3
4
5


関数名(引数) OVER (
    [PARTITION BY 列名]  -- グループ分け（省略可）
    [ORDER BY 列名]      -- 順序指定（省略可）
    [フレーム指定]        -- 計算範囲（省略可）
)

以下は、各社員の給与と部門平均給与を同時に表示する例です。

1
2
3
4
5
6
7


SELECT 
    name,
    department,
    salary,
    AVG(salary) OVER (PARTITION BY department) AS dept_avg_salary
FROM employees
ORDER BY department, salary DESC;

実行結果は以下の通りです。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12


    name    | department | salary  |    dept_avg_salary
------------+------------+---------+-----------------------
 田中太郎    | 経営       | 1200000 | 1200000.0000000000
 高橋美咲    | 開発       |  850000 |  662500.0000000000
 加藤理恵    | 開発       |  620000 |  662500.0000000000
 中村優子    | 開発       |  600000 |  662500.0000000000
 小林翔太    | 開発       |  580000 |  662500.0000000000
 鈴木花子    | 営業       |  800000 |  596000.0000000000
 佐藤次郎    | 営業       |  750000 |  596000.0000000000
 伊藤健一    | 営業       |  550000 |  596000.0000000000
 渡辺さくら  | 営業       |  520000 |  596000.0000000000
 山本大輔    | 営業       |  480000 |  596000.0000000000

ウィンドウ関数と集計関数の違い

通常の集計関数とウィンドウ関数の違いを明確にするため、同じデータに対する処理を比較します。

1
2
3
4


-- 通常の集計関数: 部門ごとに1行にまとめられる
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;

1
2
3
4
5


 department |      avg_salary
------------+-----------------------
 経営       | 1200000.0000000000
 営業       |  596000.0000000000
 開発       |  662500.0000000000

1
2
3
4


-- ウィンドウ関数: 各行が保持され、集計値が付加される
SELECT name, department, salary,
       AVG(salary) OVER (PARTITION BY department) AS avg_salary
FROM employees;

ウィンドウ関数では元の行数が維持され、各行に集計結果が付加されます。

順位付け関数 - ROW_NUMBER / RANK / DENSE_RANK

ROW_NUMBER - 連番を振る

ROW_NUMBER()は、パーティション内で各行に一意の連番を振ります。同じ値があっても異なる番号が割り当てられます。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10


SELECT 
    name,
    department,
    salary,
    ROW_NUMBER() OVER (
        PARTITION BY department 
        ORDER BY salary DESC
    ) AS salary_rank
FROM employees
ORDER BY department, salary_rank;

実行結果は以下の通りです。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12


    name    | department | salary  | salary_rank
------------+------------+---------+-------------
 田中太郎    | 経営       | 1200000 |           1
 高橋美咲    | 開発       |  850000 |           1
 加藤理恵    | 開発       |  620000 |           2
 中村優子    | 開発       |  600000 |           3
 小林翔太    | 開発       |  580000 |           4
 鈴木花子    | 営業       |  800000 |           1
 佐藤次郎    | 営業       |  750000 |           2
 伊藤健一    | 営業       |  550000 |           3
 渡辺さくら  | 営業       |  520000 |           4
 山本大輔    | 営業       |  480000 |           5

RANK と DENSE_RANK - 同順位の扱い

RANK()とDENSE_RANK()は同じ値に対して同じ順位を付けますが、次の順位の扱いが異なります。

1
2
3
4
5
6
7
8
9


-- 同じ給与の社員がいる場合の動作を確認
SELECT 
    name,
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
    RANK() OVER (ORDER BY salary DESC) AS rank,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees
ORDER BY salary DESC;

実行結果は以下の通りです。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12


    name    | salary  | row_num | rank | dense_rank
------------+---------+---------+------+------------
 田中太郎    | 1200000 |       1 |    1 |          1
 高橋美咲    |  850000 |       2 |    2 |          2
 鈴木花子    |  800000 |       3 |    3 |          3
 佐藤次郎    |  750000 |       4 |    4 |          4
 加藤理恵    |  620000 |       5 |    5 |          5
 中村優子    |  600000 |       6 |    6 |          6
 小林翔太    |  580000 |       7 |    7 |          7
 伊藤健一    |  550000 |       8 |    8 |          8
 渡辺さくら  |  520000 |       9 |    9 |          9
 山本大輔    |  480000 |      10 |   10 |         10

同じ値がある場合の違いを明確にするため、給与データを追加して確認してみます。

1
2
3
4
5


-- 給与が同じ社員がいる場合の例（概念的な説明）
-- 例: 給与 600000 が2人いた場合
--   ROW_NUMBER: 1, 2, 3, 4  （一意の番号）
--   RANK:       1, 2, 2, 4  （同順位の次は飛ぶ）
--   DENSE_RANK: 1, 2, 2, 3  （同順位でも次は連続）

実践例: 部門ごとの上位2名を抽出

ROW_NUMBER()を使って、各部門の給与上位2名を抽出する例です。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15


WITH ranked_employees AS (
    SELECT 
        name,
        department,
        salary,
        ROW_NUMBER() OVER (
            PARTITION BY department 
            ORDER BY salary DESC
        ) AS rank
    FROM employees
)
SELECT name, department, salary
FROM ranked_employees
WHERE rank <= 2
ORDER BY department, salary DESC;

実行結果は以下の通りです。

1
2
3
4
5
6
7


    name    | department | salary
------------+------------+---------
 田中太郎    | 経営       | 1200000
 高橋美咲    | 開発       |  850000
 加藤理恵    | 開発       |  620000
 鈴木花子    | 営業       |  800000
 佐藤次郎    | 営業       |  750000

実践例: 重複データの排除

ROW_NUMBER()を使って、特定の条件で重複するデータから最新の1件だけを抽出できます。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16


-- 各社員の直近の売上のみを取得
WITH ranked_sales AS (
    SELECT 
        s.*,
        e.name AS employee_name,
        ROW_NUMBER() OVER (
            PARTITION BY s.employee_id 
            ORDER BY s.sale_date DESC
        ) AS rn
    FROM sales s
    JOIN employees e ON s.employee_id = e.employee_id
)
SELECT employee_name, sale_date, amount, product_category
FROM ranked_sales
WHERE rn = 1
ORDER BY amount DESC;

実行結果は以下の通りです。

1
2
3
4
5
6
7


 employee_name | sale_date  | amount | product_category
---------------+------------+--------+------------------
 鈴木花子       | 2025-12-01 | 180000 | 電子機器
 佐藤次郎       | 2025-12-10 | 140000 | 家具
 渡辺さくら     | 2025-12-15 | 110000 | 電子機器
 伊藤健一       | 2025-12-05 |  95000 | 電子機器
 山本大輔       | 2025-12-08 |  60000 | 家具

行間比較関数 - LAG / LEAD

LAG - 前の行の値を参照

LAG()関数は、現在の行から指定した行数だけ前の行の値を取得します。前月比や前日比の計算に便利です。

1
2
3


LAG(column, offset, default) OVER (...)
-- offset: 何行前を参照するか（デフォルト1）
-- default: 前の行が存在しない場合の値（デフォルトNULL）

以下は、月別売上と前月との差分を計算する例です。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14


WITH monthly_sales AS (
    SELECT 
        DATE_TRUNC('month', sale_date) AS month,
        SUM(amount) AS total_amount
    FROM sales
    GROUP BY DATE_TRUNC('month', sale_date)
)
SELECT 
    TO_CHAR(month, 'YYYY年MM月') AS 月,
    total_amount AS 売上,
    LAG(total_amount, 1) OVER (ORDER BY month) AS 前月売上,
    total_amount - LAG(total_amount, 1) OVER (ORDER BY month) AS 前月比
FROM monthly_sales
ORDER BY month;

実行結果は以下の通りです。

1
2
3
4
5


     月     |  売上  | 前月売上 | 前月比
------------+--------+----------+--------
 2025年10月 | 880000 |          |
 2025年11月 | 890000 |   880000 |  10000
 2025年12月 | 585000 |   890000 | -305000

LEAD - 次の行の値を参照

LEAD()関数は、現在の行から指定した行数だけ後の行の値を取得します。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17


-- 各売上と次回売上までの日数を計算
SELECT 
    e.name,
    s.sale_date,
    s.amount,
    LEAD(s.sale_date, 1) OVER (
        PARTITION BY s.employee_id 
        ORDER BY s.sale_date
    ) AS next_sale_date,
    LEAD(s.sale_date, 1) OVER (
        PARTITION BY s.employee_id 
        ORDER BY s.sale_date
    ) - s.sale_date AS days_to_next_sale
FROM sales s
JOIN employees e ON s.employee_id = e.employee_id
WHERE e.name = '鈴木花子'
ORDER BY s.sale_date;

実行結果は以下の通りです。

1
2
3
4
5
6
7
8


   name   | sale_date  | amount | next_sale_date | days_to_next_sale
----------+------------+--------+----------------+-------------------
 鈴木花子  | 2025-10-01 | 150000 | 2025-10-05     |                 4
 鈴木花子  | 2025-10-05 |  80000 | 2025-10-12     |                 7
 鈴木花子  | 2025-10-12 | 200000 | 2025-11-03     |                22
 鈴木花子  | 2025-11-03 | 120000 | 2025-11-15     |                12
 鈴木花子  | 2025-11-15 |  95000 | 2025-12-01     |                16
 鈴木花子  | 2025-12-01 | 180000 |                |

実践例: 売上の増減トレンド分析

LAGを使って、売上の増減を視覚的に表示する例です。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20


WITH daily_sales AS (
    SELECT 
        sale_date,
        SUM(amount) AS total_amount
    FROM sales
    GROUP BY sale_date
)
SELECT 
    sale_date AS 日付,
    total_amount AS 売上,
    LAG(total_amount) OVER (ORDER BY sale_date) AS 前回売上,
    CASE 
        WHEN total_amount > LAG(total_amount) OVER (ORDER BY sale_date) THEN '↑'
        WHEN total_amount < LAG(total_amount) OVER (ORDER BY sale_date) THEN '↓'
        WHEN total_amount = LAG(total_amount) OVER (ORDER BY sale_date) THEN '→'
        ELSE '-'
    END AS トレンド
FROM daily_sales
ORDER BY sale_date
LIMIT 10;

実行結果は以下の通りです。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12


    日付    |  売上  | 前回売上 | トレンド
------------+--------+----------+----------
 2025-10-01 | 150000 |          | -
 2025-10-02 |  90000 |   150000 | ↓
 2025-10-05 |  80000 |    90000 | ↓
 2025-10-08 |  65000 |    80000 | ↓
 2025-10-12 | 200000 |    65000 | ↑
 2025-10-15 |  55000 |   200000 | ↓
 2025-10-20 | 110000 |    55000 | ↑
 2025-10-22 |  85000 |   110000 | ↓
 2025-10-25 |  45000 |    85000 | ↓
 2025-11-02 | 120000 |    45000 | ↑

累計・移動計算 - SUM OVER / AVG OVER

累計（Running Total）の計算

SUM() OVER()を使用すると、累計値を簡単に計算できます。ORDER BYを指定すると、その順序で累計が計算されます。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13


WITH daily_sales AS (
    SELECT 
        sale_date,
        SUM(amount) AS daily_amount
    FROM sales
    GROUP BY sale_date
)
SELECT 
    sale_date AS 日付,
    daily_amount AS 日次売上,
    SUM(daily_amount) OVER (ORDER BY sale_date) AS 累計売上
FROM daily_sales
ORDER BY sale_date;

実行結果は以下の通りです。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13


    日付    | 日次売上 | 累計売上
------------+----------+----------
 2025-10-01 |   150000 |   150000
 2025-10-02 |    90000 |   240000
 2025-10-05 |    80000 |   320000
 2025-10-08 |    65000 |   385000
 2025-10-12 |   200000 |   585000
 2025-10-15 |    55000 |   640000
 2025-10-20 |   110000 |   750000
 2025-10-22 |    85000 |   835000
 2025-10-25 |    45000 |   880000
 2025-11-02 |   120000 |  1000000
 ...

パーティション別の累計

PARTITION BYを組み合わせることで、グループごとの累計を計算できます。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12


SELECT 
    e.name,
    s.sale_date,
    s.amount,
    SUM(s.amount) OVER (
        PARTITION BY s.employee_id 
        ORDER BY s.sale_date
    ) AS 個人累計売上
FROM sales s
JOIN employees e ON s.employee_id = e.employee_id
WHERE e.name IN ('鈴木花子', '佐藤次郎')
ORDER BY e.name, s.sale_date;

実行結果は以下の通りです。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13


   name   | sale_date  | amount | 個人累計売上
----------+------------+--------+--------------
 佐藤次郎  | 2025-10-02 |  90000 |        90000
 佐藤次郎  | 2025-10-20 | 110000 |       200000
 佐藤次郎  | 2025-11-08 |  75000 |       275000
 佐藤次郎  | 2025-11-25 | 160000 |       435000
 佐藤次郎  | 2025-12-10 | 140000 |       575000
 鈴木花子  | 2025-10-01 | 150000 |       150000
 鈴木花子  | 2025-10-05 |  80000 |       230000
 鈴木花子  | 2025-10-12 | 200000 |       430000
 鈴木花子  | 2025-11-03 | 120000 |       550000
 鈴木花子  | 2025-11-15 |  95000 |       645000
 鈴木花子  | 2025-12-01 | 180000 |       825000

移動平均（Moving Average）の計算

ウィンドウフレームを指定することで、直近N件の移動平均を計算できます。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16


WITH daily_sales AS (
    SELECT 
        sale_date,
        SUM(amount) AS daily_amount
    FROM sales
    GROUP BY sale_date
)
SELECT 
    sale_date AS 日付,
    daily_amount AS 日次売上,
    ROUND(AVG(daily_amount) OVER (
        ORDER BY sale_date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    )) AS 直近3日移動平均
FROM daily_sales
ORDER BY sale_date;

実行結果は以下の通りです。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11


    日付    | 日次売上 | 直近3日移動平均
------------+----------+-----------------
 2025-10-01 |   150000 |          150000
 2025-10-02 |    90000 |          120000
 2025-10-05 |    80000 |          106667
 2025-10-08 |    65000 |           78333
 2025-10-12 |   200000 |          115000
 2025-10-15 |    55000 |          106667
 2025-10-20 |   110000 |          121667
 2025-10-22 |    85000 |           83333
 2025-10-25 |    45000 |           80000

ウィンドウフレームの指定

ウィンドウフレームは、ウィンドウ関数が計算に使用する行の範囲を定義します。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10


-- ROWS: 物理的な行数で指定
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW  -- 現在行と前2行

-- RANGE: 値の範囲で指定（ORDER BY列の値に基づく）
RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW

-- 代表的なフレーム指定
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW  -- 最初から現在行まで（累計）
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING  -- 現在行から最後まで
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING          -- 前後1行ずつ（3行の平均など）

CTEとウィンドウ関数の組み合わせ

複雑な分析クエリの構築

CTEとウィンドウ関数を組み合わせることで、複雑な分析クエリを段階的に構築できます。

以下は「月別・カテゴリ別の売上を集計し、前月比と構成比を計算する」例です。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44


WITH monthly_category_sales AS (
    -- ステップ1: 月別・カテゴリ別の売上集計
    SELECT 
        DATE_TRUNC('month', sale_date) AS month,
        product_category,
        SUM(amount) AS category_amount
    FROM sales
    GROUP BY DATE_TRUNC('month', sale_date), product_category
),
monthly_total AS (
    -- ステップ2: 月別の総売上を追加
    SELECT 
        month,
        product_category,
        category_amount,
        SUM(category_amount) OVER (PARTITION BY month) AS monthly_total
    FROM monthly_category_sales
),
with_prev_month AS (
    -- ステップ3: 前月の売上を追加
    SELECT 
        month,
        product_category,
        category_amount,
        monthly_total,
        LAG(category_amount) OVER (
            PARTITION BY product_category 
            ORDER BY month
        ) AS prev_month_amount
    FROM monthly_total
)
-- 最終結果: 前月比と構成比を計算
SELECT 
    TO_CHAR(month, 'YYYY年MM月') AS 月,
    product_category AS カテゴリ,
    category_amount AS 売上,
    ROUND(100.0 * category_amount / monthly_total, 1) AS 構成比,
    CASE 
        WHEN prev_month_amount IS NULL THEN '-'
        WHEN category_amount >= prev_month_amount THEN '+' || (category_amount - prev_month_amount)
        ELSE (category_amount - prev_month_amount)::TEXT
    END AS 前月比
FROM with_prev_month
ORDER BY month, category_amount DESC;

実行結果は以下の通りです。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10


     月     | カテゴリ | 売上  | 構成比 | 前月比
------------+----------+-------+--------+---------
 2025年10月 | 電子機器 | 495000|   56.3 | -
 2025年10月 | 家具     | 170000|   19.3 | -
 2025年10月 | 文具     | 165000|   18.8 | -
 2025年11月 | 電子機器 | 470000|   52.8 | -25000
 2025年11月 | 家具     | 225000|   25.3 | +55000
 2025年11月 | 文具     | 170000|   19.1 | +5000
 2025年12月 | 電子機器 | 385000|   65.8 | -85000
 2025年12月 | 家具     | 200000|   34.2 | -25000

売上ランキングと達成率の分析

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34


WITH employee_sales AS (
    -- 社員別の総売上を集計
    SELECT 
        e.employee_id,
        e.name,
        e.department,
        SUM(s.amount) AS total_sales
    FROM employees e
    LEFT JOIN sales s ON e.employee_id = s.employee_id
    WHERE s.sale_id IS NOT NULL
    GROUP BY e.employee_id, e.name, e.department
),
ranked_sales AS (
    -- ランキングと統計情報を追加
    SELECT 
        *,
        RANK() OVER (ORDER BY total_sales DESC) AS overall_rank,
        RANK() OVER (PARTITION BY department ORDER BY total_sales DESC) AS dept_rank,
        SUM(total_sales) OVER () AS company_total,
        AVG(total_sales) OVER () AS company_avg,
        MAX(total_sales) OVER () AS top_sales
    FROM employee_sales
)
SELECT 
    name AS 社員名,
    department AS 部門,
    total_sales AS 売上,
    overall_rank AS 全社順位,
    dept_rank AS 部門内順位,
    ROUND(100.0 * total_sales / company_total, 1) AS 売上構成比,
    ROUND(100.0 * total_sales / company_avg, 1) AS 平均比,
    ROUND(100.0 * total_sales / top_sales, 1) AS トップ比
FROM ranked_sales
ORDER BY overall_rank;

実行結果は以下の通りです。

1
2
3
4
5
6
7


  社員名   | 部門 |  売上  | 全社順位 | 部門内順位 | 売上構成比 | 平均比 | トップ比
-----------+------+--------+----------+------------+------------+--------+----------
 鈴木花子   | 営業 | 825000 |        1 |          1 |       35.0 |  175.5 |    100.0
 佐藤次郎   | 営業 | 575000 |        2 |          2 |       24.4 |  122.3 |     69.7
 渡辺さくら | 営業 | 365000 |        3 |          3 |       15.5 |   77.7 |     44.2
 伊藤健一   | 営業 | 315000 |        4 |          4 |       13.4 |   67.0 |     38.2
 山本大輔   | 営業 | 175000 |        5 |          5 |        7.4 |   37.2 |     21.2

実践的な分析クエリ例

顧客のリピート分析

売上データから、顧客ごとの購入パターンを分析します。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28


WITH customer_orders AS (
    -- 顧客ごとの注文を時系列で取得
    SELECT 
        employee_id AS customer_id,  -- この例では employee_id を customer として扱う
        sale_date,
        amount,
        ROW_NUMBER() OVER (
            PARTITION BY employee_id 
            ORDER BY sale_date
        ) AS order_number,
        LAG(sale_date) OVER (
            PARTITION BY employee_id 
            ORDER BY sale_date
        ) AS prev_order_date
    FROM sales
)
SELECT 
    customer_id,
    COUNT(*) AS 注文回数,
    SUM(amount) AS 累計購入額,
    ROUND(AVG(amount)) AS 平均購入額,
    MIN(sale_date) AS 初回注文日,
    MAX(sale_date) AS 最終注文日,
    ROUND(AVG(sale_date - prev_order_date)) AS 平均注文間隔日
FROM customer_orders
GROUP BY customer_id
HAVING COUNT(*) > 1
ORDER BY 累計購入額 DESC;

実行結果は以下の通りです。

1
2
3
4
5
6
7


 customer_id | 注文回数 | 累計購入額 | 平均購入額 | 初回注文日 | 最終注文日 | 平均注文間隔日
-------------+----------+------------+------------+------------+------------+----------------
           2 |        6 |     825000 |     137500 | 2025-10-01 | 2025-12-01 |             12
           3 |        5 |     575000 |     115000 | 2025-10-02 | 2025-12-10 |             17
           6 |        4 |     365000 |      91250 | 2025-10-15 | 2025-12-15 |             20
           5 |        4 |     315000 |      78750 | 2025-10-08 | 2025-12-05 |             19
           7 |        3 |     175000 |      58333 | 2025-10-25 | 2025-12-08 |             22

パーセンタイル分析

PERCENT_RANK()とNTILE()を使用した分布分析です。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17


WITH employee_performance AS (
    SELECT 
        e.name,
        e.department,
        COALESCE(SUM(s.amount), 0) AS total_sales
    FROM employees e
    LEFT JOIN sales s ON e.employee_id = s.employee_id
    GROUP BY e.employee_id, e.name, e.department
)
SELECT 
    name AS 社員名,
    department AS 部門,
    total_sales AS 売上,
    NTILE(4) OVER (ORDER BY total_sales DESC) AS 四分位,
    ROUND(100 * PERCENT_RANK() OVER (ORDER BY total_sales DESC), 1) AS パーセンタイル順位
FROM employee_performance
ORDER BY total_sales DESC;

実行結果は以下の通りです。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12


  社員名   | 部門 |  売上  | 四分位 | パーセンタイル順位
-----------+------+--------+--------+-------------------
 鈴木花子   | 営業 | 825000 |      1 |               0.0
 佐藤次郎   | 営業 | 575000 |      1 |              11.1
 渡辺さくら | 営業 | 365000 |      1 |              22.2
 伊藤健一   | 営業 | 315000 |      2 |              33.3
 山本大輔   | 営業 | 175000 |      2 |              44.4
 高橋美咲   | 開発 |      0 |      2 |              55.6
 加藤理恵   | 開発 |      0 |      3 |              55.6
 中村優子   | 開発 |      0 |      3 |              55.6
 小林翔太   | 開発 |      0 |      4 |              55.6
 田中太郎   | 経営 |      0 |      4 |              55.6

WINDOW句による再利用

同じウィンドウ定義を複数回使用する場合、WINDOW句で名前を付けて再利用できます。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12


SELECT 
    e.name,
    s.sale_date,
    s.amount,
    ROW_NUMBER() OVER w AS 順番,
    SUM(s.amount) OVER w AS 累計,
    AVG(s.amount) OVER w AS 平均
FROM sales s
JOIN employees e ON s.employee_id = e.employee_id
WHERE e.name = '鈴木花子'
WINDOW w AS (ORDER BY s.sale_date)
ORDER BY s.sale_date;

実行結果は以下の通りです。

1
2
3
4
5
6
7
8


   name   | sale_date  | amount | 順番 |  累計  |        平均
----------+------------+--------+------+--------+--------------------
 鈴木花子  | 2025-10-01 | 150000 |    1 | 150000 | 150000.0000000000
 鈴木花子  | 2025-10-05 |  80000 |    2 | 230000 | 115000.0000000000
 鈴木花子  | 2025-10-12 | 200000 |    3 | 430000 | 143333.3333333333
 鈴木花子  | 2025-11-03 | 120000 |    4 | 550000 | 137500.0000000000
 鈴木花子  | 2025-11-15 |  95000 |    5 | 645000 | 129000.0000000000
 鈴木花子  | 2025-12-01 | 180000 |    6 | 825000 | 137500.0000000000

パフォーマンスと注意点

CTEのマテリアライゼーション

PostgreSQL 12以降では、CTEのマテリアライゼーション（一時テーブルへの保存）を制御できます。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11


-- 強制的にマテリアライズ（一時テーブルに保存）
WITH MATERIALIZED cte AS (
    SELECT * FROM large_table
)
SELECT * FROM cte;

-- マテリアライズしない（インライン展開）
WITH NOT MATERIALIZED cte AS (
    SELECT * FROM large_table
)
SELECT * FROM cte WHERE id = 123;

CTEを1回しか参照しない場合はNOT MATERIALIZED（またはデフォルト動作）が効率的です。複数回参照する場合や副作用のある関数を含む場合はMATERIALIZEDが有効です。

ウィンドウ関数のインデックス活用

ウィンドウ関数のORDER BY句に合わせたインデックスを作成することで、ソートコストを削減できます。

1
2
3
4
5


-- 売上日時でソートするウィンドウ関数に対応
CREATE INDEX idx_sales_date ON sales(sale_date);

-- パーティションとソートの両方に対応
CREATE INDEX idx_sales_emp_date ON sales(employee_id, sale_date);

実行計画の確認

複雑なクエリではEXPLAIN ANALYZEで実行計画を確認することが重要です。

1
2
3
4
5
6


EXPLAIN ANALYZE
WITH ranked AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY amount DESC) AS rn
    FROM sales
)
SELECT * FROM ranked WHERE rn <= 10;

詳細な実行計画の読み方については、PostgreSQL EXPLAIN ANALYZE入門 - 実行計画を読み解くを参照してください。

まとめ

本記事では、PostgreSQLのCTEとウィンドウ関数を使った高度なクエリの書き方を解説しました。

CTEを活用することで得られるメリットは以下の通りです。

複雑なクエリを論理的なブロックに分割し、可読性を向上できる
再帰CTEにより、階層構造やツリー構造のデータを効率的に処理できる
同じCTEを複数箇所で参照でき、クエリの重複を排除できる

ウィンドウ関数を活用することで得られるメリットは以下の通りです。

ROW_NUMBER、RANK、DENSE_RANKでグループ内の順位付けや重複排除ができる
LAG、LEADで前後の行と比較し、差分や増減を計算できる
SUM OVER、AVG OVERで累計や移動平均を効率的に算出できる

これらの機能を組み合わせることで、従来はアプリケーション側で実装していた複雑なデータ分析処理を、SQL単体でエレガントに記述できるようになります。

はじめに#

前提条件#

サンプルデータの準備#

CTE（共通テーブル式）の基本#

CTEとは何か#

サブクエリとの比較#

複数のCTEを連結する#

再帰CTE - 階層データの処理#

再帰CTEの基本構造#

組織階層の取得#

特定のマネージャー配下の全社員を取得#

再帰CTEの注意点#

ウィンドウ関数の基本#

ウィンドウ関数とは#

ウィンドウ関数と集計関数の違い#

順位付け関数 - ROW_NUMBER / RANK / DENSE_RANK#

ROW_NUMBER - 連番を振る#

RANK と DENSE_RANK - 同順位の扱い#

実践例: 部門ごとの上位2名を抽出#

実践例: 重複データの排除#

行間比較関数 - LAG / LEAD#

LAG - 前の行の値を参照#

LEAD - 次の行の値を参照#

実践例: 売上の増減トレンド分析#

累計・移動計算 - SUM OVER / AVG OVER#

累計（Running Total）の計算#

パーティション別の累計#

移動平均（Moving Average）の計算#

ウィンドウフレームの指定#

CTEとウィンドウ関数の組み合わせ#

複雑な分析クエリの構築#

売上ランキングと達成率の分析#

実践的な分析クエリ例#

顧客のリピート分析#

パーセンタイル分析#

WINDOW句による再利用#

パフォーマンスと注意点#

CTEのマテリアライゼーション#

ウィンドウ関数のインデックス活用#

実行計画の確認#

まとめ#

参考リンク#

はじめに

前提条件

サンプルデータの準備

CTE（共通テーブル式）の基本

CTEとは何か

サブクエリとの比較

複数のCTEを連結する

再帰CTE - 階層データの処理

再帰CTEの基本構造

組織階層の取得

特定のマネージャー配下の全社員を取得

再帰CTEの注意点

ウィンドウ関数の基本

ウィンドウ関数とは

ウィンドウ関数と集計関数の違い

順位付け関数 - ROW_NUMBER / RANK / DENSE_RANK

ROW_NUMBER - 連番を振る

RANK と DENSE_RANK - 同順位の扱い

実践例: 部門ごとの上位2名を抽出

実践例: 重複データの排除

行間比較関数 - LAG / LEAD

LAG - 前の行の値を参照

LEAD - 次の行の値を参照

実践例: 売上の増減トレンド分析

累計・移動計算 - SUM OVER / AVG OVER

累計（Running Total）の計算

パーティション別の累計

移動平均（Moving Average）の計算

ウィンドウフレームの指定

CTEとウィンドウ関数の組み合わせ

複雑な分析クエリの構築

売上ランキングと達成率の分析

実践的な分析クエリ例

顧客のリピート分析

パーセンタイル分析

WINDOW句による再利用

パフォーマンスと注意点

CTEのマテリアライゼーション

ウィンドウ関数のインデックス活用

実行計画の確認

まとめ

参考リンク