これを、下記の横型のデータの持ち方のテーブルのように変換して検索結果を得たいというのが要望だった。, 要望は満たされているのだが、(個人的に)サブクエリが美しくないので、CASE文でもうちょっとスマートに書いてみる。, 初期の要望については前述の検索クエリで満たされたわけだが、ピボット検索についてはパフォーマンスが気になったので、もうちょっと突っ込んで色々とやってみた。, とりあえず、縦型データ構造のテーブルに性能検証用のデータを大量に登録して、クエリの性能を見てみる。Fakerを使って1万ユーザ分のダミーデータを生成して、ピボット検索を試してみた。, こちらは1秒ちょっと。圧倒的な差がついた。まぁクエリの内部処理順を考えるとやる前から予想できていた結果なんだが……巷で云われる「MySQLの(洗練されていない)サブクエリは遅い」という定説が改めて証明された次第だw  ──で、肝心なクエリの結果は下記のようになる。, アンピボットのやり方としては、前述のユニオンでやる方式ともう一つCROSS JOIN方式があるので、そちらも紹介しておこう。, 結局CROSS JOIN時にユニオンで結合してるじゃん……という突っ込みはあるのだが、まぁ、こんな方式もあるよ……と云う一例だ。ちなみに、MySQLでのCROSS JOIN(交差結合)はJOINやINNER JOINと同等で、SQLite等他のSQLのCROSS JOINとは厳密な挙動が異なる。なので、CROSS JOINの部分は単にJOINとしても構わない。ただ、CROSS JOIN句を使うと暗黙的にON句が不要であることを明文化できるので、アンピボットの時はCROSS JOINが使われるようだ。, 最終的にアンピボットの時はどちらを使うべきなのか? それは次項の性能検証後に判断してみよう。, さて、アンピボット検索についてもどの程度の性能差があるのか、前項での二つのクエリを比較してみよう。早速、Fakerで検証対象のテーブルに約1万件のデータを投入して比較してみる。, 私の予想を裏切って、CROSS JOIN方式の方がパフォーマンスが良かったが、誤差の範囲と云えなくもない。もう少し詳しく、 EXPLAIN FORMAT=json でそれぞれのクエリコストを比較してみた。, ユニオンで結合するテーブル毎にクエリコストが2096.20が発生していて、合計で8384.8が総クエリコストのようだ。結合前の同じテーブルを4回もフルスキャンしているので、パッと見で効率が悪そうな感じを受ける。, 一方、CROSS JOIN型はテーブルを結合した後に一回だけ検索するので、総クエリコストが8104.83とユニオン型より若干少なくなっている。 SQLでの文字列結合に関しての備忘録です。 RDBMSによって使用できる関数が異なりますが、今回はSQL Server向けの内容をまとめていきます。 文字列の結合について. 複数の select ステートメントの結果を結合する際には、結果表に何を含めるかを選択できます。結果表にすべての行を含めるか、両方の select ステートメントの結果表にある行のみを含めるか、または最初の select ステートメントの結果表に固有の行のみを含めるかを選択できます。 先ごろWordPressのプラグイン関連の問い合わせで、データベースのとあるテーブルについてデータの持ち方の縦横を変換したいという要望をもらい、テーブル構造の縦横変換をやってみた。 Why not register and get more from Qiita? しかしこの「JOIN」。結合させる方式が複数存在します。 そんなとき役立つのがJOINという命令です。これを使いこなせれば、できることが飛躍的に増えるでしょう! ボスニア語 / Bosanski  クエリコストで比較してもCROSS JOIN型の方がパフォーマンスが良いようだ。適切なインデックスを貼ってやれば、さらなるパフォーマンスの向上が見込めるだろう。, 結論として、アンピボット検索を行うならばクエリはCROSS JOIN型の方が良いということがわかった。, 縦持ちから横持ちへのピボット検索については、問い合わせをもらったこともあり、結構利用するシーンがありそうだと思った。もしバックオフィス用の集計やデータ管理の用途として限定的に使うのであれば、VIEWを使ったピボット検索も非常に有効だと思われる。, 一方で、アンピボット検索については使いどころがいまいちピンと来ないが、ワンクエリのSQLだけでやれることだけはわかった。, フリーランスのWEBデベロッパー。元々コンシューマゲーム機のチェッカーで、ゲーム好き。WEB業界はWEBデザイナーとして出発し、いまではクラウド系インフラ構築から、アプリケーション開発、サービスコンサルティング等々、およそWEB関連のよろず屋をやってます。最近のお仕事はLaravel+React、WordPress+Vue.jsのハイブリッド。Vue.jsが楽しすぎる!もうReactやりたくないw. 韓国語 / 한국어 カザフ語 / Қазақша オランダ語 / Nederlands フランス語 / Français ポルトガル語 / ポルトガル / Português/Portugal スペイン語 / Español セルビア語 / srpski Help us understand the problem. MySQL/MariaDBで検索クエリ(SELECT)結果に連番をつける方法はかんたんに見つかりますが、細かい解説がすぐには見つかりません。, 細かいことは忘れても(わからなくても)、SQLをさわったことがあればなんとくなくやっていることは分かります。, のかたち。SQLでは変数を使うことができます。ただし、DBMSごとに定義の仕方はちがいますが。, SELECT句にインクリメントを入れることで、検索クエリの結果の1行に対して『num = num +1』が実行され連番の列(column)が作られます。, と思う人もいるでしょう。これは変数値の初期化です。たとえば、次のSQLを1回以上実行してみてください。, 1回目の連番の続きになってしまいました。変数の値は、1回のSQL実行だけで使うものではありません。そしてひとつのSQL文でリセットされることもありません。, OracleやPostgresについてはよく分かりません。ここ数年使ってないので。それぞれ調べて下さい。, ググって調べると、変数名は『num』を使っているし、『@numでできます。』と言っているものもあります。, でもこれは『変数名』なので、とくに決まりはありません。『no』でもいいし『counter』でもいいです。, なんとなくですが、変数名ではなくて『@num』というSQLのルールだと思っているフシもあるような気がします。, この方法はWebフレームワークなどで使えるかどうか分かりません。フレームワークでは、SQL文作成のクラス・メソッドが用意されているからです。, じゃあ、どうしてFROM句に初期化処理を入れるのか? もう一回SQL文を見てみましょう。, インクリメント処理は3、初期化処理は2の1回で、初期化処理の条件を満たしています。, MySQL, MariaDBでは、テーブルのカラムを使わないSELECT句はFROM句の省略ができます。, 最終的に使ってないだけで、じつは不要な列(column)ができています。クロス結合は複数テーブルの結果の全パターン結合だから。, ひとつ言い忘れてました。この連番のつけ方はボクがオリジナルで考えたわけではなく、ググるとすぐに出てくる情報です。, https://qiita.com/mpyw/items/c9925e915a362b65b024, https://blog.toshimaru.net/mysql-virtual-table/, このサイトはスパムを低減するために Akismet を使っています。コメントデータの処理方法の詳細はこちらをご覧ください。, システムエンジニア&プログラマ。キャリア16年。SIerに12年、フリーと会社員を交互に繰り返すこと4年。, まったく何もしないのはダメなので、今まで得てきたものをWebサイトで発信しながら少しだけ活動中。, コメント等いただいておりますが、回答・回答は遅くなりがちです。(1週間以内で早い方。), 初対面でいきなりケンカ腰で来られると、『いきなり刺されるかも?』と同じくらいに感じるので、100%レスポンスがあるとはかぎりません。. ドイツ語 / Deutsch スロベニア語 / Slovenščina DISQUS terms of service. みなさんこんにちは!フリーランスプログラマーのsatoです。 今回はunionについて見ていきましょう。unionは二つ以上のselectの結果を、統合して表示してくれる仕組みです。これを使えれば、また一つ高度な結果表示を行えますのでぜひ覚えてしまいましょう。 タイ語 / ภาษาไทย ルーマニア語 / Română アラビア語 / عربية カタロニア語 / Català What is going on with this article? ロシア語 / Русский クロアチア語 / Hrvatski Please note that DISQUS operates this forum. ヘブライ語 / עברית  ちなみに縦型にデータを持っているテーブル構造を横型の持ち方へ変換することを「ピボット(Pivot)」、その逆を「Unpivot(アンピボット)」と云う。MS OfficeのExcelでクロス集計を行う時などに使う「ピボットテーブル」を利用した事がある人には理解しやすいと思う。, 実例として、下記のような縦型にデータを持っているテーブル(テーブル名:vtable)がある。, WEBフロントエンドでは、フォームのID:21に属する入力フォームが3つあり、ファーストネーム用のフィールド(a101)とファミリーネーム用のフィールド(a102)、性別用のフィールド(a103)からの値がこのテーブルに格納されるという建付けだ。入力者のユーザID的な値はentry_idである。  また、下記のように条件を付与して特定データを抽出する時なども、わかりやすいクエリで処理ができるようになる。, ただし、VIEW経由でのピボット検索は、検索のたびに元テーブルの全データをVIEWへ置換するというオーバーヘッドが発生するので、素のSELECTよりその分だけ時間がかかってしまうというデメリットがある。データ数によってはVIEWを使わないピボット検索じゃないと高パフォーマンスが担保できなくなるリスクがあるので注意が必要だ。, もう一つ気になるのが、縦から横への変換であるピボット検索の逆、横から縦へのアンピボット検索についてだ。ピボットについてはNoSQL等のキー・バリュー型のデータを論理構造を持つテーブルへ変換するというケーススタディなどが思い浮かぶのだが、逆のアンピボットの変換はどんなケースで必要になるのだろうか。考えられるとすれば、論理テーブルのデータを高速検索するためのシーケンス系テーブルを作成したり……とかか? まぁ必要性があまり思いつかないが、アンピボット検索もやってみよう。, まず、実例を示そう。アンピボット検索の対象となる、データを横持ちで持っているリレーショナルデータベースでは一般的な論理型テーブル(テーブル名: htable)は下記の通りだ。ダミーデータはまたFakerで作成した。, 上記のクエリの注意点は二つある。まず結果出力時に疑似的に生成するフィールド名に「key」を使う場合、MySQLの予約語と重複するのでバッククオートで括ってやる必要があるのだ。そして、ユニオンした結果に対してのORDER BYによるソートはインデックスが使えないため、パフォーマンスに悪影響を及ぼすということだ。今回のように1万件程度のデータ数であればさほど気にする必要もないが、データ数によってはクエリを見直す必要が出てくるかもしれない。 この結合は、以下のようなプロセスを経て行われます。(図7-10.)まず、purchase_historyとresourceの結合を行い、その結果得られた検索結果に、さらにclass_nameを結合させます。 図7-10.複数のテーブルの結合 …  まぁ、こういうひどいクエリを作ってしまった時は、EXPLAINでどんだけひどいか見てみると愉(たの)しいし、おまけに勉強にもなる。, ほら、ありました「DEPENDENT SUBQUERY」。基本、サブクエリ使っていて遅い時は十中八九この相関サブクエリが問題である。詳しくは、, そんなわけで、ピボット検索のクエリを考えるときは、できる限りサブクエリは使わないように注意しよう。, さて、ここまででピボット検索についてはほぼOKなのだが、毎回ピボット検索用のSELECTクエリを発行するのは面倒だ。クエリの記述量も多いので、ストアドプロシージャにピボット検索を登録しておくというのも一案だが、ここはお手軽にVIEWを作ってしまうのが良いかと思った。, やり方は簡単で、前出のエレガントなピボット検索のクエリを元にCREATE VIEWするだけだ。こうしてVIEWを作っておけば、ピボット検索する時は、, ──と云うように、SELECTクエリをさらに簡略化できる。 スロバキア語 / Slovenčina イタリア語 / Italiano 中国語 (簡体字) / 简体中文 トルコ語 / Türkçe you can read useful information later efficiently. 複数の select ステートメントの結果を結合する際には、結果表に何を含めるかを選択できます。結果表にすべての行を含めるか、両方の select ステートメントの結果表にある行のみを含めるか、または最初の select ステートメントの結果表に固有の行のみを含めるかを選択できます。 日本語 / 日本語 DISQUS’ privacy policy. When you sign in to comment, IBM will provide your email, first name and last name to DISQUS. By commenting, you are accepting the みなさんこんにちは!フリーランスプログラマーのsatoです。 今回はunionについて見ていきましょう。unionは二つ以上のselectの結果を、統合して表示してくれる仕組みです。これを使えれば、また一つ高度な結果表示を行えますのでぜひ覚えてしまいましょう。 サーバ上のファイルに出力します。 構文. ベトナム語 / Tiếng Việt. 以下の構文で抽出結果をファイルに出力することができます。 SQL Serverにおける文字列結合の方法としては、大きく下記の2つに分けられます。  ちなみに、ORDER BY句で使えるFIELD関数はカラム値の並び順を任意に指定できるものだ。上記の例のように文字列候補に対しても表示順を指定できるので、覚えておくと何気に重宝する。 That information, along with your comments, will be governed by フィンランド語 / Suomi みなさんこんにちは!フリーランスプログラマーのsatoです。 複数テーブルの結合を行いたい! ポルトガル語 / ブラジル/Brazil / Português/Brasil By following users and tags, you can catch up information on technical fields that you are interested in as a whole, By "stocking" the articles you like, you can search right away. union 句を使用することで、別々の select 文によって取得したデータを結合して 1 つのデータとして取得することができます。ここでは union 句を使っては別々に取得したデータを結合して取得する方法について解説します。 ブルガリア語 / Български create文により作成した2表の結合と、同結果が得られるsql文 H14午前問30 社員表と部門表からcount(*)を用いたSQLにより得られた結果 ポーランド語 / polski マケドニア語 / македонски 検索 mysql> SELECT id_c, id_p, fullname FROM customer JOIN purchase; ERROR 1052 (23000): Column 'id_c' in field list is ambiguous mysql> SELECT purchase.id_c, id_p, fullname FROM customer JOIN purchase; 2つ以上のテーブルの結合 中国語 (繁体字) / 繁體中文 ギリシャ語 / Ελληνικά (dualテーブルはMySQL, MariaDBに用意されている仮想テーブル) FROM句はどうゆうテーブルを作るのか? SQLをちょっと変更して、FROM句のクロス結合の結果を見てみます。 (最終的なクエリ(SELECT句)で使うテーブルの結果のこと。 SELECT * FROM vtable_view; (結果省略) 10000 rows in set (0.97 sec) ... ちなみに、MySQLでのCROSS JOIN(交差結合)はJOINやINNER JOINと同等で、SQLite等他のSQLのCROSS JOINとは厳密な挙動が異なる。なので、CROSS JOINの部分は単にJOINとしても構わない。 MySQLでのファイル出力方法について。 バージョン5.7.11でmysqlクライアントを使用して確認しています。 SELECT INTO OUTFILE を使用する. デンマーク語 / Dansk 英語 / English チェコ語 / Čeština create文により作成した2表の結合と、同結果が得られるsql文 H14午前問30 社員表と部門表からcount(*)を用いたSQLにより得られた結果 IBM Knowledge Center で検索する, IBM Knowledge Center は JavaScript を使用します。 スクリプトが使用不可になっているか、ご使用のブラウザーではサポートされていません。 JavaScript を使用可能にし、再試行してください。. スウェーデン語 / Svenska 3.1 MySQLでは「+」での結合は不可能 「+」はSQL Serverで使用される書式ですが、現時点でMySQLで使う方法はないようです。移植や流用の際には、忘れずにCONCATに置き換えてご利用下さい。 ハンガリー語 / Magyar ノルウェー語 / Norsk