pivot)ピボットテーブルbyエクセルじゃないよSQLだよ ※ピポットかな? – ざったなぶろぐ
より分かりやすく!格安スマホのギアベストクーポン紹介ページリニューアル!!(毎日更新です!!)
XBRL文書の解析に成功しました!!

データ収集に成功しました!これはうれしい!!

ゆうほうを見る!!

pivot)ピボットテーブルbyエクセルじゃないよSQLだよ ※ピポットかな?

この記事は約 9 分で読めます。

 

PIVOT

SQLで書くピボットテーブル

Excelで便利な機能であるピポットテーブルですが、SQLでも書くことができます。

出来るだけわかりやすく説明してみます。

ただ、SQLで書くピボットテーブルですが、相当

必要性

が発生しない限りは使わないと思います。

理由は、Excelでやった方が圧倒的に早いからです・・・・

 

テーブル作成

まずはテストデータを投入するためのテーブルを作成します。

以下のクエリでテスト用のテーブルを作成してください。

 

流し込むだけで作ってくれるはずです。

出来上がったら 上位1000行 を表示して確認してみましょう。

以下の通り、何も無い状態が確認できますね。

それでは次にテストデータを投入しますが、ここで書くとかなり長くなりますので、

このページの最下部にコピペ用のデータを準備しています。

お試しする人は下まで行ってコピーしてきてください。

 

挿入が完了したとして・・・・

 

投入結果を確認してみます。

改めて、 上位1000行 を表示して確認してみましょう。

以下の通りある程度データが入っている事が確認できます。

 

 

そもそも、ピボットテーブルで何ができるのか?

この基本が頭にないと、思うように書くことができないと思います。

行列の入れ替えだとかクロス集計だとかいろいろ言えますが、

仕上がった結果は二次元の表が出来上がる

と、思ってください。

 

 

 

うん。

自分で言っておきながら当たり前ですね。

これは例えが悪いですね・・・・・

 

集計された二次元の表が出来上がる!!

と、思ってください。

こういえばしっくりきましたね。

 

そうです。

ピボットテーブルの出来上がりは集計です。

そのため、sum,count,avg等の集計関数を使うと言う事を頭に入れておいてください。

Excelのピボットテーブルではかなり 当たり前 の事ですが、 SQLのピボットテーブルもこの集計が基本となります。

 

まずは書いてみる。

単純にselect * from を書いて見ます。

この基本に対して、以下の赤点線で括った部分を付け足すような感じです。

この、

に、記載する内容ですが、以下のような構文となります。

+++++

PIVOT
(
集計関数(集計したい対象) FOR 行にしたい元の列 IN (元の列の中にある項目たち)
) as 別名

+++++

それぞれを説明してきます。

集計関数

イメージしやすいようにあえてExcelで表現するとこれ等です。

SQLで言えば、MAXとかMINとかAVGとかですね。

 

集計したい対象

今回のテストデータだとこれです。

 

他にも数量も可能ですね。

大事なのは、名前 ではないと言う事です。

以前に集計関数の説明をしたときと同じこと言いますが、

鱈と蛸のMAXは何ですか?

という不毛な質問はしないですよね。

集計するならば、単価などの数値データが基本となります。

※countで個数を数えるならばその限りではありませんが、その場合でも 単価 の行で十分です。

 

 

行にしたい元の列

ちょっと説明しにくいですが、今回のテストデータだとたまたま説明しやすいです。

今回のテストデータは

月ごとに単価の平均値(もしくは最大値や合計)を求める!

といったお題になると思います。もしくは

魚の名前ごとに平均値(もしくは最大値や合計)を求める!

なので、名前になります。

※今回はにしてみます。

元の列の中にある項目たち

と、いう事で 月 のデータに入っている、1月~12月 を列に持ってきます。

※下の図の選択している部分です。

これを構文に当てはめると

以下のようになります。

 

+++++

