24時の鐘が鳴っても帰らない シンデレラ、そういう存在に私はなりたい。
当たり前のように日付跨ぎだしたな。。。 今日は22時過ぎから書いてたのですが 色々右往左往してたら過ぎ過ぎの過ぎになりました。
記事名は流行りの転生モノです。 転職って転生みたいなもんやないですか。 ええんですよ。
実際、特に経済が発達している国 つまりここでは日本を指します。
この日本においては、”就職”というものが 非常に重要視されます。所属している企業 年収、役職、全てステータスとして見られるわけです。
自営業という亜種もいますがこちらは 戦士というに等しく畏怖、いや尊敬の念を抱く存在ですが 別の生き物でありますので割愛致します。
そのような重要なステータスの発行元の 所属企業を変更する、これが転職と言われるものです。 これ、もう転生といって差し支えないと思います。 自分を見られる要素が全て一新されるわけです。 社会的価値のUPDATEです。 あるいは新レコードのINSERTというわけです。
現代の転生ものの流行、 これは現代人の転職ブームを 予知しているのかもしれませんね。
現代における転職は即ち、転生に等しい。 この考え方はこのビルに埋められた日本、 お金が全てな日本という国に刺さるのではないでしょうか。
刺さりませんね。たまになんで自分はまだ 精神病院に通っていないか疑問に覚えます。 これは即ち社会が私を常人として扱うからですね。
さて、前述のことから分かる通り転職を果たしております。 して、SQLに関わることもあるのですが、 そんな矢先にSQLで除外をしたいケースが生まれました。
除外したい内容は以下のイメージ
・商品リストに対し、 購入者の購入商品を除外して表示したい。
・別の言い方をすると、購入者Aさんが 未購入の商品を表示したい。 こんな感じです。 商品テーブル、購入履歴テーブルの2つが存在しているとして、 購入リストはあるが、未購入リストがない。 私がほしいのは未購入リストなわけです。 だって未購入のものを売りつけないといけませんからね。 購入済のものを検索しても仕方がないわけです。 賢いなぁ私は。
さて、一転して賢さがマイナスを振り切る自体に 陥りました。SQLの内容が思いつかなかったのです。 今の私は一度でもSQLの発行でミスをすると 即退職になるので、絶対に間違うわけにはいきません。 どんなに処理が長く細かく複雑になろうと 実行結果が正である必要があります。 では、以下からSQLの設計思想と ダミーのテーブルなどによる実行結果を貼っていきます。 ここまでが前置きです。 ■SQLで必要なもの
まず、SQLの構築、つまりDBから どの情報が欲しいか。です。 今回必要なのは ・ユーザー ・未購入商品の商品情報 この観点のカラムが出力できれば成功です。
そして、前提としては上記の 2つのテーブルが存在しています。 商品テーブルとユーザーの購入履歴テーブルですね。
ここで、実際にSQLを実行していきたいと思います。 手始めに、 商品テーブル = ITEM 購入履歴テーブル = BUYER というテーブル名でまずテーブルを作成します。
今回使用させて頂きますのは、 MySQL?ORACLE? NO,It’s NO.
ブラウザで簡単にSQLが発行できるDokoqlさんをお借りしました。 URL: https://dokoql.com/d/Dokoqlv/index.html
こちら、SQLの勉強に利用する場合に、 環境準備の手間が皆無（ユーザー登録すら不用） ということでSQLを0から始めるにはとてもいい環境です。 DB運用とか考えたら勿論自分で構築すべきなのはわかりますが、 どうかその一言は今だけは言わないで頂ければと思います。
さて、まずは初期化とテーブル作成で以下のSQLを発行します。
–初期テーブル削除 DROP TABLE 家計簿; DROP TABLE 家計簿アーカイブ; DROP TABLE 家計簿集計; –商品テーブル作成 CREATE TABLE ITEM ( ジャンル VARCHAR(20), 名称 VARCHAR(20), 価格 INTEGER); –購入履歴テーブル作成 CREATE TABLE BUYER ( ユーザー VARCHAR(20), ジャンル VARCHAR(20), 名称 VARCHAR(20), 価格 INTEGER); —
※試験的によくあるプログラム書いてるブログの 真似をCSS抜きのテキストで再現するため、 背景色と文字色をそれっぽくしてみました。
次に、使用するレコードをINSERTしてみます。
–商品テーブルに値を追加 INSERT INTO ITEM (ジャンル,名称,価格) VALUES (‘青果’,’メロン’,100), (‘青果’,’バナナ’,200), (‘青果’,’りんご’,300), (‘青果’,’スイカ’,400), (‘青果’,’みかん’,500), (‘精肉’,’牛肉’,500), (‘精肉’,’豚肉’,400), (‘精肉’,’鶏肉’,300), (‘精肉’,’鹿肉’,200), (‘精肉’,’猪肉’,100); –購入履歴に以下略 INSERT INTO BUYER (ユーザー,ジャンル,名称,価格) VALUES (‘私’,’青果’,’メロン’,100), (‘私’,’青果’,’バナナ’,200), (‘私’,’精肉’,’牛肉’,500), (‘私’,’精肉’,’豚肉’,400), (‘我’,’青果’,’りんご’,300), (‘我’,’青果’,’スイカ’,400), (‘我’,’青果’,’みかん’,500); —
画面上のテーブルのところがこうなっているかと思います。
さて、それではSQLを発行したいと思います。 実行結果として望ましいのは、 私さん、我さんが未購入のものの商品データですので、
私さんが 青果から、リンゴ/すいか/メロン 精肉から、鶏肉/鹿肉/猪肉
我さんが 青果から、メロン/バナナ 精肉から、牛肉/豚肉/鶏肉/鹿肉/猪肉
の１3レコードを取得したい。 という感じです。
さて、SQLの発行をするわけなんですが、 最初に思いついたのが以下です。
SELECT BUYER.ユーザー,ITEM.* FROM ITEM LEFT JOIN BUYER ON ITEM.ジャンル = BUYER.ジャンル AND ITEM.名称 <> BUYER.名称 –ここで除外したつもり ORDER BY BUYER.ユーザー;
まぁ、取得結果はひどいものです。 商品リストに対し、購入履歴からジャンルで 結合させたあとに、購入履歴と名称が不一致なもの。 という風に結合しています。 おかげで、青果同士で絞ったとしても 例えば「リンゴ」と不一致のものは 商品リストに存在するその他4レコード 全てが該当して取得されているわけです。
もうぐちゃぐちゃな結果なわけです。
さて、ここで考え方の過ちの１つなんですが、 テーブルの結合条件の記述です。 これはいつ条件を絞るかの違いでしかなく、 機能的にはWHERE句と同様の機能を果たします。 ※結合時に条件を絞ると絞ってから結合 WHERE句で書くととりあえず結合してから絞る 付き合ってからHするかHしてから付き合うか、 みたいな話ですね。うわ、書いてて最低だなこれ。
つまり、WHERE句の役割 即ち取得条件の絞り込みなわけですが、 ＜＞,!= これらの否定条件はあくまで “否定”でしかなく、”除外”とは違うわけです。 この絞り込みはまさに不等号による比較ではなく、 IN句や、EXISTSによる対象の絞り込みが必要になります。
絞り込む前に、上記のSELECT文は 重複も多いので、一旦DISTINCTを行ってもう一度見てみましょう。 DISTINCT後実行結果
これ、私さんに視点をつけると、 商品テーブルの全商品に私さんの 名前を付与しているような状態です。 つまり、商品テーブルでユーザーを 特定できているような状態ですね。
しかし、我さんの方はジャンルの結合条件の際、 精肉のジャンルを購入履歴で保有していないため、 青果のみが出力されていますね。
私さんはここまでくるとこの結果に対し、 購入結果に存在するレコードをNOT EXISTSでも 条件でつけてあげれば仕上がります。
ということは我さんも全商品に対して ユーザー名を付与できてしまえばいいわけです。 ここで過ちその2なんですが、 ジャンルの結合は不要だったわけです。 結合条件からジャンルを削除して、 更に購入履歴のレコードは除外してみましょう。 除外の時は異ジャンル。同名称商品の存在を 考慮して条件は両方設定します。 発行するSQLはこちら
SELECT TABLE1.* FROM (SELECT DISTINCT –最初に使用したSQLを流用 BUYER.ユーザー,ITEM.* FROM ITEM LEFT JOIN BUYER ON ITEM.名称 <> BUYER.名称 ) AS TABLE1 WHERE NOT EXISTS (SELECT BUYER.* FROM BUYER WHERE TABLE1.ユーザー = BUYER.ユーザー AND TABLE1.ジャンル = BUYER.ジャンル AND TABLE1.名称 = BUYER.名称) ORDER BY TABLE1.ユーザー;
この時、最初に使用したSELECT文を流用しますので、 丸ごとインラインビューにしてみました。 その上で、インラインビュー内の ユーザー・ジャンル・名称 3つが一致するレコードは取得対象外としています。
実行結果
はい、これで想定通りのレコードが取得できました。
ただ、こんなやり方でやるのはおかしいな。と思うのですよね。 購入履歴に存在しないユーザー単位の商品を取得する。 もっとスマートな方法があるように思えるのです。 商品テーブルにユーザー名を付与して購入履歴から除外する。 この考え方はそれほどおかしくないと思うのですが、 商品名の不一致という結合は無理やり感が強いと言うか。。。。 後DISTINCT使うのもなんか違う気がします。 上記は整理の為に、みたいな文言で DISTINCTを使用していますが、実際インラインビュー内か SELECT文そのものにDISTINCTを書かないと 重複レコードが発生します。
釈然としないまま結果が成功してしまった。 という結果なのでいまいちわかっていないというお話です。
因みに、今回使用させて頂いたDokoQL様ですが、 INSERT文の書き方などで気づく方もいるんじゃないかと 思いますが、使用されているのはPostgreSQLです。 INSERT文1つで複数投入できるの好きです。
Adieu.
Comments