重複データより一番古いデータのみを条件付きで抽出

質問

カルテテーブルに登録されたデータから、
日時が2007-02-28以前で、
顧客ごとに一番古い(最初の)データのみを抽出し、
ショップ番号が1である顧客のみを、
絞り込みたいのですが、
教えてもらえますでしょうか？。

※顧客の一番古いデータがショップ1以外であれば省きたい。
※また、日付の期間中の抽出顧客ごとのカルテ数も知りたい。
(2回目以降は別の店舗とスタッフでもカウントする)

MySQL 4.0.24-standardを使用しています。

カルテテーブル(chart_table)
---------------------------------------
kokyaku | shop | staff | date |
1 1 1 2006-06-27
1 1 5 2006-12-11
2 1 2 2007-01-27
2 2 3 2007-02-07
3 3 4 2007-02-04
1 1 2 2007-03-12

結果1
---------------------------------------
kokyaku | shop | staff | date |
1 1 1 2006-06-27
2 1 2 2007-01-27

結果2
---------------------------------------
kokyaku | cnt |
1 2
2 2

何卒、よろしくお願い致します。

回答No.1

yambejp · ベストアンサー

mysql4.0はサブクエリがつかえないですよね？
そうなるとテンポラリに一度落とした上で、外部結合することになります。

create temporary table temp
select kokyaku,shop,min(date) as date
from chart_table
where 1
and shop=1
group by kokyaku;
select t.kokyaku,t.shop,c.staff,t.date
from temp as t
inner join chart_table as c
on c.date=t.date
and c.kokyaku=t.kokyaku
and c.shop=1

今回の課題・・・
もとのテーブルにIDになりそうなプライマリキーが設定されていないため
絞りこみが非常にしにくいです。
正規化を心がけてください。

ちなみに、簡単にやろうとすると
select kokyaku,shop,staff,min(date) as date
from chart_table
where 1
and shop=1
group by kokyaku;
なんて感じでできそうな気がしますが、これはstaffの項目が
保証されませんので、あやまりです。

それと、結果２はなにがやりたいのかいまいちわかりません。
shopが1の場合のkokyaku毎のカウントをとりたいのであれば
select kokyaku,count(*) as cnt
from chart_table
where 1
and shop=1
group by kokyaku;
ですが、結果は
kokyaku | cnt
1 3
2 1
ですよね？
よく仕様を再検討ください

補足

早いアドバイスありがとうございました。
また、説明不足ですみませんでした。
これは指定期間中のスタッフごとによる再来店率を表示させる
グラフを算出したいと思いまして質問致しました。
ですので、最初に対応したスタッフの顧客が2回目に、
他の店舗に行く事も考えて、事前に店舗を絞り込む事が出来ません。。。
また、過去の時点に於いての、計算ですので期間絞り込み中における、
抽出となります。

ですので、カウント結果２は
kokyaku | cnt
1 2
2 2
になります。
とてもややこしくてすみませんでした。

回答No.3

chukenkenkou

文章で説明されても、非常に分かりにくいです。自分で考えたSQLは、全然ないのですか？

課題の丸投げ、具体的に分からないところを示さない作業依頼は、ここでの違反行為ですよ？

結果１で得たkokyakuに関し、抽出期間は同じで、すべてのshopについて件数を知りたいということでしょうか？

参考URL：

http://blog.goo.ne.jp/oshietegoo/c/2fd47a0436464f7e861d622ad803e8ab

回答No.2

yambejp

1回目のshopが1の顧客について来店回数をカウントする・・・
という意味でしょうか？
それならkokyaku=1の顧客は3回来店していますが、どのような計算根拠で
カウントは3ではなくて2なのですか？
（カルテ数っていうのがうまく説明されていないのかなぁ・・・）

それと結果１はやはりだめでしたか？

お礼

ご返信ありがとうございます。
ANo1を参考にさせて頂きテンポラリを3回重ねて思う結果を抽出出来ました。
無駄な問い合わせが多いですが、これ以上の方法が解りませんでした。泣!!

1回目のテンポラリ
---------------------------------------
CREATE TEMPORARY TABLE summary_table
SELECT * FROM chart_table
WHERE date <= 2007-02-28
ORDER BY date

2回目のテンポラリ
---------------------------------------
CREATE TEMPORARY TABLE summary_table1
SELECT kokyaku,staff,date,shop,
MIN(date) AS min_date,Count(date) AS cnt_date
FROM summary_table
GROUP BY kokyaku
ORDER BY date

3回目のテンポラリ
---------------------------------------
CREATE TEMPORARY TABLE summary_table1_2
SELECT staff,shop,Count(staff) AS cnt_staff,
Sum(CASE WHEN (cnt_date >= 2) THEN 1 ELSE 0 END) AS sum_repeater
FROM summary_table1
GROUP BY staff

後は、PHP上でリピート率を計算しました。
$staff_value = (($sum_repeater)/($cnt_staff))*100;