ビュー(VIEW) — 定義と用途

本記事で使うデータ — employee / department / sales

ビュー（VIEW）は、SELECT文に名前を付けて保存し、その名前をテーブルのように参照できる仕組みです。

テーブルと違ってデータ自体は持たず、参照されるたびに保存されたSELECTが実行されます。

演習に入る前に、本記事で使う 3 つのテーブル — employee / department / sales — の列定義とデータのサンプルを確認しておきます。

① PRAGMA table_info(employee); / PRAGMA table_info(department); / PRAGMA table_info(sales);で 3 テーブルの列定義を確認してください。

② SELECT * FROM employee LIMIT 5; / SELECT * FROM department LIMIT 5; / SELECT * FROM sales LIMIT 5;で先頭 5 行のデータをプレビューしてください。

CREATE VIEW — 複雑なクエリに名前を付ける

ビューはCREATE VIEW ビュー名 AS SELECT ...;で定義します。

定義した後はSELECT * FROM ビュー名と書くだけで、保存されたSELECTが実行され、その結果が返ります。

実体のデータを持たない保存されたクエリなので、もとのテーブルが更新されると、ビューを参照した結果も最新の状態になります。

不要になったビューはDROP VIEW ビュー名;で削除します。

同じ名前のビューを作り直したいときはDROP VIEW IF EXISTS ビュー名;を先に実行してからCREATE VIEWすると、再実行しても失敗しません。

-- 部署ごとの人数を出すクエリにビューとして名前を付ける例
DROP VIEW IF EXISTS dept_headcount;
CREATE VIEW dept_headcount AS
SELECT d.dept_name, COUNT(e.emp_id) AS headcount
FROM department d
LEFT JOIN employee e ON e.dept_id = d.dept_id
GROUP BY d.dept_name;

-- 定義後はテーブルのように参照できる
SELECT dept_name, headcount FROM dept_headcount
ORDER BY headcount DESC;

-- 不要になったら削除
DROP VIEW dept_headcount;

「社員一覧に部署名と勤務地を併記した一覧を、毎回 JOIN を書かずに参照したい」という要件を想定します。1 回の実行で、ビューの定義・参照・削除までを自己完結させてください。（正しく実行できれば解説が表示されます）

① employeeとdepartmentをdept_idで LEFT JOIN し、emp_id、name、salary、dept_name、locationを返すビューをemp_deptという名前で定義してください。同じ名前で何度実行しても失敗しないように、定義の前に既存の同名ビューを削除する文を入れてください。

② 定義したビューからname、dept_name、salaryを取り出し、salaryの降順で先頭 5 行を表示してください。

③ 最後にこのビューを削除して、ページ内に定義を残さないようにしてください。

ビューの用途 — 再利用・権限・抽象化

ビューを使う主な目的は 3 つあります。

再利用: よく使う結合や集計を 1 つの名前にまとめ、同じクエリを何度も書かずに済ませます。

権限: テーブルの一部の列・行だけを見せるビューを作り、利用者にはテーブルそのものではなくビューだけにアクセスを許せます（給与の高い列を隠す、自分の部署の行だけ見せる、など）。

抽象化: 利用側はビューの内部構造（どのテーブルをどう結合しているか）を知らなくても、ビュー名と返る列だけ知っていれば使えます。

-- ① 抽象化: 入社年でラベル付けしたビュー（利用側は式を意識しない）
DROP VIEW IF EXISTS emp_cohort;
CREATE VIEW emp_cohort AS
SELECT name, hired_on,
  CASE
    WHEN hired_on < '2018-01-01' THEN 'Veteran'
    WHEN hired_on < '2021-01-01' THEN 'Mid'
    ELSE 'Recent'
  END AS cohort
FROM employee;

SELECT cohort, COUNT(*) AS cnt
FROM emp_cohort
GROUP BY cohort
ORDER BY cohort;

DROP VIEW emp_cohort;

-- ② 権限: 給与列を含めず公開してよい列だけのビュー
DROP VIEW IF EXISTS emp_public;
CREATE VIEW emp_public AS
SELECT emp_id, name, dept_id FROM employee;
DROP VIEW emp_public;

「社員を給与帯（High / Mid / Low）に分類するロジックを 1 か所にまとめ、もとのemployeeを更新するとビューの集計結果が自動で最新の状態に変わることを確かめたい」という要件を想定します。ビューは保存されたSELECTで実体データを持たないため、参照のたびにもとのテーブルを評価し直します。

① 同名ビューを削除してから、salaryが 6,500,000 以上なら High、5,000,000 以上 6,500,000 未満なら Mid、それ未満なら Low となるtier列を含むビューemp_tierを定義してください。

② tierごとの人数をcntという別名で集計し、更新前の人数を確認してください。

③ employeeに新しい社員（emp_id 999 / name 'Zoe' / dept_id 1 / manager_id NULL / city 'Tokyo' / salary 8000000 / hired_on '2024-01-01'）を INSERT してください（High に該当）。

④ ②と同じ集計クエリをもう一度実行し、High の人数が 1 増えたことを確認してください（もとのテーブル更新がビューにすぐ反映される証拠です）。

⑤ 後片付けとして、追加したemp_id = 999の行を DELETE し、ビューを DROP してください。

集計をまとめたビューで利用側を短くする

ビューには結合だけでなくGROUP BYの集計結果も保存できます。

社員ごとの売上合計のように、利用側が何度も使う集計を 1 つのビューにまとめておくと、利用側はJOINやGROUP BYを書かずに、すでに集計済みの行をSELECTするだけで済みます。

下の例ではsalesを社員ごとに集計し、employeeと結合して社員名を付けたビューを定義します。

利用側は「誰がいくら売ったか」をビュー名で参照するだけで、内部の結合と集計を意識しません。

-- 部署別の売上合計に部署名を付けたビュー
DROP VIEW IF EXISTS dept_sales;
CREATE VIEW dept_sales AS
SELECT d.dept_name, SUM(s.amount) AS total
FROM sales s
JOIN employee e   ON s.emp_id = e.emp_id
JOIN department d ON e.dept_id = d.dept_id
GROUP BY d.dept_name;

-- 利用側は集計済みのビューを SELECT するだけ
SELECT dept_name, total FROM dept_sales
ORDER BY total DESC;

DROP VIEW dept_sales;

「営業成績ダッシュボードで、社員ごとの売上合計の上位を毎回 JOIN・集計せずに参照したい」という要件を想定します。1 回の実行で、ビューの定義・参照・削除まで自己完結させてください。

① employeeとsalesをemp_idで結合し、社員ごとの売上合計をtotalという別名で集計したビューをemp_salesという名前で定義してください。ビューの列はemp_id、name、totalの 3 列にしてください。定義の前に同名ビューを削除する文を入れてください。

② 定義したビューからnameとtotalを取り出し、totalの降順、同額ならnameの昇順で並べて先頭 5 行を表示してください。

③ 最後にこのビューを削除してください。