助けて＞＜ 3つのテーブルで簡単な集計

質問

下記のような３種類のテーブルがあります。
これを集計して、指定したユーザーの指定月のアクセス日時と売上げ金額を表にしたいと思っています。

こんな感じに。

+--+--------+----------+-------+-----+
| id | username| date 　 | access | sales |
+--+--------+----------+-------+-----+
| 1 | admin | 2010-11-13 | 　 2 |　5000 |
| 1 | admin | 2010-11-14 | 　 2 |　5000 |
| 1 | admin | 2010-11-15 | 　1 | 20000 |
+--+--------+----------+-------+-----+

SELECT u.id AS id, u.username, DATE(a.created_at) AS date, COUNT(*) AS access, SUM(s.amount) AS sales
FROM sf_guard_user u INNER JOIN sales s ON u.username = s.user_id LEFT JOIN access_log a ON u.username = a.user_id
WHERE (u.username = 'admin' AND a.created_at > '2010-11-01 00:00:00' AND a.created_at < '2010-11-30 23:59:59') GROUP BY date ORDER BY a.created_at;
とやってみたのですがダメでした；；

■ユーザー情報テーブル
mysql> select id,username from user;
+--+---------+
| id | username |
+--+---------+
|　1 | admin 　　|
+--+---------+

■アクセスログテーブル
mysql> select * from access_log;
+--+-------+--------+------------------+
| id | user_id | ip　　　　 | created_at　 　　　　|
+--+-------+--------+------------------+
|　1 | admin　 | 127.0.0.1 | 2010-11-13 21:56:54 |
|　2 | admin　 | 127.0.0.1 | 2010-11-13 21:56:54 |
|　3 | admin　 | 127.0.0.1 | 2010-11-14 21:56:54 |
|　4 | admin　 | 127.0.0.1 | 2010-11-14 21:56:54 |
|　5 | admin　 | 127.0.0.1 | 2010-11-15 21:56:54 |
+--+-------+--------+------------------+

■売上げ金額テーブル
mysql> select * from sales;
+--+-------+------+------------------+
| id | user_id | amount| created_at　 　　　　|
+--+-------+------+------------------+
|　1 | admin　 |　 5000 | 2010-11-13 21:56:54 |
|　2 | admin　 |　 5000 | 2010-11-14 21:56:54 |
|　3 | admin　 |　 5000 | 2010-11-15 21:56:54 |
|　4 | admin　 |　 5000 | 2010-11-15 21:56:54 |
+--+-------+------+------------------+

回答No.4

yuu_x · ベストアンサー

SELECT
s.user_id AS id
,username
,a.ip
,a.`date`
,access
,sales
FROM (
SELECT
user_id
,ip
,COUNT(ip) AS access
,DATE(created_at) AS `date`
FROM access_log
WHERE YEAR(created_at)=2010 AND MONTH(created_at)=11
GROUP BY user_id, `date`, ip
) AS a
LEFT JOIN (
SELECT
user_id
,SUM(amount) AS sales
,DATE(created_at) AS `date`
FROM sales
WHERE YEAR(created_at)=2010 AND MONTH(created_at)=11
GROUP BY user_id, `date`
) AS s
ON a.user_id=s.user_id AND a.`date`=s.`date`
JOIN user AS u ON a.user_id=u.id

view 作ってしまいたい気分だ。
何か引っかかるけどまぁいい。アクセスとセールスっておそらく一連の流れだから、まとめてしまってもいんじゃない。

回答No.3

yambejp

>10000円ですね

で、よければたとえばこんな感じでいけます。
（元テーブルの正規化が微妙な気がしますが）

select user.id , user.username, sub1.date , access , sales
from(
select user_id,date(created_at) as date ,count(*) as access
from access_log
group by user_id,date) as sub1
inner join (
select user_id,date(created_at) as date ,sum(amount) as sales
from sales
group by user_id,date
) as sub2 on sub1.user_id=sub2.user_id and sub1.date=sub2.date
inner join user on user.username=sub1.user_id
WHERE user.username = 'admin'
AND sub1.date between '2010-11-01' AND '2010-11-30'

回答No.2

yambejp

11/15の売り上げはなぜ20000円なのでしょう？

お礼

間違えでした、10000円ですね＾＾；

回答No.1

t-ka

DATETIME型をCASTかCONVERT・・でDATE型もしくは文字列型などに適宜変換する。
(2010-11-13 21:56:54)を(2010-11-13)の様に
例えば　CAST(created_at,DATE) AS aaa_time　など

　　　　　 SELECT , , GROUP BY CAST( , ) AS ex1 FROM access_log
　　　　　 SELECT , , CAST( , ) AS ex2 FROM sales

結合する。 JOIN　・・・　ON access_log.ex1 = sales.ex2

お礼

省略されててよく分からないんですけど、１つのSQL文じゃ難しいんでしょうか？
どうやって記述するんでしょうか？

また、言い忘れましたがアクセスや売上げの無い日付も存在します。
対応できますでしょうか？