こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

(再度SQLのご指導を願います)

先日にこちらの掲示板でご親切な方からアドバイスをいただき
試した結果、以下の通り若干の不具合が発生し、再度ご質問する次第です。

恐縮ながら以下にご教授いただければ幸いです。



テーブルは以下で仮定します。

・注文テーブル

購入日 レジコード 顧客コード 顧客種別 商品NO購入金額 購入個数 年代
2011/01/01 111111 123456 通常 100 ¥100 1 20代
2011/01/02 111112 129999 通常 100 ¥300 3 50代
2011/01/02 111112 129999 通常 200 ¥200 1 50代
2011/01/02 111112 129999 通常 500 ¥500 2 50代
2011/02/01 111113 122222 新規 100 ¥100 1 90代
2011/02/05 111114 133333 新規 400 ¥400 1 10代
2011/02/11 111115 144444 通常 1000 ¥1,000 1 30代
2011/02/11 111115 144444 通常 100 ¥100 1 30代
2011/03/01 111116 155555 新規 100 ¥300 3 20代
2011/03/01 111116 155555 新規 200 ¥400 2 20代
2011/03/05 111117 166666 通常 500 ¥250 1 30代
2011/03/15 111118 177777 通常 400 ¥800 2 40代
2011/03/16 111119 188888 通常 100 ¥100 1 50代
2011/03/16 120000 123456 通常 100 ¥1,000 10 20代
2011/03/16 120000 123456 通常 400 ¥400 1 20代
2011/03/16 120000 123456 通常 200 ¥400 2 20代
2011/03/30 121000 144444 通常 100 ¥300 3 30代
2011/03/30 121000 144444 通常 1000 ¥3,000 3 30代
2011/03/30 121000 144444 通常 500 ¥750 3 30代


・割引テーブル

商品NO 商品名開始日終了日
100 ごはん 2011/03/01 2011/03/30
100 ごはん 2011/01/01 2011/01/31

抽出したい内容
(1)注文テーブルにある購入日で日付を指定の上、(betweenで2010/06/01-2011/05/31までなど)
商品NOを特定の1品目、もしくは複数選択をした上で、その商品に対する期間内の購入数・購入金額を求めたい。
ただし、"割引テーブル"に差し掛かる期間のみ、別の列で分けて表示させたい。
(つまり割引期間と通常期間を列で分けることで売上の差を見極めたいのです。)



これを以下の構文で試してみました。

