ちょっと話題の記事

[SQL] SQL文の書き順 〜PostgreSQL Tutorialを使いながら解説〜

宇都宮 洋

2016.07.22

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

はじめに

いざSQL文を書こうと思ったけど、どこから書けばよいのか迷うことはないでしょうか？

まずはselect句から書こうかな。。
使用するテーブルはわかっているのでfrom句から。。
where句はちょっと長くなるから後回しにして。。。

などなど

長いSQL文を書く必要がある時に、どこから書けばいいのか迷う方（僕もそうです）に読んでいただければと思います。

今回ご紹介する書き方は、あくまでも僕がSQL文を書く時に気をつけている方法ですので、他にもっといい書き方・書き順がある可能性があります。
今回は順番にフォーカスしているため、個々の句についての解説は割愛します。

環境

MacOS 10.10.5
PostgreSQL 9.4.5

実行順序

SQLが実行される順番は以下のようになっています。

FROM → WHERE → GROUPBY → HAVING → SELECT → ORDERBY

一方でSQLの構文の並び順はこのようになっています。

SELECT → FROM → WHERE → GROUPBY → HAVING → ORDERBY

SQLを書くときに迷うのはこの順番の違いが原因の１つではないかと思います。
なので僕はSQLを書くときには実行順番を意識しながら書くようにしています。

使用するテーブル

PostgreSQL Tutorialのサンプル(dvdrental)を使用してSQLを書いてみます。サンプルのデータベースを用意する方法はこちらの記事を参考にしていただければすぐに用意できます。データが少し古いですが動作確認なので気にしないことにします。

select rental_id,customer_id,rental_date from rental order by rental_date desc;

rental

 rental_id | customer_id |     rental_date     
-----------+-------------+---------------------
     11739 |         373 | 2006-02-14 15:16:03
     14616 |         532 | 2006-02-14 15:16:03
     11676 |         216 | 2006-02-14 15:16:03
     15966 |         374 | 2006-02-14 15:16:03
     13486 |         274 | 2006-02-14 15:16:03
     15894 |         168 | 2006-02-14 15:16:03
     14928 |         472 | 2006-02-14 15:16:03
     15430 |         282 | 2006-02-14 15:16:03
     14204 |         287 | 2006-02-14 15:16:03
     13578 |         352 | 2006-02-14 15:16:03
(16044 rows) 以下省略

実際のサンプルデータでは他のカラムもあるのですが、今回は他のカラムは使わないのでこういうカラム定義のデータを使うんだなくらいでご理解ください。

select customer_id,first_name,last_name,email from customer order by customer_id asc;

customer

 customer_id | first_name | last_name |                email                
-------------+------------+-----------+-------------------------------------
           1 | Mary       | Smith     | mary.smith@sakilacustomer.org
           2 | Patricia   | Johnson   | patricia.johnson@sakilacustomer.org
           3 | Linda      | Williams  | linda.williams@sakilacustomer.org
           4 | Barbara    | Jones     | barbara.jones@sakilacustomer.org
           5 | Elizabeth  | Brown     | elizabeth.brown@sakilacustomer.org
           6 | Jennifer   | Davis     | jennifer.davis@sakilacustomer.org
           7 | Maria      | Miller    | maria.miller@sakilacustomer.org
           8 | Susan      | Wilson    | susan.wilson@sakilacustomer.org
           9 | Margaret   | Moore     | margaret.moore@sakilacustomer.org
          10 | Dorothy    | Taylor    | dorothy.taylor@sakilacustomer.org
(599 rows) 以下省略

customerも他のカラムもありますが今回は使用しないので上のようなカラム定義のテーブルを使うんだなくらいでご理解ください。

求める結果

今回は2006年以降に複数回レンタルしたユーザーのフルネーム・アドレス・レンタル回数をレンタル回数が多い順に表示するSQLを書きます。

     fullname     |                email                 | count 
------------------+--------------------------------------+-------
 TammySanders     | tammy.sanders@sakilacustomer.org     |     3
 AnnetteOlson     | annette.olson@sakilacustomer.org     |     2
 HelenHarris      | helen.harris@sakilacustomer.org      |     2
 LawrenceLawton   | lawrence.lawton@sakilacustomer.org   |     2
 HeatherMorris    | heather.morris@sakilacustomer.org    |     2
 JordanArchuleta  | jordan.archuleta@sakilacustomer.org  |     2
 GailKnight       | gail.knight@sakilacustomer.org       |     2
 MildredBailey    | mildred.bailey@sakilacustomer.org    |     2
 ChristineRoberts | christine.roberts@sakilacustomer.org |     2
 CassandraWalters | cassandra.walters@sakilacustomer.org |     2
 AllisonStanley   | allison.stanley@sakilacustomer.org   |     2
 BillGavin        | bill.gavin@sakilacustomer.org        |     2
 FlorenceWoods    | florence.woods@sakilacustomer.org    |     2
 SoniaGregory     | sonia.gregory@sakilacustomer.org     |     2
 MorrisMccarter   | morris.mccarter@sakilacustomer.org   |     2
 MiguelBetancourt | miguel.betancourt@sakilacustomer.org |     2
 CarolynPerez     | carolyn.perez@sakilacustomer.org     |     2
 CathySpencer     | cathy.spencer@sakilacustomer.org     |     2
 JustinNgo        | justin.ngo@sakilacustomer.org        |     2
 MargieWade       | margie.wade@sakilacustomer.org       |     2
 NatalieMeyer     | natalie.meyer@sakilacustomer.org     |     2
 LucyWheeler      | lucy.wheeler@sakilacustomer.org      |     2
 ElmerNoe         | elmer.noe@sakilacustomer.org         |     2

