データ駆動型の意思決定がビジネスにおいてますます重要になる中、データベース技術の精通は不可欠です。
特に、SQLはデータを操作し分析するための強力なツールであり、その高度な機能を理解することは、複雑なデータセットから深い洞察を引き出す能力を高めます。
応用編第1回のこのカリキュラムでは、高度なSQLテクニックに焦点を当て、データの効果的な抽出、処理、そして分析方法を体系的に学びます。
1.サブクエリとそのタイプ
サブクエリとは、他のSQLクエリの中で使用されるSQLステートメントです。
これは、結果を生成するために外側のクエリと連携して動作します。サブクエリは通常、SELECT、INSERT、UPDATE、または DELETE ステートメントの中で利用されます。
サブクエリのタイプ
サブクエリには主に二つのタイプがあります：
非相関サブクエリと相関サブクエリです。
1. 非相関サブクエリ
• 定義: 非相関サブクエリは、外側のクエリから独立して実行され、一度だけ評価されます。その結果は外側のクエリによって使用される定数として機能します。
• 例: 従業員テーブルから、全従業員の平均年齢以上の年齢を持つ従業員の名前を選択する。
SELECT name FROM employees
WHERE age >= (SELECT AVG(age) FROM employees);• 実際の現場での使用例: 結果が外側のクエリの実行に影響を与えず、一度計算すれば良い場合に適しています。
2. 相関サブクエリ
• 定義: 相関サブクエリは、外側のクエリの各行に対して個別に評価されるサブクエリです。外側のクエリの列を参照することが多く、その参照によって結果が変わることが特徴です。
• 例: 各部署で最高給与を受け取っている従業員の名前を選択する。
SELECT name, salary, department_id FROM employees AS e1
WHERE salary = (
SELECT MAX(salary) FROM employees AS e2
WHERE e1.department_id = e2.department_id
);• 使用例: 外側のクエリの行ごとに結果が異なる必要がある場合や、外側のクエリのデータに基づいてさらに情報を絞り込む必要がある場合に適しています。
それでは、実際に問題を解いてみましょう！
実践例題1
• 問題: 各部署の平均給与以上を稼ぐ従業員の名前と給与、部署IDをリストする。
問題を解く前に下記SQLを実行し、DBを作成してください。
-- employees テーブルの作成
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(100),
salary DECIMAL(10, 2),
department_id INT
);
-- サンプルデータの挿入
INSERT INTO employees (id, name, salary, department_id) VALUES
(1, 'Alice', 50000.00, 1),
(2, 'Bob', 55000.00, 1),
(3, 'Charlie', 40000.00, 1),
(4, 'David', 60000.00, 2),
(5, 'Eve', 45000.00, 2),
(6, 'Fiona', 70000.00, 2),
(7, 'George', 48000.00, 3),
(8, 'Hannah', 52000.00, 3),
(9, 'Ian', 51000.00, 3),
(10, 'Judy', 75000.00, 4),
(11, 'Kyle', 80000.00, 4),
(12, 'Liam', 55000.00, 4);
-- 確認のためのSelect文
SELECT * FROM employees;2.複雑なデータ分析のためのウィンドウ関数
ウィンドウ関数の基本
ウィンドウ関数は、データセット全体にわたって値を計算する一方で、個々の行を保持する特殊なタイプのSQL関数です。
これにより、データの関係性を維持しつつ、複雑な計算を行うことができます。
定義
ウィンドウ関数とは、クエリの結果セットに対して定義された「ウィンドウ」内で各行に対して関数を適用することを可能にするSQLの機能です。
これにより、行間で情報を比較したり、ランク付けしたりすることができます。
主なウィンドウ関数
• ROW_NUMBER(): 各行にユニークな連続する番号を割り当てます。
• RANK(): 同じ値の行には同じランクを割り当て、次のランクは「スキップ」されます（例: 1, 2, 2, 4）。
• DENSE_RANK(): 同じ値の行には同じランクを割り当てますが、次のランクはスキップされません（例: 1, 2, 2, 3）。
• LEAD(): 指定した数だけ後の行の値を返します。
• LAG(): 指定した数だけ前の行の値を返します。
• SUM(): ウィンドウ内の値の合計を計算します。
• AVG(): ウィンドウ内の値の平均を計算します。
活用例
ウィンドウ関数を用いて、同じ部署内での従業員の給与ランキングを計算する例を下記に挙げます。
SELECT name, salary, department_id,
RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank
FROM employees;それでは、実際に問題を解いてみましょう！
実践例題2
• 課題: 販売データに基づいて、各販売員の売上合計とそのランクを計算するクエリを作成する。
問題を解く前に下記SQLを実行し、DBを作成してください。
-- sales テーブルの作成
CREATE TABLE sales (
sales_id INT PRIMARY KEY,
salesperson_id INT,
amount DECIMAL(10, 2),
sale_date DATE
);
-- サンプルデータの挿入
INSERT INTO sales (sales_id, salesperson_id, amount, sale_date) VALUES
(1, 101, 1000.00, '2022-07-01'),
(2, 102, 1500.00, '2022-07-01'),
(3, 103, 500.00, '2022-07-02'),
(4, 101, 2500.00, '2022-07-03'),
(5, 102, 300.00, '2022-07-03'),
(6, 104, 2200.00, '2022-07-04'),
(7, 101, 800.00, '2022-07-04'),
(8, 103, 1200.00, '2022-07-05'),
(9, 102, 1100.00, '2022-07-06'),
(10, 104, 1000.00, '2022-07-07');
-- 確認のためのSelect文
SELECT * FROM sales;3.CTE（共通テーブル式）
CTEの活用
CTEは、複雑なSQLクエリを構築する際に、クエリの可読性を高めるために非常に有効なツールです。
CTEを使用することで、繰り返し利用される結果セットを一時的に名前付きのテーブルとして定義し、そのテーブルをクエリ内で何度も参照することが可能になります。
定義
CTEとは、「Common Table Expression」の略で、クエリの中で定義される再利用可能な一時的な結果セットです。
これは、特に複雑なクエリや、クエリ内でのデータの階層的な処理において有用です。
メリット
• 可読性の向上: CTEを利用することで、複雑なクエリを小さな部分に分割して、各部分を明確にすることができます。
• 再利用性: 一度定義したCTEはクエリ内で複数回参照することができ、これによりコードの重複を減らすことができます。
• デバッグの容易さ: クエリの各部分を独立してテストしやすくなるため、デバッグプロセスが簡単になります。
使用例
CTEは、データの階層的な処理、複雑な集計、または同一クエリ内でのデータの複数回の利用に特に適しています。
例えば、部署ごとの最高給与を受け取る従業員のリストを取得するクエリなどが挙げられます。
それでは、実際に問題を解いてみましょう！
実践例題3
• 課題: 複数の製品カテゴリにわたる売上データを集計し、最も売上が高い製品を特定するクエリをCTEを用いて書く。
問題を解く前に下記SQLを実行し、DBを作成してください。
-- products テーブルの作成
CREATE TABLE products (
product_id INT PRIMARY KEY,
product_name VARCHAR(255),
category_id INT,
price DECIMAL(10, 2),
sales DECIMAL(10, 2)
);
-- サンプルデータの挿入
INSERT INTO products (product_id, product_name, category_id, price, sales) VALUES
(1, 'Product A', 1, 20.00, 150.00),
(2, 'Product B', 1, 30.00, 300.00),
(3, 'Product C', 2, 15.00, 120.00),
(4, 'Product D', 2, 25.00, 200.00),
(5, 'Product E', 3, 50.00, 400.00),
(6, 'Product F', 3, 60.00, 600.00),
(7, 'Product G', 4, 10.00, 50.00),
(8, 'Product H', 4, 40.00, 250.00);
-- 確認のためのSelect文
SELECT * FROM products;4.まとめ
SQLの高度な技術を習得することは、データベース操作の効率と分析の質を飛躍的に向上させる鍵となります。
このカリキュラムでは、サブクエリ、ウィンドウ関数、CTE（共通テーブル式）の三つの高度なテーマに焦点を当て、それぞれの理論的背景と具体的な応用方法を深掘りしました。
これらの概念を学ぶことで、複雑なデータセットに対して精度高く、効率的に操作を行う能力を身につけることができるでしょう。
今回のカリキュラムで行なった問題の解答は後日公開します！
