SQLでの集計についての質問です。

データベースはMySQL5.0です。

-id
-quatity
-datetime(DATETIME)

このようなテーブルにレコードが数万から数十万件あります。
ここから、年別(2008)、月別(2008-04)にデータを集計したいのですが、現在以下のようにしています。

SELECT SUM(quantity) FROM TABLE WHERE SUBRSTRING(datetime,1,4) = '2008'
SELECT SUM(quantity) FROM TABLE WHERE SUBRSTRING(datetime,1,7) = '2008-04'

データが増えればそれ相応に処理に時間がかかるのですが、それが遅いのか早いのか分かりません。

そこで質問なのですが、このやり方はオーソドックスなのでしょうか?
年度別、月別の集計において、テーブル設計も含め、何か定石のようなものがあれば教えてください。

回答の条件
  • 1人5回まで
  • 登録:2008/04/13 10:30:41
  • 終了:2008/04/15 10:46:57

ベストアンサー

id:chuken_kenkou No.2

chuken_kenkou回答回数722ベストアンサー獲得回数542008/04/13 16:20:04

ポイント150pt

日付の範囲検索や日付演算を行う場合、年月を別の列に分けると、検索条件がやや複雑になります。

今回の場合、まず、インデクスの付け方で工夫してみてください。

create index tbl1ix1 on tbl1(`datetime`,`quantity`)

上記のインデクスを定義すれば、データ部をアクセスせず、インデクスだけで結果を得られます。

検索条件は、一般的には日付関数を使います。

select sum(`quantity`) from tbl1 where year(`datetime)=2008;
select sum(`quantity`) from tbl1 where date_format(`datetime`,'%Y-%m')='2008-04';
id:southgate_01

アドバイスありがとうございます。

さっそく取り掛かります。

2008/04/13 20:03:10

その他の回答(2件)

id:pahoo No.1

pahoo回答回数5960ベストアンサー獲得回数6332008/04/13 10:48:11

ポイント50pt

MySQL4.1 以上では date 関数が使えるので、集計するだけなら、「datetime型のデータから日付別の集計を行う」を参考にしてください。


年別、月別集計が予測されるなら、datetimeを

  • year:integer
  • month:integer
  • day:integer

にフィールド分割しておくといいでしょう。(timeもあるなら適宜分割)

実測していないので確証はありませんが、date関数を使うより速度が向上すると思います。

id:southgate_01

アドバイスありがとうございます。

フィールドを分割してやってみようと思います。

2008/04/13 12:24:22
id:chuken_kenkou No.2

chuken_kenkou回答回数722ベストアンサー獲得回数542008/04/13 16:20:04ここでベストアンサー

ポイント150pt

日付の範囲検索や日付演算を行う場合、年月を別の列に分けると、検索条件がやや複雑になります。

今回の場合、まず、インデクスの付け方で工夫してみてください。

create index tbl1ix1 on tbl1(`datetime`,`quantity`)

上記のインデクスを定義すれば、データ部をアクセスせず、インデクスだけで結果を得られます。

検索条件は、一般的には日付関数を使います。

select sum(`quantity`) from tbl1 where year(`datetime)=2008;
select sum(`quantity`) from tbl1 where date_format(`datetime`,'%Y-%m')='2008-04';
id:southgate_01

アドバイスありがとうございます。

さっそく取り掛かります。

2008/04/13 20:03:10
id:ken33jp No.3

ken33jp回答回数928ベストアンサー獲得回数132008/04/13 21:30:15

ポイント50pt

2008-1-1<=datetime and datetime <2009-01-01

-------------

該当のSELECT文のdatetimeがインデックスを使用してるかどうかを確認したほうが良いです。

SUBRSTRINGとかいれると、インデックスとして使用できないと思うのでNG。

id:southgate_01

アドバイスありがとうございます。

インデックスを利用して最初よりも随分早くなったのですが、こちらの方法もさっそく試してみます。

2008/04/13 21:57:08

コメントはまだありません

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません