ユニークアイテムの推移を集計

Question

下記のようなテーブルがあるときに、直近1日間,7日間,30日間に購入されたproductの
ユニーク数を日毎に集計したいと思っています。

テーブルイメージ

create date / product / price
2016-05-12 15:16:17 / bread / 340
2016-05-13 11:10:31 / icecream / 280

抽出イメージ

date / yesterday / 7days / 30days
2016-05-12/ 3 / 7 / 31
2016-05-13/ 4 / 8 / 29

上記のように、5/12時点では、
前日に購入されたユニークproductが3点、直近の30日間では31点
のような結果を抽出したいのですが、方法のイメージが付きません。
方向性だけでもご教授いただけると幸いです。
よろしくお願いいたします。

※追記
本日分については、下記のSQLで出せると思うのですが、これを日ごとに出す方法がわかりません。

select 
	count(case when datediff(DATE_FORMAT(NOW(), '%Y-%m-%d'),DATE_FORMAT(create_date, '%Y-%m-%d')) = 1 then product end) as yesterday,
	count(case when datediff(DATE_FORMAT(NOW(), '%Y-%m-%d'),DATE_FORMAT(create_date, '%Y-%m-%d')) between 1 and 7 then product end) as 7days,
	count(case when datediff(DATE_FORMAT(NOW(), '%Y-%m-%d'),DATE_FORMAT(create_date, '%Y-%m-%d')) between 1 and 30 then product end) as 30days
from テーブル名

Answer 1

製品ごとに直近期間でナンボ売れたかという集計をするという認識であってますでしょうか？
その前提で書いてみたものを提案します。
どちらも似たような結果になりますが、プランAのほうは売れてない日は行がでません。

■テストで使ったテーブルスキーマ
CREATE TABLE temp (
created datetime,
pid int(11),
price int(11)
)

■プランA（CASEで集計）
SELECT
DATE_FORMAT(created, '%Y-%m-%d') AS date,
SUM(CASE WHEN DATEDIFF(NOW(),created) < 2 THEN 1 ELSE 0 END) AS yeaterday,
SUM(CASE WHEN DATEDIFF(NOW(),created) < 8 THEN 1 ELSE 0 END) AS 7days,
SUM(CASE WHEN DATEDIFF(NOW(),created) < 31 THEN 1 ELSE 0 END) AS 30days
FROM
temp
WHERE pid = 2
GROUP BY DATE_FORMAT(created, '%Y-%m-%d')

※違う製品の集計を取るときはWHERE句内のpidを入れ替えます。
※集計スパンを変えるときはCASE式内のDATEDIFFと比較する数字を編集します。

■プランB（JOINして集計）
SELECT
calendar.date,
IF(7days.cnt IS NULL, 0 ,7days.cnt) AS 7days,
IF(30days.cnt IS NULL, 0 ,30days.cnt) AS 30days
FROM
(
SELECT
DATE_FORMAT(created, '%Y-%m-%d') AS date,
count()
FROM
temp
GROUP BY DATE_FORMAT(created, '%Y-%m-%d')
) AS calendar
LEFT JOIN
(
SELECT
DATE_FORMAT(created, '%Y-%m-%d') AS date,
count() AS cnt
FROM
temp
WHERE
pid = 2 AND DATEDIFF(NOW(),created) < 8
GROUP BY DATE_FORMAT(created, '%Y-%m-%d')
) AS 7days ON calendar.date = 7days.date
LEFT JOIN
(
SELECT
DATE_FORMAT(created, '%Y-%m-%d') AS date,
count(*) AS cnt
FROM
temp
WHERE
pid = 2 AND DATEDIFF(NOW(),created) < 31
GROUP BY DATE_FORMAT(created, '%Y-%m-%d')
) AS 30days ON calendar.date = 30days.date

Comments

[hermitagejp]

失礼、スパンごとに売れたアイテムのユニーク数が知りたいのですね。
SELECT
DATE_FORMAT(created, '%Y-%m-%d') AS date,
COUNT(CASE WHEN DATEDIFF(NOW(),created) < 2 THEN pid ELSE 0 END) AS yeaterday,
COUNT(CASE WHEN DATEDIFF(NOW(),created) < 8 THEN pid ELSE 0 END) AS 7days,
COUNT(CASE WHEN DATEDIFF(NOW(),created) < 31 THEN pid ELSE 0 END) AS 30days
FROM
temp
GROUP BY DATE_FORMAT(created, '%Y-%m-%d')
こちらでどうでしょうか？