1. ホーム
  2. のらマネージャーのブログ
  3. WebDBを作ろう

WebDBを作ろう

第13回 相関を探そう!SQLちょっと応用講座

2002年1月15日 12:00

さて、前回はサイトの流れに沿って必要なSQLを紹介していきました。
こういう、SQLで行う仕事を一般にはOLTPといいます。まぁリアルタイムで発生するトランザクションを捌くことが目的です。
Web-DBにとって当然これも重要なのですが、やはりWeb-DBがWeb-DBであるメリットを考えると、このアクションを通じて集まったデータを分析してサービスに反映させたいところです。そういう作業を一般にはデータマイニングとかOLAPとかいいます。
そもそも、こういうのをやるためにWeb-DBを作るといっても過言じゃありません。 ここでは、そういうものの基礎になる相関の探し方を、いくつかを、あらっぽく紹介します。
ほとんどselect文を論理的に並べるだけです。 特にwhere句のなかの作り方がポイントになります。 selectを制するものは世界を制するといったところでしょうか。

○ある商品と一緒によく買われる商品を探す。
これが出来ると便利ですよね。ある商品のページに、その商品へのリンクつけたりすればうまくいきそうですよね。

購買履歴テーブルがポイントになります。

一緒に買う商品というのは、以下のように定義できます。
1.まず、ある商品のIDでレコードをとってくる。
2.次にそのレコードの日時をみて、その日時に同時に買った商品をリストアップする。
3.そこで一番登場頻度の高い商品が、一緒に買うことが多い商品である。

ですので、ここで見るのは、購買履歴テーブル一つで十分です。

この言葉を、SQLで実現するとどうなるでしょう?
まず、1は
 select 日付 from 購買履歴テーブル where 商品ID=ある商品のID
となります。
 次に、2は
 select 商品ID from 購買履歴テーブル
 where 日付= (select 日付 from 購買履歴テーブル where 商品ID=ある商品のID)
となります。
後は一覧を見て、手で数えても良いですが、SQLの機能は活用しましょう。
 select 商品ID,count(*) from 購買履歴テーブル
 where 日付= (select 日付 from 購買履歴テーブル where 商品ID=ある商品のID)
 GROUPR BY 商品ID
とやると、各商品IDと、カウント数が並んで出てきますので、それを比較すればすぐ分かります。

○ある商品を買う人の嗜好にあった商品を探す。
これも、上のような使い方ができますよね。結構便利です。

これも購買履歴テーブルだけでも十分行けます。
ある商品を買う人の嗜好にあった商品は以下のように定義できます。
1.ある商品を買った人の顧客IDをとってくる。
2.その顧客IDが買った商品の一覧を取ってくる。
3.そこで、登場頻度が高い商品がその人の嗜好にあった商品である。

この定義も、SQLで実現してみましょう。
 1.select 顧客ID from 購買履歴テーブル where 商品ID=ある商品のID
 2.select 商品ID from 購買履歴テーブル where
  顧客ID=(select 顧客ID from 購買履歴テーブル where 商品ID=ある商品のID)
 3.select 商品ID,count(*) from 購買履歴テーブル where
  顧客ID=(select 顧客ID from 購買履歴テーブル where 商品ID=ある商品のID)
  GROUPE BY 商品ID
このときに、恐らく1番に来るのは、ある商品それ自体が来ますので、実際には2番目に来る商品が問題になる商品となります。

○ある人の嗜好にあったいままで買ったことがない商品を探す。
マイページに実装したり、メールでのリコメンドとかに活用したいですよね。

これも購買履歴テーブルだけでも十分行けます。
ある人の嗜好にあった商品は以下のように定義できます。
1.ある人が買ったことがある商品IDをとってくる。
2.この商品を買ったことがある顧客IDを取ってくる。
3.この顧客IDの人が買ったことがある商品一覧を取ってくる。
4.次に、1の商品IDを全て削除する。
5.残ったもののうち、登場頻度が高いものが、嗜好にあった買った
  ことのないものである。

どんなSQLになるかは、練習問題ということで。
次回からは、こうしたことを踏まえて実際に作る講座に移ります。 いままで全然分からなかった人も、次回以降進みながら読み返してもらえると また理解できるかと思います。----- EXTENDED BODY:

第12回 テーブルを使おう!SQL基礎講座

2001年12月15日 12:00

さて、先のテーブルの作りは何となく分かって頂けたかと思います。
次に、このテーブルの中身を入れたり操作したりということが必要になって来ます。 それをやるのがSQLです。SQLの基本はデータを
「参照する」「追加する」「変更する」「削除する」
の4つからなります。例えば、通販サイトのことを考えてみましょう。

