【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

Feedly API変更後のChrome拡張機能「RSS Subscription」での登録方法

Facebook にシェア
Pocket

自分はRSSリーダーにはFeedlyを使っているのですが、前からフィード(RSS)を登録する際には、RSS Subscription Extension(by Google)というGoogle Chromeの拡張機能を利用しています。

最近は新しく登録する頻度も減っていたのですが、先日、気になったフィードを登録しようとすると、エラーとなって下記のような画面になりました。
{"errorCode":404,"errorId":"ap1int-sv2.2018030605.2029573","errorMessage":"API version not found"}

よくよく調べてみると、いつの間にかFeedlyのフィード登録用APIのURLが変わっていたようです。今まで、『http://cloud.feedly.com/#subscription/feed/%s』というURLで設定していたのですが、どうやら、『https://feedly.com/i/subscription/feed/%s』というふうに変更になったそうようです。

1月に変わったそうなのでかなり遅い情報という(正確にいうと、変わったというより新しいURLのAPIは前からあって、1月に古いAPIが使えなくなったようです)。

今後やりたいこと、もしくはやめようと思っていること

Facebook にシェア
Pocket

前回、ブログが止まっている間にやったことを書いたので、今回は今後やりたいこと(やめようと思っていること)について書いていきます。

このサイトをHTTPSにする

このブログで使っているレンタルサーバーはミニバードというサービスなのですが、どうやら先日、無料で独自SSLを取得できるようになったそうです。
サポートマニュアル /格安・大容量レンタルサーバーのミニバード

というわけで、近いうちにhttpsにしようと思っています(それぐらいならすぐにできるとは思うのですが、記録して残しておきたいのでまとまった時間に)。

それにしても、ミニバードが順次『スターサーバー』に移行すると前にあったけど、いったいいつに移行されるんだ。何かしなきゃダメなのか?
ネットオウル株式会社 ニュースリリース|無料で独自SSLが使える!クラウド型高速レンタルサーバー『スターサーバー』リリースおよび既存サービスの統合のお知らせ(8月7日予定)

パソコンを買う

今使っているパソコンのOSはWindows7です。2011年購入して、ずっとこの端末を使っています。

「FMV ESPRIMO FH56/DD」という端末です。下記に発売当時の記事があったのでリンクを貼っておきます。
やっぱり気になる“地デジ”PC:“テレビよし、省スペースよし、価格よし”の新作ボードPC――「FMV ESPRIMO FH56/DD」を試す (1/4) – ITmedia PC USER

確か、2年前の春頃にパソコンが壊れかけたのでその時に新しいのを買おうかと思いました。「どの項目も今より使っている以上のスペックは最低限ほしいよなぁ。まあ、同じぐらいのスペックの端末はムーアの法則どおり当時より安くなってるだろう」何て思いながら探しました。

簡単に現在使っているパソコンのスペックを下記に列挙します。

  • 一体型PCの20型
  • CPU:Core i5
  • メモリ:4GB(これは8GBに付け替えてはいます)
  • HDD容量:2TB
  • BDXL対応のBlu-ray Discドライブを装備
  • Office Home and Business 2010付属
  • 地上/BS/110度CSデジタルの3波ダブルチューナー装備

他にもいろいろあるのですが、最低限これぐらいは備えてるのがいいなと思いました。当時購入した価格程度で。

このパソコンを自分は10万円弱で購入しました。ですが、今このスペックのパソコンを探そうとすると20万円ほどかかってしまいます。何で今はこんなに高いんだと思って考えました。そうか、あの時は1ドル80円程度の円高だったからかと。

まあ、円高が原因だけだったら15万ほどで買えてもよさそうな気がしますが、なかなか見つからないんですよね。

ただ、最近になって「最近は配信サイトでテレビも見れるし、その機能はいらないか」「OfficeはLibreOfficeでいいか」「ドライブは外付けでいいか」「クラウドストレージ使ってるし外付けHDDもあるから、容量はそんなに必要ないか」なんて思ってきました。

