PostgreSQL: PostgreSQL応用機能総合演習

最終更新：2026-08-26

1. 学習内容

ウィンドウ関数を使用した勤怠統計とランキング
ビューとマテリアライズドビューを使用した複雑なクエリのカプセル化
クエリパフォーマンスをチューニングするインデックス戦略の設計
JSONBを使用した柔軟な従業員スキルタグの保存
従業員検索のための全文検索の統合
完全な従業員管理システムデータベースプロジェクトの完成

2. ストーリー

AliceはSaaS企業のデータベースエンジニアです。人事部門から従業員管理システムのデータベース構築を依頼されました。要件は以下の通りです:

勤怠統計: 月次の出勤日数、遅刻回数、残業時間を従業員ごとに集計し、ウィンドウ関数でランキング
業績ランキング: 多次元評価から複合スコアを算出し、マテリアライズドビューにキャッシュ
スキルタグ: 各従業員のスキルタグは固定されていないため、JSONBで柔軟に保存
従業員検索: 氏名、スキル、部署などで全文検索を使って素早く従業員を検索

Aliceはウィンドウ関数、ビュー、インデックス、JSONB、全文検索を組み合わせてこのプロジェクトを完成させる必要があります。

3. 概念: プロジェクトデータベース設計

(1) ER図

