再来店検索

質問

お店に来たお客様の再来店率を調べようと思います。

データベースは
| m_id | date |
で、m_idにメンバーIDが、dateに来店年月日が入ります。

ここで一月の総客数をまず出します。
select * from tbl where date like '2004-06-%' group by m_id;
これで一月の総件数が出ます。

ここからが問題なのですが、
その後にお客様が再度来店した場合に再来店の人数をとりたいのです。
検索結果としては、
2004年6月全来店人数：1000人
当月再来店人数：100人
1ヵ月後再来店人数：300人
2ヵ月後再来店人数：150人
3ヵ月後再来店人数：100人
4ヵ月後再来店人数：50人
再来店人数合計：700人

このような表示がしたいのです。

条件としては、
１）毎日来る人もいる
２）再来店人数の合計を取りたいので、一回出てきた人はその後カウントされない
（来月と再来月に来ている場合でも来月のみカウントして、再来月はカウントしない）

現在考えていることはテンポラリテーブルを作成することも考えていますが、
CREATE TEMPORARY TABLE tmp_table SELECT * FROM tbl where date like '2004-06-%';
などとすると2004-06のデータしか残らなくなっていまい、2004-07などのデータで検索できなくなってしまいます。

PHPでやれないことはないですが、場合非常に重くなりそうなので、SQLだけでできればと思います。

ちなみにMysqlのバージョンは4.0.21なので副問い合わせは使用できません。

以上のことができないでしょうか。
よろしくお願いします。

回答No.1

nekoyama555 · ベストアンサー

私は元々Oracleな人だったんで、
最初サブクエリーがないのでかなり困ってたんですが、
なれれば、どうにでもなるものですね。

＃最近はMySQL大好きです。

下記は私ならこうする。ってパターンです。
もう少し格好いい方法があるかもしれませんが、
とりあえず、これでも算出できると思います。

-- テストデータ
CREATE TABLE raiten (m_id char(8), dt date);

insert into raiten values
("a01", '2004-07-01'),
("a01", '2004-08-02'),
("a01", '2004-09-01'),
("a01", '2004-10-01'),
("a02", '2004-07-02'),
("a02", '2004-10-10'),
("a03", '2004-07-10'),
("a03", '2004-11-01'),
("a03", '2004-12-01'),
("a04", '2004-07-01'),
("a04", '2005-07-02'),
("a05", '2004-07-01'),
("a05", '2004-08-02'),
("a06", '2004-09-02'),
("a07", '2004-07-15'),
("a07", '2004-09-02');

まず、集計対象となる月の顧客をリストアップします。

CREATE TEMPORARY TABLE summary_base
SELECT *
FROM
raiten
WHERE
dt BETWEEN '2004-7-01' and '2004-07-31';

その後、ユーザーごとに集計対象月以降の最小の日付を求め、
何ヶ月経過したか計算します。
2004と7を引き算していますが、これは集計対象の月を入れます。
適宜集計ごとに編集が必要です。
@syukeibiみたいに変数を使うとベター
（もうちょっとスマートな方法があるかも。）

CREATE TEMPORARY TABLE user_syukei
SELECT
r.m_id,
(YEAR(MIN(r.dt)) - 2004) * 12 + MONTH(MIN(r.dt)) - 7 as sairaiten_tuki
FROM
raiten r
INNER JOIN summary_base s ON ( r.m_id = s.m_id )
WHERE
r.dt > '2004-07-31'
GROUP BY
r.m_id

準備完了です。
あとは、それぞれ集計します。
４ヶ月以降が必要なければ、ここで消し込みを入れてください。

SELECT
CONCAT(sairaiten_tuki, 'ヶ月後来店人数') AS '月',
CONCAT(count(m_id), '人') AS '来店人数'
FROM
user_syukei
GROUP BY
sairaiten_tuki

これは簡単ですね。サマリーです。

SELECT
'再来店人数合計' AS '月',
CONCAT(count(*), '人') AS '来店人数'
FROM
user_syukei

お礼

ご回答ありがとうございます。
このようなやり方もあるんですね。
なんとなくできそうですが、条件が少し満たしていないので補足します。

テストデータは以下のようなものを使用します。

CREATE TABLE raiten (m_id char(8), dt date);

insert into raiten values
("a01", '2004-07-01'),
("a01", '2004-08-02'),
("a01", '2004-09-01'),
("a01", '2004-10-01'),
("a02", '2004-07-02'),
("a02", '2004-10-10'),
("a03", '2004-07-10'),
("a03", '2004-11-01'),
("a03", '2004-12-01'),
("a04", '2004-07-01'),
("a04", '2005-07-02'),
("a05", '2004-07-01'),
("a05", '2004-08-02'),
("a06", '2004-09-02'),
("a07", '2004-06-10'),
("a07", '2004-07-15'),
("a07", '2004-09-02'),
("a08", '2004-07-15'),
("a09", '2004-07-05'),
("a09", '2004-07-15'),
("a09", '2004-07-30'),
("a09", '2004-08-15'),
("a09", '2004-09-15'),
("a09", '2004-10-15'),
("a10", '2004-07-15'),
("a10", '2004-07-06'),
("a10", '2004-09-15');

・同じ月に複数回来店することもある。
・該当月に来店していて、以後来店されない場合がある。

やり方を教えていただいたので、あとは自分で工夫しながらやってみます。

ありがとうございました。
自分でできたら報告します。

補足

できましたー！

## 顧客をリストアップ。

# ここを入れないとa10が7/15を取ってしまう・・・ ORDER BY と GROUP BY を一緒に使うと正常に動作しないのかい？
CREATE TEMPORARY TABLE raiten2 SELECT * FROM raiten WHERE dt LIKE '2004-07-%' ORDER BY dt;

# 該当月初回来店日を入れる
CREATE TEMPORARY TABLE summary_base SELECT * FROM raiten2 WHERE dt LIKE '2004-07-%' GROUP BY m_id;

## 経過月をおいれますす

CREATE TEMPORARY TABLE user_syukei
SELECT r.m_id,(YEAR(MIN(r.dt)) - 2004) * 12 + MONTH(MIN(r.dt)) - 7 as sairaiten_tuki FROM raiten r
INNER JOIN summary_base s ON ( r.m_id = s.m_id ) WHERE r.dt != s.dt and r.dt > s.dt GROUP BY r.m_id ORDER BY r.dt

以下は同じ。

初回来店日当日に複数回来ることもありますが、
当日に来店した場合は翌日から検索することにしました。

思ったとおりの動作をさせることができました。
ありがとうございました。