20190523のSQLに関する記事は3件です。

投稿日：2019-05-23T20:27:52+09:00

1日1問解いてSQLをマスター！SQLの学習サービス Qdash を作った話

目次

はじめに

なぜ作ろうと思ったか

使ってる技術

同じようなサービスを作りたい人のために実装を紹介

1日に1回、指定の時間に問題を切り替えて表示する

質問箱風なOGP画像を生成

これから

最後に

はじめに

Qdash という1日1問形式の SQL 学習サービスを作りました。

https://q-dash.jp

問題画面

回答画面

なぜ作ろうと思ったか

とあるデータを見たいけど、いつも SQL を書ける人にお願いしている。
勉強はしたいが、中々勉強する時間が取れない。

と思ってるディレクター、営業、マーケターの方！！
でも1日の中でも隙間時間はありますよね？

その隙間時間に問題を解き、SQL は任せて！と自信を持って言えるようになりませんか？
というのがサービスのコンセプトです。

使っている技術

・ Ruby
・ Rails
・ ServiceWorker / Webpush
・ Heroku

Rails / Heroku で手早く作りました。
push 通知をするために ServiceWorker と Webpush にも挑戦しました。 (この記事では触れません)

実装紹介

似たようなサービスを作りたい人のために実装をいくつか紹介します！

1日に1回、指定の時間に問題を切り替えて表示する

SQL を学びたい人にとって負担にならないように1日1問という仕様にしました。
あなたならどのように実装しますか？

今回は Cookie と Heroku scheduler を使って実装してみました。
class HomeController < ApplicationController
  def index
    @question = today_question
    delete_cookie_if_question_changed

    @answer = Answer.find_by(id: answer_id)
  end

  private

    # flesh set by scheduler.rake
    def today_question
      today_question = TodayQuestion.last || TodayQuestion.create_randomly
      today_question.question
    end

    def delete_cookie_if_question_changed
      answer = @question.answers.find_by(id: answer_id)
      cookies.delete('answer_id') if answer.nil?
    end

    def answer_id
      cookies['answer_id']
    end
end
回答をした際に cookie をセットします。
class AnswersController < ApplicationController
  def create
    answer = Answers::Factory.build(answer_choice_params)
    if answer.save
      cookies['answer_id'] = answer.id
      redirect_to root_path
    else
      redirect_to root_path, notice: '回答に失敗しました。'
    end
  end

  private

    def answer_choice_params
      params.permit(:answer_choice_id)
    end
end
Heroku scheduler で1日1回、今日の問題をDBに登録しています。
時間と実行するコマンドはアドオンを入れた後に Heroku の管理画面から設定することができます。
# sheduler.rake

task set_question_randomly: :environment do
  TodayQuestion.create_randomly
end
昨日と同じ問題が出たらテンションが下がるのでそれは避けるようにしています。
class TodayQuestion < ApplicationRecord
  belongs_to :question

  def self.create_randomly
    question = nil

    loop do
      last_question = TodayQuestion.last.question
      question = Question.pick_randomly

      break if last_question.id != question.id
    end

    create(question: question)
  end
end
質問箱風なOGP画像を生成

続いて、質問箱風な OGP の生成処理です。
問題の作成時に問題のタイトルを画像に書き込み、画像を S3 にアップロードします。 (active_storage を利用)

そして、シェアした時に作成した問題文が書かれたOGP画像が表示されるようにします。
# Gemfile
gem 'mini_magic'
class OgpImageGenerator
  def initialize(question: question)
    @question = question
  end

  def generate
    image = load_base_image
    image.combine_options do |c|
      c.gravity 'West'
      c.pointsize 30
      c.draw "text 55,0 '#{title}'"
      c.size "70x"
      c.fill '#000'
      c.font Rails.root.join('public', 'hiraginokakugoW6.ttc').to_s
      c.interline_spacing 10
      c.kerning 4
    end
    image
  end

  private

    def title
      ApplicationController.helpers
                           .sanitize(@question.title)
                           .chars.each_slice(15)
                           .map(&:join)
                           .join("\n")
    end

    def load_base_image
      MiniMagick::Image.read(Rails.root.join('public', 'images', 'ogp_base.png'))
    end
