0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@ktamido(緑川 京太)in株式会社BeeX

【PostgreSQL 総復習】4. SELECT・ウィンドウ関数・CTE (WITH句)

0
Last updated at Posted at 2026-04-07

「完全攻略 PostgreSQL: 現場で使える最強データベース入門 」という書籍をベースに、自分なりに気になったことやよく使いそうな機能をまとめていきます。

作業リポジトリはこちら:

1. SELEC文の基本構文と実行順序

SELECT文の基本構文

SELECT [ 列名 | 式 | * ]      -- 取得する列名や計算式を指定
FROM テーブル名                -- データを取得するテーブルやビューを指定
[WHERE 条件]                  -- 行の絞り込み条件
[GROUP BY 列名]               -- グルーピングするカラムを指定
[HAVING 条件]                 -- グループ単位での絞り込み条件
[ORDER BY 列名 [ASC|DESC]]    -- 結果の並び順
[LIMIT 数]                    -- 取得する行数の制限
[OFFSET 数]                   -- 取得開始位置の指定

SELECT文の実行順序

NOTE: 実行順序エイリアスにも関係します。
例えば、SELECT 句で定義したエイリアスは ORDER BY 句では利用可能ですが HAVING 句では利用できません。

  1. FROM 指定したテーブルからデータを取得。JOINもこの段階で処理される。
  2. WHERE 取得した行を条件で絞り込み
  3. GROUP BY WHEREで絞り込んだ結果をグループ化
  4. HAVING グループ化した結果を条件で絞り込み
  5. SELECT 取得する列や計算式を選択
  6. ORDER BY 結果瀬戸を指定した列で並び替える
  7. LIMIT / OFFSET 返却する行数や開始位置を制御

サンプルクエリ

SELECT category_id, AVG(price) AS avg_price
FROM products
WHERE created_at >= '2026-01-01'
GROUP BY category_id
HAVING AVG(price) > 5000
ORDER BY avg_price ASC
LIMIT 10;

式と関数

文字列関数

関数 説明 例 結果
length(str) 文字数を返す length('こんにちは') 5
octet_length(str) バイト数を返す octet_length('こんにちは') 15
upper(str) 大文字に変換 upper('hello') HELLO
lower(str) 小文字に変換 lower('HELLO') hello
initcap(str) 単語の先頭を大文字に initcap('hello world') Hello World
concat(str1, str2, ...) 文字列を連結 concat('a', 'b', 'c') abc
concat_ws(sep, str1, ...) 区切り文字付きで連結 concat_ws('-', '2025', '01', '06') 2025-01-06
substring(str, start, len) 部分文字列を取得 substring('PostgreSQL', 1, 4) Post
trim(str)
ltrim(str) rtrim(str)もある		 前後の空白を除去 trim(' hello ') hello
replace(str, from, to) 文字列を置換 replace('abc', 'b', 'x') axc
reverse(str) 文字列を反転 reverse('hello') olleh
lpad(str, len, fill) 左を埋める lpad('5', 3, '0') 005
rpad(str, len, fill) 右を埋める rpad('5', 3, '0') 500
strpos(str, sub) 部分文字列の位置 strpos('PostgreSQL', 'gre') 5
split_part(str, delim, n) 分割してn番目を取得 split_part('a,b,c', ',', 2) b
string_to_array(str, delim) 配列に分割 string_to_array('a,b,c', ',') {a,b,c}
array_to_string(arr, delim) 配列を文字列に array_to_string('{a,b,c}', '-') a-b-c
regexp_replace(str, pattern, replacement) 正規表現で置換 regexp_replace('abc123', '[0-9]', 'X', 'g') abcXXX
regexp_matches(str, pattern) 正規表現でマッチ regexp_matches('abc123', '[0-9]+') {123}

数値関数

関数 説明 例 結果
abs(n) 絶対値 abs(-5) 5
round(n) 四捨五入（整数） round(4.6) 5
round(n, s) 四捨五入（小数点以下s桁） round(3.14159, 2) 3.14
trunc(n) 切り捨て（整数） trunc(4.9) 4
trunc(n, s) 切り捨て（小数点以下s桁） trunc(3.14159, 2) 3.14
ceil(n) / ceiling(n) 切り上げ ceil(4.1) 5
floor(n) 切り下げ floor(4.9) 4
mod(a, b) 剰余 mod(10, 3) 1
power(a, b) aのb乗 power(2, 3) 8
sqrt(n) 平方根 sqrt(16) 4
ln(n) 自然対数 ln(2.718) 0.999...
log(base, n) 任意の底の対数 log(2, 8) 3
sign(n) 符号（-1, 0, 1） sign(-5) -1
random() 0〜1のランダム値 random() 0.37...
greatest(a, b, ...) 最大値 greatest(1, 5, 3) 5
least(a, b, ...) 最小値 least(1, 5, 3) 1

日付・時刻関数

