Summary  
This chapter covers the use of the AVG() aggregate function to compute average values over entire columns or filtered subsets within SQL queries.

General domain of usage  
Relational database querying

`AVG()` は、特定のカラムの平均値を返す関数。

例：

`AVG()` は、特定の条件を満たす行に対しても使用可能。例：

SELECT AVG(population) 
FROM country
WHERE population < 4478500;

import unittest
import psycopg2
import pandas as pd
import decimal

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_avg_surfacearea_correctness(self):
        """Check that the query returns the correct average of the surfacearea column from country table."""
        with open("query.sql", "r") as file:
            query = file.read()
        # Calculate expected average
        self.cursor.execute("SELECT surfacearea FROM country")
        surfaceareas = [row[0] for row in self.cursor.fetchall() if row[0] is not None]
        expected_avg = sum(surfaceareas) / len(surfaceareas) if surfaceareas else None

        self.cursor.execute(query)
        result = self.cursor.fetchone()[0]
        # Accept float rounding error up to 6 decimals
        if isinstance(result, decimal.Decimal):
            result_float = float(result)
        else:
            result_float = result
        if isinstance(expected_avg, decimal.Decimal):
            expected_avg_float = float(expected_avg)
        else:
            expected_avg_float = expected_avg
        condition = expected_avg is not None and abs(result_float - expected_avg_float) < 1e-6
        _dynamic_test(
            self,
            condition,
            "Query returns the correct average value of surfacearea.",
            f"Expected average: {expected_avg}, but got: {result}."
        )

    def test_query_uses_avg_function(self):
        """Check that the query uses the AVG() function."""
        with open("query.sql", "r") as file:
            query = file.read()
        condition = "avg" in query.lower()
        _dynamic_test(
            self,
            condition,
            "Query uses the AVG() function.",
            "Query does not use the AVG() function."
        )

    def test_query_uses_select(self):
        """Check that the query uses the SELECT statement."""
        with open("query.sql", "r") as file:
            query = file.read()
        condition = query.strip().lower().startswith("select")
        _dynamic_test(
            self,
            condition,
            "Query uses the SELECT statement.",
            "Query does not use the SELECT statement."
        )

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):
    import re
    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

SELECTやWHEREを使用したデータの取得やフィルタリング、ORDER BYによる結果の並べ替え、COUNT、AVG、SUMなどの集約関数による集計まで、SQLの基本を学びます。実践的な課題を通じて、実際のデータセットに自信を持ってクエリを実行するスキルを身につけます。

SQLのSELECT文を使用して、テーブルから単一列、複数列、すべての列を取得する方法を学習します。重複しない行の取得や、クエリで返される結果数の制限方法についても解説します。

ORDER BY句を使用してクエリ結果を整理する方法を学びます。1つまたは複数の列でデータを並べ替え、昇順または降順で並べ替え方向を制御し、より明確な出力を実現します。



WHERE句を使用してクエリ結果をフィルタリングする方法を学びます。等価性および非等価性のテスト、値の範囲チェック、データ内のNULL値の識別について解説します。

WHERE句とANDおよびOR演算子を組み合わせて検索条件を絞り込む方法を学びます。NOT演算子を使用して、クエリから特定の結果を除外する方法についても解説します。



SQLの集約関数を使用してテーブルデータを要約する方法を学びます。AVGおよびSUMで平均値や合計値を計算し、COUNTで行数をカウントし、MAXおよびMINで最大値や最小値を取得する方法を習得します。



チャレンジ：AVG() 関数

チャレンジ：AVG() 関数

解答