end
class Question < ApplicationRecord
  ..
  before_save :generate_ogp_image
  before_update :generate_ogp_image

  has_one_attached :ogp_image

  def generate_ogp_image
    return unless self.will_save_change_to_title?

    ogp_image_path = OgpImageGenerator.new(question: self).generate.path
    file = File.open(ogp_image_path)

    self.ogp_image.attach(
      io: file,
      filename: "question_ogp_image_#{id}_#{SecureRandom.urlsafe_base64}.png", content_type: "image/png"
    )
    file.close
  end
end
問題を作成すると、このような OGP 画像が自動で生成されます。

これから

ログイン機能とコース機能を作って問題を解けるようにしたいと思っています。
ログインしていなくても問題を解くことができる一方で、熱量の高い人はログインしてまとまった時間で勉強できたら良いですね。

実は一番大変なのは機能の実装より SQL の問題を作ることだったりしますw

最後に

詳細な使い方については note に書いているので見ていただけたら幸いです。
https://note.mu/usabdelah/n/n0b7f8fd30ed5

Qdash で学んだことを活かして実務で SQL を書き、
データの可視化をやっていく人が増えたらいいなと思っています。

ぜひ、Qdash で SQL の問題を解いてみてください。

https://q-dashjp

　 Tweet
Qiitaで続きを読む

投稿日：2019-05-23T14:35:50+09:00

なんで、SQLで「カラム名 = null」,「カラム名 <> null」は０件なのか

何故、NULL は = で比較したらあかんのか

新人さんたちは、不思議な値「NULL」でよく躓いてしまいます。
特に、教えていてアプローチに悩むのが、SQLの「IS NULL」という比較構文です。
私も、理解していない点が多くあったので、わからない点を纏めてみました。

やさしい上級者の方は、恐れ入りますが間違いがあったらご指摘頂けますと幸いです…

経緯

教育を行っていた際、"where x = null"と記述した際、0件なのであれば、
逆となる条件の"where x <> null"はなぜ全件じゃないのか？と聞かれたことが発端でした。

そこで、3値論理というワードを全く知らなかった自分のため、
また、NULLとは何ぞやといった点をわかりやすく教えるため、記述しました。

～(おさらい)本題に行く前に、WHEREってなんだっけ～

SQLの概念と、SELECT,FROM,WHEREの概念を簡単に理解している前提で書きます。

WHEREはDBに保有しているデータに、条件と一致するデータか～と質問する為の構文です。

「あなたは15歳以上ですか？」と10人から確認したい場合、
10人に質問しなければいけませんよね。

DBも同じで、10行(レコード)あった場合、1件づつ、合計10回問いかけを行っています。
いくつか、想定動作と結果をイメージしながら考えてみましょう。

hogeテーブル

id name age

1001 たろう 10

1002 じろう 15

1003 NULL 20

例えば、hogeテーブルから、年齢が15歳以上のデータ（レコード）を取得したいとします。

SQL記述例：
SQL*Plus
--※１のSQL例
SQL> SELECT *
  1> FROM hoge
  2> WHERE age >= 15;
上記のSQLを記述した場合、各行で下記のような計算が行われます。

id name age 計算条件結果

1001 たろう 10 10は15以上？偽

1002 じろう 15 15は15以上？真

1003 NULL 20 20は15以上？真

DBは真の計算結果となるものを返すので、
1002,1003の2件を取得します。

また、新人さんが混乱しやすい問題として、このようなSQLがあります。
SQL*Plus
--※１のSQL例
SQL> SELECT *
  1> FROM hoge
  2> WHERE 1 = 1;
よく、1=1ってなんだ？って身構えられるのですが、
単純に「1と1」を全レコードで比較しているだけです。

id name age 計算条件結果

1001 たろう 10 1は1? 真

1002 じろう 15 1は1? 真

1003 NULL 20 1は1? 真

この場合、1001,1002,1003の3件が返却されます。

～(おさらい)WHEREでnullを=で比較するとどうなるんだっけ～

SQLで例えば、[カラム名] = NULLのように比較演算子を利用し、NULLと比較をした場合、
カラムにNULLが入っているレコードがあった場合でも、結果は0件です。
SQL*Plus
--※１のSQL例
SQL> SELECT *
  1> FROM hoge
  2> WHERE name is NULL;