erDiagram
    EMPLOYEES ||--o{ ATTENDANCE : "持つ"
    EMPLOYEES ||--o{ PERFORMANCE : "受ける"
    EMPLOYEES }o--|| DEPARTMENTS : "所属する"

    EMPLOYEES {
        int employee_id PK
        text name
        int department_id FK
        text position
        date hire_date
        jsonb skills
        tsvector search_doc
    }

    DEPARTMENTS {
        int department_id PK
        text dept_name
        text location
    }

    ATTENDANCE {
        int attendance_id PK
        int employee_id FK
        date attend_date
        text status
        time check_in_time
        time check_out_time
        numeric overtime_hours
    }

    PERFORMANCE {
        int perf_id PK
        int employee_id FK
        int year
        int quarter
        numeric technical_score
        numeric communication_score
        numeric leadership_score
        numeric overall_score
    }

▶ サンプル: 基本テーブル構造の作成

SQL

CREATE TABLE departments (
  department_id SERIAL PRIMARY KEY,
  dept_name TEXT NOT NULL,
  location TEXT NOT NULL
);

CREATE TABLE employees (
  employee_id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  department_id INT NOT NULL REFERENCES departments(department_id),
  position TEXT NOT NULL,
  hire_date DATE NOT NULL DEFAULT CURRENT_DATE,
  skills JSONB NOT NULL DEFAULT '[]',
  search_doc tsvector
);

CREATE TABLE attendance (
  attendance_id SERIAL PRIMARY KEY,
  employee_id INT NOT NULL REFERENCES employees(employee_id),
  attend_date DATE NOT NULL,
  status TEXT NOT NULL CHECK (status IN ('present','late','absent','leave')),
  check_in_time TIME,
  check_out_time TIME,
  overtime_hours NUMERIC(4,2) DEFAULT 0
);

CREATE TABLE performance (
  perf_id SERIAL PRIMARY KEY,
  employee_id INT NOT NULL REFERENCES employees(employee_id),
  year INT NOT NULL,
  quarter INT NOT NULL CHECK (quarter BETWEEN 1 AND 4),
  technical_score NUMERIC(5,2) CHECK (technical_score BETWEEN 0 AND 100),
  communication_score NUMERIC(5,2) CHECK (communication_score BETWEEN 0 AND 100),
  leadership_score NUMERIC(5,2) CHECK (leadership_score BETWEEN 0 AND 100),
  overall_score NUMERIC(5,2) CHECK (overall_score BETWEEN 0 AND 100),
  UNIQUE (employee_id, year, quarter)
);

TEXT 📖 参照専用

CREATE TABLE

(2) テストデータの挿入

▶ サンプル: 部署と従業員の挿入

SQL

INSERT INTO departments (dept_name, location) VALUES
  ('Engineering', 'Floor 3'),
  ('Sales', 'Floor 2'),
  ('Marketing', 'Floor 1'),
  ('HR', 'Floor 1');

INSERT INTO employees (name, department_id, position, hire_date, skills) VALUES
  ('Alice Chen', 1, 'Senior Engineer', '2022-03-15',
   '["PostgreSQL","Python","Docker","Kubernetes"]'),
  ('Bob Wang', 1, 'Junior Engineer', '2023-06-01',
   '["Java","Spring","MySQL"]'),
  ('Charlie Zhang', 2, 'Sales Manager', '2021-01-10',
   '["Negotiation","CRM","English","French"]'),
  ('Diana Liu', 3, 'Marketing Specialist', '2023-09-20',
   '["SEO","Content Writing","Google Analytics"]'),
  ('Edward Wu', 1, 'DevOps Engineer', '2022-11-01',
   '["Docker","Kubernetes","AWS","Terraform"]'),
  ('Fiona Li', 4, 'HR Manager', '2020-05-15',
   '["Recruiting","Employee Relations","Payroll"]');

TEXT 📖 参照専用

INSERT 0 1

▶ サンプル: 勤怠データの挿入

SQL

INSERT INTO attendance (employee_id, attend_date, status, check_in_time, check_out_time, overtime_hours)
SELECT
  e.employee_id,
  d.dt::date,
  CASE WHEN random() < 0.05 THEN 'absent'
       WHEN random() < 0.12 THEN 'late'
       ELSE 'present'
  END,
  CASE WHEN random() < 0.12 THEN '09:15' ELSE '08:55' END,
  CASE WHEN random() < 0.15 THEN '19:00' ELSE '18:00' END,
  CASE WHEN random() < 0.15 THEN 1.0 ELSE 0 END
FROM employees e
CROSS JOIN (
  SELECT generate_series('2025-01-01'::timestamp, '2025-03-31'::timestamp, '1 day') AS dt
) d
WHERE EXTRACT(dow FROM d.dt) NOT IN (0, 6);

TEXT 📖 参照専用

INSERT 0 1

▶ サンプル: 業績データの挿入

SQL

INSERT INTO performance (employee_id, year, quarter, technical_score, communication_score, leadership_score, overall_score)
VALUES
  (1, 2025, 1, 92, 85, 88, 89.0),
  (2, 2025, 1, 78, 72, 65, 72.3),
  (3, 2025, 1, 70, 90, 85, 82.0),
  (4, 2025, 1, 75, 88, 72, 78.3),
  (5, 2025, 1, 88, 76, 80, 82.0),
  (6, 2025, 1, 65, 92, 90, 82.3);

TEXT 📖 参照専用

INSERT 0 1

4. 概念: ウィンドウ関数 — 勤怠統計とランキング

(1) 月次勤怠サマリー

▶ サンプル: 従業員ごとの月次勤怠

SQL

SELECT
  e.employee_id,
  e.name,
  d.dept_name,
  TO_CHAR(a.attend_date, 'YYYY-MM') AS month,
  COUNT(*) FILTER (WHERE a.status = 'present') AS present_days,
  COUNT(*) FILTER (WHERE a.status = 'late') AS late_days,
  COUNT(*) FILTER (WHERE a.status = 'absent') AS absent_days,
  SUM(a.overtime_hours) AS total_overtime
FROM employees e
JOIN attendance a ON e.employee_id = a.employee_id
JOIN departments d ON e.department_id = d.department_id
GROUP BY e.employee_id, e.name, d.dept_name, TO_CHAR(a.attend_date, 'YYYY-MM')
ORDER BY e.employee_id, month;

TEXT 📖 参照専用

 count 
-------
     5
(1 row)

関数	説明
`COUNT(*) FILTER (WHERE ...)`	条件付きカウント（PostgreSQL固有、SUM(CASE)より明確）
`TO_CHAR(date, 'YYYY-MM')`	月単位でグルー��化

(2) 部署内勤怠ランキング

▶ サンプル: ウィンドウ関数による部署内ランキング

SQL

SELECT
  e.name,
  d.dept_name,
  COUNT(*) FILTER (WHERE a.status = 'present') AS present_days,
  SUM(a.overtime_hours) AS total_overtime,
  RANK() OVER (PARTITION BY d.dept_name ORDER BY COUNT(*) FILTER (WHERE a.status = 'present') DESC) AS attendance_rank,
  RANK() OVER (PARTITION BY d.dept_name ORDER BY SUM(a.overtime_hours) DESC) AS overtime_rank
FROM employees e
JOIN attendance a ON e.employee_id = a.employee_id
JOIN departments d ON e.department_id = d.department_id
WHERE a.attend_date BETWEEN '2025-01-01' AND '2025-03-31'
GROUP BY e.employee_id, e.name, d.dept_name
ORDER BY d.dept_name, attendance_rank;

TEXT 📖 参照専用

 count 
-------
     5
(1 row)

ウィンドウ関数	同値の扱い	ユースケース
`RANK()`	同値でランクをスキップ（1,1,3）	同値を許容するランキング
`DENSE_RANK()`	同値でランクをスキップしない（1,1,2）	連続ランキング
`ROW_NUMBER()`	同値を許容しない（1,2,3）	一意ランキング

▶ サンプル: 累積出勤日数（累積合計）

SQL

SELECT
  e.name,
  a.attend_date,
  a.status,
  SUM(CASE WHEN a.status = 'present' THEN 1 ELSE 0 END)
    OVER (PARTITION BY e.employee_id ORDER BY a.attend_date) AS cumulative_present
FROM employees e
JOIN attendance a ON e.employee_id = a.employee_id
WHERE e.employee_id = 1
ORDER BY a.attend_date;

TEXT 📖 参照専用

  result  
----------
   42.50
(1 row)

▶ サンプル: 今月と先月の比較（LAG）

SQL

WITH monthly_stats AS (
  SELECT
    e.employee_id,
    e.name,
    TO_CHAR(a.attend_date, 'YYYY-MM') AS month,
    COUNT(*) FILTER (WHERE a.status = 'present') AS present_days
  FROM employees e
  JOIN attendance a ON e.employee_id = a.employee_id
  GROUP BY e.employee_id, e.name, TO_CHAR(a.attend_date, 'YYYY-MM')
)
SELECT
  name,
  month,
  present_days,
  LAG(present_days) OVER (PARTITION BY employee_id ORDER BY month) AS prev_month,
  present_days - LAG(present_days) OVER (PARTITION BY employee_id ORDER BY month) AS diff
FROM monthly_stats
ORDER BY employee_id, month;

TEXT 📖 参照専用

 count 
-------
     5
(1 row)

5. 概念: ビューとマテリアライズドビュー — 業績ランキングのカプセル化

(1) ビューとマテリアライズドビュー

観点	ビュー	マテリアライズドビュー
データ保存	しない。クエリごとに動的計算	する。保存された結果を直接読み取る
データ鮮度	リアルタイム	手動REFRESHが必要
クエリパフォーマンス	元のクエリと同じ	高速（事前計算済み）
ユースケース	頻繁に更新されるデータ	レポート/統計、変更の少ないデータ

▶ サンプル: 業績ランキングビューの作成

SQL

CREATE VIEW v_performance_ranking AS
SELECT
  e.employee_id,
  e.name,
  d.dept_name,
  e.position,
  p.year,
  p.quarter,
  p.technical_score,
  p.communication_score,
  p.leadership_score,
  p.overall_score,
  RANK() OVER (PARTITION BY d.dept_name ORDER BY p.overall_score DESC) AS dept_rank,
  RANK() OVER (ORDER BY p.overall_score DESC) AS company_rank
FROM employees e
JOIN performance p ON e.employee_id = p.employee_id
JOIN departments d ON e.department_id = d.department_id;

SELECT * FROM v_performance_ranking WHERE quarter = 1 ORDER BY company_rank;

TEXT 📖 参照専用

CREATE TABLE

▶ サンプル: 業績ランキングマテリアライズドビューの作成

SQL

CREATE MATERIALIZED VIEW mv_performance_ranking AS
SELECT
  e.employee_id,
  e.name,
  d.dept_name,
  p.year,
  p.quarter,
  p.overall_score,
  RANK() OVER (ORDER BY p.overall_score DESC) AS company_rank
FROM employees e
JOIN performance p ON e.employee_id = p.employee_id
JOIN departments d ON e.department_id = d.department_id
WITH DATA;

-- 業績データが変更されたらリフレッシュ
REFRESH MATERIALIZED VIEW mv_performance_ranking;

-- 同時実行リフレッシュ（読み取りをブロックしない）
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_performance_ranking;

TEXT 📖 参照専用

CREATE TABLE

REFRESH方法	ロック	説明
`REFRESH MATERIALIZED VIEW`	ACCESS EXCLUSIVE	読み取りと書き込みをブロック。一意インデックス不要
`REFRESH ... CONCURRENTLY`	SHARE	読み取りをブロックせず。一意インデックスが必要

▶ サンプル: 勤怠サマリーマテリアライズドビュー

SQL

CREATE MATERIALIZED VIEW mv_attendance_summary AS
SELECT
  e.employee_id,
  e.name,
  d.dept_name,
  TO_CHAR(a.attend_date, 'YYYY-MM') AS month,
  COUNT(*) FILTER (WHERE a.status = 'present') AS present_days,
  COUNT(*) FILTER (WHERE a.status = 'late') AS late_days,
  COUNT(*) FILTER (WHERE a.status = 'absent') AS absent_days,
  SUM(a.overtime_hours) AS total_overtime
FROM employees e
JOIN attendance a ON e.employee_id = a.employee_id
JOIN departments d ON e.department_id = d.department_id
GROUP BY e.employee_id, e.name, d.dept_name, TO_CHAR(a.attend_date, 'YYYY-MM')
WITH DATA;

TEXT 📖 参照専用

 count 
-------
     5
(1 row)

6. 概念: インデックス戦略チューニング

(1) インデックス設計の原則

原則	説明
高頻度のWHERE条件で使用されるカラムにインデックス	最も基本的なユースケース
JOINカラムにインデックス	外部キーカラムにはデフォルトではインデックスなし。手動で作成
過剰なインデックスを避ける	各インデックスが書き込みのオーバーヘッドを追加
複合インデックスのカラム順序に注意	等価条件カラムを先に、範囲条件カラムを後に
EXPLAIN ANALYZEで確認	実際の実行計画が最終的��判断基準

▶ サンプル: コアクエリ用のインデックス作成

SQL

-- 従業員と日付範囲による勤怠クエリ用インデックス
CREATE INDEX idx_attendance_emp_date
ON attendance (employee_id, attend_date);

-- 年/四半期による業績クエリ用インデックス
CREATE INDEX idx_performance_emp_quarter
ON performance (employee_id, year, quarter);

-- 部署別従業員用インデックス
CREATE INDEX idx_employees_dept
ON employees (department_id);

-- 部分インデックス: 欠勤以外の勤怠レコードのみインデックス
CREATE INDEX idx_attendance_present
ON attendance (employee_id, attend_date)
WHERE status != 'absent';

TEXT 📖 参照専用

CREATE TABLE

インデックス▶類	構文	ユースケース
B-tree（デフォルト）	`CREATE INDEX idx ON t(col)`	等価、範囲、ソート
複合インデックス	`CREATE INDEX idx ON t(col1, col2)`	複数カラムの組み合わせクエリ
部分インデックス	`CREATE INDEX idx ON t(col) WHERE ...`	条件に合致する行のみインデックス
式インデックス	`CREATE INDEX idx ON t(lower(col))`	関数結果に対するクエリ

▶ サンプル: EXPLAIN ANALYZEでインデックス効果を確認

SQL

-- インデックス前: Seq Scan
EXPLAIN ANALYZE
SELECT * FROM attendance WHERE employee_id = 1 AND attend_date >= '2025-01-01';

-- インデックス後: Index Scan
CREATE INDEX idx_attendance_emp_date ON attendance (employee_id, attend_date);
EXPLAIN ANALYZE
SELECT * FROM attendance WHERE employee_id = 1 AND attend_date >= '2025-01-01';

TEXT 📖 参照専用

CREATE TABLE

▶ サンプル: カバリングインデックスでテーブルアクセスを回避

SQL

-- カラムを含めてテーブルアクセスを回避
CREATE INDEX idx_attendance_covering
ON attendance (employee_id, attend_date)
INCLUDE (status, overtime_hours);

-- このクエリはインデックスのみで完結し、テーブルアクセス不要
EXPLAIN ANALYZE
SELECT status, overtime_hours
FROM attendance
WHERE employee_id = 1 AND attend_date >= '2025-01-01';

TEXT 📖 参照専用

CREATE TABLE

7. 概念: JSONBによるスキルタグ保存

(1) スキルタグのJSONB設計

▶ サンプル: 従業員スキルの検索

SQL

-- 特定スキルの検索
SELECT name, skills
FROM employees
WHERE skills @> '["PostgreSQL"]';

-- 複数スキルの検索（いずれかを持つ）
SELECT name, skills
FROM employees
WHERE skills ?| array['PostgreSQL', 'Docker'];

-- 複数スキルの検索（すべてを持つ）
SELECT name, skills
FROM employees
WHERE skills @> '["Docker", "Kubernetes"]';

TEXT 📖 参照専用

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

▶ サンプル: スキルを行に展開

SQL

SELECT
  e.name,
  jsonb_array_elements_text(e.skills) AS skill
FROM employees e
ORDER BY e.name;

TEXT 📖 参照専用

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

▶ サンプル: スキルごとの従業員数カウント

SQL

SELECT
  skill,
  COUNT(*) AS employee_count
FROM (
  SELECT jsonb_array_elements_text(skills) AS skill FROM employees
) sub
GROUP BY skill
ORDER BY employee_count DESC;

TEXT 📖 参照専用

 count 
-------
     5
(1 row)

(2) JSONBインデックスでスキルクエリを高速化

▶ サンプル: GINインデックスの作成

SQL

CREATE INDEX idx_employees_skills ON employees USING gin (skills);

-- GINインデックスは@>と?|クエリをサポート
EXPLAIN ANALYZE
SELECT name FROM employees WHERE skills @> '["PostgreSQL"]';

TEXT 📖 参照専用

CREATE TABLE

▶ サンプル: 構造化JSONBへのスキルタグのアップグレード

SQL

-- 単純な配列から構造化スキルにアップグレード
ALTER TABLE employees ADD COLUMN skill_profile JSONB DEFAULT '{}';

UPDATE employees SET skill_profile = '{
  "primary": ["PostgreSQL", "Python"],
  "secondary": ["Docker"],
  "certifications": ["AWS Solutions Architect"],
  "years_of_experience": {"PostgreSQL": 5, "Python": 8}
}'::jsonb
WHERE employee_id = 1;