答えは以下のSQLになります。

select
  B.first_name || B.last_name as fullname 
 ,email
 ,count(*) 
from 
  rental as A 
  inner join 
  customer as B 
  on A.customer_id = B.customer_id 
where 
  A.rental_date >= '2006-01-01' 
group by 
  B.customer_id 
having
 count(*) > 1 
order by 
 count(*) desc;

まずどこから書けば良いか迷いませんか？
特にselect句から書いたりすると、いきなりB.first_nameがあり、Bってなんだ？？　と僕は慣れない間は戸惑ったりしていました。

書き順

まずfrom句から書きます。

select
 *
from
 rental as A
  inner join
 customer as B
  on A.customer_id = B.customer_id
;

何回レンタルしたのかを数えるためにrentalテーブル、フルネームとメールアドレスを取得するためにcustomerテーブルを使うのはわかっているのでfrom句で結合します。
エイリアス(as句)はこのタイミングで定義しましょう。
最初にfrom句を書いてエイリアスもこのタイミングで定義することで、以後エイリアスで迷う心配がなくなります。
このタイミングで１度実行して確認します。select句には * を書いていますが、後から使うのがわかっているカラムをselect句に入れて都度確認するといったことをすることもあります。

次にwhere句を書きます。

select
 *
from
 rental as A
  inner join
 customer as B
  on A.customer_id = B.customer_id
where
 A.rental_date >= '2006-01-01'
;

次にgroupby句を書きます。

select
 *
from
 rental as A
  inner join
 customer as B
  on A.customer_id = B.customer_id
where
 A.rental_date >= '2006-01-01'
group by
 B.customer_id
;

ここまではSQLの実行順序にしたがって書いてきましたが、このタイミングで気をつけないといけないのはgroupby句でグループ化しているのでselect句は*だとエラーが吐かれます。
僕は都度確認しながらSQLを書き進めたいので、ここでselect句を書きます。

select
B.first_name || B.last_name as fullname
,email
,count(*)
from
 rental as A
  inner join
 customer as B
  on A.customer_id = B.customer_id
where
 A.rental_date >= '2006-01-01'
group by
 B.customer_id
;

この時点でテーブルから抽出した結果をグループ化しているので、かなりすっきりとした結果が返ってきます。間違ったSQLを書いていればこの辺りで気づくことが多いのではないでしょうか？

次にhaving句でグループ化した結果からさらに抽出する条件を書きます。

select
B.first_name || B.last_name as fullname
,email
,count(*)
from
 rental as A
  inner join
 customer as B
  on A.customer_id = B.customer_id
where
 A.rental_date >= '2006-01-01'
group by
 B.customer_id
having
 count(*) > 1
;

最後に必要ならorderby句でソートします。

select
B.first_name || B.last_name as fullname
,email
,count(*)
from
 rental as A
  inner join
 customer as B
  on A.customer_id = B.customer_id
where
 A.rental_date >= '2006-01-01'
group by
 B.customer_id
having
 count(*) > 1
order by 
 count(*) desc;
;

実行します。

     fullname     |                email                 | count 
------------------+--------------------------------------+-------
 LucyWheeler      | lucy.wheeler@sakilacustomer.org      |     2
 AnnetteOlson     | annette.olson@sakilacustomer.org     |     2
 HelenHarris      | helen.harris@sakilacustomer.org      |     2
 LawrenceLawton   | lawrence.lawton@sakilacustomer.org   |     2
 HeatherMorris    | heather.morris@sakilacustomer.org    |     2
 JordanArchuleta  | jordan.archuleta@sakilacustomer.org  |     2
 GailKnight       | gail.knight@sakilacustomer.org       |     2
 MildredBailey    | mildred.bailey@sakilacustomer.org    |     2
 TammySanders     | tammy.sanders@sakilacustomer.org     |     3
 ChristineRoberts | christine.roberts@sakilacustomer.org |     2
 CassandraWalters | cassandra.walters@sakilacustomer.org |     2
 AllisonStanley   | allison.stanley@sakilacustomer.org   |     2
 BillGavin        | bill.gavin@sakilacustomer.org        |     2
 FlorenceWoods    | florence.woods@sakilacustomer.org    |     2
 SoniaGregory     | sonia.gregory@sakilacustomer.org     |     2
 MorrisMccarter   | morris.mccarter@sakilacustomer.org   |     2
 MiguelBetancourt | miguel.betancourt@sakilacustomer.org |     2
 CarolynPerez     | carolyn.perez@sakilacustomer.org     |     2
 CathySpencer     | cathy.spencer@sakilacustomer.org     |     2
 JustinNgo        | justin.ngo@sakilacustomer.org        |     2
 MargieWade       | margie.wade@sakilacustomer.org       |     2
 NatalieMeyer     | natalie.meyer@sakilacustomer.org     |     2
 ElmerNoe         | elmer.noe@sakilacustomer.org         |     2

この結果から比較的最近、複数回レンタルをしたユーザー。つまり優良顧客の抽出ができました。

最後に

順番を意識するまでは、書くより先にどこから書くか考えるという手間がありました。
順番を心がけるだけで、まずSQLを書くことから始める。ということができるようになりましたので、それだけでも大きな収穫だったと自分では思っています。

参考

今回の記事を書くにあたり、以下のサイトと羽生章洋さんの「元祖SQL書き方ドリル」を参考にさせていただきました。
http://kaya-soft.com/sqlserver2008-toranomaki/beginner/select_master2/

AWS

関連記事

AWSの各DBの特徴が把握できる【Planning and Designing Databases on AWS】を受講してみた

ちゃあこ

2023.12.22