関数 説明 例 結果
now() 現在日時（トランザクション開始時） now() 2025-01-06 10:30:00+09
current_timestamp 現在日時（now()と同じ） current_timestamp 2025-01-06 10:30:00+09
current_date 現在日付 current_date 2025-01-06
current_time 現在時刻 current_time 10:30:00+09
clock_timestamp() リアルタイム現在時刻 clock_timestamp() 呼び出し時点の時刻
date_trunc(field, source) 指定精度で切り捨て date_trunc('month', now()) 2025-01-01 00:00:00
extract(field from source)(SQL標準)
date_part(field, source) 		日時要素を抽出 extract(year from now())
date_part('month', now()) 		2025
1
age(timestamp) 現在からの経過時間 age('2000-01-01') 25 years 6 days (INTERVAL型)
age(ts1, ts2) 2つの日時の差 age('2025-01-06', '2020-01-01') 5 years 5 days (INTERVAL型)
to_char(datetime, format) 日時を文字列に変換 to_char(now(), 'YYYY-MM-DD') 2025-01-06
to_date(str, format) 文字列を日付に変換 to_date('2025-01-06', 'YYYY-MM-DD') 2025-01-06
to_timestamp(str, format) 文字列をタイムスタンプに to_timestamp('2025-01-06 10:30', 'YYYY-MM-DD HH24:MI') 2025-01-06 10:30:00
make_date(y, m, d) 日付を作成 make_date(2025, 1, 6) 2025-01-06
make_timestamp(...) タイムスタンプを作成 make_timestamp(2025, 1, 6, 10, 30, 0) 2025-01-06 10:30:00

date_trunc のfield一覧

field 説明
microseconds マイクロ秒
milliseconds ミリ秒
second 秒
minute 分
hour 時
day 日
week 週
month 月
quarter 四半期
year 年

to_charのフォーマット

パターン 説明 例
YYYY 4桁の年 2025
MM 月（01-12） 01
DD 日（01-31） 06
HH24 時（00-23） 14
HH12 時（01-12） 02
MI 分（00-59） 30
SS 秒（00-59） 45
AM / PM 午前/午後 PM
Day 曜日名 Monday
Mon 月名（短縮） Jan
TZ タイムゾーン JST

集約関数

関数 説明 例
count(*) 行数 SELECT count(*) FROM users
count(column) NULL以外の行数 SELECT count(email) FROM users
count(distinct column) ユニークな値の数 SELECT count(distinct status) FROM orders
sum(column) 合計 SELECT sum(amount) FROM orders
avg(column) 平均 SELECT avg(price) FROM products
max(column) 最大値 SELECT max(created_at) FROM users
min(column) 最小値 SELECT min(price) FROM products
array_agg(column) 配列に集約 SELECT array_agg(name) FROM users
string_agg(column, delim) 文字列に連結 SELECT string_agg(name, ', ') FROM users
bool_and(column) すべてtrueか SELECT bool_and(is_active) FROM users
bool_or(column) いずれかtrueか SELECT bool_or(is_admin) FROM users
json_agg(column) JSON配列に集約 SELECT json_agg(row_to_json(t)) FROM t
jsonb_agg(column) JSONB配列に集約 SELECT jsonb_agg(data) FROM items

条件式

関数・構文 説明 例
CASE WHEN ... THEN ... ELSE ... END 条件分岐 CASE WHEN status = 1 THEN 'active' ELSE 'inactive' END
COALESCE(a, b, ...) NULLを別の値に置き換える。
引数を左から評価していき、最初のNULLではない値を返す。		 COALESCE(nickname, name, 'anonymous')
NULLIF(a, b) 特定の値をNULLにする。a == b ならNULLを返す。 NULLIF(value, 0)

CASE式の例

SELECT 
  name,
  CASE 
    WHEN age < 20 THEN '未成年'
    WHEN age < 65 THEN '成人'
    ELSE '高齢者'
  END AS age_group
FROM users;
--     name    | age_group 
-- ------------+-----------
--  山田太郎   | 成人
--  鈴木花子   | 成人
--  田中一郎   | 高齢者
--  佐藤雪     | 未成年
-- ...


-- 簡略形（値の比較）
SELECT 
  id,
  CASE status
    WHEN 'pending' THEN '保留中'
    WHEN 'paid' THEN '支払済'
    WHEN 'shipped' THEN '発送済み'
    WHEN 'delivered' THEN '配達済み'
    WHEN 'cancelled' THEN 'キャンセル済み'
    ELSE '不明'
  END AS status_label
FROM orders;
--                  id                  |  status_label  
-- --------------------------------------+----------------
--  49a93d17-2bd5-4ca6-85a9-35e161bea684 | 発送済み
--  247a16e5-f803-42c8-88af-c4d990f2bcc3 | 支払済
--  bdfc9139-e9fc-4880-942b-21f7243a242c | 保留中
-- ...

COALESCE の例

COALESCE(a, b, c, ...) は引数を左から評価していき最初のNULLではない値を返します。

-- 表示名のフォールバック
-- ニックネームがあれば使う、なければ本名、それもなければ「匿名」
SELECT COALESCE(nickname, name, 'anonymous') AS display_name FROM users;
--  display_name 
-- --------------
--  たろさん
--  はなちゃん
--  田中一郎

-- NULLを0として扱う
-- discountがNULLの場合、0として扱う
SELECT
  name,
  discount,
  price,
  floor(price - price * COALESCE(discount, 0) / 100) AS final_price
FROM
  products;
--                     name                     | discount |   price   | final_price 
-- ---------------------------------------------+----------+-----------+-------------
--  iPhone 15 Pro 256GB                         |          | 179800.00 |      179800
--  Galaxy S24 Ultra                            |     5.00 | 189800.00 |      180310
--  Pixel 8 Pro                                 |    10.00 | 159900.00 |      143910


-- 外部結合でのNULLの処理
-- 注文がないユーザーはNULLではなく0と表示される
SELECT 
  u.name,
  COALESCE(SUM(o.total_amount), 0) AS total_orders
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.name;
--     name    | total_orders 
-- ------------+--------------
--  原駿       |    176800.00
--  山崎颯太   |     37980.00
--  池田海斗   |    179800.00

NULLIFの例

NULLIF(a, b) は a == b ならNULLを返します。

-- 0除算の回避
SELECT 100 / NULLIF(divisor, 0) FROM data;  -- NULL