当然、はじめに、商品情報テーブルへ商品情報を「追加(1)」します。また、場合によっては、一度入力した商品情報を「変更(2)」したり、廃盤になれば「削除(3)」もすることがあるでしょう。
そして、次に、お客がサイトに来ます。お客のIDなんかを発行したりします。そしてそのIDと顧客情報を、顧客情報テーブルに「追加(4)」します。当然お客は、商品情報を検索して「参照(5)」します。そして、その「参照(6)」したものが気に入れば、ショッピングバスケットに「追加(7)」して行きます。気が変わって、バスケットから出す(「削除(8)」する)こともあるでしょう。
そして決済して、商品購入が完了します。
つぎは、ショップオーナーが、注文情報と購買履歴を「参照(9)」し、 必要な商品箱詰めします。顧客情報を「参照(10)」して送り先を確認して 送付して、ばんざーい、1件終了。
以下エンドレス......。

と、なるわけです。このとき、データベースに働きかけるSQLのコマンドは 以下の4つになります

参照:select
  構文:select フィールド名or演算式 from テーブル名 where 条件式
  意味:テーブルの中から条件式を満たすレコードを取りだし、指定した
     フィールド名の内容、もしくは演算結果を出力する。
  例文:select * from table where i=1

追加:insert
  構文:insert into テーブル名 (フィールド名) values (値)
  意味:テーブルへフィールドと対応する値をそれぞれ持つレコードを
     挿入する。
  例文:insert into table (f1,f2,f3) values (1,'a',0)

変更:update
  構文:update テーブル名 set フィールド名 = 値 where 条件式
  意味:テーブルの中で条件式を満たすレコードの指定したフィールド名
     の内容を値に変更する。
  例文:update table set f1=2 where i=1

削除:delete
  構文:delete from テーブル名 where 条件式
  意味:テーブルの中で条件式を満たすレコードを削除する。
  例文:delete from table where i=1

そうすると、上で示したアクションは、それぞれ以下のような感じのSQLへと書きかえることができます。(文法的に正しいというわけではありません。意味合いとしてです。)

(1) insert into 商品情報テーブル values (商品情報もろもろ)
(2) update 商品情報テーブル set 変更内容 where 商品ID=変更商品ID
(3) delete from 商品情報テーブル where 商品ID=削除商品ID
(4) insert into 顧客情報テーブル values (IDほか顧客情報)
(5) select * from 商品情報テーブル where ジャンルID=今見ているジャンル
(6) select * from 商品情報テーブル where 商品ID=個別商品ID
(7) insert into 購買履歴テーブル values (顧客IDと商品ID)
(8) delete from 購買履歴テーブル where 商品ID=個別商品ID
(9) select * from 購買履歴テーブル where 顧客ID=今買い物してる人のID
                      and 購入済フラグ='no'
(10)select * from 顧客情報テーブル where 顧客ID=今買い物した人のID

こういう簡単なSQLへと上記のアクションをデータベースの言葉に書き返ることができます。本当はもっと厳密に記載しないと動かないのですが、今日のところはこんな感じの理解で問題ありません。
次回は、もう少し複雑なSQLを紹介します。----- EXTENDED BODY:

第11回 テーブルを作ろう

2001年11月15日 12:00

今回はDB上のテーブル(表)です。
データベース、特にRDBMSにおいて一番基本になるオブジェクトです。とは言っても、具体的なテーブルを作るコマンド云々の話でもありません。
取りあえず、テーブル設計の基礎の基本と言ったところでしょうか。
ユーザーの挙動を「ある仮説に基づいて」テーブルにぼんぼん放りこむことがユーザーを知る具体的なプロセスです。

「んなこと言ったって、ユーザーのことについての仮説を考えたいからWeb-DBを使いたいんじゃん。仮説がはじめにあるんだったら誰も苦労せんわい」

という方、ごもっともです。この世界では、いくつかの暗黙の了解というか、暗黙の仮説がいくつかあります。それに基づいて基本的テーブルをまず決めて行くことが重要です。まず明確な仮説を通販向けサイトを例にしていくつか。

1・Web上でのユーザーの挙動はユーザー自身の何らかの性質をあらわす。
2・商品を買うという行動は、ユーザーの性質をあらわす挙動である。
3・商品を見るという行動も、ユーザーの性質をあらわす挙動である。

小難しく書きましたが、1は要はWebでなんかやるという行動が、ユーザー自 身のなんかを表しているということ、2、3は買い物やそれに準ずる行動がユーザーを代表する物であるとみなすと決めていること。
要は、この、1、2、3をあらわすテーブルを作ることがはじめの1歩であるということです。通販じゃないと、2、3の部分がまた違う仮説が必要になります。

