MySQL 2つのテーブルから情報を得る

質問

【質問内容】
出稿した求人広告に関する情報を収めたデータベース内に、以下の2つのテーブルがあります
※以下の文章を読むより、添付画像をご覧頂いたほうが分かりやすいと思いますが

1.出稿した求人広告の情報
テーブル名：ad_info
id　ID
plan　広告プラン名
date_from　DATE 広告開始日 2011-12-16
date_until　DATE 広告終了日 2011-12-31
price　広告料金

2.求人サイトへのアクセス情報（広告掲載期間のみ毎日2時間おきに収集）
テーブル名：daily_access
id ID
created_at DATETIME　クローリングした日時 2011-12-16 17:00:00
pv プレビュー数
applicants 応募者数
occupation_ranking 職種別画面での自社の掲載順位
newly_ranking 新着企業画面での掲載順位

この2つのテーブルからad_infoの広告ごとに（その掲載期間ごとに）、daily_accessのデータを集計したいと思っています。しかしそのためのSQL文がうまく書けず、詰まっています。

どうか良い方法を教えていただけませんでしょうか。

あるいは「テーブル構造自体を変えたほうがいいよ」、「SQL文の書き方が良くないよ」など直接関係のないことでも大歓迎です。
よろしくお願いします。

【MySQLバージョン情報】
$ mysql --version
mysql Ver 14.14 Distrib 5.5.16, for Linux (x86_64) using EditLine wrapper

【テーブル作成文】
CREATE TABLE `ad_info` (
`id` INTEGER NOT NULL AUTO_INCREMENT,
`plan` VARCHAR(30) DEFAULT NULL,
`date_from` DATE DEFAULT NULL,
`date_until` DATE DEFAULT NULL,
`price` INTEGER DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `daily_access` (
`id` INTEGER NOT NULL AUTO_INCREMENT,
`created_at` DATETIME NOT NULL,
`pv` INTEGER DEFAULT NULL,
`applicants` INTEGER DEFAULT NULL,
`occupation_ranking` INTEGER DEFAULT NULL,
`newly_ranking` INTEGER DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

回答No.2

yambejp · ベストアンサー

ちなみに今読み解ける範囲ではこんな感じでしょうか？

SELECT
i.date_from,
i.date_until,
i.plan,
sum(d.pv) as pv,
sum(d.applicants) as applicants,
avg(d.occupation_ranking) as occupation_ranking,
avg(d.newly_ranking) as newly_ranking,
price
FROM ad_info as i
LEFT JOIN daily_access as d
ON date(d.created_at) BETWEEN i.date_from and i.date_until
GROUP BY i.id

お礼

こちらを試させていただきましたが、まさに求めていたものそのものです。

| date_from | date_until | plan | pv | applicants | occupation_ranking | newly_ranking | price |

| 2011-12-13 | 2011-12-26 | ベーシックプラン | 2322 | 34 | 77.2330 | 72.4078 | 52500 |
| 2012-01-01 | 2012-01-14 | ベーシックプラン | 144 | 7 | 67.7500 | 72.1250 | 52500 |

LEFT JOINとON、ASの使い方、GROUPにidを指定こと。
基本的なことなのでしょうが、大変参考になりました
。ありがとうございます。

回答No.1

yambejp

申し訳ない、画像が読めないのでテキストに書き起してもらえますか？

補足

ご回答ありがとうございます。
Googleドキュメントで画像を公開しましたのでそちらでご覧下さい。

https://docs.google.com/document/d/1cH3LH3qyAzSE67pT5dCM9_jtoqv67fGHJ5gId9WsVwU/edit