タグ: sql

データベーススペシャリスト試験の過去問で気になった問題があったので実機で試すことにしました。

現場ではこのような状況にならないようにSQLを書くので気にしたこともありませんでしたが、AVG関数の対象にNULLが含まれている場合、それは分子・分母から除外されて計算されます。

この点について実際にSQLを実行しながら確認していきます。

環境構築

動作確認用のMySQLのコンテナを作成します。

$ docker run -d --name test-mysql -p 3306:3306 -e MYSQL_ROOT_PASSWORD=password mysql:8.3

コンソールまたはGUIツールで接続し、データベースを作成します。

CREATE DATABASE test;
USE test;

今回、動作確認はすべてDataGripで行っていますが、その他のツールを使用しても結果は変わりません。

商品テーブルと商品別売上実績テーブルを作成する

問題文自体は記載しませんが、令和4年秋期 データベーススペシャリスト試験 午前IIの問7の問題となります。手元に問題集がある方はそちらを参照してください。

概要を簡単に説明すると、商品テーブルと商品別売上実績テーブルが提示され、それらに対して問題文で与えられたSQLを実行するとどのような結果が返ってくるかを問う問題です。

AVG関数やGROUP BYの働き、左外部結合LEFT OUTER JOINによる結合についての理解度を問う問題となっています。

問題文と同様の商品テーブルと商品別売上実績テーブルを作成します。

-- 商品テーブル
CREATE TABLE products (
    product_code VARCHAR(4), -- 商品コード
    product_name VARCHAR(255), -- 商品名
    product_rank CHAR(1), -- 商品ランク
    PRIMARY KEY (product_code)
);

商品テーブルには商品を一意に特定する商品コードに加え、商品を格付けするための商品ランクというカラムがあることがわかります。

-- 商品別売上実績テーブル
CREATE TABLE sales_by_product (
    product_code VARCHAR(4), -- 商品コード
    total_sales_amount integer, -- 売上合計金額
    PRIMARY KEY (product_code)
);

商品別売上実績には商品ごとに売上合計金額が格納されていることがわかります。この問題ではこの売上合計金額から売上平均金額を求めています。　

次に問題文と同じデータをインサートします。

-- 商品テーブル
INSERT INTO products VALUES ('S001', 'PPP', 'A');
INSERT INTO products VALUES ('S002', 'QQQ', 'A');
INSERT INTO products VALUES ('S003', 'RRR', 'A');
INSERT INTO products VALUES ('S004', 'SSS', 'B');
INSERT INTO products VALUES ('S005', 'TTT', 'C');
INSERT INTO products VALUES ('S006', 'UUU', 'C');

-- 商品別売上実績テーブル
INSERT INTO sales_by_product VALUES ('S001', 50);
INSERT INTO sales_by_product VALUES ('S003', 250);
INSERT INTO sales_by_product VALUES ('S004', 350);
INSERT INTO sales_by_product VALUES ('S006', 450);

登録したデータを確認しておきます。

まずは商品テーブルです。

SELECT * FROM products;

=>
S001,PPP,A
S002,QQQ,A
S003,RRR,A
S004,SSS,B
S005,TTT,C
S006,UUU,C

次に商品別売上実績テーブルです。すべての商品について売上実績があるというわけではないことがわかります。

SELECT * FROM sales_by_product;

=>
S001,50
S003,250
S004,350
S006,450

問題のSQLを確認する

まずは問題文のSQLを確認します。

SELECT
    AVG(T2.total_sales_amount) AS `売上平均金額`
FROM
    products T1
    LEFT OUTER JOIN sales_by_product T2
    ON
        T1.product_code = T2.product_code
WHERE
    T1.product_rank = 'A'
GROUP BY
    T1.product_rank
;

商品ランクごとに売上合計金額の平均を取得し、商品ランクがAのレコードのみ表示しています。

結果を確認するまえに、商品ランクがAのレコードを集計せずに抽出するとどうなるか確認してみます。

SELECT
    T1.product_code AS `商品コード`,
    T1.product_name AS `商品名`,
    T2.total_sales_amount AS `売上合計金額`
FROM
    products T1
    LEFT OUTER JOIN sales_by_product T2
    ON
        T1.product_code = T2.product_code
WHERE
    T1.product_rank = 'A'
;

=>
S001,PPP,50
S002,QQQ,
S003,RRR,250

上記の実行すると、商品ランクAの商品コードS002の売上合計金額がNULLになっていることがわかります。

このSQLで抽出された3レコードについてAVG関数を適用したときに、分子は50+250=300であることははっきりしていますが、分母は2でしょうか？3でしょうか？

その答えを確認するために問題文のSQLを実行してみます。

SELECT
    AVG(T2.total_sales_amount) AS `売上平均金額`
