この実験 (lab) では、高度なテクニックを探求することで、PostgreSQL のクエリ作成スキルを向上させます。別のクエリの結果に基づいてデータをフィルタリングするために、WHERE 句内でサブクエリを使用する方法を学びます。
この実験 (lab) では、クエリの可読性とモジュール性を向上させるために、共通テーブル式 (CTE: Common Table Expressions) を定義して利用する方法を説明します。さらに、ROW_NUMBER などのウィンドウ関数を適用して、行のセット全体で計算を実行します。最後に、GROUP BY および HAVING 句を使用してデータをグループ化およびフィルタリングし、データセットから有意義な洞察を抽出する方法を習得します。
このステップでは、SQL クエリの WHERE 句内でサブクエリを使用する方法を学びます。サブクエリは、内部クエリまたはネストされたクエリとも呼ばれ、別のクエリの中に埋め込まれたクエリです。サブクエリは、取得するデータをさらに制限するための条件として、メインクエリで使用されるデータを返すために使用されます。
WHERE 句におけるサブクエリの理解 (Understanding Subqueries in the WHERE Clause)
WHERE 句のサブクエリは通常、カラムの値をサブクエリの結果と比較するために使用されます。サブクエリが最初に実行され、その結果が外部クエリで使用されます。
基本的な構文 (Basic Syntax):
SELECT column1, column2
FROM table_name
WHERE column_name IN (SELECT column_name FROM another_table WHERE condition);ここで、サブクエリ (SELECT column_name FROM another_table WHERE condition) は値のセットを返します。外部クエリは、column_name がそのセットに含まれる table_name から行を選択します。
シナリオ (Scenario):
employees と departments の 2 つのテーブルがあると仮定します。employees テーブルには、employee_id、employee_name、department_id などの従業員に関する情報が含まれています。departments テーブルには、department_id や department_name などの部署に関する情報が含まれています。
「Sales」部署で働くすべての従業員を見つけたいとします。
ステップ 1: テーブルの作成とデータの挿入 (Step 1: Create the Tables and Insert Data)
まず、postgres ユーザーを使用して PostgreSQL データベースに接続します。
sudo -u postgres psql次に、departments テーブルを作成します。
CREATE TABLE departments (
department_id SERIAL PRIMARY KEY,
department_name VARCHAR(50)
);サンプルデータを departments テーブルに挿入します。
INSERT INTO departments (department_name) VALUES
('Sales'),
('Marketing'),
('Engineering');次に、employees テーブルを作成します。
CREATE TABLE employees (
employee_id SERIAL PRIMARY KEY,
employee_name VARCHAR(50),
department_id INTEGER REFERENCES departments(department_id)
);サンプルデータを employees テーブルに挿入します。
INSERT INTO employees (employee_name, department_id) VALUES
('Alice', 1),
('Bob', 2),
('Charlie', 1),
('David', 3),
('Eve', 2);ステップ 2: サブクエリの作成 (Step 2: Write the Subquery)
次に、WHERE 句でサブクエリを使用して、「Sales」部署で働くすべての従業員を見つけるクエリを作成します。
SELECT employee_name
FROM employees
WHERE department_id IN (SELECT department_id FROM departments WHERE department_name = 'Sales');説明 (Explanation):
(SELECT department_id FROM departments WHERE department_name = 'Sales') は、department_name が「Sales」である departments テーブルから department_id を選択します。この場合、1 が返されます。1 のみ) に department_id が含まれる employees テーブルから employee_name を選択します。ステップ 3: クエリの実行と結果の表示 (Step 3: Execute the Query and View the Results)
psql ターミナルでクエリを実行します。次の出力が表示されます。
employee_name
---------------
Alice
Charlie
(2 rows)これは、Alice と Charlie が「Sales」部署で働く従業員であることを示しています。
ステップ 4: EXISTS を使用したサブクエリ (Step 4: Using EXISTS with Subqueries)
WHERE 句でサブクエリを使用するもう 1 つの方法は、EXISTS 演算子を使用することです。EXISTS 演算子は、サブクエリ内の行の存在をテストします。サブクエリが行を返す場合は true を返し、それ以外の場合は false を返します。
EXISTS を使用して同じ結果を達成する例を次に示します。
SELECT employee_name
FROM employees e
WHERE EXISTS (
SELECT 1
FROM departments d
WHERE d.department_id = e.department_id
AND d.department_name = 'Sales'
);このクエリは、前のクエリと同じ結果を達成しますが、IN の代わりに EXISTS 演算子を使用します。
説明 (Explanation):
SELECT 1 FROM departments d WHERE d.department_id = e.department_id AND d.department_name = 'Sales' は、現在の従業員と同じ department_id を持つ「Sales」という名前の部署があるかどうかを確認します。department_id を持つ「Sales」部署がある場合)、EXISTS 演算子は true を返し、従業員の名前が選択されます。psql ターミナルでクエリを実行します。以前と同じ出力が表示されます。
employee_name
---------------
Alice
Charlie
(2 rows)これで、WHERE 句でサブクエリを使用して、別のテーブルの条件に基づいてデータをフィルタリングすることに成功しました。また、サブクエリで EXISTS 演算子を使用する方法も学びました。
このステップでは、PostgreSQL で共通テーブル式 (CTE: Common Table Expression) を定義して使用する方法を学びます。CTE は、単一の SELECT、INSERT、UPDATE、または DELETE ステートメント内で参照できる一時的な名前付き結果セットです。CTE は、複雑なクエリをより単純で読みやすい部分に分割するのに役立ちます。
CTE の理解 (Understanding CTEs)
CTE は WITH 句を使用して定義されます。これらは、クエリの実行期間中のみ存在します。
基本的な構文 (Basic Syntax):
WITH cte_name AS (
SELECT column1, column2
FROM table_name
WHERE condition
)
SELECT column1, column2
FROM cte_name
WHERE condition;ここで、cte_name は CTE に付ける名前です。括弧内の SELECT ステートメントは、CTE の結果セットを定義します。外側の SELECT ステートメントは、CTE を通常のテーブルであるかのようにクエリします。
シナリオ (Scenario):
前のステップの employees および departments テーブルを引き続き使用して、CTE を使用して従業員の名前と対応する部署名を見つけます。
ステップ 1: テーブルとデータの確認 (Step 1: Verify the Tables and Data)
employees および departments テーブルが存在し、前のステップからのデータが含まれていることを確認します。これは、psql ターミナルで次のクエリを実行して確認できます。
SELECT * FROM departments;
SELECT * FROM employees;テーブルまたはデータが見つからない場合は、前のステップを参照してテーブルを作成し、データを挿入してください。
ステップ 2: CTE の定義 (Step 2: Define a CTE)
次に、employees テーブルと departments テーブルを結合して、従業員名と部署名を取得する EmployeeDepartments という名前の CTE を定義します。
WITH EmployeeDepartments AS (
SELECT
e.employee_name,
d.department_name
FROM
employees e
JOIN
departments d ON e.department_id = d.department_id
)
SELECT employee_name, department_name
FROM EmployeeDepartments;説明 (Explanation):
WITH EmployeeDepartments AS (...) 句は、EmployeeDepartments という名前の CTE を定義します。SELECT ステートメントは、employees テーブル (エイリアス e) を departments テーブル (エイリアス d) と department_id カラムで結合します。SELECT ステートメントは、EmployeeDepartments CTE から employee_name と department_name を取得します。ステップ 3: クエリの実行と結果の表示 (Step 3: Execute the Query and View the Results)
psql ターミナルでクエリを実行します。次の出力が表示されます。
employee_name | department_name
---------------+-----------------
Alice | Sales
Bob | Marketing
Charlie | Sales
David | Engineering
Eve | Marketing
(5 rows)これは、すべての従業員の名前と対応する部署名を示しています。
ステップ 4: フィルタリングに CTE を使用する (Step 4: Using CTEs for Filtering)
CTE を使用してデータをフィルタリングすることもできます。たとえば、EmployeeDepartments CTE を使用して、「Sales」部署で働くすべての従業員を見つけます。
WITH EmployeeDepartments AS (
SELECT
e.employee_name,
d.department_name
FROM
employees e
JOIN
departments d ON e.department_id = d.department_id
)
SELECT employee_name
FROM EmployeeDepartments
WHERE department_name = 'Sales';説明 (Explanation):
SELECT ステートメントに WHERE 句を追加して、結果を「Sales」部署で働く従業員のみを含むようにフィルタリングします。psql ターミナルでクエリを実行します。次の出力が表示されます。
employee_name
---------------
Alice
Charlie
(2 rows)これは、Alice と Charlie が「Sales」部署で働く従業員であることを示しています。
これで、CTE を定義して使用し、テーブルを結合してデータをフィルタリングすることに成功しました。CTE は、複雑な SQL クエリの可読性と保守性を大幅に向上させることができます。
このステップでは、PostgreSQL でウィンドウ関数を適用する方法を学びます。ウィンドウ関数は、現在の行に関連するテーブル行のセット全体で計算を実行します。これらは集計関数に似ていますが、集計関数とは異なり、ウィンドウ関数は行を単一の出力行にグループ化しません。代わりに、結果セットの各行に値を提供します。
ウィンドウ関数の理解 (Understanding Window Functions)
ウィンドウ関数は、計算の対象となる行のウィンドウを定義するために OVER() 句を使用します。OVER() 句には、ウィンドウをさらに定義するために PARTITION BY および ORDER BY 句を含めることができます。
基本的な構文 (Basic Syntax):
SELECT
column1,
column2,
WINDOW_FUNCTION(column3) OVER (
[PARTITION BY column4]
[ORDER BY column5]
) AS alias_name
FROM
table_name;WINDOW_FUNCTION: ウィンドウ関数の名前 (例：ROW_NUMBER、RANK、SUM、AVG)。OVER(): 関数が動作するウィンドウを指定します。PARTITION BY: 行をパーティションに分割し、ウィンドウ関数は各パーティションに個別に適用されます。ORDER BY: 各パーティション内の行の順序を定義します。alias_name: 計算されたウィンドウ関数の結果のエイリアス (別名)。シナリオ (Scenario):
前のステップの employees および departments テーブルを引き続き使用して、ROW_NUMBER() ウィンドウ関数を使用して、employee_name に基づいて、それぞれの部署内の各従業員に一意のランクを割り当てます。
ステップ 1: ROW_NUMBER() ウィンドウ関数の適用 (Step 1: Apply the ROW_NUMBER() Window Function)
次に、ROW_NUMBER() ウィンドウ関数を使用して、部署内の各従業員にランクを割り当てるクエリを作成します。
SELECT
employee_name,
department_id,
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY employee_name) AS employee_rank
FROM
employees;説明 (Explanation):
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY employee_name): これはウィンドウ関数です。
ROW_NUMBER(): ウィンドウ内の各行に一意の連番整数を割り当てます。PARTITION BY department_id: department_id に基づいて行をパーティションに分割します。これは、ランキングが各部署に対して個別に行われることを意味します。ORDER BY employee_name: 各パーティション内の行がランク付けされる順序を指定します。この場合、従業員は employee_name でアルファベット順にランク付けされます。employee_rank: これは、ウィンドウ関数の結果に付けられたエイリアスです。ステップ 2: クエリの実行と結果の表示 (Step 2: Execute the Query and View the Results)
psql ターミナルでクエリを実行します。次の出力が表示されます。
employee_name | department_id | employee_rank
---------------+---------------+---------------
Alice | 1 | 1
Charlie | 1 | 2
Bob | 2 | 1
Eve | 2 | 2
David | 3 | 1
(5 rows)これは、各従業員の名前、部署 ID、および部署内のランクを示しています。たとえば、Alice は部署 1 で 1 位、Charlie は部署 1 で 2 位です。
ステップ 3: CTE でのウィンドウ関数の使用 (Step 3: Using Window Functions with CTEs)
CTE 内でウィンドウ関数を使用して、クエリをより整理することもできます。CTE を使用して、前のクエリを書き換えてみましょう。
WITH RankedEmployees AS (
SELECT
employee_name,
department_id,
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY employee_name) AS employee_rank
FROM
employees
)
SELECT
employee_name,
department_id,
employee_rank
FROM
RankedEmployees;このクエリは前のクエリと同じ結果を生成しますが、CTE を使用してウィンドウ関数の計算をカプセル化します。
psql ターミナルでクエリを実行します。以前と同じ出力が表示されます。
このステップでは、PostgreSQL で GROUP BY および HAVING 句を使用して、行をグループ化し、グループ化された結果をフィルタリングする方法を学びます。GROUP BY 句は、指定されたカラムで同じ値を持つ行を、部署ごとの従業員数を見つけるなどの要約行にグループ化します。HAVING 句は、指定された条件に基づいて、これらのグループ化された行をフィルタリングするために使用されます。
GROUP BY と HAVING の理解 (Understanding GROUP BY and HAVING)
COUNT、SUM、AVG、MIN、MAX) を使用して、各グループの値を計算します。GROUP BY 句によって作成されたグループをフィルタリングします。これは WHERE 句に似ていますが、個々の行ではなくグループに対して動作します。基本的な構文 (Basic Syntax):
SELECT
column1,
aggregate_function(column2)
FROM
table_name
WHERE
condition
GROUP BY
column1
HAVING
aggregate_function(column2) condition;column1: グループ化するカラム。aggregate_function(column2): 各グループの column2 に適用される集計関数。WHERE: グループ化前に行をフィルタリングします。GROUP BY: column1 の値に基づいて行をグループ化します。HAVING: グループ化後に、集計関数の結果に基づいてグループをフィルタリングします。シナリオ (Scenario):
employees および departments テーブルを引き続き使用して、GROUP BY と HAVING を使用して、複数の従業員がいる部署を見つけます。
ステップ 1: 部署でグループ化し、従業員をカウントする (Step 1: Group by Department and Count Employees)
まず、従業員を部署でグループ化し、各部署の従業員数をカウントするクエリを作成します。
SELECT
d.department_name,
COUNT(e.employee_id) AS employee_count
FROM
employees e
JOIN
departments d ON e.department_id = d.department_id
GROUP BY
d.department_name;説明 (Explanation):
SELECT d.department_name, COUNT(e.employee_id) AS employee_count: これは、部署名と各部署の従業員 ID のカウントを選択します。FROM employees e JOIN departments d ON e.department_id = d.department_id: これは、employees テーブルと departments テーブルを department_id カラムで結合します。GROUP BY d.department_name: これは、行を部署名でグループ化するため、COUNT() 関数は各部署の従業員数をカウントします。ステップ 2: クエリの実行と結果の表示 (Step 2: Execute the Query and View the Results)
psql ターミナルでクエリを実行します。次の出力が表示されます。
department_name | employee_count
-----------------+----------------
Engineering | 1
Marketing | 2
Sales | 2
(3 rows)これは、各部署の従業員数を示しています。
ステップ 3: HAVING でフィルタリングする (Step 3: Filter with HAVING)
次に、HAVING 句を追加して、結果を複数の従業員がいる部署のみを含むようにフィルタリングします。
SELECT
d.department_name,
COUNT(e.employee_id) AS employee_count
FROM
employees e
JOIN
departments d ON e.department_id = d.department_id
GROUP BY
d.department_name
HAVING
COUNT(e.employee_id) > 1;説明 (Explanation):
HAVING 句が追加されています。
HAVING COUNT(e.employee_id) > 1: これは、従業員 ID のカウントが 1 より大きい部署のみを含むようにグループをフィルタリングします。ステップ 4: クエリの実行と結果の表示 (Step 4: Execute the Query and View the Results)
psql ターミナルでクエリを実行します。次の出力が表示されます。
department_name | employee_count
-----------------+----------------
Marketing | 2
Sales | 2
(2 rows)これは、複数の従業員がいる部署 (Marketing と Sales) のみを示しています。
これで、GROUP BY 句と HAVING 句を使用して、行をグループ化し、条件に基づいてグループ化された結果をフィルタリングすることに成功しました。
この実験 (Lab) では、高度な PostgreSQL クエリの作成方法を学びました。まず、WHERE 句内でサブクエリを使用して、別のクエリの結果に基づいてデータをフィルタリングする方法を学びました。これには、サブクエリの構文と適用を理解して、内部クエリによって返される値のセットに対してカラム値を比較することが含まれます。
次に、クエリの可読性とモジュール性を向上させるために、共通テーブル式 (CTE: Common Table Expressions) を定義して利用する方法に進みました。CTE を使用すると、複雑なクエリをより単純で管理しやすい部分に分割できます。
次に、ROW_NUMBER などのウィンドウ関数を適用して、行のセット全体で計算を実行しました。ウィンドウ関数は集計関数に似ていますが、行を単一の出力行にグループ化するのではなく、結果セットの各行に値を提供します。
最後に、GROUP BY 句と HAVING 句を使用してデータをグループ化およびフィルタリングし、データセットから意味のある洞察を抽出する方法を習得しました。GROUP BY 句は、指定されたカラムで同じ値を持つ行を要約行にグループ化し、HAVING 句は、指定された条件に基づいてこれらのグループ化された行をフィルタリングします。