ID   NAME
---- -----
1003 NULL

--※２のSQL例
SQL> SELECT * FROM hoge
  2> WHERE name = NULL;

レコードが選択されませんでした。
また、BETWEENを利用した場合についても、同様の動作をします。
SQL*Plus
--※SQL例
SQL> SELECT * FROM hoge
  2> WHERE name BETWEEN 0 and NULL;

--ANDに置き換えて記述した場合(上記と同等のSQL)
SQL> SELECT * FROM hoge
  2> WHERE name >= 0 and name <=NULL;

レコードが選択されませんでした。
(NOTをつけない)INの場合、ORでの探索条件となるので、
NULL値は出ませんが、他の比較値(下記例だと1001のこと)は正しく取得出来ます。
SQL*Plus
--このSQLは1003のNULLさんは対象外となる
SQL> SELECT * FROM hoge
  2> WHERE name IN (NULL,1001);

ID   NAME
---- -----
1003 NULL

--INで記載されている内容と同等のSQL
SQL> SELECT * FROM hoge
  2> WHERE name = NULL -- NULL値を=で比較するため、こちらの比較結果は0件
  3> OR name = "たろう"; -- 「たろう」に合致するレコードはあるので、1件返却される

ID   NAME
---- -----
1001 たろう
問題提起(なんで「カラム名 <> null」は0件なのか)

新人さんが、この結果はおかしい、バグってる！と騒いでいました。
SQL*Plus
SQL> SELECT * FROM hoge
  2> WHERE NOT(name = NULL);

レコードが選択されませんでした。
主張としては、[カラム名] = NULLが0件なのであれば、
その否定は全件なのではないかといったお話でした。

私もこの主張は正しいな…と感じてしまったので調べました。
どうやら、3値論理という概念が深く関係しており、まず3値論理を理解しなければわからないようです。

3値論理とは

3値論理とは真偽値をTRUE,FALSEの2つに加えて、UNKNOWN(不明)という3つの値で管理する方法です。¹

ここでいう、UNKNOWNとはどういったデータかというと、おおまかに下記の2種類に分類されます。

①「家の総移動距離」や「モグラの飛行時間」のような、論理的に存在できない値
※ハウルの動く城とか、タイヤの付いた家、飛ぶモグラ等の
　前提になると話がすごくややこしくなるので、一般的な概念で考えて下さい…

②「タモリの目の色」や「星の数」や「一部アイドルの実年齢」のような、わからない（観測できない）値
※こちらも、「タモリサングラス外す」等で検索すると画像が出てきますが、²
　そんな画像はない前提でお願いします…

NULL値は、TRUEでもなく、FALSEでもなく、このUNKNOWNという第3値に分類されます。

UNKNOWNを比較してみよう

上記が理解できると、なんとなく分かる人もいるかもしれませんが、

WHERE 星の数 = 100000000000000のような荒唐無稽な質問は
もしかしたらTRUEかもしれませんし、FALSEかもしれませんが、わかりません！！というのが最適解になってしまいます。

星の数計算条件結果

NULL null は100000000000000? UNKNOWN

したがって、結果がTRUEではないため、返却されません。
逆にWHERE not(星の数 = 100000000000000)であってもこの結果はUNKNOWNです。

星の数計算条件結果

NULL null は100000000000000ではない？ UNKNOWN

だってわからないんですもん。

おまけ

少し応用的な話になってしまいますが、NOT INの挙動を聞くと、
1回目は大体まちがって認識されているのですが、上記らへんを押さえていれば、実は簡単に解けます。
SQL*Plus
SQL> SELECT * FROM hoge
  2> WHERE name NOT IN(NULL,1001);

--同等のSQL
SQL> SELECT * FROM hoge
  2> WHERE name <> NULL 
  3> AND name <> 1001;
not in はその名の通り、羅列した要素のいずれとも合致していないという条件のため、
AND と<>で構成されることになります。

このとき、<> NULLは0件で、AND演算なので、必ず0件になります。

https://codezine.jp/article/detail/532 ↩

タモリの目の色は何色なのか、画像から解析できませんでした。 ↩

id	name	age
1001	たろう	10
1002	じろう	15
1003	NULL	20

id	name	age	計算条件	結果
1001	たろう	10	10は15以上？	偽
1002	じろう	15	15は15以上？	真
1003	NULL	20	20は15以上？	真