では、まず1をあらわすテーブルから作ります。ユーザーの基本情報のテーブルがそれに相当します。
一般にはこんな感じでしょうか。

-----列名------+--中身--
ユーザーID  :一意キー
ユーザー名   :文字列
メールアドレス :文字列
パーミッション :On-Off
住所      :文字列
郵便番号    :文字列
電話番号    :文字列

列名というのはテーブルの要素です。
その要素がどうなっているのかというのを中身と表現しておきましょう。
ここで重要なのがユーザーIDという物。一意キーであるということが非常に重要です。この世にそのユーザーIDを持つユーザーが一人しかいないということを意味します。

次に2をあらわすテーブルを考えましょう。これは、二つのテーブルが必要になります。
ひとつは商品マスターに相当する商品自体のテーブル(商品テーブル)。
もうひとつは、度のユーザーが度の商品を買い物したかということを残すテーブル(購買履歴テーブル)が必要です。

商品テーブル
-----列名------+--中身--
商品ID    :一意キー
商品名     :文字列
仕入値     :数値
売価      :数値
紹介文     :文字列

購買履歴テーブル
-----列名------+--中身--
購買履歴ID  :キー
商品ID    :外部キー
ユーザーID  :外部キー
購入日     :日付

ここで最大のポイントは、購買履歴テーブルです。
色々組み立て方はあるのですが、今回はこういう組み立て方を紹介します。
ポイントは、キーの使い方です。購買履歴IDはただのキーです。
一意ではないのでいくつも同じ数字が存在します。
ただし考え方としては1回の買い物で同じキーを使います。
3つの商品を一度に購入すれば、3つの同じ購買履歴IDを持ったレコード(テーブルの中身)が出来あがります。
そしてもう二つが外部キーの商品IDとユーザーID。
今まで作ったテーブルの商品IDとユーザーIDに相当します。
要するにID番号だけ指定すれば、誰が何を買ったのかが明確に決められると言うわけです。
次に3番のテーブルを作るということを続けます。これは2の問題の応用ということで今回は解答は書きません。
ただこのテーブルは、本当に基本的な仮説だけでしか出来ていませんので、サイトの発展に応じて気長に改変して行く必要があります。
ここまででようやくテーブルが出来たのですが、このテーブルの中身ってどうやって入れて行くの?ということが次に必要です。
それと同時に、このテーブルにたまった中身からどうやって情報を引き出すの? ということも必要です。

順を追って次回は、テーブルへ中身をためる方法を解説します。また後の回で、テーブルを作る実際の作業を紹介できれば良いなと思います。----- EXTENDED BODY:

第10回 お客のことを知ろう

2001年10月15日 12:00

さて、前回、お客のことを知るためにまずお客を特定しましょう。
ということで、各種技術を紹介しました。次にお客についての様々なことを知る必要があります。
たまったデータベースからお客のことをどのように理解すればいいのでしょうか?

今回は具体的手法ではなく、その考え方について解説していきます。まず、お 客があって、お客の買ったものというのがデータに溜まっていくとします。どのように理解していけば、お客への次のアクションに繋がるルールを発見できるでしょうか?

1・傾向を知る
これが一番基本的です。個々のお客が、何をどれくらいかったのかを時系列なんかで見れば、次どれくらい買ってくれるかをある程度予想できます。 当たるかどうかは別にして。

2・関連を知る
たとえば、商品間の関連を知ることも可能です。あるお客さんが、一緒に買う 可能性の高い物を調べれば、そのお客さんがあるものを買ったとき、一緒にその商品を紹介すれば、買ってくれる可能性が高くなるわけです。

3・仲間を知る
このへんから、高度な話です。が、簡単に流しましょう。たとえばAさんとBさんの買う製品がよく似ていたとします。AさんとBさんを仲間とみなしてお きます。あるとき、Aさんがとある新製品を気に入ってかったとします。そうすると、仲間とみなせるBさんもその製品を知れば買うかもしれません。

4・あきらめる
これが最新技術の賜物です。いわゆる、ガラガラポン!というやつです。データを放りこむと、勝手にルールが出てくる物もあります。便利そうですが、使えないルールもいっぱい出てくる不思議な手法です。

実際の現場では、1~3を組み合わせた考え方でお客のことを分析します。
こうした手続きでルールを見出す活動やツールをデータマイニングやOLAPと呼びます。とうぜん、お客とその商品だけの関係以外にも、多種多様なデータを用いて行われます。
次回は、Web-DBでこのような手法をどのように実装して行くかをお話したいと思います。----- EXTENDED BODY:

第9回 ユーザーを知る

