とべ％MySQL学習中 です。

On Sat, 25 Dec 1999 09:44:24 +0900
"Isamu Narimatsu" <isamu@xxxxxxxxxx> wrote:

>成松です。Win98でMySQLを使用しています。
>ウェブサイトにバナーを表示する度、そして、バナーをクリックする度にMySQLにそ
>の会社コード等からなるレコードを追加しようと思います。
>そして、バナーのPV(表示回数)および、クリック数をクエリーで調べようと思いま
>す。

まず確認しておきたいのですが、それぞれのカラムの意味は
   id : ユニークキーを作るためで処理上の意味はない。
   comid : 表示/クリックされた会社コード
   １クリック or 表示ごとに新しく行をInsert
でよいのですよね。

結論から言うとこのテーブルの構成だとMySQLでは難しいのではないでしょうか。
なぜかというと、たとえば以下のようなレコードがあったとします。

 bpvlog             bclicklog
   +-------------+    +-------------+
   | id  | comid |    | id  | comid |
   +-------------+    +-------------+
   | 1   | 100   |    | 1   | 200   |
   | 2   | 200   |    | 2   | 100   |
   | 3   | 200   |    | 3   | 100   |
   | 4   | 200   |    +-------------+
   | 5   | 300   |
   +-----+-------+

この２つのテーブルを単純にJoinすると

   +------+-------+------+-------+
   |bpvlog        | bclicklog    |
   | id   | comid | id   | comid |
   +------+-------+------+-------+
   |    1 |   100 |    1 |   200 |
   |    2 |   200 |    1 |   200 |  #
   |    3 |   200 |    1 |   200 |  #
   |    4 |   200 |    1 |   200 |  #
   |    5 |   300 |    1 |   200 |
   |    1 |   100 |    2 |   100 |
   |    2 |   200 |    2 |   100 |
   |    3 |   200 |    2 |   100 |
   |    4 |   200 |    2 |   100 |
   |    5 |   300 |    2 |   100 |
   |    1 |   100 |    3 |   100 |   #
   |    2 |   200 |    3 |   100 |
   |    3 |   200 |    3 |   100 |
   |    4 |   200 |    3 |   100 |
   |    5 |   300 |    3 |   100 |
   +------+-------+------+-------+

となります。

>ここで、以下のようにSQL文を作成しました。
>
>select count(bpvlog.comid),count(bclicklog.comid) from bpvlog,bclicklog
>
ここでこのSQL文を発行しても上の表のbpvlog.comidとbclicklog.comidともに
値の入っている行数は等しくなっています。そのため結果の行数が等しく
なってしまいます。

>where 文で bpvlog.comid=bclicklog.comid としても右の値も左の値も同じになって
>しまいます。

where句にこの条件を追加しても上の表で"#"をつけたレコードだけが抽出される
ので結果的に両方の値は等しくなります。

>解決策としてはどのような、方法が考えられるのでしょうか？
>

Oracleとかのsubselectが使えるDBMSだったら、会社マスタを"M"として
  select (select count(*) from bpvlog where comid = M.comid),
         (select count(*) from bclicklog where comid = M.comid)
    from M
といった感じでできると思うのですがmySQLではまだ未対応かな？
3.23からsubselectに対応するような記述があったけどどうなのでしょう。

MySQLでやるとしたら中間に集計テーブルを用意して

 bpvcount
+----------+------------------+------+-----+---------+----------------+
| Field    | Type             | Null | Key | Default | Extra          |
+----------+------------------+------+-----+---------+----------------+
| comid    | int(11)          |      | PRE |         |                |
+----------+------------------+------+-----+---------+----------------+
| cnt      | int(11)          | YES  |     |  Null   |                |
+----------+------------------+------+-----+---------+----------------+

 bclickcount
+----------+------------------+------+-----+---------+----------------+
| Field    | Type             | Null | Key | Default | Extra          |
+----------+------------------+------+-----+---------+----------------+
| comid    | int(11)          |      | PRE |         |                |
+----------+------------------+------+-----+---------+----------------+
| cnt      | int(11)          | YES  |     |  Null   |                |
+----------+------------------+------+-----+---------+----------------+

INSERT INTO bpvcount 
  SELECT comid, count(*) FROM bpvlog GROUP BY comid;

INSERT INTO bclickcount 
  SELECT comid, count(*) FROM bpvlog GROUP BY comid;

としたあとで
 SELECT bpvcount.comid, bpvcount.cnt, bclickcount.cnt
   FROM bpvcount LEFT OUTER JOIN bclickcount 
           ON bpvcount.comid = bclickcount.comid;

でcomidごとの表示数とクリック数が集計できるとおもいます。
ただしこの文では一度も表示されずにクリックだけされたcomidがないことを
前提としています。

あとはテーブル自体の構成を変えて

blog(記録テーブル)
+----------+------------------+------+-----+---------+----------------+
| Field    | Type             | Null | Key | Default | Extra          |
+----------+------------------+------+-----+---------+----------------+
| id       | int(10) unsigned |      | PRI | 0       | auto_increment |
| comid    | int(11)          | YES  |     | NULL    |                |
| pv       | int              | YES  |     | NULL    |                |
| click    | int              | YES  |     | NULL    |                |
+----------+------------------+------+-----+---------+----------------+
というようなテーブルにして
 表示だったら (pv,click)を(1,Null)
 クリックだったら (pv,click)を(Null,1)
というようにすると

SELECT id, count(pv), count(click)
  FROM blog
  GROUP BY id;

といった感じで表示できると思います。

-----
  戸部 貴紀 <takanori@xxxxxxxxxx>

>