-- 主要スキルで検索
SELECT name, skill_profile -> 'primary' AS primary_skills
FROM employees
WHERE skill_profile -> 'primary' @> '["PostgreSQL"]';

-- 資格で検索
SELECT name
FROM employees
WHERE skill_profile -> 'certifications' @> '["AWS Solutions Architect"]';

-- 経験年数で検索
SELECT name
FROM employees
WHERE (skill_profile -> 'years_of_experience' ->> 'PostgreSQL')::int >= 3;

TEXT 📖 参照専用

UPDATE 3

8. 概念: 従業員検索のための全文検索

(1) tsvector検索ドキュメントの構築

▶ サンプル: 検索ドキュメント��ラムとトリガーの作成

SQL

-- 検索ドキュメントカラムを追加
ALTER TABLE employees ADD COLUMN search_doc tsvector;

-- 検索ドキュメントを構築する関数を作成
CREATE FUNCTION employees_search_update() RETURNS trigger AS $$
BEGIN
  NEW.search_doc :=
    setweight(to_tsvector('english', COALESCE(NEW.name, '')), 'A') ||
    setweight(to_tsvector('english', COALESCE(NEW.position, '')), 'B') ||
    setweight(to_tsvector('simple', COALESCE(
      array_to_string(
        ARRAY(SELECT jsonb_array_elements_text(NEW.skills)), ' '
      ), '')), 'C');
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- トリガーを作成
CREATE TRIGGER trg_employees_search
BEFORE INSERT OR UPDATE OF name, position, skills ON employees
FOR EACH ROW EXECUTE FUNCTION employees_search_update();

