Go to Qiita Advent Calendar 2024 Top
LoginSignup
7
8

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

@aconit96

SQLアンチパターン勉強会　第十四回：アンビギュアスグループ

Last updated at Posted at 2018-11-25

はじめに

本エントリーは某社内で実施するSQLアンチパターン勉強会向けの資料となります。

本エントリーで書籍「SQL アンチパターン」をベースに学習を進めます。書籍上でのサンプルコードはMySQLですが、本エントリーでのサンプルコードはT-SQLに置き換えて解説します。

アンビギュアスグループ　とは

アンビギュアスグループは、グループ内で最大値（または最小値）を持つ行を取得する際、その行のグループ化されていない列を取得する際に発生するアンチパターンです。
アンビギュアスグループが発生した場合、発行対象のクエリはエラーになるか、信頼性の低い値を返すことになります。
SQL Serverでは、アンビギュアスグループが発生した場合、クエリエラーとなり実行できません。

-- 例）こちらは実行可能
SELECT  
 product_id,
 Max(date_reported) AS latest  
FROM 
 Bugs
INNER JOIN 
 BugsProducts 
ON
 Bugs.bug_id = BugsProducts.bug_id
GROUP BY 
 product_id

-- 例）bug_idを追加で取得しようとすると、クエリエラーとなる
SELECT  
 product_id,
 Max(date_reported) AS latest,
 Bugs.bug_id
FROM 
 Bugs
INNER JOIN 
 BugsProducts 
ON
 Bugs.bug_id = BugsProducts.bug_id
GROUP BY 
 product_id

-- メッセージ 8120、レベル 16、状態 1、行 4
--列 'bug_id' は選択リスト内では無効です。この列は集計関数または
--GROUP BY 句に含まれていません。

単一値の原則(Single-Value Rule)

単一値の原則とは、「SELECT句で列挙した列について、行グループごとに単一の値でなければならない」という規則です。GROUP BY句で指定した列および集約関数の結果については、単一値の原則が守られることが保証されます。しかし、GROUP BY句で指定していない列についてはこの限りではなく、その列の値がすべて同じ値であることを保証できません。

SELECT  
 product_id,
 Max(date_reported) AS latest,
 Bugs.bug_id
FROM 
 Bugs
INNER JOIN 
 BugsProducts 
ON
 Bugs.bug_id = BugsProducts.bug_id
GROUP BY 
 product_id

この例では、追加したbug_id列が問題となります。product_idとbug_idは一対多の関係にあります。ですので、product_idをGROUP BY句で集約した際、対応する複数のbug_idの全ての値を表すことができません。以下に、単一値の原則に反する例を記載します。

date_reported列に最大値が二つ存在し、かつこの2行のbug_idが異なる

2つある最大値のどちらに紐づくbug_idが取得されるか不明となります。

SELECT句の中で、MAXとMINの両方を指定する

-- 例2：SELECT句の中で、MAXとMINの両方を指定する
SELECT  
 product_id,
 Max(date_reported) AS latest,
 Min(date_reported) AS earliest,
 Bugs.bug_id
FROM 
 Bugs
INNER JOIN 
 BugsProducts 
ON
 Bugs.bug_id = BugsProducts.bug_id
GROUP BY 
 product_id

大抵の場合、最大値と最小値は異なる行に存在します。この例の場合、最大値と最小値のどちらの値に紐づくbug_idが取得されるか不明となります。

集約関数で返された値と一致する行がテーブルにない

-- 例3：集約関数で返された値と一致する行がテーブルにない
SELECT  
 product_id,
 --日付を数値に変換して計算を試みる。どのみちSQLServerでは実行不能。
 AVG(DATEPART(dayofyear,date_reported)) AS total_project_estimate,
 Bugs.bug_id
FROM 
 Bugs
INNER JOIN 
 BugsProducts 
ON
 Bugs.bug_id = BugsProducts.bug_id
GROUP BY 
 product_id

total_project_estimateはテーブルには存在しない値なので、対応するbug_idがありません。

アンチパターンの見つけ方

SQLiteとMySQLを除いた殆どのデータベース製品では、このアンチパターンが発生した場合クエリエラーとなります。SQLiteとMySQLを利用する場合はクエリエラーとならず、クエリは予期せぬ結果を返すこととなります。

アンチパターンの利用と関数従属性

以下のようなクエリは単一値の原則を満たしていませんが、MySQLまたはSQLiteを利用している場合はさほど問題とならない場合があります。

SELECT  
 b.reported_by,
 a.account_name
FROM 
 Bugs b
INNER JOIN 
 Accounts a 
ON
 b.reported_by = a.account_id
GROUP BY 
 b.reported_by

上記のクエリの場合、account_name列はGROUP BY句でも集約関数でも指定されていないため、単一値の原則に違反しています。しかしながら、以下の理由によりaccount_nameの値は一意に定まるため、上記のクエリは期待通りの結果を返します。

  • account_nameはAccountsテーブルでaccount_idと一対一対応している
  • account_idはreported_byと一対一対応している

このように、どちらか片方の値が定まると対応するもう片方の値も一意に定まる関係を、「関数従属性」と呼びます。

アンチパターンの解決策

アンビギュアスグループの解決策は、「曖昧でない列を利用すること」となります。以下に解決例を記載します。