で、いろいろ考えた末にたどりついたのが、「いっそのこと、Macbookでも買ってみようかな」と。「プログラマならMacを使ったほうが良い、特にWeb系なら」とあちこちで耳にするので、前から気になってはいましたが、今までほぼ全くといっていいほど触ったことがありません(触ったと行ってもちょっとした動作確認程度)。ただ、「全くもってWindows環境から離れるというのはそれはそれでいやだなぁ」なんて思ったので、いっそのこと安めのWindowsデスクトップ端末と二台持ちにしようかとも考えたのですが、調べてみると、BootcampとかParallels Desktopという機能を使うとMacにWindows環境を構築できるのだとか。

ただ、「ノートパソコンだと画面サイズ小さくなるし、買うとしたらデスクトップのほうがいいかなぁ。でも、それだとスタバでMac使えないし」なんて考えたりしました(スタバには普段から行きませんし、行くつもりもないですが)。

と思ったら、Macbookにはクラムシェルモードなんてものがあることが発覚。
MacBookProをデスクトップパソコンとして使える「クラムシェルモード」のやり方、メリット・デメリット、必要な商品など!! – エルパの盤上この一手

こうなってくると、Macbookでも問題ない気がしてきます。というわけで、買うとしたらMacbookを買おうとは思っています。個人的には、Parallels Desktopをクラムシェルモードで利用したいと思うのですが、両方利用していた感想の記事が見つからず(両方利用している人のブログは見つかったのだけど、同時に使っている記事は見つからなかった)。

どちらにせよ、買うとしたらMacbookにするかMacbook Airにするか、Macbook Proにするか、スペックや容量はどうするかと考えなきゃいけないことはあるのですが、そのへんはもう少し調べていこうと思います。楽天マガジンで最新のMacFanという雑誌を読んだらAirはやめたほうがいいらしいのだけど、価格はAirが安いしなぁ。そもそもも、外部ディスプレイで利用するならRetinaかどうかとかそんなことあまり気にならないだろうし……。どうしたもんか。なんだかんだで、結局Windowsにするとなる気もしなくはない(今のWindowsってLinuxも動くらしいし)。

奨学金を全額返済する

いつになるかわからないのですが、返し終わるのがこのまま続けていると30代後半ぐらいだったと思います。多分、貯金はすでに奨学金で借りた分以上になっているので、そろそろ全額返済してしまってもいいかなと思っています(ただ、残りいくら返せばいいのかは分かってない)。

今年は、今まで女性のとの縁なんて全く無かった人間が、結婚相談所に登録して婚活をやってみようと思っているぐらいなので、借金ぐらいは返したいとたほうがいいかなと思いまして(あまり借金しているという感覚がないから怖いです)。

ただ、よくよく考えてみると、利子1%ほどの借金を無理に全額返さなくても、その分、株とか投資信託とか、いっそのこと最近話題のソーシャルレンディングとかやったほうが利回りいいんじゃないかと思わなくもないので、これはどうしようかちょっと迷っています。

ジムを辞める

確か、2013年から通っているスポーツジム。運動習慣をつけて健康になろうと思ったら、ジムに通い始めてから風邪を引きやすくなり、なぜか太ってもきていて思ったほど効果を感じないでます(さすがに、筋肉量と体力は通う前と比べるとだいぶあがった実感はありますが)。

ただ、これは月8000円とまあそれなりに高い。しかも、一度いくと2時間ぐらい利用するので時間もかかる(これはもう少し早く帰ったらいいだけの話なのだけど)。後、今入っているプランが30歳以下のプランなので、来年になったらまた高くなるという。

いっそのこと、辞めてエアロバイクでも買おうかななんて考えています。そう思っていたら、誕生日プレゼントにスポーツウェアとスポーツバッグをプレゼントしてもらえたという。どうしたもんか……。

iDeCoを始める

イデコいいですよね。イデコ。名前もかわいいので、iDeCoのアピールキャラクターイデコちゃんとかでてきそうな気もするのですが、そんなことはないようです。

知ってる人も多いと思いますが、簡単にいっておくと、iDeCoは個人型確定拠出年金のことで、60歳まで引き出せない代わりに運用益が非課税になり、さらに所得控除にもなるので税金も減るというお得な制度となっています。60歳まで引き出せないというのはデメリットですが、老後のために貯金するというぐらいであればiDeCoにいれたほうがいいのではないかと思います(他にも手数料がかかるというデメリットがありますが、多分、所得控除で減る税金よりも少なくなるのではないかなと思っています)。

