今日は8/2ではないのでアレですが
今日売れた商品はこんな感じ
mysql> SELECT product_name,COUNT(id) AS num FROM `table` WHERE `date`='2016/8/2' GROUP BY product_name;
product_name | num |
ZAURUS | 4 |
掃除機 | 1 |
過去1週間(1日前と8日前の間)に売れた商品
mysql> SELECT product_name,COUNT(id) AS num_old FROM `table` WHERE `date` BETWEEN '2016/8/2' - INTERVAL 8 DAY AND '2016/8/2' - INTERVAL 1 DAY GROUP BY product_name;
product_name | num_old |
iPhone | 1 |
ZAURUS | 1 |
エアコン | 2 |
掃除機 | 3 |
これをLEFT JOIN
mysql> SELECT * FROM (SELECT product_name,COUNT(id) AS num FROM `table` WHERE `date`='2016/8/2' GROUP BY product_name) a LEFT JOIN (SELECT product_name,COUNT(id) AS num_old FROM `table` WHERE `date` BETWEEN '2016/8/2' - INTERVAL 8 DAY AND '2016/8/2' - INTERVAL 1 DAY GROUP BY product_name) b USING (product_name);
product_name | num | num_old |
ZAURUS | 4 | 1 |
掃除機 | 1 | 3 |
平均の倍売れてるものだけ抽出
mysql> SELECT * FROM (SELECT product_name,COUNT(id) AS num FROM `table` WHERE `date`='2016/8/2' GROUP BY product_name) a LEFT JOIN (SELECT product_name,COUNT(id) AS num_old FROM `table` WHERE `date` BETWEEN '2016/8/2' - INTERVAL 8 DAY AND '2016/8/2' - INTERVAL 1 DAY GROUP BY product_name) b USING (product_name) WHERE (num_old IS NULL) OR (num > num_old / 7 * 2);
product_name | num | num_old |
ZAURUS | 4 | 1 |
掃除機 | 1 | 3 |
CURDATE() とかを使えばそれらしくなるのではないでしょうか?
SELECT * FROM (SELECT product_name,COUNT(id) AS num FROM `table` WHERE `date`=CURDATE() GROUP BY product_name) a LEFT JOIN (SELECT product_name,COUNT(id) AS num_old FROM `table` WHERE `date` BETWEEN CURDATE() - INTERVAL 8 DAY AND CURDATE() - INTERVAL 1 DAY GROUP BY product_name) b USING (product_name) WHERE (num_old IS NULL) OR (num > num_old / 7 * 2);