FLATz

谷口です。

今回はMySQLで知っていると便利な構文その２として、集計時に便利な構文を紹介します。 集計をする際は、主にGROUP BYを多く用いると思いますので、そこに焦点を当てて説明します。

• 説明の準備
• 日付関数
• WITH ROLLUP オプション
• 数値実験（時間測定）

説明の準備

次のようなカラム構成のテーブルがあるとして、以下説明します。

テーブル名： order * id: int(11)

• product: varchar(32)
• ordered_at: datetime
• price: int(11)
• quantity: int(11)

日付関数

MySQLの組み込み関数には日付操作用の関数が多数用意されてます。 日付関数によるGROUP BY 節の指定も可能で、なかなか使い勝手が良いです。

ordered_atという日付のカラムに対し、日付関数を用いて様々な期間での集計が可能となります。 特にYEAR(), MONTH(), DAY(), DAYNAME(), EXTRACT()が便利です。

• YEAR(date): dateの年部分を返す
• MONTH(date): dateの月部分を返す
• DAY(date): dateの日部分を返す
• DAYNAME(date): dateの曜日を返す
• EXTRACT(TYPE FROM date): dateのTYPEで指定した部分を返す（YEAR_MONTHなど）

年毎に売上げを集計したい場合は、以下のようなSQL文で取得できます。

SELECT YEAR(ordered_at) as year, SUM(price * quantity) as total_price FROM order GROUP BY YEAR(ordered_at);

年月毎（2007年8月、2007年9月）に売上げを集計したい場合は、以下のようなSQL文で取得できます。

SELECT EXTRACT(YEAR_MONTH FROM ordered_at) year_month, SUM(price * quantity) as total_price FROM order GROUP BY EXTRACT(YEAR_MONTH FROM ordered_at);

WITH ROLLUPオプション

上述した個別集計に加えて、全体の合計も取得したい場合、このWITH ROLLUPオプションを用いることで、実現できます。 年毎の売上げのSQL文を例にすると、

そのまま実行

 +------+-------------+ 
 | year | total_price | 
 +------+-------------+ 
 | 2000 |     8756740 | 
 | 2001 |     8588720 | 
 | 2002 |     8644120 | 
 | 2003 |     8570520 | 
 | 2004 |     8506160 | 
 | 2005 |     8593260 | 
 | 2006 |     8607920 | 
 +------+-------------+

WITH ROLLUPオプションをつけて実行

  +------+-------------+ 
  | year | total_price | 
  +------+-------------+ 
  | 2000 |     8756740 | 
  | 2001 |     8588720 | 
  | 2002 |     8644120 | 
  | 2003 |     8570520 | 
  | 2004 |     8506160 | 
  | 2005 |     8593260 | 
  | 2006 |     8607920 | 
  | NULL |    60267440 | 
  +------+-------------+

このように、yearフィールドがNULLのレコードが付いて返ってきます。これが全体の合計を表します。さらにmonthも加えて、下記のようなSQL文を実行すると、

SELECT YEAR(ordered_at) as year, MONTH(ordered_at) as month, SUM(price * quantity) as total_price FROM order GROUP BY YEAR(ordered_at), MONTH(ordered_at);

+------+-------+-------------+ 
| year | month | total_price | 
+------+-------+-------------+ 
| 2000 |     1 |      712520 | 
| 2000 |     2 |      708120 | 
| 2000 |     3 |      717800 | 
| 2000 |     4 |      774240 | 
| 2000 |     5 |      736160 | 
| 2000 |     6 |      705840 | 
・・・ 
| 2006 |     8 |      722220 | 
| 2006 |     9 |      730840 | 
| 2006 |    10 |      682280 | 
| 2006 |    11 |      728100 | 
| 2006 |    12 |      709560 | 
| 2006 |  NULL |     8607920 | 
| NULL |  NULL |    60267440 | 
+------+-------+-------------+

という風になります。2006/NULLのレコードは2006年の合計を意味します。 GROUP BYで複数フィールドを指定する際には順序に気をつけましょう。 例ではyear→monthの順で指定したので各年で各月の合計を表すレコードが返るようになっていますが、 month→yearの順で指定すると、各月で各年の合計を表すレコードが返るようになります。

数値実験

WITH ROLLUPオプションの性能評価として、下記の3件を50000レコードと100000レコードに対して100回実行した時間を示します。（1回当たりの時間はそれぞれを100で割った値となります。） 基本となるSQL文は次の通りです。

SELECT product, YEAR(ordered_at) as year, MONTH(ordered_at) as month, SUM(quantity) as total_quantity, SUM(quantity*price) as total_price FROM counting GROUP BY product, YEAR(ordered_at), MONTH(ordered_at)

なお、SQL文の発行はすべてPEAR::DBで行なっています。

• WITH ROLLUP
  • 50000件：60.5[s]
  • 100000件：124.3[s]
• WITH ROLLUPをつけずに、合計クエリを別途発行
  • 50000件：88.9[s]
  • 100000件：175.0[s]
• 合計算出処理をPHPで
  • 50000件：31.4[s]
  • 100000件：61.0[s]

合計算出はPHPなどのプログラムに任せた方が良さそうですね･･･。

おわりに

集計処理は非常に時間がかかる処理ではありますが、必要に迫られることが多い処理ですので、 処理の選定に困った際に、選択の一助になれば幸いです。 また、日付関数にはまだまだ便利な使い方がありますので、みなさんも模索してみてください。

参考文献

MySQL AB :: MySQL 4.1 リファレンスマニュアル :: 6.3.4 日付と時刻関数

MySQL AB :: MySQL 4.1 リファレンスマニュアル :: 6.3.7.2 GROUP BY の修飾子

この記事に関するお問い合わせはこちら