【SQL】特定のデータがあるグループのみ抽出する方法

Facebook にシェア
Pocket

SQLで集計する時に、ある特定の値があるグループのみ抽出したいということがあると思います。先日、仕事でそういうことをしたいということがあったのですが、すぐには思いつかず、仕事を終えた後に思いついたので、紹介したいと思います。

今回、使うのは下記のようなサンプルテーブル。テーブル名はtest1で、フィールドがグループ番号、名前、年齢、点数とします(名前と年齢は今期見てるアニメからとっています)。

ここから、中学生(12~14歳)がいるグループのみのグループ番号と平均点を取得するSQLを取得するには下記のようにすればとれます。

上記の結果が下記です。

確かに、グループに中学生のいる1と2のみの平均点が表示されました。

上記のSQLは何をしているかというと、まずはGROUP BYで全データを集計し、HAVINGで12~14歳の年齢がいるグループのみに絞っています。

なぜ、これで絞れるかというと、CASEの条件(ここでは「age BETWEEN 12 AND 14」)に一致した値があれば1という値が含まれ、無ければ0しかないということになるからです。それぞれの行にCASE文の結果を付与すると下記のようになります。

そこからMAX関数で最大値をとってくるので1が含まれていれば1、1が含まれていなければ0となるので、1に等しい値を抽出した結果、条件に一致したグループのみを取得できることになります。

逆に、特定のデータが入っていないもののみを抽出したいという場合は、「= 1」の箇所を「<> 1」とするか、「= 0」とすることで、取得することができます。

なお、HAVINGを使わなくても、サブクエリーを使うことで同じことができます。

ただ、HAVINGを使ったほうがシンプルなので、基本的にはHAVINGを使ったほうがいいと思います(実行計画のコストも、HAVINGを使ったほうが少しだけ低いようです)。

HAVINGは今までほとんど使ってこなかったのですが、使い始めると結構便利なことに気づきました。いろいろ応用もできそうなので、使いこなしていきたいです。

2018/03/26追記:そういえば、この記事を書こうと最初に思ったときは、条件は中学生以下、つまり「MAX(CASE WHEN age <= 14 THEN 1 ELSE 0 END) = 1」にしようとしていました。ただ、これだと、「MIN(age) <= 14」でもいいことに気づき、中学生が含まれるかどうかという条件に変更しました。こういう条件は、シンプルに考えられるようになりたいです。

外部結合したテーブルの値で内部結合するなという話

Facebook にシェア
Pocket

最近、仕事では複雑なSQLばっかり書いています。

ただ、今関わっているプロジェクトでは上から設計書を渡されてそれをコードとして書くのですが、SQL部分はほとんどそのままで、違うのはテーブル名やフィールド名が日本語になっているぐらいという感じです(場合によっては、効率が悪かったり、ムダにSQLを分けているなと思うと、こちらで書いて後から設計書に反映してもらうこともあります。変更が大きかったりすると嫌がられてるのが伝わってきます)。

そんな中で出てきたコードが下記のようなコード。自分も書いてるときは何も思わずにそのまま書いてしまっていました。

問題です。table1のsubid(ここでは1とする)に対応するtable2のidが見つからない場合、結果はどうなるでしょう。

答えは、

id field
1 null

とはなりません。

なぜなら、table1のsubidに対応するtable2がないのだから、内部結合しているtable2に対応するtable3もないから。で、外部結合だと対応する値が無くても出力しますが、内部結合だと対応する値が無いと出力しないので、結果、出力しないことになります。

なので、上記のSQLはtable3の結合も内部結合として、

とするのが正解なはずです。多分、table1にあってtable2に対応する値が無いことはあるけれど、table2にあって対応する値がtable3に無いことは無い(絶対にある)ので、こういうSQLを作ってしまったんだと思います。

ところで、今のプロジェクトで使っているDBMSはDB2の10.1なのですが、上記のようなSQLにおいて、なぜかWHERE句にANDを追加すると値が出力されるのだけど、無いと出力されないという摩訶不思議な現象が発生しました(ANDを使って条件を追加すると、通常は結果が変わらないか少なくなるはず)。

いろいろ調べてみると、上記のように結合部分がおかしかったので修正したら直りはしたのですが、なぜこんな現象がでたのかは謎です(条件の部分に関しては、上記の結合箇所は全く関係無かったし)。DB2のバグっぽいのだけど、そういう現象って報告されてたりするのだろうか。条件によってでたりでなかったりしたので、再現方法はわからないのだけど。

画面内に入ったり出たりした時の処理を行う「Intersection Observer」について

Facebook にシェア
Pocket

先日、超速! Webページ速度改善ガイド ── 使いやすさは「速さ」から始まる (WEB+DB PRESS plus)という本を読んで知ったのですが、最近のWebブラウザのJavaScriptには、「Intersection Observer」というAPIがあるらしく、このAPIを使うことで画面内に入ったか(もしくは画面外に出たか)どうかを判定できるそうです。