PIVOT (
sum([単価]) FOR [月] IN ([ 1月], [ 2月], [ 3月], [ 4月], [ 5月], [ 6月], [ 7月], [ 8月], [ 9月], [ 10月], [ 11月], [ 12月])
) as P

+++++

通してSQL文にするとこの通りです。

 

 

SQL文を流してみます。

こんな感じです。

・・

・・・

・・・・・

わ、わかりにくいです。

やめましょう・・・・・

 

基本はシンプルで!!

Excelのピポットテーブルもですが、行に来る項目が複数あると集計結果がぱっと見わかりにくいです。

SQLも同じです。

もう一度以下の例を見てみます。

行に来る項目が、種別・名前・数量と3種類もあります。

テストデータの場合、幸い種別はという言葉の1種類しかないですが、名前と数量でデータが分散します。

PIVOTした結果が分かりにくくなります。※この時点で出力結果自体は正しく出ていますが、わかりにくいです。

慣れないうちは、行に来る値は1種類にしておきましょう。

では、テストデータは以下の用に加工して利用する事にしましょう。

 

この様な形なら、わかりやすいですね。

以前にもやった、Select結果を一気にテーブルに流し込むやり方でテーブルを再度、作ってみます。

おさかなテーブルからおさから2テーブルを作ります。

こんなかんじでーす。

 

実行したら上位1000行を確認してみます。

ちゃんとデータが入っていますね。

それではこのテーブルを使って改めてピボットを実施してみます。

実行結果についてだいぶ見やすくなりましたね。

 

ちょっとエクセルで検算してみましょう。

1月の鱈の8799が正しいか確認してみます。

エクセルで1月と鱈でフィルタした結果です。

ちゃんと、8799って出てますね。

この様な形でSQLを使ってピポットを表現する事が出来ました。

ただ列に持ってくる、例えば1月から12月は固定できます。

最悪、3月のデータが無くても3月と書いておいても違和感ないです。

ただ、月ではなく、名前を列側に持ってきてしまった場合で、途中から

太刀魚

が、データとして入ってきた場合はSQL文を書き換える必要があります。

ん~

SQL文で書けるとカッコいいですが、Excelでやった方が早い場合はさっさとExcelでやっちゃいましょう。

 

 

 

 

テストデータ

今回の実験で利用したテストデータです。

コピペで貼り付けれます。

 

種別  売上月 名前 単価 数量
 1月 100 1
 2月 900 2
 3月 3000 8
 5月 2000 5
 6月 3452 8
 7月 354 7
 8月 300 8
 9月 3000 9
 10月 2345 10
 11月 2000 4
 12月 3452 4
 3月 6000 8
 4月 324 4
 5月 2353 5
 6月 234 9
 7月 100 7
 8月 900 8
 9月 3000 9
 10月 2345 10
 11月 2000 4
 12月 3452 4
 1月 354 1
 2月 300 2
 6月 234 8
 1月 354 1
 2月 300 9
 10月 100 9
 11月 900 4
 12月 3000 4
 1月 2345 1
 2月 2000 2
 3月 3452 8
 4月 354 4
 5月 300 5
 6月 6000 9
 7月 324 7
 8月 2353 8
 9月 234 9
 10月 100 10
 11月 900 4
 12月 3000 4
 1月 2345 1
 2月 2000 2
 3月 3452 8
 4月 354 9
 5月 300 5
 6月 6000 8
 7月 324 7
 8月 2353 4
 2月 2000 9
 4月 354 4
 8月 2353 8
 9月 234 9
 10月 100 9
 11月 900 4
 10月 100 10
 11月 900 4
 12月 3000 4
 1月 2345 1
 2月 2000 2
 3月 3452 8
 4月 354 4
 7月 324 9
 5月 300 9
 1月 6000 1
 6月 900 9
 7月 3000 7
 8月 2345 8
 9月 2000 9
 10月 3452 10
 11月 354 4

 

 

関連記事(一部広告含む)
スポンサーリンク
4.上級者向けクエリ
kazuyoumeをフォローする
ざったなぶろぐ