id	name	age	計算条件	結果
1001	たろう	10	1は1?	真
1002	じろう	15	1は1?	真
1003	NULL	20	1は1?	真

星の数	計算条件	結果
NULL	null は100000000000000?	UNKNOWN

星の数	計算条件	結果
NULL	null は100000000000000ではない？	UNKNOWN

　 Tweet
Qiitaで続きを読む

投稿日：2019-05-23T14:35:50+09:00

何故、NULL は=で比較したらあかんのか

何故、NULL は = で比較したらあかんのか

新人さんたちは、不思議な値「NULL」でよく躓いてしまいます。
特に、教えていてアプローチに悩むのが、SQLの「IS NULL」という比較構文です。
私も、理解していない点が多くあったので、わからない点を纏めてみました。

やさしい上級者の方は、恐れ入りますが間違いがあったらご指摘頂けますと幸いです…
また、この記事は、無職やめ太郎さんインスパイアです。

経緯

教育を行っていた際、"where x = null"と記述した際、0件なのであれば、
"where x <> null"はなぜ全件じゃないの？と聞かれたことが発端でした。

そこで、3値論理というワードを全く知らなかった自分のため、説明できるように噛み砕いた記事です。
また、NULLとは何ぞやといった点にアプローチ出来るためにこれからの新人さんたちに教えるために記述致しました。

おはなし

むかしむかし(約2日前)、あるところにプログラム未経験の新入社員さんと、
講師の私がいました。

新入社員さんは社内でSQL（Oracle Database SQL）のお勉強に日々勤しんでおり、
私はSQLの書き方を教えていました。

ある日、where文でのNULL値の比較方法について教えていたときのことです。

～where文でのNULL値の比較方法を教えていた時～

私「NULLを比較したいときはis NULLを使うんやで」（※1）

新人「じゃあ、NULLを等価演算子(=)やIN,between等で比較したらエラーになるの？」
私「NULLを=,>,<等で比較したら、SQLは動くけど、結果は0件やで（なんでかわからんけど）」（※2）

hogeテーブル

id name

1001 たろう

1002 じろう

1003 NULL
SQL*Plus
--※１のSQL例
SQL> SELECT *
  1> FROM hoge
  2> WHERE name is NULL;

ID   NAME
---- -----
1003 NULL

--※２のSQL例
SQL> SELECT * FROM hoge
  2> WHERE name = NULL;

レコードが選択されませんでした。
　
私「勿論、betweenも一緒で、0~NULLとかNULL~0のような比較の場合も0件やで」
SQL*Plus
--※SQL例
SQL> SELECT * FROM hoge
  2> WHERE name between 0 and NULL;

レコードが選択されませんでした。
新人「じゃあ、INの中にNULLが入っている場合も0件になるの？」

私「INに関しては、NULLが入っていても、0件になるとは限らんで」
私「NULLであるレコードは取得出来ないけど、それ以外の指定された値は正常に取れるで」

私「IN句でやってることをORで書き換えるとわかりやすいかな、書いてみるわ」
SQL*Plus
--このSQLは1003のNULLさんは対象外となる
SQL> SELECT * FROM hoge
  2> WHERE name IN (NULL,1001);

ID   NAME
---- -----
1003 NULL

--INで記載されている内容と同等のSQL
SQL> SELECT * FROM hoge
  2> WHERE name = NULL -- NULL値を=で比較するため、こちらの比較結果は0件
  3> OR name = "たろう"; -- 「たろう」に合致するレコードはあるので、1件返却される

ID   NAME
---- -----
1001 たろう
私「INの中に記載されたもの全てを、OR条件で比較するからこういったSQLと同等になるはず」
私「但し、= NULLのデータは存在しないので、1003番さんは帰ってこないんや」

新人「なんとなく全容が解りました。多分、きっと、恐らく理解しました！」
私「うい。（なんやその自信のなさは…）」

後日

新人「先生、実際に動かしてみて、何故か想定と違う動きをするんですが…」
私「ちょっと画面見せて？」
SQL*Plus
SQL> SELECT * FROM hoge
  2> WHERE NOT(name = NULL);