-- 既存行を更新
UPDATE employees SET name = name; -- トリガーが発火し、search_docが設定される

TEXT 📖 参照専用

INSERT 0 1

▶ サンプル: GINインデックスの作成

SQL

CREATE INDEX idx_employees_search ON employees USING gin (search_doc);

TEXT 📖 参照専用

CREATE TABLE

(2) 検索関数

▶ サンプル: 従業員検索関数

SQL

CREATE FUNCTION search_employees(p_query TEXT)
RETURNS TABLE (
  employee_id INT,
  name TEXT,
  position TEXT,
  dept_name TEXT,
  skills JSONB,
  rank REAL,
  headline TEXT
) AS $$
BEGIN
  RETURN QUERY
  SELECT
    e.employee_id,
    e.name,
    e.position,
    d.dept_name,
    e.skills,
    ts_rank(e.search_doc, websearch_to_tsquery('english', p_query)) AS rank,
    ts_headline(
      'english',
      COALESCE(e.name, '') || ' ' || COALESCE(e.position, ''),
      websearch_to_tsquery('english', p_query),
      'StartSel=<mark>,StopSel=</mark>'
    ) AS headline
  FROM employees e
  JOIN departments d ON e.department_id = d.department_id
  WHERE e.search_doc @@ websearch_to_tsquery('english', p_query)
  ORDER BY rank DESC;