SELECT t.購入日, t.商品NO,
Sum(iif(t.購入日<w.開始日 or t.購入日>w.終了日,t.購入個数,0)) AS 通常購入,
Sum(Switch(t.購入日<w.開始日 Or t.購入日>w.終了日,0,True,t.購入個数)) AS 割引購入,
Sum(Switch(t.購入日<w.開始日 Or t.購入日>w.終了日,t.購入金額,True,0)) AS 通常購入金額,
Sum(Switch(t.購入日<w.開始日 Or t.購入日>w.終了日,0,True,t.購入金額)) AS 割引購入金額
FROM 注文テーブル AS t INNER JOIN 割引テーブル AS w ON t.商品NO = w.商品NO
WHERE (((t.商品NO)=100) AND ((t.購入日) Between #1/1/2011# And #3/30/2011#))
GROUP BY t.購入日, t.商品NO;

実行結果

購入日 商品NO 通常購入 割引購入 通常購入金額 割引購入金額
2011/01/01 100 1 1 ¥100 ¥100     
2011/01/02 100 3 3 ¥300 ¥300     
2011/02/01 100 2 0 ¥200 ¥0
2011/02/11 100 2 0 ¥200 ¥0
2011/03/01 100 3 3 ¥300 ¥300
2011/03/16 100 11 11 ¥1,100 ¥1,100
2011/03/30 100 3 3 ¥300 ¥300

上記のようになりました。

上記の割引テーブルで仮定すると、1月と3月の売り上げは”割引購入"と"割引購入金額"に記載され、2月の分は"通常購入"と"通常購入数"にその時の売り上げを出したいと考えております。

現在の結果の場合、割引テーブルで設定された割引期間内の"通常購入"と"通常購入数"にも数字が入ってしまっています、、。

理想の抽出結果は以下の通りです。

購入日 商品NO 通常購入 割引購入 通常購入金額 割引購入金額
2011/01/01 100 0 1 ¥0 ¥100     
2011/01/02 100 0 3 ¥0 ¥300     
2011/02/01 100 2 0 ¥200 ¥0
2011/02/11 100 2 0 ¥200 ¥0
2011/03/01 100 0 3 ¥0 ¥300
2011/03/16 100 0 11 ¥0 ¥1,100
2011/03/30 100 0 3 ¥0 ¥300




(2)注文テーブルにある購入日で日付を指定の上、(上記(1)と同様)商品NOを特定の1品目、もしくは複数選択をした上で、年代別で顧客種別ごとの客数を知りたい場合


※上の客数は顧客種別かレジコードをカウントすれば良いと思いますが、一人のお客様が一回の会計で5品目買ったらレコードは5つになるため、重複を防ぐ必要があります。(対象の品物を何回購入したことがあるか表示したいので、、)

また、"顧客種別"は通常・新規・フリーの三種類の言葉で分類分けされて登録されてますが、通常のみ、設定した抽出対象期間前にも1回でも購入があれば"リピート"としてカウント、過去に購入なく抽出対象期間内で初めて購入があった場合は"初めて"にカウントしたいです。


これを以下構文で試してみました。


SELECT
t.商品NO
, t.年代
, sum(switch(t.顧客種別='通常' and k.顧客コード is not null, 1 , true ,0)) as リピート
, sum(switch(t.顧客種別='通常' and k.顧客コード is not null, 0 , true ,1)) as 初めて
, sum(switch(t.顧客種別='新規' , 1 , true ,0)) as 新規
, sum(switch(t.顧客種別='フリー', 1 , true ,0)) as フリー
FROM 注文テーブル t
LEFT JOIN
(SELECT 顧客コード
FROM 注文テーブル
WHERE 購入日<#2011/02/28#) k
ON t.顧客コード = k.顧客コード
WHERE t.購入日 between #2011/03/01# and #2011/03/31#
and t.商品NO = 100
GROUP BY t.商品NO, t.年代


抽出結果

商品NO 年代 リピート 初めて 新規 フリー
100 20代 1 1 1 0
100 30代 2 0 0 0
100 50代 0 1 0 0

たとえば、3月1日~3月31日までの期間で抽出をすると
商品NO100の商品を買った30代のリピートの方は1名(顧客NO144444)だけに
なると思いますが、抽出結果では2名になってしまいます。

・対象の商品"100"を設定した対象期間内(上記の構文でいうと2011/03/01-2011/03/31)
でしか購入がなかった場合は"初めて"としてカウント

・対象の商品"100"を設定した対象期間内(上記の構文でいうと2011/03/01-2011/03/31)
で購入があり、且つその設定日付以前にも同じ商品の購入があった場合は"リピート"として
カウント(設定日以前に同じリピート客が同じ商品を100回買ったとしても、100カウントではなく1カウント)リピートのお客様が"何回買ったか?"ではなく、"リピートしてる人が何人いるか?"を表したいのです。

これがが出来ないか頑張っています、、。


一番上のテーブルを仮定して理想の抽出結果は

商品NO 年代 リピート 初めて 新規 フリー
100 20代 1 0 1 0
100 30代 1 0 0 0
100 50代 0 1 0 0


お手数をおかけしますが、なにとぞご教授をお願いいたします。

投稿日時 - 2011-08-23 20:09:43

QNo.6962803

すぐに回答ほしいです

質問者が選んだベストアンサー

No1です。

リピートは

>対象期間内で購入があり、且つその設定日付以前にも同じ商品の購入があった場合

でしたね。

---
FROM 注文テーブル t
LEFT JOIN
(SELECT 顧客コード
FROM 注文テーブル
WHERE 購入日<#2011/02/28#) k
ON t.顧客コード = k.顧客コード



FROM 注文テーブル t
LEFT JOIN
(SELECT 顧客コード,商品NO
FROM 注文テーブル
WHERE 購入日<#2011/02/28#
GROUP BY 顧客コード,商品NO) k
ON t.顧客コード = k.顧客コード
and t.商品NO = k.商品NO

投稿日時 - 2011-08-24 11:21:46

このQ&Aは役に立ちましたか?

0人が「このQ&Aが役に立った」と投票しています

回答(3)

ANo.3

前回のミスのところを取り込んでいませんでした。

---
誤り:
, sum(switch(t.顧客種別='通常' and k.顧客コード is not null, 0 , true ,1)) as 初めて

正しい
, sum(switch(t.顧客種別='通常' and k.顧客コード is null, 1 , true ,0)) as 初めて

---
補足

問題解決の方法はまずGROUP BYをはずしてみましょう

そうすればどのレコードがどんなふうに抽出されているかがわかります。

投稿日時 - 2011-08-26 08:53:34

ANo.1

これでどうでしょう

---
(1)
SELECT
t.購入日
, t.商品NO
, sum(switch(w.商品NO is null,t.購入数,True,0)) AS 通常購入
, sum(switch(w.商品NO is null,0,True,t.購入数)) AS 割引購入
, sum(switch(w.商品NO is null,t.購入金額,True,0)) AS 通常購入金額
, sum(switch(w.商品NO is null,0,True,t.購入金額)) AS 割引購入金額
FROM T_注文 AS t
LEFT JOIN T_期間限定割引 AS w
ON (t.購入日 between w.開始日 and w.終了日) AND (t.商品NO=w.商品NO)
GROUP BY t.購入日, t.商品NO
ORDER BY t.購入日

---
(2)
(SELECT 顧客コード
FROM 注文テーブル
WHERE 購入日<#2011/02/28#)



(SELECT 顧客コード
FROM 注文テーブル
WHERE 購入日<#2011/02/28#
GROUP BY 顧客コード)

----
(補足)

INNER または LEFT JYOIN すると JYOINされる側に

対象レコードが2件以上あると元のレコードがそれに

あわせて増幅されます。

注文テーブル
商品 レジ
100 111

割引テーブル
商品 開始
100 2011/1/1
100 2011/3/1

SELECT t.*,w.*
FROM 注文 t
INNER JOIN 割引 w
ON t.商品 = t.商品

結果
100 111 2011/1/1
100 111 2011/3/1

投稿日時 - 2011-08-24 09:25:55

補足

今回もご指導いただきありがとうございました!
(1)については上記の内容で解決しました!

(2)についてですが、ご指導いただいた内容に沿って修正したところ
一部不具合が生じました、、。


修正した文章:

SELECT
t.商品NO
, t.年代
, sum(switch(t.顧客種別='通常' and k.顧客コード is not null, 1 , true ,0)) as リピート
, sum(switch(t.顧客種別='通常' and k.顧客コード is not null, 0 , true ,1)) as 初めて
, sum(switch(t.顧客種別='新規' , 1 , true ,0)) as 新規
, sum(switch(t.顧客種別='フリー', 1 , true ,0)) as フリー
FROM 注文テーブル t
LEFT JOIN
(SELECT 顧客コード,商品NO
FROM 注文テーブル
WHERE 購入日<#2011/02/28#
GROUP BY 顧客コード,商品NO) k
ON t.顧客コード = k.顧客コード
and t.商品NO = k.商品NO
WHERE t.購入日 between #2011/03/01# and #2011/03/31#
and t.商品NO = 100
GROUP BY t.商品NO, t.年代

この内容で抽出した結果:

商品NO 年代 リピート 初めて 新規 フリー
100 20代 1 1 1 0
100 30代 1 0 0 0
100 50代 0 1 0 0


3月に100を購入した20代の人は全部で2件
新規の"155555"と通常の"123456"であり、
新規の"155555"は新規としてカウントされてますが、
"123456"の方は1月に購入履歴があるため、リピートとしてカウントされると思います。

しかし、結果の表にはなぜか"初めて"にも1件入っており、
3月で20代が計3件の購入があったことになっています、、、。

投稿日時 - 2011-08-26 06:48:45

あなたにオススメの質問