skillup

技術ブログ

Database

SQL case式やウィンドウ関数の威力

投稿日:

プログラマには「プログラムは思った通りに動かない。書いた通りに動く。」「バグではない仕様です」なんて面白い格言がいろいろありますが、データベースの世界にも「WHERE句で条件分岐させるのは素人のやること。プロはSELECT句で分岐させる」という格言があるそうです。

初めて聞いたのですが、ひとえに私の経験が浅いからでしょう(爆)

SQL実践入門を読んでいて面白かったSQL問題などをちょこちょこと書いていこうと思います。(データなどはちょっといじっていますが)

テーブル名 postalcode

pcode district_name
4130001 サンプル県サンプル市1
4130002 サンプル県サンプル市2
4130103 サンプル県サンプル市3
4103213 サンプル県サンプル市4
4380824 サンプル県サンプル市5

上記のようなリストがあった時、検索語句(4130033)の合致度が高いもの3つを選べ。

合致度とは4130033と合致している桁数が多いものですね。

例えばこの場合、4130001と4130002は41300まで合致していますので、合致度が高くなります。

この選び方ですが、whereを使わずにcase式でサクッと行けます。

もう一問。今度はウィンドウ関数を使うと比較的楽に解ける問題です。

テーブル名 receipts

cust_id seq price
A 1 500
A 2 1000
A 3 700
B 5 100
B 6 50000
B 7 300
B 9 200
B 12 1000
C 10 600
C 20 100
C 45 200
C 70 50
D 3 2000

上記のテーブルでcust_idのseqが最大値のものと最小値の差を求めよ。
※例 Aの場合はseq=1が最小値でseq=3が最大値なので差は200となります。

この場合まず、cust_idごとの最大値と最小値を求めなければなりませんが、サブクエリを作るなどやると式が複雑になります。PostgreSQLなどであればPARTTIONを使い、

これで上のテーブルにランクをのせたテーブルにすることが出来ました。

この後はmax_seq_rank =1 のpriceとmin_seq_rank=1の引き算を行います。

これまたcaseを使うと下記のように比較的楽になります。

コツとしては一気にやらずに部分から組み立てていったほうがよいでしょう。また引き算の部分はまずおのおのの数値を出した後計算したほうがよいです。

私もいきなりやろうとすると挫折しましたので・・・・

-Database
-

執筆者:


comment

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

関連記事

no image

JPAでの多対多のリレーション

以前、このエントリーでJPAのリレーションについて説明しました。 今回は多対多について説明します。 Contents1 テーブル構成2 ソース2.1 CDのエンティティ2.2 Artistのエンティテ …

no image

SQL基礎 条件式はunionよりもcaseで

複雑な条件式があったときにcase式を使うことでパフォーマンスを向上させることができます。 ※一般にunionを使うよりも高速なことが多い。 例1 ある条件により別の列を使いたいとき、 [crayon …

no image

データベース設計のアンチパターン 複数表結合,大作SQL,Blob型の乱用

データベースのアンチパターンに関して。 以前下記ブログでも書いたんですが設計のスキルに関してもう少し身に着ける必要があるとおもい、チェックします。 論理設計のグレーノウハウ サロゲートキー 論理設計の …

no image

データ構造の基礎知識 後編 木構造

データベースの学習をしていたときの復習です。 データ構造の基礎知識 前編 メモリとポインタ、配列と連結リスト データ構造の基礎知識 中編 ハッシュ 今回はもう少し複雑な「木構造」について考えてみます。 …

no image

自己結合のイメージ

前回に引き続き結合について考えます。 例えば下記のようなテーブルがあり、重複行を削除するとします。 1 りんご 50 2 みかん 100 3 みかん 100 4 みかん 100 5 バナナ 80 この …