END;
$$ LANGUAGE plpgsql;

-- テスト: エンジニアを検索
SELECT * FROM search_employees('engineer');

-- テスト: Dockerスキルを検索
SELECT * FROM search_employees('Docker');

TEXT 📖 参照専用

CREATE TABLE

9. 概念: 実践的な総合クエリ

(1) 多次元従業員レポート

▶ サンプル: 従業員総合情報レポート

SQL

CREATE VIEW v_employee_dashboard AS
SELECT
  e.employee_id,
  e.name,
  d.dept_name,
  e.position,
  e.hire_date,
  EXTRACT(YEAR FROM age(CURRENT_DATE, e.hire_date)) AS years_of_service,
  e.skills,
  p.overall_score AS latest_perf_score,
  p_r.company_rank,
  a_s.present_days AS q1_present,
  a_s.late_days AS q1_late,
  a_s.total_overtime AS q1_overtime
FROM employees e
JOIN departments d ON e.department_id = d.department_id
LEFT JOIN LATERAL (
  SELECT overall_score FROM performance
  WHERE employee_id = e.employee_id
  ORDER BY year DESC, quarter DESC LIMIT 1
) p ON true
LEFT JOIN LATERAL (
  SELECT company_rank FROM mv_performance_ranking
  WHERE employee_id = e.employee_id
  ORDER BY year DESC, quarter DESC LIMIT 1
) p_r ON true
LEFT JOIN LATERAL (
  SELECT
    COUNT(*) FILTER (WHERE status = 'present') AS present_days,
    COUNT(*) FILTER (WHERE status = 'late') AS late_days,
    SUM(overtime_hours) AS total_overtime
  FROM attendance
  WHERE employee_id = e.employee_id
    AND attend_date BETWEEN '2025-01-01' AND '2025-03-31'
) a_s ON true;