便利そうなので、試しに使ってみました。画面に四角い要素を100個並べて、画面に入ったり出たりすることで背景色が変わるサンプルです。試しに、今まで知ってはいたけど使ったことがないCSSアニメーションや、ES6から実装されたletやconstやアロー関数も使っています。

HTML

CSS

JavaScript

サンプル:Intesection Observeテスト

スクロールしたり画面サイズを変更したりすることで、四角い要素が画面内に入ると、背景色が1秒かけて青からオレンジに変わると思います(IE以外の最新ブラウザ対応)。

ただ、すばやくスクロールしているとたまにうまく動作しないことがあるみたいです。あまりアテにしすぎるのもよくないのかも(自分がよく分かってない可能性もおおいにあります)。

なお、IntersectionObserverの第2引数にはどの要素の交差を判定するか(なければブラウザのビューポート)のrootと、ルート回りの余白を指定できるrootMargin、それと可視性がどれぐらい入ってるか出ているかでコールバックを実行するthresholdというオプションがあるそうです。
参考:Intersection Observer API – Web API インターフェイス | MDN

超速!  Webページ速度改善ガイド ── 使いやすさは「速さ」から始まる (WEB+DB PRESS plus)
佐藤 歩 泉水 翔吾
技術評論社
売り上げランキング: 11,340

JavaScriptで日時の加減算を行う

Facebook にシェア
Pocket

またもや、改訂新版JavaScript本格入門 ~モダンスタイルによる基礎から現場での応用までから。

JavaScriptの日時処理は少し面倒だという印象があります。上で紹介した本を読んでも、ES2015でもそれは変わってないらしく、例えばフォーマットによる日時を付与した関数なんてものも用意されてないらしいです。

その点ではMoment.jsというライブラリ(参考:Moment.jsを使う – Qiita)がよさげではあるのですが、今回は素のJavaScriptを使った加減算について。

というのもやり方は簡単で、DateオブジェクトのgetXxxxxメソッドとsetXxxxxメソッドを使えばいいだけだそう。実は、本をよむまで、getとつくメソッドがあるのは知ってたけど、setとつくメソッドがあるのは知らなかった。

使い方としては以下。

見て分かるように、月をまたいでもちゃんと計算される(それにしてもなぜ、月だけ0始まりにしたのだろうか。最初間違えて、『new Date(2016,11,6,22,30);』として12月6日になって混乱した)。

これを応用したら、指定の日付の月末日を取得することができる。

ちなみに、setXxxxメソッドの戻り値は、Dateオブジェクトではなくて1970 年 1 月 1 日 00:00:00 からのミリ秒数。Dateオブジェクトだったらメソッドチェーンが利用できたので少し面倒。まあ、目的は変更後の日付を返すのではなく、自分自身の日付を変更するということだから仕方ないか。

まあ、やろうと思えば下記のようにできなくはない。

いや、これはメソッドチェーンとは言わないか。

というよりよくよく考えたら、Dateオブジェクトを新しく生成すればいいだけでした(調べて気づいた)。

なお、日付通しの計算については、getTimeメソッドを利用して数値化したうえで、1単位をミリ秒数で表した数で割らなきゃいけないそうです。

なんで標準でこれぐらいの計算が簡単にできるメソッドが用意されていないのだろうか……。ES2015では追加されるんじゃないかと期待していたのだけど……。

JavaScriptで、できるかぎり小数演算の誤差を少なくする方法

Facebook にシェア
Pocket

JavaScriptにかぎらず、プログラミングでの小数を含む演算では誤差が発生してしまうことがよくあります。例えば、『0.2+0.1』という単純な足し算すら、『0.30000000000000004』という結果になってしまいます(Chromeで確認した場合)。その対応方法が改訂新版JavaScript本格入門 ~モダンスタイルによる基礎から現場での応用までに書いてあったのでメモしておきます。

対応方法といっても単純なことで、ようは小数をいったん小数の長さ分の10の累乗を掛けて整数にしてから演算し、それから10の累乗分した数を割って正しい値を取得するというもの。最後の10の累乗で割るときに誤差が発生してしまうのではないかと思いそうですが、割られる方の数が整数であれば誤差は発生しないようです(例えば、『12.35/10』の結果は『1.2349999999999999』となるけど、『1235/1000』の結果は『1.235』となる)。

というわけで、小数の演算に対応した関数を書いてみました。

足し算、引き算、掛け算、割り算の4つの関数がありますが、上半分はすべて同じです。本来ならこの部分は共通化しておいたほうがいいでしょうね。エラー処理は余計だったかもしれません(この処理がなくても、どのみち例外エラーが発生するのだし、throwで返すのではなく、0やnullを返すとかでもいいかも)。

エラー処理はまず引数が数値かどうかチェックし、その後に指数表記かどうかを調べています。

実行した結果は下記のとおりです(『//=>』より右側が実行結果)。

ところで今回、小数って英語でなんていうんだけと思って調べて、英語でdecimalということを初めて知りました。decimalというと、10進数というイメージがあるのだけど、小数という意味もあったのかと。どっちの意味で使ってるか分かりづらいことってないのだろうか。