レコードが選択されませんでした。
新人「name = NULLは全件、条件に対してfalseなんですよね？」
新人「その否定なので、trueになりませんか？」
新人「つまり、name <> NULLとかNOT(name = NULL)は全件取得になりませんか？」

私「お、おう、せやったっけな…？(俺もそう思うんやけど、やばい、わからん)」
私「ちょっとお腹痛いからトイレ行ってくるわ、その後で説明するわ（とっとと調べよ）」

3値論理を理解する

私「新人さんの言ってる通りや、なんで全件帰ってこないんや？」
私「そもそもそういえばなんで、NULLはis NULLで比較せなあかんのや…？」
私「とりあえず、isで比較する理由を調べたらそのうちわかるやろ(スマホで検索～)」

siri「お前、5年生のくせに3値論理を正しく理解してないやろ」
siri「SQLの論理体系は3値論理に基づいてるんやで」¹
私「なんや3値論理て」

siri「そもそも、NULLってのは、①男性の出産回数とかみたいな適用出来ない値、」
siri「②タモリの目の色、みたいな確認できない未知の情報のときに使う印（≠値）なんやで」
私「それはわかる、人間テーブルで例えると、①前者は宇宙人の出身国～、②後者は一部アイドルの実年齢～みたいな感じとかやな？」

siri「そこまで理解してるなら、よく考えたらわかるやろがい」
iPhone「NULLってのは値じゃないし、よくわからない不思議な印なんやで」
iPhone「よくわからない情報と比較する＝よくわからない結果やで」

私「確かに、タモリの目の色は黒？って聞かれたらわからんって答えるし、」
私「タモリの目の色は黒ではない色？って聞かれたらわからんって答えるな」²
私「そもそも、値として使っちゃ駄目という前提の地点で、上の例は破綻してるけど…」
私「つまり、true = falseはfalseやけど、true = nullはnullが帰ってくるわけやな」

その後

私「かくかくしかじか、タモリが云々ってことで、NOT(name = NULL);も、name = NULLも0件やで」
新人「はい、理解しました（こいつ調べてきたな）」

応用的な問題

私「ちゅうわけで、ちょっと応用になるけど、このSQLを実行した時の結果はどう出るかわかる？」
SQL*Plus
SQL> SELECT * FROM hoge
  2> WHERE name NOT IN(NULL,1001);
新人「えと、要約するとINの中に入っていないデータを全部取得するんですよね？」
新人「つまり、書き換えると、こういう意味になる、といった認識であってますか…？」
SQL*Plus
SQL> SELECT * FROM hoge
  2> WHERE name <> NULL 
  3> AND name <> 1001;
私「せやな、いずれにも合致しないデータになるから、ANDで≠になるで」

新人「で、name <> NULLは結果として合致するデータはなくて、」
新人「AND演算子だからこれは0件になりますか？」

私「正解、その解法であってるで」

かくして…

新人さんたちはSQLのNULLの比較方法を理解し、
その後のJavaで"is null"と記述し、コンパイルエラーになるのでした。
おしまい。

https://codezINe.jp/article/detail/532 ↩

タモリの目の色は何色なのか、画像から解析できませんでした。 ↩

id	name
1001	たろう
1002	じろう
1003	NULL

　 Tweet
Qiitaで続きを読む

20190523のSQLに関する記事は3件です。

1日1問解いてSQLをマスター！SQLの学習サービス Qdash を作った話

目次

はじめに

問題画面

回答画面

なぜ作ろうと思ったか

使っている技術

実装紹介

1日に1回、指定の時間に問題を切り替えて表示する

質問箱風なOGP画像を生成

これから

最後に

なんで、SQLで「カラム名 = null」,「カラム名 <> null」は０件なのか

何故、NULL は = で比較したらあかんのか

経緯

～(おさらい)本題に行く前に、WHEREってなんだっけ～

～(おさらい)WHEREでnullを=で比較するとどうなるんだっけ～

問題提起(なんで「カラム名 <> null」は0件なのか)

3値論理とは

UNKNOWNを比較してみよう

おまけ

何故、NULL は=で比較したらあかんのか

何故、NULL は = で比較したらあかんのか

経緯

おはなし

～where文でのNULL値の比較方法を教えていた時～

後日

3値論理を理解する

その後

応用的な問題

かくして…

日付別

タグ一覧