2001年9月15日 12:00

さて、まずインターネットのユーザーの挙動を知るために必要なことはなんでしょう?
そうです、まずそのサイトにきた人を特定することが重要です。インターネット社会は匿名の社会といわれるように、放っておくとサイトにきた人を区別す ることは不可能です。まず、サイトにきた人を特定する「仕掛け」が必要です。今回は、この特定する仕掛けについて色々お話をしたいと思います。

実はこのWeb-DBが、うまく行くかどうかをこの仕掛けが左右すると言っても過言ではありません。
ユーザーを完全に特定できると、そのユーザーについての あらゆる情報をDB上に蓄積することが出来ますので、そのユーザーの趣味嗜好が丸裸になるようなもので、ちょっと人には言えないサイトに行った履歴や、 その人の実名住所電話番号なんかとセットでそれを持つことで、「あー。うちのサイトに来たAさんは○○な趣味をもつんだなぁ」なんてこと が丸分かりだったりします。これが知りあいだったりなんかしたら、目も当てられないわけです。ですから、ユーザーの方も特定されることを嫌いますし、企業側も蓄積した情 報を他にもらさないモラルが必要なわけです。
では、ユーザーを特定する方法をいくつか紹介します。

ID、パスワードでのログイン----------------------------
よくあるパターンです。会員登録とかさせてIDとパスワードを発行します。で、そのユーザーがサイトに来るたびにIDとパスワードを入力させて、そのサイト に来た来訪者を特定します。ID発行時に会員登録などでそのユーザーの現実世界での個人情報を入手できるので比較的有効なDBを構成することが出来ます。 その一方で、サイトに行くたびにIDとパスワードを聞いてきますので、ある意味非常にうっとうしいシステムです。
企業内のイントラネットを使ったWeb-DBやよっぽど特殊で魅力あるコンテンツの場合は有効ですが、普通のサービスではユーザーが引いてしまうこと請合い です。また、ID、パスワードを忘れる人もいますので、同じ人が何度も登録してしまうことがあります。そうすると、DBのなかに同姓同名の人が何人も居る 不思議な世界になってしまいます。

IPアドレス、サーバーネーム------------------------------
ユーザーにばれずに、個人を特定するひとつの手法です。基本的に、サーバーにアクセスすると、どこの端末からアクセスしているのかという情報が、サーバーに到達します。どこの端末なのかを特定するものの手がかりがIPアドレスやサーバーネームと言われる物です。
こればっかりは、どうやっても必ず手に入りますので、苦労なく、同一の端末からきている人を区別することが出来ます。
この手法ユーザーに知られないという特徴がありますが、こっちもユーザーのことがあんまり分かりません。どこの誰だか分からない人が二人居た場合、二人が「違う」ということ区別がつきますが、それぞれが「誰か」は区別がつきません。最大の欠点はダイアルアップユーザーに対しては役に立たないということです。
広く浅いユーザー解析や、他の手法と組み合わせると非常に有効な手法です。

クッキー-------------------------------------------------
食いもんじゃありません。有名ですから皆さん御存知だとは思います。PCに特定のファイルを持たせて、そのサイトに来たときに、そのファイルに情報を書きこんだり、再訪者の場合そのファイルから特定番号を読みこんだりします。これは非常に強力なユーザー特定手法です。
初来訪かどうかの区別が簡単につきますので、初来訪者はプロフィールを入力させて、二回目以降は何も意識しないで、普通にユーザーがサイトを楽しむこ とが出来る様に出来ます。当然、個々のPCに仕掛けを施しますのでダイアルアップユーザーでも、この手法は有効です。
ただ、余りにも何も意識しないで色々な情報を奪われるのでユーザー側が過剰に嫌っている傾向もあります。

現行の大半のWeb-DBはこの三つが特定方法の主流と言っても過言ではありません。この他にも、ハードディスクの製造番号を読み取る方法とか、特定のUSBキーみたいなものを着ける方法など、色々あります。Web-DBで提供するサービスとその対象を見比べてユーザー特定方法を決めて行く必要があります。
ここでは扱いませんが、近年だとBtoBにおいてはセキュリティのトレンドからPKI(公開鍵インフラストラクチャ)を用いた、証明書などを使うことも増えつつあります。

次回は、こうして特定したユーザーの挙動を分析する手法をいくつか紹介します。----- EXTENDED BODY:

アイリンクへのお問い合わせ

お問い合わせメールフォームはこちら

2016年12月移転 北海道旭川市神楽1条7丁目4−8 お問合わせはメール・SNSアカウントで mail
  • twitter
  • facebook
  • google
  • noimage
  • noimage
  • noimage