やりたい事

構造の同じ複数のテーブルから、ある条件にマッチするレコードを集計して合算した結果を表示させたい。

集計にはユーザーIDをキーとして集計し、1ユーザーが複数レコードを所持している場合は、まとめて1つと計測する。

ただし、1ユーザーがテーブルを跨いでレコードを持つことはなく、1ユーザーが複数持つレコードは全て同一テーブルに存在するものとする。

 

テーブル構造のイメージ

user_ask_id にユーザーに対する質問のIDがあり、
idが1と2に対して答えているユーザーの総数が知りたい。という内容。

 

GROUP BY と COUNT

よくやる間違い

普通であればcount(*)でレコード総数が取れるが、GROUP BY句を使った場合は集約結果をカウントしてしまう

今回であれば上記結果の行数である13448 という結果が欲しいわけだ。

 

サブクエリを使用する方法

やり方はいくつかあるが、今回はサブクエリを仕様して集計する。

ただし、上記SQLはサブクエリではインデックスが効いているが、その後のcount(*)はフルスキャンなので件数によっては負荷が大きくなるので注意。

その他、一次変数を使う方法やdistinctを使用する方法もあるが、今回は割愛する。

今回は管理側が調査目的で実行するSQLなのでそこまでパフォーマンス気にしてませんが、実装に入れ込んで、ページロード毎に呼ばれるような場合はもうちょっとちゃんと設計(というかテーブル設計から見直すべきではあるが)して下さい。

 

複数テーブルの結合

さて、上記で1テーブル辺りの集計数は取れたのだが、今回は同じ構造のテーブルがさらに9つある想定。

1つ1つクエリを発行して結果を手動で足すような事はナンセンスなので、
ここはちゃんとSQLを作って10あるテーブルの合計数を集計したい。

 

UNIONを使った結合

UNION句を使用する事で、複数のテーブルの実行結果を結合する事が出来る。

UNION 集合演算子とは複数の SELECT 文を1つに組み合わせる演算子である。 複数の問い合わせを1つに結合することから、それぞれの問い合わせの抽出項目のリストは同数、かつ、同じグループの データ型 でなければ結合することができない。
http://www.shift-the-oracle.com/sql/union-operator.html

単純にUNION句で結果を結合した場合

まぁ、そりゃこうなるよね。という感じになる。

色々やり方はあるだろうが、以下のような感じで合算する。

UNION句で結果を結合したのち、サブクエリ化して結果をCOUNTする。

これで複数テーブルの結果の合算を結果として得る事ができる。

 

COUNTした数字を持つテーブルをサブクエリかして、SUM句で合算する

 

UNION句で結果を結合してCOUNTする方法を用いて、10あるテーブルの結果の合計を求める。

これで求めたい結果を得ることが出来た。

※なお、処理にかかった時間が4秒少しだが、これは一度実行してクエリキャッシュがあるからで、実際のクエリ実行時は45秒程かかってます。

 

サブクエリを使用して結果を結合

また、同じ結果を求める別のアプローチとして、サブクエリでカウントしたそれぞれの結果を足す方法もある。

こちらの方が、クエリの実行結果は早い。

また、今回のケースだけで考えるならば記述もこちらのほうがスッキリするかなぁ、という印象。

 

パフォーマンス計測

ガバガバSQLなので、パフォーマンス・チューニング以前に設計から見なおせ的な意見もあると思いますが、一応。

EXPLAIN句を使うことで、クエリの実行結果の詳細を見ることが出来ます。

 

 

 

おまけ(もし1ユーザーが複数テーブルに跨いでレコードを保持していた場合)

さて、ここまで来て、しっかり内容を読んで下さってる方がいた場合、上記のSQLの大きな欠点に気づいていることだろうと思う。

ただし、1ユーザーがテーブルを跨いでレコードを持つことはなく、1ユーザーが複数持つレコードは全て同一テーブルに存在するものとする。

と最初に条件に書いた通り、上記の集計方法では、user_idが複数テーブルにまたがってレコードを保持していた場合、それらは別々でカウントされてしまう
という欠点を持っている(ガバガバにも程がある!)

なので、もし複数テーブルにまたがってuser_idのレコードが散在している場合は以下の様な方法を取る事で正確な数を集計する事が出来る。

 

UNIONで結果を結合してからグルーピングする。

 

 

久しぶりにMySQLさわって色々とSQL文を書いて楽しかった!!

SQLは奥が深いです……

 

 


