月別と週別の売上げデータを、ユーザー毎で集計するSQL文をGROUP BYを2つもちいて記述することができるのでしょうか？

Question

月別と週別の売上げデータを、ユーザー毎で集計するSQL文を記述しております。

Salesが売上データ
Userが本システムを使って下さっているユーザー

月別の売上げデータでしたら、単純にGROUP BYだけでことがすんだのですが、
それに加えて、User毎でも集計しなくてはいけません。

GROUP BY date_format(created_at, '%Y-%m')

GROUP BYをふたつつかってSQL文を書いていたりしたのですが、上手くいかず、質問させて頂きました。

また、今回の実装はpythonのスクリプトでかいている(MySQLdbを使用)ため、

Userの一覧を取得するSQLを実行
↓
userの数だけループ
↓
ループの中で、月別の売上げデータで集計し、INSERTする

ということも考えましたが、Userの数はどんどんと増えていくため、
このやり方はあまり良くない気がしております。

DBの負荷をなるべく減らしてあげるようなSQL文を書けるようになりたいです。

宜しくお願いします。

追記

まだ、テーブル設計などをどうしようかという段階ですので、そもそも設計自体
きれいな形になっておりません。

| sales | CREATE TABLE `sales` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `sales_price` int(11) DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |


| users | CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `email` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
) ENGINE=InnoDB AUTO_INCREMENT=35 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |

MonthReport
'month_date'
'sum_price'
'user_id'

Comments

[退会済みユーザー]

SHOW CREATE TABLE LIKE 'テーブル名' で得られるテーブル定義を提示してください。

[qaz3330]

今、設計中の段階のため、仕様みたいなものはありませんが、今ある分を追記しました。

Answer 1

-- 月ごと
SELECT sum(sales_price) AS price
FROM sales 
GROUP BY user_id, YEAR(created_at), MONTH(created_at);

-- 週ごと
SELECT sum(sales_price) AS price
FROM sales 
GROUP BY user_id, YEAR(created_at), DATE_FORMAT(created_at, '%U');

Comments

[qaz3330]

こういう風に記述できるのですね。ありがとうございます。早速試してみます。
ちなみに、今後仮に時間別に集計したいとなった場合

SELECT sum(sales_price) AS price
FROM sales
GROUP BY user_id, YEAR(created_at), DATE_FORMAT(created_at, '%H');

%Hか%kに変えてあげればいいのでしょうか？

DATE_FORMAT関数で%Uといったような使い方ができるのとこも大変勉強になりました。
http://pentan.info/sql/mysql/func/date_format.html

[退会済みユーザー]

> %Hか%kに変えてあげればいいのでしょうか？
意図する値が取得できるのであれば、良いと思います。

どこの誰が書いたかわからない記事より、公式リファレンスです。
https://dev.mysql.com/doc/refman/5.6/ja/date-and-time-functions.html

[qaz3330]

ありがとうございます。基本的に公式リファレンスを参照するようにします。（間違ったインプットするのを避けるためにも）

[退会済みユーザー]

> 間違ったインプットするのを避けるためにも
そのとおりです。
ブログとかだと、間違いがあっても修正されずに放置されていたり、前提とするバージョンによっては間違いであったりします。

[qaz3330]

どうしても検索結果の上位を参照してしまうことがおおいですが、公式ページを読むようクセづけたいと思います。

また、自分の環境でも上手く集計することができました。

ちなみに、この集計結果をもとに集計テーブルにINSERTしたいのですが、
一つのSQL文（たとえば次のような形）でできるのでしょうか？

INSERT INTO user_table (id, name, memo, status) VALUES
(1, 'test1', 'メモ', 1),
(2, 'test2', 'メモ', 1),
(3, 'test3', 'メモ', 1),
(4, 'test4', 'メモ', 1);

それともpythonのプログラムで実装しているため、ループを用いてやったほうがいいのでしょうか？

集計SQLを実行
↓
レコード数だけループ
↓
ループの中で、INSERTする

[退会済みユーザー]

Python については触ったことがないので、回答できません。
INSERT INTO table SELECT ~ で一発インサート可能だと思いますけど。

Answer 2

ちなみに、最初から週別、月別で集計することがわかっているのであれば
日付データを加工してグルーピングするのは無駄が多いので
個別のデータに当該週の日曜日（or月曜日）、当該月の1日を埋め込んでおくのが
高速化の第一歩です。

また、上記を設定していれば必要ないですが
GROUP BY句に式を入れるのはあまり得策ではありません。
通常SELECT句で別名をつけ、その別名でGROUP BYするようにしてください

Comments

[qaz3330]

ご回答ありがとうございます。
個別のデータに当該週の日曜日（or月曜日）、当該月の1日を埋め込むというテクニックは初耳で非常に興味があります。

こちらは、週次の場合は、埋め込んだ日付を起点にて、7日でグルーピングして・・・というイメージでいるため、結局グルーピングをするのかなと思いました。

私の理解が足りていないだけかとは思いますが、
もし差し支えなければ、もう少し教えていただけないでしょうか？
（どういうSQLになるのかイメージしきれておりません・・・。）

宜しくお願いします。

[qaz3330]

GROUP BYを使わずに実行できるのでしたら非常に魅力的です。