一般的な人事データベースでは、各部門で最も高給な従業員を見つけることが求められる場合があります。これはサブクエリが非常に役立つ典型的なシナリオであり、各従業員の給与を同じ部門内で比較し、最も高い給与を得ている従業員のみを返す必要があります。この課題では、すべての部門ごとに給与が最も高い従業員を特定するクエリを作成します。`departments` テーブルと `employees` テーブルが利用可能で、各従業員は部門に紐付けられており、`salary` の値を持っています。サブクエリを使用して、各部門ごとに最も高給な従業員を特定することが目標です。

import unittest
import psycopg2
import pandas as pd

class TestPostgresQuery(unittest.TestCase):
    @classmethod
    def setUpClass(cls):
        cls.connection = psycopg2.connect(
            host="localhost",
            user="agent",
            password="qwerty",
            dbname="testdb",
            sslmode="disable"
        )
        cls.cursor = cls.connection.cursor()

    def test_columns_and_row_count(self):
        """Check that the query returns correct columns and number of rows (one for each highest paid employee per department)."""
        with open("query.sql", "r") as file:
            query = file.read()
        self.cursor.execute(query)
        rows = self.cursor.fetchall()
        columns = [desc[0] for desc in self.cursor.description]
        expected_columns = ['department_name', 'employee_name', 'salary']
        _dynamic_test(
            self,
            set(expected_columns) == set(columns),
            "Query returns the correct columns.",
            f"Query should return columns: {expected_columns}, but got: {columns}"
        )
        # Check that each department's top salary is present (even if more than one employee has it)
        self.cursor.execute('SELECT COUNT(*) FROM (SELECT department_id, MAX(salary) FROM employees GROUP BY department_id) t')
        expected_min_rows = self.cursor.fetchone()[0]
        _dynamic_test(
            self,
            len(rows) >= expected_min_rows,
            "Query returns at least one row per department.",
            f"Expected at least {expected_min_rows} rows, but got {len(rows)}."
        )

    def test_highest_salary_per_department(self):
        """Check that only the highest paid employee(s) per department are present."""
        with open("query.sql", "r") as file:
            query = file.read()
        self.cursor.execute(query)
        rows = self.cursor.fetchall()
        columns = [desc[0] for desc in self.cursor.description]
        result_df = pd.DataFrame(rows, columns=columns)
        # Build expected result
        self.cursor.execute("SELECT d.name AS department_name, e.name AS employee_name, e.salary FROM employees e JOIN departments d ON e.department_id = d.department_id")
        all_rows = self.cursor.fetchall()
        all_df = pd.DataFrame(all_rows, columns=['department_name', 'employee_name', 'salary'])
        expected = []
        for dept in all_df['department_name'].unique():
            dept_df = all_df[all_df['department_name'] == dept]
            max_salary = dept_df['salary'].max()
            top_earners = dept_df[dept_df['salary'] == max_salary]
            for _, row in top_earners.iterrows():
                expected.append({'department_name': row['department_name'], 'employee_name': row['employee_name'], 'salary': row['salary']})
        expected_df = pd.DataFrame(expected)
        # Merge to find missing top earners
        merged = pd.merge(expected_df, result_df, on=['department_name', 'employee_name', 'salary'], how='outer', indicator=True)
        missing = merged[merged['_merge'] == 'left_only']
        extra = merged[merged['_merge'] == 'right_only']
        _dynamic_test(
            self,
            missing.empty,
            "All highest paid employees per department are present.",
            f"Missing top earners: {missing[['department_name','employee_name','salary']].to_dict('records')}"
        )
        _dynamic_test(
            self,
            extra.empty,
            "No extra rows are present.",
            f"Extra rows found: {extra[['department_name','employee_name','salary']].to_dict('records')}"
        )

    def test_sorting(self):
        """Check that results are sorted by department name and then employee name."""
        with open("query.sql", "r") as file:
            query = file.read()
        self.cursor.execute(query)
        rows = self.cursor.fetchall()
        columns = [desc[0] for desc in self.cursor.description]
        result_df = pd.DataFrame(rows, columns=columns)
        sorted_df = result_df.sort_values(['department_name', 'employee_name']).reset_index(drop=True)
        _dynamic_test(
            self,
            result_df.reset_index(drop=True).equals(sorted_df),
            "Results are sorted by department name and employee name.",
            "Results are not sorted correctly by department name and employee name."
        )

def _dynamic_test(test_case, condition, success_message, failure_message):
    if condition:
        test_case._testMethodName = success_message
        test_case.assertTrue(True, success_message)
    else:
        test_case._testMethodName = failure_message
        test_case.fail(failure_message)

def normalize_text(text):
    text = text.lower()
    text = re.sub(r"\\s{2,}", " ", text)
    text = re.sub(r"\\s*([,:?])\\s*", r"\\1 ", text)
    return text.strip()

if __name__ == "__main__":
    unittest.main()


test_solution.py

実際のシナリオを通じて、SQLにおけるサブクエリの記述と応用の技術を習得します。本モジュールでは、サブクエリの理論と実践、SELECT、WHERE、FROM句での利用、相関サブクエリについて解説します。

注文、顧客、商品に焦点を当てたeコマースデータセットを使用してサブクエリを探求します。サブクエリを活用してインサイトを抽出し、ビジネス上の課題を解決する方法を学びます。

チャレンジ：各部門で最も高給取りの従業員

解答