More than 3 years have passed since last update.

批評空間SQL実行フォーラムで読むSQLクックブック(レシピ1.1~1.6、1.8~1.13)

Posted at 2022-03-28

参考文献

記事をかいたきっかけ

参考文献1.を買って試しにクエリを書きたいと思った。
参考文献2.で興味ある内容のDBが3.でいじれることを知り、やってみようかなと思った。
以上の理由に加えてこの記事の筆者は単純に文章がド下手くそであったため、ドキュメント作成の練習がしたかったから記事を作成した。

以下では、参考文献1.にあるレシピa.bから考えた実行内容レシピa.beを実行するためのクエリと、各クエリのポイントについて簡単にまとめる。整理した範囲は、1章レコードの取得のレシピ1.1から1.13までと言いたかったが、1.7についてはあまり理解できていないのであとでまとめたい。

以下では、もしレシピa.be内で取り出す列名を具体的に書いていなかった場合*により全部の列を要求することとする。

レシピ1.1 テーブルからすべての行と列を取得する

→レシピ1.1e テーブルbrandlistからすべての行と列を取得する

ポイントは、多分select * from テーブル名の構文。

select *
  from brandlist;

レシピ1.2 テーブルから行の一部を取得する

→レシピ1.2e テーブルbrandlistからidの値が1の行を取得する

ポイントは、where句による条件の指定だと考えられる。

select *
  from brandlist
 where id = 1;

レシピ1.3 複数の条件を満たす行を取得する行の一部を取得する

→レシピ1.3e idの値が10以下で、urlに値が入っている行を取得する

ポイントは、where句におけるor、andの使い方。
is not null については、レシピ1.11で扱っているのでそこで取り上げる。

select *
  from brandlist
 where id <= 10
  and url is not null;

レシピ1.4 テーブルから列の一部を取得する

→レシピ1.2e テーブルbrandlistから列idとbrandfuriganaを取得する

ポイントは、select * ではなくselect ほしい列1, ほしい列2とするところ。

select id, brandfurigana
  from brandlist;

レシピ1.5 列に意味のある名前をつける

→レシピ1.5e テーブルbrandlistの列名urlをsiteurlとして取得して表示する

ポイントは、select 既存の列名 as 呼称したい列名によって、クエリが返す列に新しい名前をつけているところがポイント。これのことをaliasing(エイリアス化)と呼ぶこと、新しい名前(列名)をalias(エイリアス)と呼ぶことは覚えていた方がよさそう。
(あまり意味のある名前の変更にはなっていなそうなのはﾅｲｼｮ)

select url as siteurl
  from brandlist;

レシピ1.6 where句の中でエイリアス化された列を参照する

→レシピ1.6e テーブルbrandlistの列名urlをsiteurlとして、siteurlがNULLでないもののみ表示する

以下では失敗してしまうので、クエリをインラインビューでラップして、エイリアス化する点がポイントらしい。

-- レシピ1.6、失敗するクエリ
select url as siteurl
  from brandlist
 where siteurl is not NULL;

インラインビューとしてラップした結果は以下の通りであり、from句以下の部分ではエイリアスのxを使わないとエラーを吐いてしまうがその理由はまだわかっていないのであとで詳しくまとめたい。

-- レシピ1.6、成功するクエリ
select *
  from (
select url as siteurl
  from brandlist
          ) x
 where siteurl is not NULL;

レシピ1.7 列値を連結する

(工事中。RDBMSごとの違いとかもあって、少しわからなかった。)

レシピ1.8 SELECT文で条件ロジックを使う

→レシピ1.8e テーブルbrandlistから、列brandname、medianとmedianが60未満のレコードをlow、80より上のレコードをhigh、その他をmiddleとする列qualityを取得する。

ポイントはcase句の部分で、その結果に対してas句を使うことでエイリアス化する必要がある点も意識した方がよさそうだった。

select brandname, median,
       case when median < 60 then 'low'
            when median > 80 then 'high'
            else 'middle'
       end as status
  from brandlist;

レシピ1.9 返す行数を制限する

→レシピ1.9e テーブルbrandlistから10レコードだけ返すようにレシピ1.1eのクエリを変更する。

参考文献2.で使っているRDBMSはPostgreSQLらしいので、LIMITを使って制限すればよいらしい。

select *
  from brandlist limit 10;

インラインビューとしてラップした結果は以下の通りである。

レシピ1.10 テーブルから$n$個のランダムなレコードを返す

→レシピ1.10e テーブルbrandlistからランダムに10レコードだけ返すようにレシピ1.9eのクエリを変更する。

ポイントは、PostgreSQLでは組み込み関数RANDOMをLIMITとORDER BYと共に使うことで可能になるということ。あらかじめ1.9eのクエリを複数回実行して、返ってくるレコードに変化がないことは確かめてみてもいいかもしれない。

select *
  from brandlist
 order by random() limit 10;

レシピ1.10 テーブルから$n$個のランダムなレコードを返す

→レシピ1.10e テーブルbrandlistからランダムに10レコードだけ返すようにレシピ1.9eのクエリを変更する。

select *
  from brandlist
 order by random() limit 10;

レシピ1.11 NULLを探す

→レシピ1.11e テーブルbrandlistからurlがNULLであるすべての行を返すようにレシピ1.3eのクエリを変更する。

ポイントは、レシピ1.3eのクエリにもあるようにurl is not nullの部分だと感じた。NULLは5とか文字列'hoge'などの特定の値だけでなくNULLとも比較することができないのでis not nullによりNULLかどうかを確認する必要がある。

select *
  from brandlist
 where url is not null;

レシピ1.12 NULLを実際の値に変更する

→レシピ1.12e テーブルbrandlistからurlがNULLのレコードのurlを文字列'unknown'にした上で列brandnameと列urlを返すようにレシピ1.11eのクエリを変更する。

ポイントは、組み込み関数COALESCEを利用するかcase句内でis not nullでないものの値をレシピ1.8eでみたようなやり方で変更してしまうかのどちらかによって可能であるという点だと考える。今回は、２つの方法で試した。

coalesce版

-- coalesceによる変更
-- 列名がurlではなくcoalesceになるっぽいことには、少し注意が必要かもしれない。
select brandname, coalesce(url, 'unknown')
  from brandlist;

case句版

-- case句による変更
-- こちらでは返ってくる列名がurlでなくcaseになる点に注意。
select brandname,
       case
       when url is not null then url
       else 'unknown'
       end
  from brandlist;

レシピ1.13 パターンを検索する

→レシピ1.13e テーブルbrandlistからbrandnameの最後にSOFTとつくようなレコードのみ抽出するようにレシピ1.1eのクエリを変更する。

ポイントは、LIKE演算子の利用とSQLでのワイルドカード%の利用にあると感じる。brandname LIKE '%SOFT'によってbrandnameがSOFTで終わるようなブランドのレコードのみを要求することができる。

select *
  from brandlist
 where brandname like '%SOFT';

感想

この記事のみから知見を得るのはむずかしい気がするので直したい。
- 適当なやってみた記事、いかがでしたか記事になってしまっているので改めて内容を整理したい。
レシピ1.6のエイリアス化についてはもう少し詳細を詰めたい。
レシピ1.7については、参考文献1.の2章や3章をやって慣れてきてから改めて書きたい。

次にやること、積み残し

次にやること

参考文献1.の2章

積み残し

レシピ1.6に関する詳細な説明
レシピ1.7について

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up