運用なので元本割れのリスクはありますが、分散投資やドルコスト平均法である程度リスクは抑えられるかなと(ドルコスト平均法にしてても、右肩下がりのチャートだったら意味ないですが)。全部を元本割れリスクのある商品を買わずに、一部は元本保証の定期預金タイプにするという手もありますしね。

ちなみに、資産運用だと現在、NISAで株式投資をやっています。先日、結婚相談所の登録時に趣味の一つに「株式投資」と書いたのですが、「株式投資は悪いイメージを持つ人が多いので書かないほうがいい」なんてアドバイスをもらいました。今時そんなこと思う人がいるのかと思うし、むしろそれぐらいで悪いイメージもつ人とはつきあいたくないなと思ったのでそのままにしてもらった。ちなみに、先程試しに「趣味 株式投資 婚活」でググったら、1件目にヒットしたのが下記ページ。
趣味としての株式投資について|恋愛相談Q&A|婚活・婚活サイトならyoubride(ユーブライド)

上記は2014年の書き込みだけど、こうやって株式投資に悪いイメージを持ってる人って、まだ多いのかな。株式投資が悪いことならNISAとか積立NISAとかiDeCoとかの制度を国が始めるわけないだろと思うのだけど。むしろ、投資をやってると思ったら堅実な人と思ってもらえそうと思って書いたんだけどなぁ。『インベスターZ』にも運用男子がモテると書いてあったし。
参考:積立投資をする「運用男子」がモテる時代。 | 低コストの投資信託で資産形成 | LoLo Investors

と思ってよくよく見てみたら、「株」と書くと危険なイメージがあるとも書いてあった。「株式投資」という言い方がダメなのか。「資産運用」だったらよかった。

ちなみに、最近はロボアドバイザー投資というのが気になっているので、もう少し調べてよさそうだったらそっちもやってみるかもしれません。

ケチケチしない

これはどちらかというと、ただの宣言に近いものですが、今年はお金を使う年にしようと思います。

もちろん、同じものを安く買えるのであれば安いにこしたことはないのですが、今まではちょっとぐらい質やデザインが悪そうでも安いものを選んでいるのが自分でした。実用には問題ないだろうと。ただ、お金を使わなきゃ入ってもこないというので、ちょっと高くても良さそうと思ったら買うスタンスでいきたいと思います。

前回の記事でも書きましたが、先日、abrAsusというブランドの薄い財布という財布を買いまして、これが15000円します。今までの自分だったら財布にそんな金額を使わなかったと思います。実際、FRUHとかいうブランドの薄い財布なら6000円ぐらいで買えるのでどちらにしようか迷いました。しかも、FRUHのほうが使い慣れているデザインですし。ただ、今までと同じ使い方だとまた分厚くしてしまうと思い、分厚くならない(というよりできない)デザインのabrAsusを選びました。

できるだけ現金を使わない

これも宣言に近いもの。

上のケチケチしないと矛盾すると思うかもしれませんが、ここでいう現金というのは紙幣や小銭のことです。

キャッシュレス社会ともいわれてますしね。クレジットカードか電子マネーが使えるところは増えてきてると思うので、そういうのが使えるところだったら使おうと思っています。

今までもau walletを使えるところは使うようにしていたのですが、今年は電子マネーのEdy(というより楽天カード)を作って、後、Line Payカードも作ったので、今まで以上に現金を使わない生活にしていこうと思います。Line Payは使うと送金が簡単になるので家族(特に、一人暮らししている妹)にも使ってほしいんですが、よく分からないから使いたがってないんですよね。最近はLine Payで割り勘の分のお金を払うということもあるそうで、そういう使い方はいいなと思いました。

一人暮らしする?

先日、30歳と、そろそろ実家ぐらしは引かれる年齢になりました。なので、数年前には「30になったら一人暮らししよう」なんて思っていました。

他にも、実家ぐらしより一人暮らしのほうが結婚できるとか、家族から「そろそろ一人暮らしでもしたら?」なんて言われる日もあるのですが、最近はあまりしたいとも思いません。実家ぐらしだと、親はうるさいし職場からは遠いというデメリットもあるのですが、それ以上にお金がかからないし食事も用意されているというメリットがあるので、どうにも一人暮らしする気は起きないでいます(お金がかからないとは行っても、毎月35000円を家にいれています)。

まあでも、気が向いたら部屋探して一人暮らしするかもしれません。

ブログを最低週1投稿する

何度目の宣言……