TEXT 📖 参照専用

 count 
-------
     5
(1 row)

▶ サンプル: 部署業績比較

SQL

SELECT
  d.dept_name,
  AVG(p.overall_score) AS avg_score,
  MAX(p.overall_score) AS max_score,
  MIN(p.overall_score) AS min_score,
  COUNT(*) AS employee_count,
  RANK() OVER (ORDER BY AVG(p.overall_score) DESC) AS dept_rank
FROM departments d
JOIN employees e ON d.department_id = e.department_id
JOIN performance p ON e.employee_id = p.employee_id
WHERE p.year = 2025 AND p.quarter = 1
GROUP BY d.dept_name
ORDER BY avg_score DESC;

TEXT 📖 参照専用

 count 
-------
     5
(1 row)

(2) スキルギャップ分析

▶ サンプル: 部署別スキル分布

SQL

SELECT
  d.dept_name,
  skill,
  COUNT(*) AS employees_with_skill,
  ROUND(COUNT(*)::numeric / SUM(COUNT(*)) OVER (PARTITION BY d.dept_name) * 100, 1) AS pct
FROM employees e
JOIN departments d ON e.department_id = d.department_id
CROSS JOIN LATERAL jsonb_array_elements_text(e.skills) AS skill
GROUP BY d.dept_name, skill
ORDER BY d.dept_name, employees_with_skill DESC;

TEXT 📖 参照専用

 count 
-------
     5
(1 row)

10. 実践: 完全な従業員管理システムデータベース

Aliceはすべてのモジュールを1つの完全なプロジェクトに統合します。

SQL

-- ============================================
-- 従業員管理システム - 完全なスキーマ
-- ============================================