-- 空文字をNULLとして扱う
SELECT NULLIF(email, '') AS email FROM users;

-- COALESCEとの組み合わせ
-- emailが空文字の場合は "メール未設定" とする
SELECT COALESCE(NULLIF(email, ''), 'メール未設定') AS email FROM users;

JSON関数

関数 説明 例
'{"a": 1}'::jsonb
'{"a": 1}'::json' 		文字列をJSONオブジェクトに変換 '{"a": 1}'::jsonb
'{"a": 1}'::json'
-> JSONオブジェクトからキーで取得（JSON型） data->'name'
->> JSONオブジェクトからキーで取得（テキスト型） data->>'name'
#> パスで取得（JSON型） data#>'{address,city}'
#>> パスで取得（テキスト型） data#>>'{address,city}'
jsonb_extract_path(json, ...) パスで値を取得 jsonb_extract_path(data, 'address', 'city')
jsonb_extract_path_text(...) パスでテキスト取得 jsonb_extract_path_text(data, 'name')
jsonb_set(json, path, value) 値を設定 jsonb_set(data, '{name}', '"new"')
jsonb_insert(json, path, value) 値を挿入 jsonb_insert(data, '{tags,0}', '"new"'): 配列の先頭に挿入
jsonb_array_elements(json) 配列を行に展開 jsonb_array_elements('[1,2,3]')
jsonb_object_keys(json) キー一覧を取得 jsonb_object_keys('{"a":1,"b":2}')
jsonb_each(json) キーと値のペアを行に展開 jsonb_each('{"a":1,"b":2}')
jsonb_typeof(json) JSON値の型 jsonb_typeof('"hello"') → string
row_to_json(record) レコードをJSONに row_to_json(users)
jsonb_build_object(k1, v1, ...) JSONBオブジェクトを作成 jsonb_build_object('name', 'taro', 'age', 20)
jsonb_build_array(...) JSONB配列を作成 jsonb_build_array(1, 2, 3)
jsonb_strip_nulls(json) NULL値を除去 jsonb_strip_nulls('{"a":1,"b":null}')

JSON関数の使用例

-- テーブル
CREATE TABLE sample_data_json (
    id serial PRIMARY KEY,
    data jsonb
);

INSERT INTO sample_data_json (data) VALUES 
    ('{"name": "田中", "age": 30, "tags": ["admin", "developer"]}');

-- 文字列をJSON型に変換
SELECT '{"a": 1}'::jsonb AS data;
--    data   
-- ----------
--  {"a": 1}

-- 値の取得 1 (JSON型で取得)
SELECT data#>'{tags}' AS tags FROM sample_data_json;
SELECT data->'tags' AS tags FROM sample_data_json;
SELECT jsonb_extract_path(data, 'tags') AS tags FROM sample_data_json;
--           tags          
-- ------------------------
--  ["admin", "developer"]

--値の取得 2 (JSON型で取得)
SELECT data->'tags'->0 AS first_tag FROM sample_data_json;        -- JSON型で取得
SELECT data#>'{tags, 0}' AS first_tag FROM sample_data_json;
SELECT jsonb_extract_path(data, 'tags', '0') AS first_tag FROM sample_data_json;
--  first_tag 
-- -----------
--  "admin"

-- 値の取得 (TEXT型で取得)
SELECT data->>'tags' AS tags FROM sample_data_json;
SELECT data#>>'{tags}' AS tags FROM sample_data_json;
SELECT jsonb_extract_path_text(data, 'tags') AS tags FROM sample_data_json;
--           tags          
-- ------------------------
--  ["admin", "developer"]

-- WHERE句でJSONの絞り込み
SELECT id, name, attributes FROM products WHERE attributes->>'brand' = 'Apple';
--                   id                  |            name             |                                     attributes                                     
-- --------------------------------------+-----------------------------+------------------------------------------------------------------------------------
--  5e5de64b-ab6a-4203-9b1a-6af68602e315 | iPhone 15 Pro 256GB         | {"brand": "Apple", "color": "ナチュラルチタニウム", "storage": "256GB"}
--  124874fd-31f8-40b5-a704-f767b2c94d53 | iPhone SE (第3世代)         | {"brand": "Apple", "color": "ミッドナイト", "storage": "64GB"}
--  df9bf7e7-25f2-4eaa-a90a-a9b55978e70d | Apple Watch Series 9        | {"size": "45mm", "brand": "Apple", "color": "ミッドナイト"}
--  7a777ace-71db-47ee-b05b-84557272d56e | Apple Watch Ultra 2         | {"size": "49mm", "brand": "Apple", "color": "チタニウム"}


-- 値の型
select jsonb_typeof(data->'tags') from sample_data_json;
--  jsonb_typeof 
-- --------------
--  array


-- キーの一覧を取得
SELECT jsonb_object_keys(data) AS keys FROM sample_data_json;
--  keys 
-- ------
--  age
--  name
--  tags

-- キーと値をペアに展開
SELECT jsonb_each(data) AS pair FROM sample_data_json;
--                pair                  
-- -------------------------------------
--  (age,30)
--  (name,"""田中""")
--  (tags,"[""admin"", ""developer""]")

-- キーと値のペアからキーだけを取り出す
SELECT (jsonb_each(data)).key  FROM sample_data_json;
--  key  
-- ------
--  age
--  name
--  tags

-- キーと値のペアから値だけを取り出す
SELECT (jsonb_each(data)).value FROM sample_data_json;
--           value         
-- ------------------------
--  30
--  "田中"
--  ["admin", "developer"]


