ユニークアイテムの推移を集計
- 評価
- クリップ 1
- VIEW 852
下記のようなテーブルがあるときに、直近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 テーブル名-
気になる質問をクリップする
クリップした質問は、後からいつでもマイページで確認できます。
またクリップした質問に回答があった際、通知やメールを受け取ることができます。
クリップを取り消します
-
良い質問の評価を上げる
以下のような質問は評価を上げましょう
- 質問内容が明確
- 自分も答えを知りたい
- 質問者以外のユーザにも役立つ
評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。
質問の評価を上げたことを取り消します
-
評価を下げられる数の上限に達しました
評価を下げることができません
- 1日5回まで評価を下げられます
- 1日に1ユーザに対して2回まで評価を下げられます
質問の評価を下げる
teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。
- プログラミングに関係のない質問
- やってほしいことだけを記載した丸投げの質問
- 問題・課題が含まれていない質問
- 意図的に内容が抹消された質問
- 広告と受け取られるような投稿
評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。
質問の評価を下げたことを取り消します
この機能は開放されていません
評価を下げる条件を満たしてません
質問の評価を下げる機能の利用条件
この機能を利用するためには、以下の事項を行う必要があります。
- 質問回答など一定の行動
-
メールアドレスの認証
メールアドレスの認証
-
質問評価に関するヘルプページの閲覧
質問評価に関するヘルプページの閲覧
0
製品ごとに直近期間でナンボ売れたかという集計をするという認識であってますでしょうか？
その前提で書いてみたものを提案します。
どちらも似たような結果になりますが、プラン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
投稿
-
回答の評価を上げる
以下のような回答は評価を上げましょう
- 正しい回答
- わかりやすい回答
- ためになる回答
評価が高い回答ほどページの上位に表示されます。
-
回答の評価を下げる
下記のような回答は推奨されていません。
- 間違っている回答
- 質問の回答になっていない投稿
- スパムや攻撃的な表現を用いた投稿
評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。
15分調べてもわからないことは、teratailで質問しよう！
- ただいまの回答率 89.97%
- 質問をまとめることで、思考を整理して素早く解決
- テンプレート機能で、簡単に質問をまとめられる
2016/05/20 16:58
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')
こちらでどうでしょうか？