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

この記事を読むのに必要な時間は約 16 分です。

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

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

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

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

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

様々な数の数え方
スマートにクエリを書く

様々な数の数え方

基本情報

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

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

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

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

/****** SSMS の SelectTopNRows コマンドのスクリプト  ******/
SELECT 
       [LastName]
      ,[Gender]
     FROM [AdventureWorksDW2016_EXT].[dbo].[DimCustomer]

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

お題は、

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

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

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

単純にFとMで検索する

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

/****** SSMS の SelectTopNRows コマンドのスクリプト  ******/
SELECT 
       [LastName]
      ,[Gender]
     FROM [AdventureWorksDW2016_EXT].[dbo].[DimCustomer]
	 where Gender='F'

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

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

/****** SSMS の SelectTopNRows コマンドのスクリプト  ******/
SELECT 
       [LastName]
      ,count([Gender])
     FROM [AdventureWorksDW2016_EXT].[dbo].[DimCustomer]
	 where Gender='F'
	 group by [LastName]
	 order by [LastName]

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

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

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

/****** SSMS の SelectTopNRows コマンドのスクリプト  ******/
SELECT 
       [LastName]
      ,count([Gender])
     FROM [AdventureWorksDW2016_EXT].[dbo].[DimCustomer]
	 where Gender='M'
	 group by [LastName]
	 order by [LastName]

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

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

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

/****** SSMS の SelectTopNRows コマンドのスクリプト  ******/
SELECT 
       [LastName]
	  ,[Gender]
      ,count([Gender])
     FROM [AdventureWorksDW2016_EXT].[dbo].[DimCustomer]
	 group by [LastName],[Gender]
	 order by [LastName],[Gender]

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

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

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

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

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

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

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

初めのSQL文を

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

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

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

その３）SQLテクニック副問い合わせ（inとexist）

inやexistの使い方です。ただし、お勧めしていません！inはちょっとした事では便利で直ぐに使いたくなりますが、次項の記事で説明している内容の方が圧倒的に使いやすいからです。

select * from

(
----------------------------------------
SELECT 
       [LastName]
      ,count([Gender]) as 'Fnum'
     FROM [AdventureWorksDW2016_EXT].[dbo].[DimCustomer]
	 where [Gender]='F' group by [LastName]
----------------------------------------
) 
F
,
(
----------------------------------------
SELECT 
       [LastName]
      ,count([Gender]) as 'Mnum'
     FROM [AdventureWorksDW2016_EXT].[dbo].[DimCustomer]
	 where [Gender]='M' group by [LastName]
----------------------------------------
)
M
where F.LastName=M.LastName
order by F.LastName

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

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

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

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

これ等は

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

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

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

姓の一覧
Mのテーブル
Fのテーブル

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

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

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

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

select * from
(
----------------------------------------
SELECT 
    distinct [LastName]
     FROM [AdventureWorksDW2016_EXT].[dbo].[DimCustomer]
----------------------------------------
) 
LastNameALL
left outer join
(
----------------------------------------
SELECT 
       [LastName]
      ,count([Gender]) as 'Fnum'
     FROM [AdventureWorksDW2016_EXT].[dbo].[DimCustomer]
	 where [Gender]='F' group by [LastName]
----------------------------------------
) 
F
on
LastNameALL.LastName=F.LastName
left outer join
(
----------------------------------------
SELECT 
       [LastName]
      ,count([Gender]) as 'Mnum'
     FROM [AdventureWorksDW2016_EXT].[dbo].[DimCustomer]
	 where [Gender]='M' group by [LastName]
----------------------------------------
)
M
on
LastNameALL.LastName=M.LastName
order by LastNameALL.LastName

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

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

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

select 
  LastNameALL.LastName
 ,isnull(F.Fnum,'0') as 'Fnum'
 ,isnull(M.Mnum,'0') as 'Mnum'

 from

～　以下、省略　～

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

スマートにクエリを書く

可読性

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

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

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

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

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

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

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

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

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

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

CASE式での書き換え

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

【書き換え前】

/****** SSMS の SelectTopNRows コマンドのスクリプト  ******/
SELECT 
       [LastName]
      ,[Gender]
     FROM [AdventureWorksDW2016_EXT].[dbo].[DimCustomer]
     order by lastname

【書き換え後】

/****** SSMS の SelectTopNRows コマンドのスクリプト  ******/
SELECT 
       [LastName]
     ,( case when [Gender]='F' then 1 else 0 end) 'Fnum'
     ,( case when [Gender]='M' then 1 else 0 end) 'Mnum'
     FROM [AdventureWorksDW2016_EXT].[dbo].[DimCustomer]
	 order by LastName

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

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

Fだったら0、Mだったら１
Mだったら0、Fだったら１

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

出来そうですね！

sumによる結果の集計

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

/****** SSMS の SelectTopNRows コマンドのスクリプト  ******/
SELECT 
       [LastName]
     ,sum(( case when [Gender]='F' then 1 else 0 end)) 'Fnum'
     ,sum(( case when [Gender]='M' then 1 else 0 end)) 'Mnum'
     FROM [AdventureWorksDW2016_EXT].[dbo].[DimCustomer]
	 group by LastName 
	 order by LastName

以下の通りになります。