mysqlで週次のラベルを作成

Question

mysqlを使ってデータの集計を行っています。
そこで、毎週月曜日の日付をラベルにして、
（'2016-05-09'のように）
一週間分の売上データを集計したいと思っています。

テーブルには下記のように数字が入っています。

create date / product / price
2016-05-12 15:16:17 / bread / 340

また、1件もレコードがない週がある可能性も加味して、
SQLを作成したいと思っています。

いい方法があればご教授いただきたいです。
よろしくお願いいたします。

Comments

[takasima20]

まず… SQL書けますか? 質問者のスキルがある程度分からないと回答もしにくいかなあ、と。作成されたSQLの添削ならすぐにできると思いますが、いちから書けと言われても、ねえ。:-P

[callmichael]

SQLは人並みには書けるはずです。。
日毎の集計であれば、
SELECT
DATE_FORMAT(create_date, '%Y-%m-%d') AS date,
product,
SUM(price) AS price
FROM テーブル名
GROUP BY DATE_FORMAT(create_date, '%Y-%m-%d'), product
ORDER BY DATE_FORMAT(create_date, '%Y-%m-%d'), product
とやればいいと思っているのですが、
1. データが無い日のレコードが無くなってしまう
2. 週ごとにした時に、日付で表示する方法が分からない
という点で躓いております。。

[takasima20]

そういう具体的なことを質問に追記するといいと思います。コメントだと読みにくいですからね。:-)

[callmichael]

ご指摘ありがとうございます。今後気をつけるようにいたします。

Answer 1

1. 週ごとにした時に、日付で表示する方法

については、以下のようなクエリで実現できます。

SELECT 
  DATE_SUB(DATE(create_date), INTERVAL WEEKDAY(create_date) DAY) AS date, 
  product, 
  SUM(price) AS price 
FROM テーブル名 
GROUP BY date, product 
ORDER BY date, product;

https://dev.mysql.com/doc/refman/5.6/ja/date-and-time-functions.html#function_date-sub
https://dev.mysql.com/doc/refman/5.6/ja/date-and-time-functions.html#function_date
https://dev.mysql.com/doc/refman/5.6/ja/date-and-time-functions.html#function_weekday

しかし、

1. データが無い日のレコードが無くなってしまう

をSQLだけで実現する方法は、少なくとも私には思いつきません。
方法があったとしても、非常に複雑か、トリッキーなものになるのではないかと予想します。

そのため、JavaやPHPなどのプログラム側で対処するのが良いと思います。

ちなみに、
「（GROUP BYしたときに）データのないレコードが無くなってしまう」
については、productカラムについても同じことが言えます。

Comments

[callmichael]

ありがとうございます。やり方は少し模索してみます。

Answer 2

ラベル部分はこれでどうでしょう

SELECT * FROM `テーブル名` WHERE DAYOFWEEK(`create date`) = 2

あとは　日付で比較演算だけですから　ご自分で考えて下さいね^^