煩雑な集計のクエリ

最終的に集計を行いたいのですが、
集計対象のデータの抽出と実際の集計のやり方がいまいちピンときません。

ご教示頂けますでしょうか。

テーブルはこうなっております。

*** test_table *****
id　　order_id　　price　　date
1　　　aaa　　　100　　　2009 4/1 00:00
2　　　aaa　　　200　　　2009 4/4 00:00
3　　　aaa　　　150　　　2009 4/9 00:00
4　　　aaa　　　400　　　2009 5/9 00:00
5　　　ccc　　　900　　　2009 3/1 00:00
6　　　ccc　　　500　　　2009 4/5 00:00
7　　　ccc　　　200　　　2009 4/7 00:00
8　　　www　　　100　　　2009 4/1 00:00
9　　　www　　　200　　　2009 4/9 00:00


１．order_idが同じものの中で一番若いdateが 2009/4/1 00:00-24:00 である order_id の取得
　⇒1行目と8行目が該当するので、「aaa」 と 「www」 を取得。

２．１のorder_id で、dateが、2009/4/1 00:00 - 2009/4/30 24:00 の期間である price の合計値の取得
　⇒order_id=aaaの時、1～3行目が該当するので、「450」、
　　order_id=wwwの時、8～9行目が該当するので、「300」を取得。


可能であれば、以上２つのことを１つのクエリで発行できるのかということも知りたいです。

No.1 回答者： mindatg 回答日時： 2009/05/15 23:36

ぱっと、思いついたので合ってるかどうかは知らんですが

SELECT *, SUM(price)
FROM test_table
GROUP BY order_id
WHERE (date BETWEEN '2009 4/1 00:00' AND '2009 4/1 24:00')
/* WHERE DATE_TRUNC('day',date) = '2009 4/1' */
/* MySQLだとTRUNC('day',date)かな？ */

参考程度に。
＞以上２つのことを１つのクエリで発行できるのか
余裕っす

likeで日付ごと、月ごとってのも思いついたんですがコストが掛かりそうです。

No.2 回答者： chukenkenkou 回答日時： 2009/05/16 12:10

ここのカテゴリ通り、RDBMSはMySQLなのでしょうか？

バージョンは？

>1　　　aaa　　　100　　　2009 4/1 00:00

年月の間に空白があるのは、記載ミスですか？
date列のデータ型は、何ですか？もし、datetimeであれば年月の間に空白は許されないし、varchar等を使っているのですか？もしそうであれば、年月日などのデータ形式はどうなっているのですか？

>一番若いdateが 2009/4/1 00:00-24:00

これも記載ミスでしょうか？
それとも、00:00と24:00という、同じ意味の異なる表現が存在するのですか？

>可能であれば、以上２つのことを１つのクエリで発行できるのか

MySQL 4.1以降であれば、サブクエリが実装されているので、1文で可能です。MySQL 4.0以前なら、一時表を使ったりと、事前準備が必要になります。

No.3 ベストアンサー 回答者： n09430515 回答日時： 2009/05/17 15:31

> ２．１のorder_id で、dateが、2009/4/1 00:00 - 2009/4/30 24:00 の期間である price の合計値の取得

この２の取得を「dateが、2009/4/1 00:00 - 2009/4/30 24:00 の期間である order_id 毎の price の合計値の取得」にして１の取得結果とorder_idどうしでJOINする、と考えれば煩雑ではないでしょう。

１…
select order_id, min(date) from test_table
group by order_id
having min(date) >= '2009 4/1 00:00' and min(date) < '2009 4/2 00:00'

２…
select order_id, sum(price) from test_table
where date >= '2009 4/1 00:00' and date < '2009 5/1 00:00'
group by order_id