-- 配列を行に展開
SELECT jsonb_array_elements_text(data->'tags') AS tag FROM sample_data_json;
--  id |    tag    
-- ----+-----------
--   1 | admin
--   1 | developer

-- レコードをJSONに変換
SELECT row_to_json(users) FROM users;
--     row_to_json
-- ---------------------
--  {"id":1,"email":"taro.yamada@example.com","name":"山田太郎","nickname":"たろさん","age":45,"gender":"male", ...}

-- 新しいキーと値を追加
UPDATE sample_data_json
SET data = jsonb_set(data, '{display_name}', '"tanaka"')
WHERE id = 1;
--                                                tag                                                
-- --------------------------------------------------------------------------------------------------
--  {"age": 31, "name": "田中", "tags": ["admin", "developer"], "display_name": "tanaka"}

-- 既存の値の更新
UPDATE sample_data_json 
SET data = jsonb_set(data, '{age}', '31')
WHERE id = 1;
--                             tag                             
-- -------------------------------------------------------------
--  {"age": 31, "name": "田中", "tags": ["admin", "developer"]}

-- 値の挿入 (配列の先頭)
UPDATE sample_data_json
SET data = jsonb_insert(data, '{tags, 0}', '"first"')
WHERE id = 1;
--                                  data                                 
-- ----------------------------------------------------------------------
--  {"age": 30, "name": "田中", "tags": ["first", "admin", "developer"]}

-- 配列の末尾に追加
-- NOTE: jsonb_insert(data, '{tags, -1}', '"first"') だと最後の要素の1つ手前に挿入されてしまう
-- || 演算子: 配列同士の連結
UPDATE sample_data_json 
SET data = jsonb_set(data, '{tags}', (data->'tags') || '["last"]'::jsonb)
WHERE id = 1;
--                                   tag                                   
-- ------------------------------------------------------------------------
--  {"age": 31, "name": "田中", "tags": ["first", "admin", "developer", "last"]}


-- tagsキーを持つレコードだけを抽出
SELECT data FROM sample_data_json WHERE data ? 'tags';

-- tagsキーを持つレコードだけを抽出
SELECT *
FROM sample_data_json s
WHERE EXISTS (
  SELECT 1
  FROM jsonb_each(s.data) e
  WHERE e.key = 'tags'
);

-- 既存のテーブルからjsonを生成
SELECT
  jsonb_build_object(
    'name', u.name,
    'age', u.age,
    'display_name', COALESCE(nickname, name, 'anonymous'),  -- 表示名の評価
    'order_amounts', COALESCE(jsonb_agg(o.total_amount ORDER BY created_at), '[]'::jsonb),  -- order金額の配列
    'foo', jsonb_build_array(1,2,3)
  )
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.name, u.age, u.nickname;
--                                                   jsonb_build_object                                                  
-- ----------------------------------------------------------------------------------------------------------------------
--  {"age": 45, "foo": [1, 2, 3], "name": "山田太郎", "display_name": "たろさん", "order_amounts": [189800.00, 3280.00]}

配列関数

関数 説明 例 結果
array_length(arr, dim) 配列の長さ array_length('{1,2,3}'::int[], 1) 3
array_append(arr, elem) 要素を追加 array_append('{1,2}'::int[], 3) {1,2,3}
array_prepend(elem, arr) 先頭に追加 array_prepend(0, '{1,2}'::int[]) {0,1,2}
array_cat(arr1, arr2) 配列を連結 array_cat('{1,2}'::int[], '{3,4}'::int[]) {1,2,3,4}
array_remove(arr, elem) 要素を削除 array_remove('{1,2,3}'::int[], 2) {1,3}
array_position(arr, elem) 要素の位置 array_position('{a,b,c}'::text[], 'b') 2
unnest(arr) 配列を行に展開 unnest('{1,2,3}'::int[]) 3行
array_agg(column) 行を配列に集約 SELECT array_agg(name) FROM users {田中,鈴木}

型変換関数