FROM
    products T1
    LEFT OUTER JOIN sales_by_product T2
    ON
        T1.product_code = T2.product_code
WHERE
    T1.product_rank = 'A'
GROUP BY
    T1.product_rank
;

=> 150.0000

実行結果をみると、150となっているので、2で割っていることがわかります。すなわち、NULLでない値について平均を取得していることがわかります。

COUNT関数の動作を確認する

もし、AVG関数を使わずにSUM関数とCOUNT関数を使うとどうなるのでしょうか？

COUNT関数について確認してみましょう。

COUNT(*)、COUNT(T1.product_code)、COUNT(T2.product_code)、COUNT(T2.total_sales_amount)のそれぞれの結果を確認してみます。説明の都合上、COUNT(*)を最後に掲載します。

COUNT(T1.product_code)の結果

COUNT(T1.product_code)でカウントしてみると、3が返ってきます。

SELECT
    COUNT(T1.product_code) AS `商品数`
FROM
    products T1
    LEFT OUTER JOIN sales_by_product T2
    ON
        T1.product_code = T2.product_code
WHERE
    T1.product_rank = 'A'
;

=> 3

これは特に問題ないと思います。

COUNT(T2.product_code)の結果

次にCOUNT(T2.product_code)でカウントしてみます。

SELECT
    COUNT(T2.product_code) AS `売上実績数`
FROM
    products T1
    LEFT OUTER JOIN sales_by_product T2
    ON
        T1.product_code = T2.product_code
WHERE
    T1.product_rank = 'A'
;

=> 2

すると、2が返ってきます。商品コードS002に対応する商品別売上実績テーブルのレコードがないため、2とカウントされます。

COUNT(T2.total_sales_amount)の結果

COUNT(T2.total_sales_amount)についても確認します。

SELECT
    COUNT(T2.total_sales_amount) AS `売上実績数`
FROM
    products T1
    LEFT OUTER JOIN sales_by_product T2
    ON
        T1.product_code = T2.product_code
WHERE
    T1.product_rank = 'A'
;

=> 2

これも2と返ってきます。

COUNT(*)の結果

最後にCOUNT(*)でカウントしてみます。

SELECT
    COUNT(*) AS `レコード数`
FROM
    products T1
    LEFT OUTER JOIN sales_by_product T2
    ON
        T1.product_code = T2.product_code
WHERE
    T1.product_rank = 'A'
;

=> 3

これは駆動表である商品テーブルのレコード数が返ってきます。COUNT関数を使うとき、特に意識せずにCOUNT(*)を使いがちですが、何をカウントしたいのか明確にしないと意図しない結果が得られる場合がある点に注意が必要です。

そもそも売上平均金額はいくらなのか？

問題文で問われている内容から離れて、そもそも「売上平均金額」はいくらと計算されるのが正しいのでしょうか？

商品コードS002の売上合計金額を0と考えるなら、(50+0+250)÷3=100と計算されるのが正しいと思います。そう考えると、このデータの場合、NULLを含むカラムに対して安易にAVG関数を使うのは悪手のように思います。

100と計算されるにはどうすればいいでしょうか？

商品別売上実績のレコードが必ず存在するようにする

商品別売上実績に売上のない商品のレコードが存在しないことが問題ですので、すべての商品のレコードが存在する（売上実績がなければ売上合計金額は0になる）ようにするのが解決方法となります。

必ず存在するようにデータが登録されていれば、LEFT OUTER JOINではなくINNER JOINで結合できるようになりますが、テスト環境のようなデータが十分にメンテナンスされていない環境では正しく取得できない場合がある点に気をつける必要があります。

NVL関数を使用してNULLを0とみなす

今回の問題文のテーブルおよびデータで運用するなら、NVL関数を使ってNULLを0として計算対象に含める方法がよいでしょう。MySQLにはNVL関数がないため、IFNULL関数を使います。

SELECT
    AVG(IFNULL(T2.total_sales_amount, 0)) AS `売上平均金額`
FROM
    products T1
    LEFT OUTER JOIN sales_by_product T2
    ON
        T1.product_code = T2.product_code
WHERE
    T1.product_rank = 'A'
GROUP BY
    T1.product_rank
;

=> 100.0000

AVG関数に渡す前にNVL関数（IFNULL関数）でNULLを0に変換することで売上合計金額÷ランク別商品数で平均を求めることができるようになります。

まとめ

NULLを含むAVG関数およびCOUNT関数のふるまいについて確認しました。

「平均」と一口に言ってもNULLを集計に含めるのかどうかによって計算結果が変わります。

• 「平均」の計算方法についての正しい仕様を確認する
• 結合先が必ず存在するのか存在しないことがあるのかを確認する
• 集計にNULLが含まれることがあるのであれば、NULLを含む場合の集計が正しいことを確認する

といった基本的な点をしっかりと確認していくことが重要です。