http://astone.jeez.jp/wp-content/uploads/2015/11/aud0002-009.jpghttp://astone.jeez.jp/wp-content/uploads/2015/11/aud0002-009-150x150.jpgmilksoapServer
やりたい事 構造の同じ複数のテーブルから、ある条件にマッチするレコードを集計して合算した結果を表示させたい。 集計にはユーザーIDをキーとして集計し、1ユーザーが複数レコードを所持している場合は、まとめて1つと計測する。 ただし、1ユーザーがテーブルを跨いでレコードを持つことはなく、1ユーザーが複数持つレコードは全て同一テーブルに存在するものとする。   テーブル構造のイメージ user_ask_id にユーザーに対する質問のIDがあり、 idが1と2に対して答えているユーザーの総数が知りたい。という内容。   GROUP BY と COUNT よくやる間違い 普通であればcount(*)でレコード総数が取れるが、GROUP BY句を使った場合は集約結果をカウントしてしまう 今回であれば上記結果の行数である13448 という結果が欲しいわけだ。   サブクエリを使用する方法 やり方はいくつかあるが、今回はサブクエリを仕様して集計する。 ただし、上記SQLはサブクエリではインデックスが効いているが、その後のcount(*)はフルスキャンなので件数によっては負荷が大きくなるので注意。 その他、一次変数を使う方法やdistinctを使用する方法もあるが、今回は割愛する。 今回は管理側が調査目的で実行するSQLなのでそこまでパフォーマンス気にしてませんが、実装に入れ込んで、ページロード毎に呼ばれるような場合はもうちょっとちゃんと設計(というかテーブル設計から見直すべきではあるが)して下さい。   複数テーブルの結合 さて、上記で1テーブル辺りの集計数は取れたのだが、今回は同じ構造のテーブルがさらに9つある想定。 1つ1つクエリを発行して結果を手動で足すような事はナンセンスなので、 ここはちゃんとSQLを作って10あるテーブルの合計数を集計したい。   UNIONを使った結合 UNION句を使用する事で、複数のテーブルの実行結果を結合する事が出来る。 UNION 集合演算子とは複数の SELECT 文を1つに組み合わせる演算子である。 複数の問い合わせを1つに結合することから、それぞれの問い合わせの抽出項目のリストは同数、かつ、同じグループの データ型 でなければ結合することができない。 http://www.shift-the-oracle.com/sql/union-operator.html 単純にUNION句で結果を結合した場合 まぁ、そりゃこうなるよね。という感じになる。 色々やり方はあるだろうが、以下のような感じで合算する。 UNION句で結果を結合したのち、サブクエリ化して結果をCOUNTする。 これで複数テーブルの結果の合算を結果として得る事ができる。   COUNTした数字を持つテーブルをサブクエリかして、SUM句で合算する   UNION句で結果を結合してCOUNTする方法を用いて、10あるテーブルの結果の合計を求める。 これで求めたい結果を得ることが出来た。 ※なお、処理にかかった時間が4秒少しだが、これは一度実行してクエリキャッシュがあるからで、実際のクエリ実行時は45秒程かかってます。   サブクエリを使用して結果を結合 また、同じ結果を求める別のアプローチとして、サブクエリでカウントしたそれぞれの結果を足す方法もある。 こちらの方が、クエリの実行結果は早い。 また、今回のケースだけで考えるならば記述もこちらのほうがスッキリするかなぁ、という印象。   パフォーマンス計測 ガバガバSQLなので、パフォーマンス・チューニング以前に設計から見なおせ的な意見もあると思いますが、一応。 EXPLAIN句を使うことで、クエリの実行結果の詳細を見ることが出来ます。       おまけ(もし1ユーザーが複数テーブルに跨いでレコードを保持していた場合) さて、ここまで来て、しっかり内容を読んで下さってる方がいた場合、上記のSQLの大きな欠点に気づいていることだろうと思う。 ただし、1ユーザーがテーブルを跨いでレコードを持つことはなく、1ユーザーが複数持つレコードは全て同一テーブルに存在するものとする。 と最初に条件に書いた通り、上記の集計方法では、user_idが複数テーブルにまたがってレコードを保持していた場合、それらは別々でカウントされてしまう、 という欠点を持っている(ガバガバにも程がある!) なので、もし複数テーブルにまたがってuser_idのレコードが散在している場合は以下の様な方法を取る事で正確な数を集計する事が出来る。   UNIONで結果を結合してからグルーピングする。     久しぶりにMySQLさわって色々とSQL文を書いて楽しかった!! SQLは奥が深いです……