関数 説明 例
CAST(value AS type) 型変換（SQL標準） CAST('123' AS integer)
value::type 型変換（PostgreSQL形式） '123'::integer `'{"a": 1}'::jsonb
to_char(n, format) 数値を文字列に to_char(12345.6, '999,999.99')
to_number(str, format) 文字列を数値に to_number('12,345.67', '99,999.99')

システム情報関数

関数 説明
current_user 現在のユーザー名
current_database() 現在のデータベース名
current_schema() 現在のスキーマ名
version() PostgreSQLのバージョン
pg_database_size(db) データベースのサイズ
pg_table_size(table) テーブルのサイズ
pg_total_relation_size(table) テーブル＋インデックスのサイズ
pg_size_pretty(size) サイズを人間が読める形式に 
-- 例
SELECT current_user;
--  current_user 
-- --------------
--  app

SELECT current_database();
--  current_database 
-- ------------------
--  sample
SELECT pg_size_pretty(pg_database_size('sample'));
--  pg_size_pretty 
-- ----------------
--  8606 kB

2. WHERE句・ORDER BY・GROUP BYの活用

WHERE句の活用

WHERE句はテーブルやビューから取得する行を絞り込むために利用します。

主な演算子

演算子 説明
= 等しい
<> != 等しくない
< > <= >= 比較
BETWEEN ... AND ... 範囲指定
IN (...) 複数値の一致
department_id IN (1,2,3)
LIKE パターン一致
ILIKE 大文字小文字を区別しないパターン一致
IS NULL IS NOT NULL NULL チェック
? jsonbにキーが存在するか。配列に対して使う場合は配列に要素が存在するか
?| 配列にいずれかの要素が存在するか
WHERE data->'tags' ?| ARRAY['developer', 'admin']
?& 配列にすべての要素が存在するか
WHERE data->'tags' ?& ARRAY['developer', 'admin']
@> 左の jsonb が、右の jsonb を “構造ごと” 含んでいるか
WHERE data @> '{"name":"田中"}'::jsonb
~* 正規表現によるパターンマッチング
WHERE email ~* 'example\.com$' 
SELECT
  id, name, price, attributes->>'brand' AS brand
FROM products
WHERE price >= 10000 AND attributes ? 'brand';
--                   id                  |                    name                    |   price   |       brand       
-- --------------------------------------+--------------------------------------------+-----------+-------------------
--  646bab76-0bd8-461d-9ea8-677025e1fa72 | iPhone 15 Pro 256GB                        | 179800.00 | Apple
--  93e289e5-6ab4-4891-90fd-1880c5417204 | Galaxy S24 Ultra                           | 189800.00 | Samsung
-- ...

SELECT email FROM users WHERE email ~* 'example\.com$';
--             email             
-- ------------------------------
--  taro.yamada@example.com
--  hanako.suzuki@example.com
-- ...

ORDER BY句の活用

ORDER BY 句は結果セットの並び順を指定する。列名のほか、式やエイリアス、、複数値指定も可能

-- ORDER BYに複数を指定する
SELECT
  id, name, age
FROM users ORDER BY age ASC, email ASC;
--  id |    name    | age 
-- ----+------------+-----
--  19 | 山崎颯太   |  15
--  12 | 吉田恵美   |  16

-- ORDER BYに式を設定する
SELECT
  name, price, discount
FROM products
ORDER BY floor(price - (price * discount / 100)) ASC;
--                     name             |   price   | discount 
-- -------------------------------------+-----------+----------
--  はくばく もち麦ごはん                     |    498.00 |    10.00
--  GRL リブニットトップス                    |   1299.00 |    50.00
--  Spigen iPhone 15 Pro ケース           |   2990.00 |    30.00

-- NULLの並び順の制御
ELECT name, nickname FROM users ORDER BY nickname ASC NULLS 
LAST;

GROUP BY句の活用

指定した列を基準に集計を行います。

SELECT
  p.category_id,
  max(c.name),  -- c.nameが単一値になることは自明
  AVG(price) AS average_price
FROM products p
LEFT JOIN categories c ON c.id = p.category_id
GROUP BY p.category_id
ORDER BY average_price DESC;

-- HAVING 句によるグループ条件指定
SELECT
  p.category_id,
  max(c.name),  -- c.nameが単一値になることは自明
  AVG(price) AS average_price
FROM products p
LEFT JOIN categories c ON c.id = p.category_id
GROUP BY p.category_id
HAVING AVG(price) > 100000
ORDER BY average_price DESC;

組み合わせ

-- ウィンドウ関数との組み合わせ
-- カテゴリごとに価格をランク付け
SELECT
  name,
  price,
  category_id,
  RANK() OVER (PARTITION BY category_id ORDER BY price DESC) AS price_rank
FROM products
ORDER BY category_id ASC, price_rank ASC;


-- サブクエリとの併用
-- 各カテゴリの価格3位までを抽出
SELECT *
FROM (
  SELECT
    name,
    price,
    category_id,
    RANK() OVER (PARTITION BY category_id ORDER BY price DESC) AS price_rank
  FROM products
  ORDER BY category_id ASC, price_rank ASC
) sub
WHERE sub.price_rank <= 3;

3. ウィンドウ関数

ウィンドウ関数は集約関数と似ていますが、ウィンドウ関数では集約結果を行単位で保持して分析することができます。
データ全体に対する集計値や順位付けを行いつつ、元の行の情報も保持できる点が大きな特徴です。

基本構文

ウィンドウ関数(...) OVER ([PARTITION BY 列名] [ORDER BY 列名] [ROWS|RANGE|GROUPS BETWEEN 開始位置 AND 終了位置])
  • PARTITION BY 列名 : グループ化するためのカラム
  • ORDER BY 列名 : ウィンドウ内の並び順
  • ROWS|RANGE|GROUPS BETWEEN 開始位置 AND 終了位置 : フレーム(分析対象範囲)の指定

主なウィンドウ関数一覧

ランキング系

関数 役割 同順位の扱い 典型用途
row_number() 連番を振る 同順位でも別番号 最新1件取得
rank() 順位 順位飛びあり 売上ランキング
dense_rank() 順位 順位飛びなし 表示用ランキング
percent_rank() 相対順位(0–1) あり 分布分析
cume_dist() 累積分布 あり 上位◯%判定

集計系

関数 役割 例
sum(expr) 累積合計 running total
avg(expr) 移動平均 moving average
count(expr) 件数 カウント
min(expr) / max(expr) 最小 / 最大 範囲比較
  • expr：参照したい値 (カラム名など)

前後参照系

関数 役割 典型用途
lag(expr [, offset [, default]]) 前の行の値を参照 前回との差分
lead(expr [, offset [, default]]) 次の行の値を参照 予測・比較
first_value(expr) 最初の値 基準値
last_value(expr) 最後の値 期間終端
  • expr：参照したい値 (カラム名など)
  • offset：何行前/後（省略可、デフォルト 1）
  • default：存在しない場合の値（省略可）

フレーム制御（範囲指定）

  • ROWS|RANGE|GROUPS BETWEEN 開始位置 AND 終了位置
    フレーム(分析対象範囲)を 開始位置 から 終了位置 に設定
  • ROWS|RANGE|GROUPS 開始位置
    フレーム(分析対象範囲)を 開始位置 から CURRENT ROW までに設定

フレーム単位

単位 説明
ROWS 単純な行数で範囲指定
RANGE 同じ値を同一視したい、または値の範囲（±N）で指定したい（ORDER BYの列の値に基づく）
GROUPS 同じ値を持つ行をグループとして扱う

位置指定オプション

開始位置 / 終了位置 に設定できるパラメータ

指定 意味
UNBOUNDED PRECEDING パーティションの最初の行
n PRECEDING n行/n値 前
CURRENT ROW 現在の行
n FOLLOWING n行/n値 後
UNBOUNDED FOLLOWING パーティションの最後の行

よく使うフレーム指定

  1. 最初から現在行まで (累計売上, 累計件数など)
  • ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  • ROWS UNBOUNDED PRECEDING
  1. 直近N行 (移動平均など)
  • ROWS BETWEEN 4 PRECEDING AND CURRENT ROW : 直近5行
  1. 前後を囲む範囲 (中央移動平均, ノイズ除去など)
  • ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING : 前後1行ずつ (系3行)
  1. 現在行から最後まで (残り合計, 後続データの集計など)
  • ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
  1. パーティション全体 (全体に対する割合計算など)
  • ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  1. 現在行を除く
  • ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING : 自分より前の全行
  • ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING : 自分より後の全行

フレームを指定しない場合のデフォルト値

| 条件 | デフォルトのフレーム |
| ORDER BY なし | RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING（全体） |
| ORDER BY あり | RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW（最初〜現在） |

ウィンドウ関数を利用したクエリのサンプル

ユーザーごとの最新注文を1件だけ取る

orders テーブルのレコードを user_id ごとにグルーピングし、各ユーザーの最新の注文1件を取得します。
※ GROUP BY だと status や id が取れないが、ウィンドウ関数であれば行を落とさず順位付けできる。


SELECT *
FROM (
  SELECT
    o.*,
    row_number() OVER (
      PARTITION BY user_id
      ORDER BY ordered_at DESC
    ) AS rn
  FROM orders o
) t
WHERE t.rn = 1
ORDER BY user_id ASC;
--                   id                  | user_id |  status   | total_amount |          ordered_at           |          updated_at           | rn 
-- --------------------------------------+---------+-----------+--------------+-------------------------------+-------------------------------+----
--  48ba03f9-70c0-4f4d-a781-56899405a2b6 |       1 | delivered |      3280.00 | 2025-12-30 14:16:20.473944+00 | 2026-01-24 14:16:20.473944+00 |  1
--  985bd790-d889-4b32-abf4-58c7bf0ea78c |       2 | delivered |     59800.00 | 2025-12-27 14:16:20.473944+00 | 2026-01-24 14:16:20.473944+00 |  1
--  cc381f0d-0d93-414e-9ea9-95ebc36949cc |       3 | shipped   |    164800.00 | 2026-01-19 14:16:20.473944+00 | 2026-01-24 14:16:20.473944+00 |  1

ユーザーごとの累積購入金額

orders テーブルのレコードを user_id でグルーピングし、ユーザーごとの累積購入額を計算します。

SELECT
  o.user_id,
  o.ordered_at,
  o.total_amount,
  SUM(o.total_amount) OVER (
    PARTITION BY o.user_id
    ORDER BY o.ordered_at
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW  -- 先頭から現在の行まで
  ) AS cumulative_amount
FROM orders o
ORDER BY o.user_id ASC, o.ordered_at ASC;

--  user_id |          ordered_at           | total_amount | cumulative_amount 
-- ---------+-------------------------------+--------------+-------------------
--        1 | 2025-02-09 07:07:22.330574+00 |    189800.00 |         189800.00
--        1 | 2025-03-11 07:07:22.330574+00 |      3280.00 |         193080.00
--        1 | 2025-04-20 07:07:22.330574+00 |     59800.00 |         252880.00
-- ...

前回注文との差分金額

  • lag(expr [, offset [, default]]) : 前の行の値を参照

orders テーブルのレコードを user_id でグルーピングし、前の行の total_amount と、現在行の total_amount との差分を算出します。

SELECT
  o.user_id,
  o.ordered_at,
  o.total_amount,
  o.total_amount - lag(o.total_amount) OVER (
    PARTITION BY o.user_id
    ORDER BY o.ordered_at
  ) AS diff_from_previous
FROM orders o
ORDER BY o.user_id ASC, o.ordered_at ASC;
--  user_id |          ordered_at           | total_amount | diff_from_previous 
-- ---------+-------------------------------+--------------+--------------------
--        1 | 2025-02-09 07:07:22.330574+00 |    189800.00 |                   
--        1 | 2025-03-11 07:07:22.330574+00 |      3280.00 |         -186520.00
--        1 | 2025-04-20 07:07:22.330574+00 |     59800.00 |           56520.00

カテゴリ内での価格ランキング

  • rank() : 順位をつける(順位飛びあり)
  • dense_rank() : 順位をつける(順位飛びなし)

products テーブルのレコードを category_id ごとにグルーピングし、価格が高い順にランキングをつけます。

-- rank() 順位飛びあり(同率があると順位が飛ぶ)
SELECT
  c.name AS category_name,
  p.name AS product_name,
  p.price,
  rank() OVER (
    PARTITION BY c.id
    ORDER BY p.price DESC
  ) AS price_rank
FROM products p
LEFT JOIN categories c ON p.category_id = c.id
ORDER BY c.id ASC;
--        category_name        |                product_name                 |   price   | price_rank 
-- ----------------------------+---------------------------------------------+-----------+------------
--  醤油・味噌・調味料         | キッコーマン 特選丸大豆しょうゆ 1L          |    398.00 |          1
--  醤油・味噌・調味料         | 味の素 ほんだし 120g                        |    398.00 |          1
--  醤油・味噌・調味料         | ハウス バーモントカレー 中辛 230g           |    298.00 |          3
--  醤油・味噌・調味料         | ミツカン 味ぽん 360ml                       |    298.00 |          3


-- dense_rank() 順位飛びなし(同率があっても順位が飛ばない)
SELECT
  c.name AS category_name,
  p.name AS product_name,
  p.price,
  dense_rank() OVER (
    PARTITION BY c.id
    ORDER BY p.price DESC
  ) AS price_rank
FROM products p
LEFT JOIN categories c ON p.category_id = c.id
ORDER BY c.id ASC;
--        category_name        |                product_name                 |   price   | price_rank 
-- ----------------------------+---------------------------------------------+-----------+------------
--  醤油・味噌・調味料         | キッコーマン 特選丸大豆しょうゆ 1L          |    398.00 |          1
--  醤油・味噌・調味料         | 味の素 ほんだし 120g                        |    398.00 |          1
--  醤油・味噌・調味料         | ハウス バーモントカレー 中辛 230g           |    298.00 |          2
--  醤油・味噌・調味料         | ミツカン 味ぽん 360ml                       |    298.00 |          2

商品価格とカテゴリごとの平均価格との差

products テーブルのレコードを category_id ごとにグルーピングし、平均価格を算出。それぞれの製品の価格と平均価格の差を求めます。

SELECT
  c.name AS category_name,
  p.name AS product_name,
  AVG(price) OVER (PARTITION BY c.id) AS category_avg_price,
  p.price - AVG(price) OVER (PARTITION BY p.category_id) AS diff_from_avg
FROM products p
LEFT JOIN categories c ON p.category_id = c.id
ORDER BY c.id ASC;
--        category_name        |                product_name                 |   category_avg_price   |     diff_from_avg      
-- ----------------------------+---------------------------------------------+------------------------+------------------------
--  スマートフォン             | iPhone 15 Pro 256GB                         |    134350.000000000000 |     45450.000000000000
--  スマートフォン             | Galaxy S24 Ultra                            |    134350.000000000000 |     55450.000000000000
--  スマートフォン             | Pixel 8 Pro                                 |    134350.000000000000 |     25550.000000000000

注文内で商品が占める金額の割合

order_items テーブルのレコードを order_id ごとにグルーピング。注文の合計金額と、合計金額に対して各商品の金額が占める割合を求めます。

SELECT
  oi.order_id,
  p.name AS product_name,
  oi.quantity * oi.unit_price AS item_amount,
  -- orderの合計金額
  SUM(oi.quantity * oi.unit_price) OVER (PARTITION BY oi.order_id) AS order_total,
  -- orderの合計金額の中で商品が占める金額の割合
  ROUND(  -- 小数点以下第2位まで丸める
    (oi.quantity * oi.unit_price)
    / SUM(oi.quantity * oi.unit_price) OVER (PARTITION BY oi.order_id)
    * 100,
    2
  ) AS ratio_percent
FROM order_items oi
LEFT JOIN products p ON p.id = oi.product_id
ORDER BY oi.order_id ASC, ratio_percent DESC;
--                order_id               |    product_name     | item_amount | order_total | ratio_percent 
-- --------------------------------------+---------------------+-------------+-------------+---------------
--  0281ddbf-9119-472f-8c24-22f59a208ef7 | Pixel 8 Pro         |   159900.00 |   167810.00 |         95.29
--  0281ddbf-9119-472f-8c24-22f59a208ef7 | ミルボン エルジューダ    |     5720.00 |   167810.00 |          3.41
--  0281ddbf-9119-472f-8c24-22f59a208ef7 | 薬屋のひとりごと 13巻    |     2190.00 |   167810.00 |          1.31

商品価格の移動平均（直近5件）

prooducts テーブルの直近5件の移動平均を求める

SELECT
  p.name,
  p.created_at,
  p.price,
  ROUND(  -- 小数点以下第2位まで丸める
    AVG(p.price) OVER (
      ORDER BY p.created_at
      ROWS BETWEEN 4 PRECEDING AND CURRENT ROW
    ),
    2
  ) AS moving_avg_price
FROM products p;
--                     name                     |          created_at           |   price   | moving_avg_price 
-- ---------------------------------------------+-------------------------------+-----------+------------------
--  iPhone 15 Pro 256GB                         | 2026-01-30 08:27:04.877361+00 | 179800.00 |        179800.00
--  Galaxy S24 Ultra                            | 2026-01-30 08:27:04.877361+00 | 189800.00 |        184800.00
--  Pixel 8 Pro                                 | 2026-01-30 08:27:04.877361+00 | 159900.00 |        176500.00

各カテゴリの売上トップ商品

  • order_items テーブルを products テーブルとJOINし、 products.id でグルーピングします。
  • order_items - products テーブルのレコードを category_id ごとにグルーピングし、売上(単価 * 個数)が多い順にソートして順位付けします。
SELECT
  *
FROM (
  SELECT
    max(c.name) AS category_name,
    p.name AS product_name,
    SUM(oi.quantity * oi.unit_price) AS total_sales,
    ROW_NUMBER() OVER (
      PARTITION BY p.category_id
      ORDER BY SUM(oi.quantity * oi.unit_price) DESC
    ) AS rn
  FROM order_items oi
  JOIN products p ON p.id = oi.product_id
  JOIN categories c ON c.id = p.category_id
  GROUP BY p.id
) t
WHERE t.rn = 1;
--        category_name        |                product_name                 | total_sales | rn 
-- ----------------------------+---------------------------------------------+-------------+----
--  スマートフォン             | iPhone 15 Pro 256GB                         |   539400.00 |  1
--  スマートウォッチ           | Apple Watch Ultra 2                         |   644000.00 |  1
--  モバイルアクセサリー       | Anker PowerCore 10000                       |    38390.00 |  1

4. サブクエリとCTE (WITH句)

4.1サブクエリ

サブクエリとは、SQL文の内部に含まれるSELECT文のことで、主にWHERE句、FROM句、SELECT句の中で使用されます。

WHERE句

-- カテゴリ = 'スマートフォン' の商品の一覧
SELECT
  p.name,
  p.price
FROM products p
WHERE category_id IN (
  SELECT id FROM categories WHERE name = 'スマートフォン'
);
--         name         |   price   
-- ---------------------+-----------
--  iPhone 15 Pro 256GB | 179800.00
--  Galaxy S24 Ultra    | 189800.00
--  Pixel 8 Pro         | 159900.00

FROM句

-- 商品の平均金額が30000円以上のカテゴリを抽出
SELECT category_name, avg_price::int
FROM (
  SELECT
    max(c.name) AS category_name,
    AVG(price) AS avg_price
  FROM products p
  JOIN categories c ON c.id = p.category_id
  GROUP BY category_id
)
WHERE avg_price > 30000;
--      category_name      | avg_price 
-- ------------------------+-----------
--  バッグ                 |    109860
--  掃除用品               |     76627
--  テント・タープ         |     34800

SELECT句

-- 商品名とカテゴリ名の一覧
SELECT
  name,
  (
    SELECT name
    FROM categories c
    WHERE c.id = p.category_id
  ) AS category_name
FROM products p;
--                     name                     |       category_name        
-- ---------------------------------------------+----------------------------
--  iPhone 15 Pro 256GB                         | スマートフォン
--  Galaxy S24 Ultra                            | スマートフォン
--  Pixel 8 Pro                                 | スマートフォン

4.2 CTE (Common Table Expression / WITH句)

CTEはクエリの冒頭で定義する一時的な名前付き結果セットです。

主な用途:

  • クエリの分割による可読性向上
  • 再利用可能な結果セットの定義
  • 再帰クエリの記述 (階層構造データの取得)

基本構文

WITH cte_name AS (
  SELECT ...
)

SELECT ... FROM cte_name;

基本的なCTEの利用

-- カテゴリごとの平均価格を求める
WITH category_avg_price AS (
  SELECT
    max(c.name) AS category_name,
    AVG(price) AS avg_price
  FROM products p
  JOIN categories c ON c.id = p.category_id
  GROUP BY category_id
)

-- 平均価格が30000円以上のカテゴリを一覧
SELECT * FROM category_avg_price WHERE avg_price > 30000;

再起CTE

再起CTEを利用すると階層構造を取得することができます。

  • UNION ALL : 再起CTEにおいて 起点 と 再起結果 を結合する役割を持ちます。(テーブルを縦に積み重ねていくイメージ)
-- 指定したカテゴリを親とする階層構造を取得
WITH RECURSIVE category_tree AS (
  -- 起点: 再起の起点となる親カテゴリの取得 (今回は id = 1 のカテゴリが起点)
  SELECT id, parent_id, 1 AS depth, name
  FROM categories
  WHERE id = 1

  UNION ALL  -- 再起結果のテーブルを縦に積み重ねていく

  -- 再起: すでに取得済みのカテゴリ(category_tree)を親に持つ子カテゴリを追加
  SELECT c.id, c.parent_id, ct.depth + 1, c.name
  FROM categories c
  INNER JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree;
--  id | parent_id | depth |          name          
-- ----+-----------+-------+------------------------
--   1 |           |     1 | 家電
--   9 |         1 |     2 | モバイル・通信
--  10 |         1 |     2 | PC・タブレット
--  11 |         1 |     2 | AV機器
--  12 |         1 |     2 | カメラ・映像機器
--  36 |         9 |     3 | スマートフォン
--  37 |         9 |     3 | スマートウォッチ
--  38 |         9 |     3 | モバイルアクセサリー
--  39 |        10 |     3 | ノートPC
--  40 |        10 |     3 | タブレット
--  41 |        10 |     3 | PC周辺機器
--  42 |        11 |     3 | テレビ
--  43 |        11 |     3 | オーディオ・ヘッドホン
--  44 |        11 |     3 | スピーカー
--  45 |        12 |     3 | デジタルカメラ
--  46 |        12 |     3 | アクションカメラ
--  47 |        12 |     3 | カメラアクセサリー

-- すべてのカテゴリの階層構造を取得
WITH RECURSIVE category_tree AS (
  -- 起点: 再起の起点となる親カテゴリの取得 (今回は id = 1 のカテゴリが起点)
  SELECT id, parent_id, 1 AS depth, name
  FROM categories
  WHERE parent_id IS NULL

  UNION ALL  -- 再起結果のテーブルを縦に積み重ねていく

  -- 再起: すでに取得済みのカテゴリ(category_tree)を親に持つ子カテゴリを追加
  SELECT c.id, c.parent_id, ct.depth + 1, c.name
  FROM categories c
  INNER JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree;

動作イメージ

1回目: 起点を実行 → {id=1}
       ↓ UNION ALL
2回目: 再帰実行 → {id=2, id=3}（parent_id=1の子）
       ↓ UNION ALL
3回目: 再帰実行 → {id=5, id=6}（parent_id=2,3の子）
       ↓ UNION ALL
4回目: 再帰実行 → 該当なし → 終了
0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?