import unittest
import psycopg2
import pandas as pd
import re

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_all_customers_present(self):
        """Check that all customers are present in the result, even those without orders."""
        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]
        df = pd.DataFrame(rows, columns=columns)
        # get all customer IDs from the customers table
        self.cursor.execute("SELECT customer_id FROM customers;")
        expected_customers = set(row[0] for row in self.cursor.fetchall())
        customer_ids = set(df["customer_id"].dropna().astype(int))
        _dynamic_test(
            self,
            expected_customers.issubset(customer_ids),
            "All customers are present in the result.",
            f"Missing customers: {expected_customers - customer_ids}"
        )

    def test_all_orders_present(self):
        """Check that all orders are present in the result, even those without customers."""
        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]
        df = pd.DataFrame(rows, columns=columns)
        # get all order IDs from the orders table
        self.cursor.execute("SELECT order_id FROM orders;")
        expected_orders = set(row[0] for row in self.cursor.fetchall())
        order_ids = set(df["order_id"].dropna().astype(int))
        _dynamic_test(
            self,
            expected_orders.issubset(order_ids),
            f"All orders are present in the result.",
            f"Missing orders: {expected_orders - order_ids}"
        )

    def test_customer_without_orders_status(self):
        """Check that customers without orders have match_status 'Customer without orders'."""
        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]
        df = pd.DataFrame(rows, columns=columns)
        # get all customer IDs from the customers table
        self.cursor.execute("SELECT customer_id FROM customers WHERE customer_id NOT IN (SELECT DISTINCT customer_id FROM orders WHERE customer_id IS NOT NULL);")
        no_order_customers = [row[0] for row in self.cursor.fetchall()]
        df_unmatched = df[(df["order_id"].isnull()) & (df["customer_id"].isin(no_order_customers))]
        status_ok = (df_unmatched["match_status"] == "Customer without orders").all()
        _dynamic_test(
            self,
            status_ok and len(df_unmatched) == len(no_order_customers),
            "Customers without orders have correct match_status.",
            "Not all customers without orders have correct match_status or are missing."
        )

    def test_order_without_customer_status(self):
        """Check that orders without customers have match_status 'Order without customer'."""
        with open("query.sql", "r") as file:
            query = file.read()
        # Add a customerless order by first disabling the FK constraint
        self.cursor.execute("ALTER TABLE orders DROP CONSTRAINT IF EXISTS orders_customer_id_fkey;")
        self.connection.commit()
        try:
            self.cursor.execute("INSERT INTO orders (order_id, customer_id, order_date, amount) VALUES (10001, 99999, '2024-06-05', 100.00);")
            self.connection.commit()
            self.cursor.execute(query)
            rows = self.cursor.fetchall()
            columns = [desc[0] for desc in self.cursor.description]
            df = pd.DataFrame(rows, columns=columns)
            df_unmatched = df[(df["customer_id"].isnull()) & (df["order_id"] == 10001)]
            status_ok = (df_unmatched["match_status"] == "Order without customer").all()
            _dynamic_test(
                self,
                status_ok and len(df_unmatched) == 1,
                "Orders without customers have correct match_status.",
                "Order without customer does not have correct match_status or is missing."
            )
        finally:
            self.cursor.execute("DELETE FROM orders WHERE order_id = 10001;")
            self.connection.commit()

    def test_matched_status(self):
        """Check that rows with both customer and order have match_status 'Matched'."""
        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]
        df = pd.DataFrame(rows, columns=columns)
        matched = df[(df["customer_id"].notnull()) & (df["order_id"].notnull())]
        status_ok = (matched["match_status"] == "Matched").all()
        _dynamic_test(
            self,
            status_ok,
            "Matched customer-order pairs have correct match_status.",
            "Some matched rows do not have correct match_status."
        )

    def test_result_columns(self):
        """Check that result contains required columns."""
        with open("query.sql", "r") as file:
            query = file.read()
        try:
            self.cursor.execute(query)
            columns = [desc[0] for desc in self.cursor.description]
            expected = {"customer_id","customer_name","order_id","order_date","match_status"}
            _dynamic_test(
                self,
                expected.issubset(set(columns)),
                "Result contains all required columns.",
                f"Missing columns: {expected - set(columns)}"
            )
        except Exception as e:
            _dynamic_test(self, False, "", f"Query execution failed: {e}")

    def test_ordering(self):
        """Check that unmatched records appear before matched records in the result."""
        with open("query.sql", "r") as file:
            query = file.read()
        try:
            self.cursor.execute(query)
            rows = self.cursor.fetchall()
            columns = [desc[0] for desc in self.cursor.description]
            df = pd.DataFrame(rows, columns=columns)
            if "match_status" not in df.columns:
                _dynamic_test(self, False, "", "Column 'match_status' not found in result.")
                return
            try:
                first_matched = df[df["match_status"] == "Matched"].index[0]
                last_unmatched = df[df["match_status"] != "Matched"].index[-1]
                _dynamic_test(
                    self,
                    last_unmatched < first_matched,
                    "Unmatched records appear before matched records.",
                    "Matched records appear before unmatched records."
                )
            except IndexError:
                _dynamic_test(
                    self,
                    False,
                    "",
                    "Either no matched or no unmatched records found, cannot check ordering."
                )
        except Exception as e:
            _dynamic_test(self, False, "", f"Query execution failed: {e}")

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結合の技術を習得します。本コースでは、基礎的な結合の概念から高度な多テーブルクエリまでを網羅し、複数のデータソースからデータを自信を持って分析・統合できる力を身につけます。

実践的なシナリオとハンズオンチャレンジを通じて、主要なSQL結合タイプ（INNER、LEFT、RIGHT、FULL OUTER JOIN）を探求します。

複数テーブルの結合、自己結合、および複雑なデータ分析のための高度な結合条件を習得し、SQLスキルを向上させます。

結合におけるNULL処理の詳細を習得し、結合パフォーマンスを最適化し、実際のデータ分析課題に対応します。

チャレンジ：Unmatched Records レポート

解答