-- ステップ1: テーブル（上記で既に作成済み）
-- すべてのテーブル、インデックス、トリガーが存在することを確認

-- ステップ2: コアインデックス
CREATE INDEX idx_attendance_emp_date
ON attendance (employee_id, attend_date);
CREATE INDEX idx_performance_emp_quarter
ON performance (employee_id, year, quarter);
CREATE INDEX idx_employees_dept
ON employees (department_id);
CREATE INDEX idx_employees_skills
ON employees USING gin (skills);
CREATE INDEX idx_employees_search
ON employees USING gin (search_doc);

-- ステップ3: マテリアライズドビュー
CREATE MATERIALIZED VIEW mv_dept_performance AS
SELECT
  d.dept_name,
  p.year,
  p.quarter,
  AVG(p.overall_score) AS avg_score,
  AVG(p.technical_score) AS avg_tech,
  AVG(p.communication_score) AS avg_comm,
  AVG(p.leadership_score) AS avg_lead,
  COUNT(*) AS headcount
FROM departments d
JOIN employees e ON d.department_id = e.department_id
JOIN performance p ON e.employee_id = p.employee_id
GROUP BY d.dept_name, p.year, p.quarter
WITH DATA;

CREATE UNIQUE INDEX idx_mv_dept_perf
ON mv_dept_performance (dept_name, year, quarter);

-- ステップ4: 勤怠レポート関数
CREATE FUNCTION get_attendance_report(
  p_year INT, p_month INT
) RETURNS TABLE (
  employee_id INT, name TEXT, dept_name TEXT,
  present_days BIGINT, late_days BIGINT,
  absent_days BIGINT, overtime_hours NUMERIC,
  attendance_rate NUMERIC, dept_rank BIGINT
) AS $$
BEGIN
  RETURN QUERY
  SELECT
    e.employee_id,
    e.name,
    d.dept_name,
    COUNT(*) FILTER (WHERE a.status = 'present') AS present_days,
    COUNT(*) FILTER (WHERE a.status = 'late') AS late_days,
    COUNT(*) FILTER (WHERE a.status = 'absent') AS absent_days,
    COALESCE(SUM(a.overtime_hours), 0) AS overtime_hours,
    ROUND(
      COUNT(*) FILTER (WHERE a.status IN ('present','late'))::numeric
      / NULLIF(COUNT(*), 0) * 100, 1
    ) AS attendance_rate,
    RANK() OVER (PARTITION BY d.dept_name
      ORDER BY COUNT(*) FILTER (WHERE a.status = 'present') DESC)
  FROM employees e
  JOIN attendance a ON e.employee_id = a.employee_id
  JOIN departments d ON e.department_id = d.department_id
  WHERE EXTRACT(YEAR FROM a.attend_date) = p_year
    AND EXTRACT(MONTH FROM a.attend_date) = p_month
  GROUP BY e.employee_id, e.name, d.dept_name
  ORDER BY d.dept_name, dept_rank;
END;
$$ LANGUAGE plpgsql;

-- ステップ5: 従業員検索（上記で既に作成済み）

-- ステップ6: 完全なシステムをテスト
-- 2025年1月の勤怠レポート
SELECT * FROM get_attendance_report(2025, 1);

-- 部署業績比較
SELECT * FROM mv_dept_performance
WHERE year = 2025 AND quarter = 1
ORDER BY avg_score DESC;

-- DockerとKubernetesの両方のスキルを持つ従業員を検索
SELECT name, position, skills
FROM employees
WHERE skills @> '["Docker","Kubernetes"]';

-- 全文検索で"engineer"を検索
SELECT name, position, rank
FROM search_employees('engineer');

-- データ変更時にマテリアライズドビューをリフレッシュ
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_dept_performance;
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_performance_ranking;

❓ よくある質問

Q マテリアライズドビューでREFRESH CONCURRENTLYを使用するタイミングは？

A マテリアライズドビューが頻繁に検索され、読み取りブロックが許容できない場合にCONCURRENTLYを使用します。マテリアライズドビューに少なくとも1つのUNIQUEインデックスが必要です。短時間の利用不可が許容できる場合は、通常��REFRESHの方が高速です。

Q FILTER構文とCASE WHENのどちらがパフォーマンスが良いですか？