曖昧な列を排除する

SELECT  
 product_id,
 Max(date_reported) AS latest  
FROM 
 Bugs
INNER JOIN 
 BugsProducts 
ON
 Bugs.bug_id = BugsProducts.bug_id
GROUP BY 
 product_id

bug_idが不要な場合、この対策のみで十分です。

相関サブクエリの利用

SELECT  
 bp1.product_id,
 b1.date_reported AS latest,
 b1.bug_id
FROM 
 Bugs b1
INNER JOIN 
 BugsProducts bp1
ON
 b1.bug_id = bp1.bug_id
WHERE NOT EXISTS
(
    SELECT
     *
    FROM
     Bugs b2
    INNER JOIN 
     BugsProducts bp2
    ON
     b2.bug_id = bp2.bug_id
    WHERE
         bp1.product_id = bp2.product_id
     --date_reportedが最新でないデータを抽出し、NOT EXISTで除外
     AND b1.date_reported < b2.date_reported 
)

相関サブクエリを利用することで、最新の日付を持つバグの取得を行うことができます。但し、各行の選択ごとにサブクエリを毎回実行することになるため、パフォーマンスが低下する可能性があります。

導出テーブルの利用

SELECT  
 m.product_id,
 m.latest,
 b1.bug_id
FROM 
 Bugs b1
INNER JOIN 
 BugsProducts bp1 
ON
 b1.bug_id = bp1.bug_id
INNER JOIN
 (
    --導出テーブル
    SELECT
      bp2.product_id,
      MAX(b2.date_reported) AS latest
    FROM 
     Bugs b2
    INNER JOIN 
     BugsProducts bp2 
    ON
     b2.bug_id = bp2.bug_id
    GROUP BY
     bp2.product_id
 ) AS m
ON
    bp1.product_id = m.product_id
AND b1.date_reported = m.latest

導出テーブルを利用してproduct_idごとのdate_reportedの最大値を取得し、これを取得用クエリにJOINさせることで、date_reportedの最大値を持つbug_idを取得しています。
この方法を利用すると、date_reportedの最大値を持つbug_idが複数存在した場合、以下のように複数行が出力されます。

product_id latest bug_id_list
1 2010-06-01 2248
2 2010-02-16 3456
2 2010-02-16 5150
3 2010-01-01 5678

こちらの方法についても、相関サブクエリほどではありませんがパフォーマンスが低下する可能性があります。

OUTER JOIN の利用

SELECT  
 bp1.product_id,
 b1.date_reported AS latest,
 b1.bug_id
FROM 
 Bugs b1
INNER JOIN 
 BugsProducts bp1
ON
 b1.bug_id = bp1.bug_id
LEFT OUTER JOIN
(
     Bugs AS b2
    INNER JOIN
     BugsProducts AS bp2
    ON
     b2.bug_id = bp2.bug_id
) 
ON
(
    bp1.product_id = bp2.product_id
    AND
    (
        
        -- date_reportedが最新でない行を選択
        b1.date_reported < b2.date_reported
        OR
		(
			-- date_reportedが同じ場合、bug_idが最新のものを利用
				b1.date_reported = b2.date_reported
            AND b1.bug_id < b2.bug_id
		)
    )
)
WHERE
 --date_reportedの最新でない行を取得したデータと結合し、結合しなかった行（= date_reportedが最新の行）のみ取得する
 b2.bug_id IS NULL

この方法では、導出テーブルの利用や相関サブクエリよりも良いパフォーマンスが期待できます。しかし、可読性の面では劣るため、保守の面で課題を抱える可能性があります。

（私見）相関サブクエリ・導出テーブル・OUTER JOINの利用のどれを採用するかについては、性能要件やクエリの実行計画、保守人員などを考慮したうえで、現場現場にあった方法を採用することが良いと思われます。

取得対象列への集約関数の適用

SELECT  
 product_id,
 Max(date_reported) AS latest,  
 Max(Bugs.bug_id)
FROM 
 Bugs
INNER JOIN 
 BugsProducts 
ON
 Bugs.bug_id = BugsProducts.bug_id
GROUP BY 
 product_id

date_reportedの最大値とbug_idの最大値が必ず一致することが確約されている場合のみ、上記のクエリにより目的の値を取得できます。

GROUP_CONCATの利用（MySQL, SQLiteのみ）

SELECT  
 product_id,
 Max(date_reported) AS latest,
 GROUP_CONCAT(Bugs.bug_id) AS bug_id_list  
FROM 
 Bugs
INNER JOIN 
 BugsProducts 
ON
 Bugs.bug_id = BugsProducts.bug_id
GROUP BY 
 product_id

MySQLとSQLiteの場合、各行グループの全ての値を1つに連結する関数GROUP_CONCATが用意されています。
こちらの関数を利用した場合、以下の様に結果を取得することになります。

product_id latest bug_id_list
1 2010-06-01 1234,2248
2 2010-02-16 3456,4077,5150
3 2010-01-01 5678,8063

但し、この解決法には以下のような欠点が存在します。

  • 最大値・最小値の取得の場合、どの値が対応する値であるか判別できない
  • SQL標準に準拠していないので、データベース製品の変更時に改修が必要となる

まとめ

  • 単一値の原則に従おう
7
8
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
7
8

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?