いくつかの数の数え方→CASEを使って超スッキリ – ざったなぶろぐ
超特価クーポンを毎日アップロード!!ギアベストクーポン
32GBで1万円は正直悩む・・・

メモリもSSDも半導体の値下がりが半端ないですね・・・

くりっく

いくつかの数の数え方→CASEを使って超スッキリ

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

このページではSQL文を書く上で上級者向けのテクニックです。

と言っても慣れてしまえばたいしたことはありません。大事な事は

そんなやり方があるんだ!!

という、その事実に気づくところです。

こういうのは普通だれも教えてくれないですからね。

 

様々な数の数え方

基本情報

数の数え方をいくつか紹介します。

1章で初心者向けの書き方を、2章で上級者向けの書き方をします。

まずは、ベースとなる情報です。

DB:AdventureWorksDW2016_EXTにあるTABLE:DimCustomerを使ってみたいと思います。

以下の図のような苗字性の記号が表示されているテーブルを例に考えてみたいと思います。

 

お題は、

苗字毎に、男性と女性の数を数える

です。このお題をクリアするためのSQLを長々と書いていきます。

「俺は初級は卒業した!」と、思っている人は2章だけで大丈夫です。

単純にFとMで検索する

もっとも簡単な方法として、単純にWhere区でFMで検索を行ってみます。

これは非常にわかりやすいですね。

 

この状態で LastName毎group bycountする事により数を数える事が出来ます。

※見やすくするために、order by で並び替えもしています。

Where区のGenderに対する条件をFかMに切り替えることによって、それぞれの人数を算出できます。

因みに、Mに切り替えると以下のようになります。

 

集合を、姓(LastName)だけではなく、性(Gender)も含めてcountする。

人によっては此方のやり方が先に頭に思い浮かぶ人がいるかもしれません。

SQL文で記載すると以下のようになります。

group by に LastNameだけではなくGenderも含めて集合として取り扱います。

この方法で記載すれば、一度にFとMの人数が表示されます。ただ、よく見て頂きたいのがこちらです。

青枠で括ったAlbrechtは珍しい姓なので、Fな人が一人しかいません。(Mがいません)

その下にある、Allenはそれなりに人数がいる為、人数が集計されています。

この検索方法だと、Fの数とMの数が姓毎に一つだったり、二つだったりになってしまいます。データベースの目線で見ると全く問題なく正常な動作となります。ただ、人の目で見たときはちょっと気持ち悪いですね。

そのため、見た目にも見やすくなるよう、これらの情報を横に並べてみたいと思います。

 

姓(LastName)毎にFの数とMの数を一覧表示させる。

初めのSQL文を

  • where区でF(M)で検索
  • それぞれのテーブルを結合

して、一覧表示させます。

※もし、以下の書き方で意味が分からない人は、先にこちらの記事を確認してください。

その3)SQLテクニック 副問い合わせ(inとexist)
inやexistの使い方です。ただし、お勧めしていません!inはちょっとした事では便利で直ぐに使いたくなりますが、次項の記事で説明している内容の方が圧倒的に使いやすいからです。

 

一見正しく動いたように見えるこのクエリですが、残念ながら失敗しています。

以下の図のようにエラーにはなっていませんが正しく値が取れていません。

この方法で、検索すると先ほど例示した少数派のAlbrechtが消えてしまっています。

Fのテーブルには1名いましたが、Mのテーブルにいないため、Where区で結合した部分で条件に合致しないデータが消えてしまいました。

これ等は

  • F側のテーブルにあるけど、M側のテーブルにはない
  • M側のテーブルにあるけど、F側のテーブルにはない

と、どちら側の性にも言える事になります。

 

結合するのはFのテーブルとMのテーブルではなく

  1. 姓の一覧
  2. Mのテーブル
  3. Fのテーブル

上記3テーブルと言う事になります。

姓の一覧(LastName)と結合する事により漏れはなくなります。

 

姓(LastName)毎にFの数とMの数を一覧表示させる。(やり直し)

SQL文はちょっと長くなりますが以下の通りです。

FのテーブルMのテーブルそれぞれに無いデータを意識する必要があるので、where区による結合ではなく、left outer join での結合に変えています。

この通り、お互いのテーブルに存在しないデータは NULLとして表示されるようになりました。

このままだと見かけが良くないので、重複している列の削除と、NULLの場合を 0 に置き換えてみます。

これで、以下の通り見た目もスッキリになりました。

 

スマートにクエリを書く

可読性

はい、ここからが本題であり、上級編の内容となります。

前までの章では基本的な書き方を応用して

  • 数を数える
  • テーブルを結合する

で、クエリを書いてみましたが、ぱっと見わかりずらいです。また、SQL文が長すぎます。

自分ひとりでお仕事しているならばこれでも良いでしょうが、他人に引き渡さなければならない場合等も、往々にしてあります。

そのような事も考慮して、ぱっと見で分かりにくいクエリは避けるべきです。

※じっくり、分解して見てみるとたいしたことは無く、テーブルを3個結合しているだけです。

今回のお題を改めて整理しますが、求めたい回答は以下となります。

苗字毎に、男性と女性の数を数える

これを満たすために、CASE式を使って一度、テーブルを書き換えて見ます。

CASE式での書き換え

それではCASE式で書き換えてみます。

【書き換え前】

【書き換え後】

何が、起きたかわかりますでしょうか?

わざわざ、Case式を利用して以下のような条件で表示する列を増やしています。

  • Fだったら0、Mだったら1
  • Mだったら0、Fだったら1

感の良い人ならこの段階で分かると思いますが、この表示結果、sum関数で集計できそうに見えませんか?

出来そうですね!

sumによる結果の集計

それでは集計してみましょう。

以下の通りになります。

ビックリするほどスッキリ書くことができました。

はなさん
すっきりさん♪

一気に結果を出すつもりでSQL文を書くと難しく感じます。

ただ、今回のようにワンクッションおいてSQL文を書いてみると、そこまで難しくないSQL文であることがわかります。

今回この内容を上級編として書いていますが、やっていることは初級編と中級編を組み合わせているだけで、決して難しい内容ではありません。

苦手意識を持たずに一度書いて見てください。

 

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