A パフォーマンスは基本的に同じです。PostgreSQLは内部的にFILTERをCASE WHENと同等の実行計画に最適化します。FILTER構文の方がよりクリーンで読みやすいため推奨されます。

Q LATERAL JOINとサブクエリの違いは何ですか？

A LATERALはサブクエリが外部クエリのカラムを参照でき、相関サブクエリのように動作しますが、複数行を返せます。通常のサブクエリは外部カラムを参照できません。LATERALは「外��行ごとに1つの結果を計算」するケースに適しています。

Q JSONBスキル配列と中間テーブルのどちらが良いですか？

A スキルを独立して管理する必要がある場合（追加/削除/変更、統計、リレーショナル検索）は中間テーブル（employee_skills）の方が良いです。スキルがタグ的な属性で、検索パターンが単純な場合はJSONBの方が柔軟です。このプロジェクトではスキルはタグ的なのでJSONBが適しています。

Q Dockerスキルを検索する際にenglish設定ではなくsimpleを使用する理由は？

A english設定は検索語をステミングしますが、"Docker"は固有名詞なのでステミングの効果はありません。simple設定でも動作しますが、検索語に一般的な英単語（例: "running"）が含まれる場合、english設定のステミングがより効果的です。このプロジェクトではenglish設定を統一して使用しています。

Q マテリアライズドビューをスケジュールで自動的にリフレッシュするには？

A PostgreSQLには組み込みの自動リフレッシュ機能はありません。pg_cron拡張機能でスケジュールREFRESHを実行するか、OSのcron/タスクスケジューラでpsqlコマンドを呼び出します。例: 毎日リフレッシュ SELECT cron.schedule('0 2 * * *', $$REFRESH MATERIALIZED VIEW CONCURRENTLY mv_dept_performance$$)。

Q 複合インデックスのカラム順序のルールは？

A 等価検索カラムを先に、範囲検索カラムを後にします。例: (employee_id, attend_date) は、最初にemployee_idで等価フィルタし、次にattend_dateで範囲スキャンします。順序が逆だとインデックスが使用されない可能性があります。

📖 まとめ

ウィンドウ関数は勤怠統計、ランキング、前年同期比（LAG/LEAD）、累積合計を実現
ビューは複雑なクエリをカプセル化。マテリアライズドビューは結果をキャッシュ。CONCURRENTLYリフレッシュは読み取りをブロックしない
インデックス戦略: 高頻度のWHERE/JOINカラムにインデックス。複合インデックスのカラム順序に注意。部分インデックスでメンテナンスオーバーヘッドを削減
JSONBは柔軟なスキルタグを保存。GINインデックスで包含検索を高速化。@>が最も一般的な検索
全文検索はsetweightでフィールドに重み付けし、トリガーでtsvectorを自動同期
総合プロジェクトは複数の機能を融合: ウィンドウ関数 + マテリアライズドビュー + JSONB + 全文検索 + インデックスチューニング

📝 練習問題

⭐ ウィンドウ関数を��用して、各従業員のQ1出勤日数、全社的な出勤ランク（DENSE_RANK）、および1つ上のランクの従業員より何日少ないか（LAG関数使用）を検索してください。
⭐⭐ 各部署に不足しているスキ��（他部署にはあるがその部署にはないスキル）をリストするマテリアライズドビューmv_skill_gapを作成してください。UNIQUEインデックスを作成し、CONCURRENTLYでリフレッシュし、EXPLAIN ANALYZEでリフレッシュ前後のクエリパフォーマンスを比較してください。
⭐⭐⭐ 従業員管理システムの完全なソリューションを設計: training_recordsテーブル（employee_id, course_name, completion_date, score, tags JSONB）を作成し、関数recommend_training(p_employee_id INT)を作成して、従業員の現在のスキル（employees.skills）と既存の研修（training_records.tags）に基づいて不足している研修コースを推奨してください。全文検索を使用してコース説明をマッチングし、関連度順に推奨結果を返してください。

Web-Tutorial 技術チーム

複数の開発者によって共同維持されているプログラミングチュートリアルプラットフォーム。各チュートリアルは専門分野の開発者が執筆・レビューしています。正確で信頼性の高いコンテンツを目指しています — 問題を見つけた場合はお知らせください。

私たちについて